Oracle培训基础7

Copyright©OracleCorporation,2001.Allrightsreserved.iSQL*Plus7-2Copyright©OracleCorporation,2001.Allrightsreserved.目标通过本章学习，您将可以:•在查询中使用变量。•熟悉iSQL*Plus环境。•使输出更便于理解。•创建和执行脚本。7-3Copyright©OracleCorporation,2001.Allrightsreserved.变量Iwanttoquerydifferentvalues....salary=?……department_id=?…...last_name=?...User7-4Copyright©OracleCorporation,2001.Allrightsreserved.变量使用iSQL*Plus变量:•临时存储值–单个(&)–两个(&&)–定义命令•在SQL语句中改变变量的值。•动态修改开头和结尾。7-5Copyright©OracleCorporation,2001.Allrightsreserved.&变量在变量名前加前缀(&)使用户输入值。SELECTemployee_id,last_name,salary,department_idFROMemployeesWHEREemployee_id=&employee_num;7-6Copyright©OracleCorporation,2001.Allrightsreserved.&变量121017-7Copyright©OracleCorporation,2001.Allrightsreserved.SELECTlast_name,department_id,salary*12FROMemployeesWHEREjob_id='&job_title';字符和日期型变量在子符和日期两端加单引号。7-8Copyright©OracleCorporation,2001.Allrightsreserved.指定列名、表达式和文本使用变量可以提供下面的内容:•WHERE条件•ORDERBY子句•列表达式•表名•整个SELECT语句7-9Copyright©OracleCorporation,2001.Allrightsreserved.SELECTemployee_id,last_name,job_id,&column_nameFROMemployeesWHERE&conditionORDERBY&order_column;指定列名、表达式和文本7-10Copyright©OracleCorporation,2001.Allrightsreserved.定义变量•可以使用DEFINE命令提前定义iSQL*Plus变量。DEFINEvariable=value创建一个字符型用户变量•使用DEFINE定义的变量名字中包含空格时，变量名应包含在单引号中。•定义的边令在会话级有效。7-11Copyright©OracleCorporation,2001.Allrightsreserved.DEFINE和UNDEFINE命令•定义命令在下列条件下失效:–UNDEFINE命令–退出iSQL*Plus•可以重复使用DEFINE命令改变变量。DEFINEjob_title=IT_PROGDEFINEjob_titleDEFINEJOB_TITLE=IT_PROG(CHAR)UNDEFINEjob_titleDEFINEjob_titleSP2-0135:symboljob_titleisUNDEFINED7-12Copyright©OracleCorporation,2001.Allrightsreserved.SELECTemployee_id,last_name,salary,department_idFROMemployeesWHEREemployee_id=&employee_num;DEFINE命令与&变量•使用DEFINE创建变量。•使用变量前缀(&)在SQL语句中引用变量。DEFINEemployee_num=2007-13Copyright©OracleCorporation,2001.Allrightsreserved.SELECTemployee_id,last_name,job_id,&&column_nameFROMemployeesORDERBY&column_name;使用(&&)避免为同一变量重复赋值。…&&变量7-14Copyright©OracleCorporation,2001.Allrightsreserved.old3:WHEREemployee_id=&employee_numnew3:WHEREemployee_id=200VERIFY命令使用VERIFY在iSQL*Plus中显示变量被替代前和变量被替代后的SQL语句。SETVERIFYONSELECTemployee_id,last_name,salary,department_idFROMemployeesWHEREemployee_id=&employee_num;7-15Copyright©OracleCorporation,2001.Allrightsreserved.iSQL*Plus环境•使用SET命令控制当前会话。•使用SHOW命令显示当前的设置。SETECHOONSHOWECHOechoONSETsystem_variablevalue7-16Copyright©OracleCorporation,2001.Allrightsreserved.SET命令•ARRAYSIZE{20|n}•FEEDBACK{6|n|OFF|ON}•HEADING{OFF|ON}•LONG{80|n}|ON|text}SETHEADINGOFFSHOWHEADINGHEADINGOFF7-17Copyright©OracleCorporation,2001.Allrightsreserved.iSQL*Plus格式命令•COLUMN[columnoption]•TTITLE[text|OFF|ON]•BTITLE[text|OFF|ON]•BREAK[ONreport_element]7-18Copyright©OracleCorporation,2001.Allrightsreserved.COLUMN命令控制列的输出：•CLE[AR]:清除列格式•HEA[DING]text:设置列头•FOR[MAT]format:改变列的输出格式•NOPRINT|PRINT•NULLCOL[UMN][{column|alias}[option]]7-19Copyright©OracleCorporation,2001.Allrightsreserved.COLUMN命令•创建列头：COLUMNlast_nameHEADING'Employee|Name'COLUMNsalaryJUSTIFYLEFTFORMAT$99,990.00COLUMNmanagerFORMAT999999999NULL'Nomanager'COLUMNlast_nameCOLUMNlast_nameCLEAR•显示LAST_NAME列的当前格式。•清除LAST_NAME列的当前格式设置7-20Copyright©OracleCorporation,2001.Allrightsreserved.COLUMN格式Result1234001234$1234L12341234.001,234Example999999099999$9999L99999999.999,999Element90$L.,DescriptionSinglezero-suppressiondigitEnforcesleadingzeroFloatingdollarsignLocalcurrencyPositionofdecimalpointThousandseparator7-21Copyright©OracleCorporation,2001.Allrightsreserved.BREAK命令使用BREAK命令去重。BREAKONjob_id7-22Copyright©OracleCorporation,2001.Allrightsreserved.TTITLE和BTITLE命令•显示报告头和报告尾•设置报告头。•摄制报告尾。TTI[TLE][text|OFF|ON]TTITLE'Salary|Report'BTITLE'Confidential'7-23Copyright©OracleCorporation,2001.Allrightsreserved.TTITLE和BTITLE命令•显示报告头和报告尾。•设置报告头。•设置报告尾。TTI[TLE][text|OFF|ON]TTITLE'Salary|Report'BTITLE'Confidential'7-24Copyright©OracleCorporation,2001.Allrightsreserved.使用脚本创建报告1.书写并测试SQLSELECT语句。2.保存SELECT语句到脚本文件。3.在编辑器中执行脚本。4.在SELECT语句前添加格式命令。5.在SELECT语句后添加终止符。7-25Copyright©OracleCorporation,2001.Allrightsreserved.使用脚本创建报告6.在SELECT后清除格式设置。7.保存脚本。8.在iSQL*Plus的文本框中加载脚本,点击执行按钮运行脚本。7-26Copyright©OracleCorporation,2001.Allrightsreserved.报告…7-27Copyright©OracleCorporation,2001.Allrightsreserved.报告…7-28Copyright©OracleCorporation,2001.Allrightsreserved.总结通过本章学习，您已经学会:•使用iSQL*Plus变量临时存储值。•使用SET命令控制当前iSQL*Plus环境。•使用COLUMN命令控制列的输出。•使用BREAK命令去重并将结果积分组。•使用TTITLE和BTITLE显示报告头和报告尾。7-29Copyright©OracleCorporation,2001.Allrightsreserved.Practice7OverviewThispracticecoversthefollowingtopics:•Creatingaquerytodisplayvaluesusingsubstitutionvariables•Startingacommandfilecontainingvariables