Oracle SQL&PLSQL课件：第7章 替代变量和SQL-Plus环境.ppt

单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,Oracle SQL&PL/SQL,第7,章 替代变量和,SQL*Plus,环境,编写需要输入变量的查询,定制,SQL*Plus,环境,提高输出的可读性,创建并执行一个脚本文件,保存用户定制,本章要点,交互式报表,我想立即运行一个视图,.,工资,=?,部门,=?,.,姓名,=?.,User,可以采用在命令文件或单一的,SQL,语句中嵌入替代变量的方式实现。,替代变量,使用,SQL*Plus,替代变量临时存储数据,&,&,DEFINE,命令：创建变量并声明一个值,ACCEPT,命令：读取用户输入的一行,并保存成变量。,在,SQL,语句之间传递值,动态地改变标题和脚注,从文件而不是人为输入获得输入的值。,使用,&,替代变量,当运行一个报表时，用户希望能动态地限制返回的数据，,SQL*Plus,通过用户变量的方式提供了这种灵活性。在,SQL,语句中使用&来识别每一个变量而不需要定义变量的值。,使用,&,替代变量,使用变量前缀,&,提示用户需要输入一个值,SQL SELECT,empno,ename,sal,deptno,2 FROM,emp,3 WHERE,empno,=,Enter value for employee_num:,7369,EMPNO ENAME SAL DEPTNO,-,7369 SMITH 800 20,SET VERIFY,命令,先后显示,SQL*Plus,用值替换替代变量前后的命令文本,SQL SET VERIFY ON,SQL SELECT,empno,ename,sal,deptno,2 FROM,emp,3 WHERE,empno,=,Enter value for employee_num:7369,old 3:WHERE,empno,=&employee_num,new 3:WHERE,empno,=7369,.,字符和日期的替代变量,使用单引号将日期和字符型的替代变量扩起来,SQL SELECT,ename,deptno,sal,*12,2 FROM,emp,3 WHERE job=,Enter value for job_title:,ANALYST,ENAME DEPTNO SAL*12,-,SCOTT 20 36000,FORD 20 36000,在运行时指定列、表达式及文本,使用替代变量可以补充：,WHERE,条件,ORDER BY,子句,列表达式,表名,完整的,SELECT,语句,替代变量几乎可以使用在,SQL,语句的任何地方。,在运行时指定列、表达式及文本,SQL SELECT,empno,ename,job,&column_name,2 FROM,emp,3 WHERE&condition,4 ORDER BY,Enter value for column_name:,sal,Enter value for condition:,sal,=3000,Enter value for order_column:,ename,EMPNO ENAME JOB SAL,-,7902 FORD ANALYST 3000,7839 KING PRESIDENT 5000,7788 SCOTT ANALYST 3000,使用,&,如果您想再次使用同一变量而不再重新输入，则可以使用,&,SQL SELECT,empno,ename,job,&column_name,2 FROM,emp,3 ORDER BY,Enter value for column_name:,deptno,EMPNO ENAME JOB DEPTNO,-,7839 KING PRESIDENT 10,7782 CLARK MANAGER 10,7934 MILLER CLERK 10,.,14 rows selected.,定义用户变量,您可以使用以下两个,SQL*Plus,命令中的一个预先定义用户变量：,DEFINE:,创建一个字符型用户变量,ACCEPT:,读取用户输入并将其保存在变量中,如果在使用,DEFINE,时需要要到一个空格，则必须将该空格包括在单引号之内,ACCEPT,命令,创建一个在接受用户输入时的专用提示。,明确地定义一个,NUMBER,或,DATE,类型变量。,考虑到安全因素可以隐藏用户输入。,ACCEPT,variable,datatype,FORMAT,format,PROMPT,text,HIDE,ACCEPT,命令,其中：,Variable,存储值的变量名称，如果该变量不,存在，则由,SQL*Plus,创建该变量。,Datatype,数据可以是,NUMBER、CHAR,或,DATE,FORMAT format,指定格式模型。,PROMPT text,用户输入前提示信息文本,HIDE,隐藏用户输入，例如作为口令,注意,：在,ACCEPT,命令中所使用的替代变量名不能使用&作为前缀，但是引用该变量时需要在其前面加&.,使用,ACCEPT,命令,ACCEPT dept PROMPT Provide the department name:,SELECT*,FROM dept,WHERE,dname,=UPPER(&dept),/,Provide the department name:,Sales,DEPTNO DNAME LOC,-,30 SALES CHICAGO,DEFINE,和,UNDEFINE,命令,变量会一直保持直到您：,使用,UNDEFINE,命令清除它,退出,SQL*Plus,您可以使用,DEFINE,命令来验证你所定义的变量,如果要在每次会话都定义同一变量，可以修改,login.,sql,文件在文件中写入定义该变量的脚本，这样每次启动,SQL*Plus,时，都能自动定义该变量,使用,DEFINE,命令,创建一个变量来保存部门名称,DEFINE DEPTNAME =sales(CHAR),就象使用其它变量一样使用该变量,SQL DEFINE,deptname,=sales,SQL DEFINE,deptname,SQL SELECT*,2 FROM dept,3 WHERE,dname,=UPPER(&,deptname,);,定制,SQL*Plus,环境,使用,SET,命令设置当前环境变量,使用,SHOW,命令显示当前环境变量,SQL SET ECHO ON,SQL SHOW ECHO,echo ON,SET,system_variable value,Login.,sql,文件保存了每次会话的标准环境设置及其他,SQL*Plus,命令,常用的,SET,命令变量列表,ARRAYSIZE ,20,|,n,COLSEP _|,text,FEEDBACK ,6,|,n,|OFF|ON,HEADING OFF|ON,LINESIZE ,80,|,n,LONG ,80,|,n,PAGESIZE ,24,|,n,PAUSE ,OFF,|ON|,text,TERMOUT OFF|,ON,将定制的环境保存在,login.,sql,文件中,login.,sql,文件保存了标准的环境设置及其他,SQL*Plus,命令，这些都在登录,SQL*Plus,时自动加载,可以修改,login.,sql,保存其它额外的设置命令,SQL*Plus,格式化命令,COLUMN,column option,TTITLE ,text,|OFF|ON,BTITLE ,text,|OFF|ON,BREAK ON,report_element,COLUMN,命令,控制列的显示,其中，,option,有以下可选项；,CLEAR:,清除所有列的显示格式,FORMAT,format,:,使用格式模型改变列的显示,HEADING,text,:,设置列标题,JUSTIFY,align,:,调整列标题的位置居左、居中还是居右,COLUMN,column,|,alias,option,使用,COLUMN,命令,创建列标题,显示列,ENAME,的当前设置,清除列,ENAME,的当前设置,COLUMN,ename,HEADING Employee|Name FORMAT A15,COLUMN,sal,JUSTIFY LEFT FORMAT$99,990.00,COLUMN mgr FORMAT 999999999 NULL No manager,COLUMN,ename,COLUMN,ename,CLEAR,COLUMN,格式模型,结果,N/A,1234,01234,$1234,L1234,1234.00,1,234,举例,N/A,999999,099999,$9999,L9999,9999.99,9,999,元素,A,n,9,0,$,L,.,描述,设置显示宽度为,n,个字符,每个,9,表是一位数字，不显示前导零,表示前导零,美元符号,本地货币符号,小数点的位置,千位分割符,使用,BREAK,命令,命令格式为：,BREAK on column|alias|row skip,n,|dup|page on.on report,其中：,page,表示遇到断点时产生新的一页,skip n,表示遇到断点时跳跃,n,行,duplicate,显示重复值,并且断点可以作用于：列、行、页、报表。,清除所有,BREAK,设置使用,CLEAR,命令：,CLEAR BREAK,BREAK,命令用于去掉重复的行以及断点处跳过指定的行数。,使用,TTITLE,和,BTITLE,命令,显示标题和脚注,TTITLE,text,|OFF|ON,SQL TTITLE Salary|Report,设置报表标题,设置报表脚注,SQL BTITLE Confidential,创建并运行一个脚本文件,创建脚本文件的步骤：,1,.,创建,SQL SELECT,语句,.,2.,将该,SELECT,语句保存到脚本文件,3.,从编辑器中调用该文件,4.在,SELECT,语句前增加格式化命令,5.,验证,SELECT,后的终结符,创建并运行一个脚本文件,6.在,SELECT,语句之后清除格式化命令,7.,保存脚本文件,.,8.,输入,“,START,文件名”运行脚本,