微软培训oracle学习文档

《微软培训oracle学习文档》由会员分享，可在线阅读，更多相关《微软培训oracle学习文档（32页珍藏版）》请在装配图网上搜索。

1、第七章用iSQL*Plus生成易读的输出中国科学院西安网络中心 ？ 2005OracleSQL入门7-9用iSQL*Plus生易读的输出ORACLE中酣鼾辱归通生对得中超端科加5Copyright ? Oracle Corporation, 2001. All rights reserved.进度表：时间主题35minutes讲演35minutes练习70minutes总共完成本课后，您应当能够执行下列操作:?生成有替换变量的查询?定制iSQL*Plus环境?生成更易读的输出?创建和执行脚本文件中酣鼾号旭心唉叫得中抵 端锌身纺ORAC1_e7-2Copyright ? Oracle Corpo

2、ration, 2001. All rights reserved.课程目标在本课中，你将学习怎样包含iSQL*Plus命令来产生更易阅读的 SQL输出。你能够创建包含 WHERE子句的命令文件来限制所显示的行。为了改变条件，你可以在每次命令文件运行时使用替换变量。替换变量可以代替WHERE子句中的值,文本串，甚至一个列或一个表名。用户ORACLE中酣鼾辱院通生对得中超 崎笄身第7-3Copyright ? Oracle Corporation, 2001. All rights reserved.替换变量在此以前的例子都是硬编码的，在已完成的应用程序中，用户将触发报表，并且报 表将无进一步提

3、示地运行。数据范围将被在iSQL*Plus脚本文件中的固定的 WHERE子句预先确定。用iSQL*Plus,你可以创建有提示的报表，使用替换变量提示用户提供他们自己的 值来限制返回数据的范围。你可以在命令文件或在单个的SQL语句中嵌入 替换变量一个变量可以被认为是一个容器，在其中值被临时存储，当语句运行时，值被替换。教师注释演示：7_varno.sql , 7_varyes.sql目的：举例说明用一个带替换变量的大小写敏感的查询返回所有的行。在iSQL*Plus 9.0.1.0.1中，当使用& 替换和通配符（%）于字符值时，有一处 BUG。该BUG已经被报告。7_varyes.sql 在iSQ

4、L*Plus中将产生一个错误，但对于 学生使用其他产品（例如：Forms、Reports）的后续课程，概念是重要的。你可以在SQL*Plus 中示范 7_varyes.sql。替换变MiSQL*Plus替换变量用于：? 临时存储变量- 单&符号- 双&符号- DEFINE 命令?在SQL语句之间传递变量?动态改变页眉和页脚中酣鼾号旭心唉叫得中抵 端锌身纺ORAC1_e7-4Copyright ? Oracle Corporation, 2001. All rights reserved.替换变量在iSQL*Plus中，你可以使用单个的 &替换变量临时存储值。你可以用DEFINE 命令在iSQL

5、*Plus中预先确定变量。DEFINE创建并指定一个 只到变量。限制数据范围的例子报告只获得当前的范围或指定的日期范围报告涉及的数据只与用户请求的报告有关只显示给定部门中的人员其他的交互式作用交互作用不限制用户直接与 WHERE子句的交互。同样的规则可以用于完成其他 目的。例如：动态改变页眉和页脚从文件而不是从人那里获得输入值从一个SQL语句到另一个SQL语句传递值iSQL*Plus不支持用户输入的确认检查。教师注释替换变量可以用在SQL和iSQL*Plus命令的任何地方，除了在命令提示符后作为 第一个单词。使用&替换变M用一个带有前缀&的变量提示用户输入一个值SELECTemployee_i

6、d, last_name, salary,department_idFROMemployeesWHEREemployee_id = &employee_num;ORACLEZSQL*P1US上上Define Substttution VariablesSubmit fa E-ul2n | Cmgc.l j中酣鼾号旭心唉叫得中抵 端锌身纺ORAC1_e7-5Copyright ? Oracle Corporation, 2001. All rights reserved.单&替换变量在运行一个报告时，用户通常需要动态地限定返回的数据。iSQL*Plus的用户变量提供了这种灵活性，在SQL语句中使

7、用一个&符号来表示变量，变量不需要给每个变 量定义值。符号说明&user_variable代个SQL语句中的变量，如果变量不存在，iSQL*Plus提示用户输入一个值。在幻灯片的例子中，为每个雇员号创建了一个 iSQL*Plus单&替换变量。在执行 语句时，iSQL*Plus提示用户输入一个雇员号，然后显示该雇员的雇员号、名字、薪水 和部门号。使用单 &时，如果变量不存在，每次执行命令时都会给用户提示。使用&替换变MORACLE/SQLPlusJ B 口uLDefine Subtrtutiion Variables1_ 101Submit 阿 E*E；uhori | Cwnn剧 |雨 3 WH

8、ERE印汹*= &f喇相卯mnew 3: WHERE cmplQyee_id =10】EMPLOYEE IDLAST HIAMESALARYDEPARTMENT ID101 iKDDhhar1?0M |M。总ACL应中酣鼾辱院通生对得中N崎笄身第7-6Copyright ? Oracle Corporation, 2001. All rights reserved.单&替换变量当iSQL*Plus发现SQL语句中包含一个 & 时，提示用户为SQL语句中的命名 替换变量输入一个值。一旦用户输入一个值，并且单击执行按钮，结果会显示在当前 iSQL*Plus会话的输出区域中。替换变M的字符和数字值日

9、期和字符值放在单引号之间SELECT last_name, department_id, salary*12 FROM employeesWHERE job id = &job title;Define Substituton Variables暇rr_PROGSubm select table_name from user_tables;SQL COL table_name ON FORMAT A6SQL select table_name from user_tables;SQL COL table_name ON CLESQL select table_name from user_ta

10、bles;使用COLUMN令?创建列标题COUMiNlastynmieHEEADNNG,EmpoyeeNName, COLUMN硼函JUSTIFY卿tFORMAtOO) COLUMNNmanagerrFORMATSSSSSNNULL,Mmanagerr? 显示当前对LAST NAME列的设置? 清除对LAST NAME列的设置COUMNNlast nameCLEARORACLE中酣鼾辱院通生对得中w崎笄身第7-19Copyright ? Oracle Corporation, 2001. All rights reserved.显示或清除设置使用下面的命令显示或清除当前 COLUMN 命令的设

11、置:命令说明COLUMN column显示指定列的当前设置COLUMN显示所有列的当前设置COLUMN column CLEAR清除指定列的设置CLEAR COLUMN清除所有列的设置第七章用iSQL*Plus生成易读的输出COLUMN式化模板儿系说明举例结果9单精度无前导零数字99999912340强制前导零099999001234$浮动美圆符号$9999$1234L本国货币L9999L1234.小数点的位置9999.991234.00,千位分隔符9,9991,234中酣鼾辱院通生对端寸w崎揖身络7-20Copyright ? Oracle Corporation, 2001. All ri

12、ghts reserved.COLUMN 格式化模板幻灯片显示了 COLUMN 格式化模板的例子。Oracle服务器显示一个#号串代替一个整数，该整数的数字超过在格式化模板中 提供的数字位数。Oracle服务器还显示#代替一个值，该值的格式化模板是文字数字, 但它的实际值是数字的。中国科学院西安网络中心 ？ 2005OracleSQL入门7-21使用BREAK命令用BREAK命令禁止重复值BREAKOOjb d中酣鼾号旭心唉叫得中抵 端锌身纺ORAC1_e7-21Copyright ? Oracle Corporation, 2001. All rights reserved.BREAK命令用

13、BREAK命令来把行分成几个部分，并且禁止完全相同的值。为了确保BREAK 命令有效，使用子句定制那些你正在阻断的列。语法BREAK on column|alias|row在语法中：column|alias|row禁止重复显示一个给定列的值使用CLEAR命令清除所有BREAK设置：CLEAR BREAK控制记录显示分组顺序break on deptno（不显示重复值）select deptno,enamefrom emporder by deptno;（ORDER BY子句用于控制BREAK ）显示为10 clark niller20 smith scott30 alien blake每次只有

14、一个BREAK命令起作用，但一次可以在多个列上使用BREAK命令break on列名1 on列名2记录分组break on deptno skip 2select deptno,enamefrom emporder by deptno;每个deptno之间空两行clear break （取消 BREAK 命令）break on page （每次从一新页开始）break on report （每次从一新报表开始）break on page on report （联合使用）分组计算break on deptno skip 2compute sum of sal on deptno计算每个部门的工资

15、总和skip子句使部门之间的信息分隔开第七章用iSQL*Plus生成易读的输出使用TTITLE和BTITLE命令?显示页眉和页脚TfflTLStexIOFFON?设置报告页眉TffiTLESa蒯Report?设置报告页脚BffiTLECo岫en就中酣鼾号旭心唉叫得中抵 端锌身纺ORAC1_e7-22Copyright ? Oracle Corporation, 2001. All rights reserved.TITLE 和 BTITLE 命令用TTITLE 命令格式化页眉，用 BTITLE 命令格式化页脚。页脚显示在页面的底 部。用于BTITLE 和TTITLE 的语法是同样的。下面仅说明

16、TTITLE 语法。你可以使 用竖线（|）来分割跨越几行的标题文本。语法TTITLE|BTITLE text |OFF|ON在语法中：text表示标题文本（如果文本多于一个单词，输入单引号）。OFF|ON切换标题显示为off或on。在off状态标题不可见。在幻灯片的TTITLE 例子中，设置了报告的头的第一行居中显示Salary,在它下面居中显示Report。 BTITLE的例子设置报告的页脚显示Confidential。TTITLE 在报告上自动放置日期和页号。使用TTITLE和BTITLE命令?显示页眉和页脚TfflTLStexIOFFON?设置报告页眉TffiTLESa蒯Report?设

17、置报告页脚BffiTLECo岫en就中酣鼾号旭心唉叫得中抵 端锌身纺ORAC1_e7-23Copyright ? Oracle Corporation, 2001. All rights reserved.TITLE 和 BTITLE 命令（续）注：幻灯片给出了一个删节的 TTITLE 和BTITLE 语法。对于TTITLE 和BTITLE 的不同选项含盖在另一门 SQL课程中。教师注释SQL*Plus 3.3 介绍了命令 REPHEADER 和 REPFOOTER。 REPHEADER 在每个 报告的顶部放置并格式化一个指定的报告页眉，或者列出当前REPHEADER的定义。REPFOOTER

18、在每个报告的底部放置并格式化一个指定的报告页脚，或列出当前 REPFOOTER 定义。创建脚本文件来运行一个报告1 .创建并且测试 SQL SELECT语句2 .保存SELECT语句到脚本文件中3 .装载脚本文件到一个编辑器中4 .在SELECT语句的前加格式化命令5 .检验跟随SELECT语句的终止字符中酣鼾号旭心唉叫得中抵 端锌身纺ORAC1_e7-24Copyright ? Oracle Corporation, 2001. All rights reserved.创建脚本文件来运行一个报告你既可以在SQL提示符输入每个iSQL*Plus命令，也可以在命令（或脚本）文件 中放置所有的命令

19、，包括 SELECT语句。一个典型的脚本至少由一个 SELECT 语句 和几个iSQL*Plus命令组成。怎样创建一个脚本文件1 .在SQL提示符下创建SQL SELECT语句。在你保存语句到一个文件并且应 用格式化命令之前确保报告所必须的数据是正确的。2 .保存SELECT语句到一个脚本文件中。3 .编辑脚本文件输入 iSQL*Plus命令。4 .在SELECT 语句之前添加必要的格式化命令。在SELECT 语句中不要放置iSQL*Plus 命令。5 .在SELECT语句后面跟一个运行符来验证SELECT语句，运行符可以是一个分号（;）或一个斜线（/）。创建脚本文件来运行一个报告6 .在SE

20、LECT语句之后清除格式命令7 .保存脚本文件8 .装载脚本文件到iSQL*Plus的文本窗口中，单击执 行按钮中酣鼾号旭心唉叫得中抵 端锌身纺ORAC1_e7-25Copyright ? Oracle Corporation, 2001. All rights reserved.怎样创建一个脚本文件(续)6 .在运行字符之后添加格式清除iSQL*Plus命令。作为选择，你可以在一个reset文件中存储所有格式清除命令。7 .将改变保存脚本文件中。8 .装载脚本到iSQL*Plus的文本窗口中，单击执行按钮。原则在脚本文件的iSQL*Plus命令之间可以包含空行。如果iSQL*Plus或SQL

21、*Plus命令太长，可以在当前行的结尾加一个连字号(-),在下一行继续输入。iSQL*Plus命令可以缩写。在文件的最后包含reset命令来保存原来的iSQL*Plus环境。注：REM 在iSQL*Plus中表示注释。创建一个脚本文件来创建报表，显示所有薪水低于$15,000雇员的job_ID、last_name和salary。添加一个居中、两行的表头“Employee Report” 和一个居中的页脚 “Confidential”。重新命名job_id 列标题为Job Category”,分为两行。重新命名employee_name列标题为 Employee”。重新命名salary列标题为

22、Salary” 并 且将它格式化为 $2,500.00。SET FEEDBACK OFFTTITLE Employee|Report BTITLE Confidential BREAK ON job_id COLUMN job_id HEADING Job|Category COLUMN last_name HEADING Employee COLUMN salary HEADING Salary FORMAT $99,999.99 REM * Insert SELECT statement SELECT job_id, last_name, salary FROM employees WHE

23、RE salary 15000 ORDER BY job_id, last_name /REM clear all formatting commands . SET FEEDBACK ON COLUMN job_id CLEAR COLUMN last_name CLEAR COLUMN salary CLEAR CLEAR BREAK报表样子Fri Sep 29Employee Reportpage 1JobCale-goryEmpNayEeSalaryA_ACCCMJNT|Gicl2S3：3CO.0OAC_MGRHggins(12)X0.00ADASSTVRhalen科磔,00 I：IT

24、=PROGEmsaJ6,000.00HumidS3POO.OOLorentz:S4 200.00Mk_h4ANHartsleint13j0D3D0M-.RFFF叫fSjXOQOZlgtkey$1 口即 00SA_REPAhel(11 PLX1.00GrantFpoo.00 -gylurS8.600.00CmfidsnciiORACLE中酣鼾辱院通生对得中超 崎笄身第7-27Copyright ? Oracle Corporation, 2001. All rights reserved.例(续)SET FEEDBACK OFFTTITLE Employee|ReportBTITLE Confi

25、dentialBREAK ON job_idCOLUMN job_id HEADING Job|CategoryCOLUMN last_name HEADING EmployeeCOLUMN salary HEADING Salary FORMAT $99,999.99REM * 插入SELECT语句SELECT job_id, last_name, salaryFROM employeesWHERE salary 15000 ORDER BY job_id, last_name/REM清除所有格式命令 SET FEEDBACK ONCOLUMN job_id CLEARCOLUMN last

26、_name CLEARCOLUMN salary CLEARCLEAR BREAK小结在本课中，您应该已经学会如何：? 使用iSQL*Plus替换变量临时存储值? 使用SET命令控制当前的iSQL*Plus环境? 使用COLUMN命令控制列的显示?使用BREAK命令禁止重复的值并且把行分成几个部分? 使用TTITLE和BTITLE命令显示页眉和页脚中酣鼾号旭心唉叫得中抵 端锌身纺ORAC1_e7-28Copyright ? Oracle Corporation, 2001. All rights reserved.小结在本课中，你应该已经学会关于替换变量和怎样使用他们制作报告。他们给你在 WH

27、ERE子句、列名和表达式中替换值的灵活性。你可以用下面的命令写脚本文件来定 制报告：单&替换变量双&替换变量DEFINE命令UNDEFINE 命令在命令行中的替换变量你可以用下面的命令创建一个更加易读的报告：COLUMNTTITLEBTITLEBREAK练习7概览本章练习包括下面的主题：? 创建一个查询以显示替换变量的值? 启动一个含有变量的命令文件中酣鼾辱院通生对端寸w崎揖身络7-29Copyright ? Oracle Corporation, 2001. All rights reserved.练习7概览本章练习给你机会来创建文件，这些文件可以用替换变量交互运行以创建运行时选 择标准。中

28、国科学院西安网络中心 ？ 2005OracleSQL 入门 7-31练习7确定下面两个语句是 true还是false :1 .下面的语句是有效的： DEFINE & p_val = 100True/FalseFalse2 . DEFINE命令是一个SQL命令。 True/FalseFalseThe DEFINE command is an iSQL*Plus command.3 .写一个脚本，显示受雇日期在给定的范围之间的雇员的名字、工作和受雇日期。连 接名字和工作在一起，用一个空格和一个逗号分隔，并且标注列为Employees 。在一个单独的SQL脚本文件中，使用 DEFINE 命令提供两个

29、范围，使用格式 MM/DD/YYYY。保存脚本文件为 lab7_3a.sql 和 lab7_3b.sql 。DEFINE low_date = 01/01/1998DEFINE high_date = 01/01/1999EMPLOYEESHIRE DATEMetQs,ST_CLERK15-MAR-98Vargas. ST_CLERKO9-JUL-98Taylor, SA_REP24 MAR-98SET ECHO OFFSET VERIFY OFFDEFINE low_date = 01/01/1998DEFINE high_date = 01/01/1999SELECT last_name

30、|, | job_id EMPLOYEES, hire_dateFROM employeesWHERE hire_date BETWEEN TO _DATE(&low_date, MM/DD/YYYY)AND TO_DATE(&high_date, MM/DD/YYYY) /UNDEFINE low_dateUNDEFINE high_dateSET VERIFY ONSET ECHO ON练习7 （续）4. 写一个脚本显示给定地区的所有雇员的last_name、job_Id和department_name。查找条件应该允许对部门位置大小写不敏感的查询。保存脚本文件为lab7_4.sql 。|

31、 EMPLOYEE NAMEJOBJDDEPARTMENT NAMEWhalenAD_ASSTjAdminist rationKingAD_PF?ESExecutiveKochharDe HaariAD_VPAD_VPExecutiveExecutiveHigginsACMGRAc c ci u rilingGietzAC_AC COUNTAccounting6 rows selected.SET ECHO OFFSET VERIFY OFFCOLUMN last_name HEADING EMPLOYEE NAMECOLUMN department_name HEADING DEPARTME

32、NT NAMESELECT e.last_name, e.job_id, d.department_nameFROM employees e, departments d, locations lWHERE e.department_id = d.department_idAND l.location_id = d.location_idAND l.city = INITCAP(&p_location) /COLUMN last_name CLEARCOLUMN department_name CLEARSET VERIFY ONSET ECHO ON5.修改 lab7_4.sql期、薪水和年

33、薪的报告。SALARY 和 ANNUAL在脚本中执行这些命令练习7 （续）中的代码创建给定位置的每个雇员的部门名、雇员名、受雇日歹U标签为 DEPARTMENT NAME EMPLOYEENAME、START DATE、SALARY ,在多行放置标签。重新保存脚本为lab7_5.sql ,并且DEPARTMENT NAMEEMPLOYEE NAMESTART DATEISALARYANNUALSALARYAccountingHiggins07-JUN-94512,000.00：(144,000.00；Gietz07-JUN-94SS .300.00$99,600 00Administratio

34、nWhaler|l7-SEP-87S4,400.00552,800.00ExecutiveKing17-JUN-87$24.000.005288,000.00Kochhar21-SEP-89$17.000.加$204,000.00De Haan13-JAN-93$17 皿。口$204,000.000SET ECHO OFFSET FEEDBACK OFFSET VERIFY OFFBREAK ON department_nameCOLUMN department_name HEADING DEPARTMENT|NAMECOLUMN last_name HEADING EMPLOYEE|NAME

35、COLUMN hire_date HEADING START|DATECOLUMN salary HEADING SALARY FORMAT $99,990.00COLUMN asal HEADING ANNUAL|SALARY FORMAT $99,990.00SELECT d.department_name, e.last_name, e.hire_date, e.salary, e.salary*12 asalFROM departments d, employees e, locations lWHERE e.department_id = d.department_idAND d.location_id = l.location_idAND l.city = &p_locationORDER BY d.department_name/COLUMN department_name CLEARCOLUMN last_name CLEARCOLUMN hire_date CLEARCOLUMN salary CLEARCOLUMN asal CLEARCLEAR BREAKSET VERIFY ONSET FEEDBACK ONSET ECHO ON