Oracle PL/SQL 编程手册(SQL大全)（转） 一、SQLPLUS 1引言 SQL命令 以下17个是作为语句开头的关键字： alterdroprevoke auditgrantrollback* commit*insertselect commentlockupdate createnoauditvalidate deleterename 这些命令必须以“;”结尾 带*命令句尾不必加分号，并且不存入SQL缓存区。 SQL中没有的SQL*PLUS命令 这些命令不存入SQL缓存区 @definepause #delquit $describeremark /disconnectrun acceptdocumentsave appendeditset breakexitshow btitlegetspool changehelpsqlplus clearhoststart columninputtiming computelistttitle connectnewpageundefine copy --------- 2数据库查询 数据字典 TAB用户创建的所有基表、视图和同义词清单 DTAB构成数据字典的所有表 COL用户创建的基表的所有列定义的清单 CATALOG用户可存取的所有基表清单 select*fromtab; describe命令描述基表的结构信息 describedept select* fromemp; selectempno,ename,job fromemp; select*fromdept orderbydeptnodesc; 逻辑运算符 =!=或<>>>=<<= in betweenvalue1andvalue2 like % _ innull not noin,isnotnull 谓词in和notin 有哪些职员和分析员 selectename,job fromemp wherejobin('clerk','analyst'); selectename,job fromemp wherejobnotin('clerk','analyst'); 谓词between和notbetween 哪些雇员的工资在2000和3000之间 selectename,job,salfromemp wheresalbetween2000and3000; selectename,job,salfromemp wheresalnotbetween2000and3000; 谓词like,notlike selectename,deptnofromemp whereenamelike'S%'; (以字母S开头) selectename,deptnofromemp whereenamelike'%K'; (以K结尾) selectename,deptnofromemp whereenamelike'W___'; (以W开头，后面仅有三个字母) selectename,jobfromemp wherejobnotlike'sales%'; (哪些雇员的工种名不以sales开头) 谓词isnull,isnotnull 没有奖金的雇员（即commision为null） selectename,jobfromemp wherecommisnull; selectename,jobfromemp wherecommisnotnull; 多条件查询 selectename,job fromemp wheredeptno=20 andjob!='clerk'; 表达式 +-*/ 算术表达式 选择奖金高于其工资的5%的雇员 selectename,sal,comm,comm/salfromemp wherecomm>.05*sal orderbycomm/saldesc; 日期型数据的运算 addtwodaysto6-Mar-87 6-Mar-87+2=8-Mar-87 addtwohoursto6-Mar-87 6-Mar-87+2/24=6-Mar-87and2hrs add15secondsto6-Mar-87 6-Mar-87+15/(24*60*60)=6-Mar-87and15secs 列名的别名 selectenameemployeefromemp wheredeptno=10; （别名：employee） selectename,sal,comm,comm/sal"C/SRATIO"fromemp wherecomm>.05*sal orderbycomm/saldesc; SQL命令的编辑 listorl显示缓冲区的内容 list4显示当前SQL命令的第4行，并把第4行作为当前行，在该行号后面有个*。 changeorc用新的内容替换原来在一行中第一次出现内容 SQL>c/(...)/('analyst')/ inputori增加一行或多行 appendora在一行后追加内容 del删除当前行删除SQL缓冲区中的当前行 run显示并运行SQL缓冲区中的命令 /运行SQL缓冲区中的命令 edit把SQL缓冲区中的命令写到操作系统下的文本文件， 并调用操作系统提供的编辑器执行修改。 ------------- 3数据操纵 数据的插入 insertintodept values(10,'accounting','newyork'); insertintodept(dname,deptno) values('accounting',10); 从其它表中选择插入数据 insertintoemp(empno,ename,deptno) selectid,name,department fromold_emp wheredepartmentin(10,20,30,40); 使用参数 insertintodept values(&deptno,&dname,&loc); 执行时，SQL/PLUS对每个参数将有提示用户输入 参数对应日期型或字符型数据时，可在参数上加引号，输入时就可不用引号 insertintodept values(&deptno,'&dname','&loc'); 插入空值（NULL） insertintodept values(50,'education',null); 插入日期型数据 日期型数据缺省格式：DD-MON-YY insertintoemp (empno,ename,hiredate) values(7963,'stone','07-APR-87'); 系统时间：SYSDATE insertintoemp (empno,ename,hiredate) values(7600,'kohn',SYSDATE); 数据更新 updateemp setjob='manager' whereename='martin'; updateemp setjob='marketrep' whereename='salesman'; updateemp setdeptno=40,job='marketrep' wherejob='salesman'; 数据删除 deleteemp whereempno=765; 更新的提交 commit 自动提交方式 setautocommiton 如果状态设为开，则使用inesrt,update,delete会立即提交。 更新取消 rollback 两次连续成功的commit之间的操作，称为一个事务 --------------- 4创建基表、视图 创建基表 createtabledept (deptnonumber(2), dnamechar(14), locchar(13)); 数据字典会自动更新。 一个基表最多254列。 表名列名命名规则： 限制 第一个字符必须是字母，后面可任意（包括$#_但不能是逗号）。 名字不得超过30个字符。 唯一 某一用户的基表名必须唯一，不能是ORACLE的保留字，同一基表的列名互不相同。 使用双引号 如果表名用双引号括起来，则可不满足上述规则； 只有使用双引号，才能区别大、小写； 命名时使用了双引号，在以后的操作也必须使用双引号。 数据类型： char(n)（不得超过240字符） number(n,d) date long（最多65536字符） raw（二进制原始数据） 空值处理 有时要求列值不能为空 createtabledept (deptnonumber(2)notnull, dnamechar(14), locchar(13)); 在基表中增加一列 altertabledept add(headcntnumber(3)); 修改已有列属性 altertabledept modifydnamechar(20); 注：只有当某列所有值都为空时，才能减小其列值宽度。 只有当某列所有值都为空时，才能改变其列值类型。 只有当某列所有值都为不空时，才能定义该列为notnull。 例： altertabledeptmodify(locchar(12)); altertabledeptmodifylocchar(12); altertabledeptmodify(dnamechar(13),locchar(12)); 创建视图 createviewmanagersas selectename,job,sal fromemp wherejob='manager'; 为视图列名取别名 createviewmydept (person,title,salary) asselectename,job,sal fromemp wheredeptno=10; withcheckoption选项 使用withcheckoption，保证当对视图插入或更新数据时， 该数据必须满足视图定义中select命令所指定的条件。 createviewdept20as selectename,job,sal,deptno fromemp wheredeptno=20 withcheckoption; 在做下述操作时，会发生错误 updatedept20 setdeptno=30 whereename='ward'; 基表、视图的拷贝 createtableemp2 asselect*fromemp; 基表、视图的删除 droptable表名 dropview视图名 ------------ 5SQL*PLUS报表功能 SQL*PLUS的一些基本格式命令 columndeptnoheadingdepartment columnenameheadingname columnsalheadingsalary columnsalformat$99,999.00 ttitlesamplereportfor｜hitechcorp btitlestrictlyconfidential breakondeptno computesumofsalondeptno run 表头和表尾 ttitlesamplereportfor｜hitechcorp btitlerightstrictlyconfidential “｜”表示换行，结尾不必加分号 选项有三种：leftrightcenter 使用TTITLE，系统将自动地在每页的顶部显示日期和页号。 TTITLET和BTITLE命令有效，直至重新设置表头或表尾，或退出SQL*PLUS。 下面命令使标题语句失效 TTITLEOFF BTITLEOFF 列名 column命令定义用于显示列名 若名字为一个单词，不必加引号 columnenameheadingemployee columnenameheading'employee｜name' （｜为换行） 取消栏定义 columnenameclear 列的格式 columnenameformatA15 columnsalformat$9,999.99 columncommlikesal like子句，使得某一列的格式参照另一列格式，它拷贝列名及其格式 控制记录显示分组顺序 breakondeptno （不显示重复值） selectdeptno,ename fromemp orderbydeptno; （ORDERBY子句用于控制BREAK） 显示为 10clark niller 20smith scott 30allen blake 每次只有一个BREAK命令起作用，但一次可以在多个列上使用BREAK命令 breakon列名1on列名2 记录分组 breakondeptnoskip2 selectdeptno,ename fromemp orderbydeptno; 每个deptno之间空两行 clearbreak（取消BREAK命令） breakonpage（每次从一新页开始） breakonreport（每次从一新报表开始） breakonpageonreport（联合使用） 分组计算 breakondeptnoskip2 computesumofsalondeptno 计算每个部门的工资总和 skip子句使部门之间的信息分隔开 其他计算命令 computeavgofsalondeptno（平均值） count非空值的总数 MAX最大值 MIN最小值 STD标准偏差 VAR协方差 NUMBER行数 使compute命令失效 一旦定义了COMPUTE，则一直有效，直到 关闭COMPUTE（clearcompute） SQL/PLUS环境命令 show选项 （显示当前参数设置情况） showall（显示全部参数） 设置参数 set选项值或开关 setautocommiton SET命令包括 setautocommit{off｜on｜immediate} （自动提交，OFF缺省） setecho{off｜on} （命令文件执行，是否在终端上显示命令本身，OFF缺省） setfeedback{off｜on} （ON：查询结束时，给出结果，记录数的信息，缺省； OFF：无查询结果，记录数的信息） setheading{off｜on} （ON：列的头标在报表上显示，缺省；OFF：不在报表上显示） setlinesize{n} 一行显示的最大字符数，缺省为80 setpagesize{n} 每页的行数，缺省是14 setpause{off｜on｜text} （ON：当报表超过一页时，每显示一屏就暂停显示，等待用户打回车键，再接着显示； OFF：页与页不停顿，缺省；text：页与页停顿，并向用户提示信息） SETBUFFERbuffer 设置当头的命令缓冲区，通常情况下，SQL命令缓冲区已为当前缓冲区。 由于SQL命令缓冲区只能存放一条SQL命令， 所以可用其它缓冲区来存放SQL命令和SQL*PLUS命令。 经常用到的设置可放在login.sql文件中。 SETNULL setnull'nodata' selectename,comm fromemp wheredeptno=30; 把部门30中无佣金雇员的佣金显示为“NODATA”。 setnull是SQL*PLUS命令，用它来标识空值（NULL），可以设置为任意字符串。 存盘命令SAVE save文件名 input 1selectempno,ename,job 2fromemp 3wherejob='analyst' saveresearch 目录中会增加一个research.sql文件。 编辑命令EDIT edit EDIT编辑当前缓冲区中的内容。 编辑一个文件 editresearch 调入命令GET getresearch 把磁盘上的文件内容调入缓冲区，并显示在屏幕上，文件名尾不必加.sql。 START命令 运行指定的文件 startresearch 输出命令SPOOL spooltryfile 不仅可以使查询结果在屏幕上显示，还可以使结果存入文件 停止向文件输出 spooloff 把查询结果在打印机上输出，先把它们存入一个文件中， 然后不必使用SPOOLOFF，而用： spoolout SPOOLOUT关闭该文件并在系统缺省的打印机上输出 制作报表举例 edittryfile setechooff setautocommiton setpagesize25 insertintoemp(empno,ename,hiredate) values(9999,'geiger',sysdate); insertintoemp(empno,ename,deptno) values(3333,'samson',20); spoolnew_emp select*fromemp wheredeptno=20 ordeptnoisnull / spooloff setautocommitoff 用start命令执行这个文件 -------- 6函数 字符型函数 initcap(ename);将ename中每个词的第一个字母改为大写。 如：jacksmith--JackSmith length(ename);计算字符串的长度。 substr(job,1,4); 其它 lower upper least取出字符串列表中按字母排序排在最前面的一个串 greatest取出字符串列表中按字母排序排在最后的一个串 日期函数 add_month(hiredate,5)在雇佣时间上加5个月 month_between(sysdate,hiredate)计算雇佣时间与系统时间之间相差的月数 next_day(hiredate,'FRIDAY')计算受雇日期之后的第一个星期五的日期 例 selectename,sal,next_day(sysdate,'FRIDAY')as_of fromemp wheredeptno=20; （as_of是别名） 如果不用to_char函数，日期在ORACLE中的缺省格式是'DD_MON_YY' to_char(date,datepicture) selectename,to_char(hiredate,'DyMondd,yyyy')hired fromemp wheredeptno=10; to_date(字符串,格式) insertintoemp(empno,ename,hiredate) values(7999,'asms',to_date('070387083000','MMDDYYHHMISS')); 日期型数据的格式 dd12 dyfri dayfriday ddspthtwelfth mm03 monmar monthmarch yy87 yyyy1987 例 Mar12,1987'Mondd,yyyy' MAR12,1987'MONdd,yyyy' ThursdayMARCH12'DayMONTHdd' Mar1211:00am'Monddhh:miam' Thu,thetwelfth'Dy,"the"ddspth' 算术函数 least(v1,v2) selectename,empno,mgr,least(empno,mgr)lownum fromemp whereempno0 trunc(sal,0) 取sal的近似值（截断） 空值函数 nvl(v1,v2) v1为列名，如果v1不是空值，nvl返回其列值。 v1为空值，返回v2的值。 聚组函数 selectsum(comm) fromemp; （返回一个汇总信息） 不能把sum用在select语句里除非用groupby 字符型、日期型、数字型的聚组函数 minmaxcount可用于任何数据类型 selectmin(ename) fromemp; selectmin(hiredate) fromemp; selectmin(sal) fromemp; 有多少人有工作？ selectcount(job) fromemp; 有多少种不同的工种？ selectcount(distinctjob) fromemp; countdistinct计算某一字段中不同的值的个数 其它聚组函数（只用于数字型数据） avg计算平均工资 selectavg(sal) fromemp; stddev计算工资的平均差 selectstddev(sal) fromemp; sum计算总工资 selectsum(sal) fromemp; groupby子句 selectdeptno,sum(sal),avg(sal) fromemp groupbydeptno; 按多个条件分组 每个部门的雇员数 selectdeptno,count(*) fromemp groupbydeptno; 每个部门的每个工种的雇员数 selectdeptno,job,count(*) fromemp groupbydeptno,job; 满足条件的分组 （where是针对select的，having是针对groupby的） 哪些部门的工资总和超过了9000 selectdeptno,sum(sal) fromemp groupbydeptno havingsum(sal)>9000; select小结 除去职员，哪些部门的工资总和超过了8000 selectdeptno,sum(sal) fromemp wherejob!='clerk' groupbydeptno havingsum(sal)>8000 orderbysum(sal); --------- 7高级查询 等值联接 selectempno,ename,job,emp.deptno,dname fromemp,dept whereemp.deptno=dept.deptno; 外联接 selectename,dept.deptno,loc fromemp,dept whereemp.deptno(+)=dept.deptno; 如果在dept.deptno中有的数值，在emp.deptno中没有（如deptno=40）， 则作外联接时，结果中会产生一个空值 自联接：同一基表的不同行要做联接，可使用自联接 指出每个雇员的经理名字 selectworker.ename,manager.enamemanager fromempworker,empmanager whereworker.mgr=manager.empno; 非等值联接 哪些雇员的工资属于第三级别 selectename,sal fromemp,salgrade wheregrade=3 andsalbetweenlosalandhisal; （基表salgrade：gradelosalhisal） 集合运算 行的连接 集合运算把2个或多个查询结果合并为一个 union-setunion Rowsoffirstqueryplusofsecondquery,lessduplicaterows intersect-setintersection Rowsbothquerieshaveincommon minus-setdifference rowsuniquetothefirstquery 介绍几个视图 accountview enamesaljob salesview enamesaljob researchview enamesaljob union运算 返回一个查询结果中有但又不重要的行，它将基表或视图中的记录合并在一起 所有部门中有哪些雇员工资超过2000 对应列的数据类型必须相同 selectename,sal fromaccount wheresal>2000 union selectename,sal fromresearch wheresal>2000 union selectename,sal fromsales wheresal>2000; intersect运算 返回查询结果中相同的部分 各个部门中有哪些相同的工种 selectjob fromaccount intersect selectjob fromresearch intersect selectjob fromsales; minus运算 返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。 有哪些工种在财会部中有，而在销售部中没有？ selectjobfromaccount minus selectjobfromsales; 子查询 slectename,deptno fromemp wheredeptno= (selectdeptno fromemp whereename='smith'); 多级子查询 selectename,job,sal fromemp wherejob= (selectjob fromemp whereename='clark') orsal> (selectsal fromemp whereename='clark'); 多个基表与子查询 selectename,job,sal fromemp,dept whereloc='newyork' andemp.deptno=dept.deptno andsal> (selectsal fromemp whereename='scott'); 子查询中使用聚组函数 selectename,hiredate fromemp wherehiredate= (selectmin(hiredate) fromemp); ------------ 8授权 系统权限 DBA所有权限 RESOURCE注册，创建新的基表 CONNECT，注册，查询 只有DBA才有权创建新的用户 grantconnecttoscott identifiedbytiger; DBA或用户自己可以改变用户口令 grantconnecttoscott identifiedbyleopard; 基表权限1 有两种方法获得对基表操作的权限 创建自己的基表 获得基表创建用户的许可 grantselect,insert onemp toscott; 这些权限有 selectinsertupdatedeletealterindex 把所有权限授于他人 grantallonemptoscott; 同义词 select* fromscott.emp 创建同义词 为用户allen的EMP基表创建同义词employee createsynonymemployee forallen.emp 基表权限2 你可以使其他用户有这样的权力，即其他用户可以把你的基表权限授予他人 grantall onemp toscott withgrantoption; 收回权限 系统权限只有被DBA收回 基表权限随时都可以收回 revokeinsert onemp fromscott; --------- 9索引 建立索引 createindexemp_ename onemp(ename); 删除索引 dropindexemp_ename; 关于索引 只对较大的基表建立索引（至少50条记录） 建立索引之前插入数据 对一个基表可建立任意多个索引 一般是在作为主键的列上建立索引 建立索引之后，不影响SQL命令的执行 建立索引之后，ORACLE自动维护和使用索引 保证数据唯一性 提高执行速度的同时，索引还可以保证每个记录中的每个列值是不重复的。 createuniqueindexemp_empno onemp(empno); -------- 练习和答案 有没有工资比奖金多的雇员？如果有，按工资的降序排列。 如果有两个以上的雇员工资相同，按他们的名字排序。 selectenameemployee,salsalary,commcommision fromemp wheresal>comm orderbysaldesc,ename; 列出有关雇员姓名、奖金占收百分比的信息。 要求显示时列名意义明确，按雇员姓名排序，不包括奖金未知的雇员。 selectenameemployee,(comm/(comm+sal))*100incentive fromemp wherecommisnotnull orderbyename; 在chicago（部门30）工作的所有雇员的工资上涨10%。 updateemp setsal=1.1*sal wheredeptno=30; updateemp setsal=1.1*sal wheredeptno=(selectdeptno fromdept whereloc='chicago'); 为hitech公司新建一个部门，编号为50，其它信息均不可知。 insertintodept(dname,deptno) values('faclities',50); 创建视图，三个列名，其中不包括职员信息 createviewemployee("employeename", "employeenumber", "employeejob") asselectename,empno,job fromemp wherejob!='clerk'; 制作工资报表，包括雇员姓名、受雇时间（按星期计算），工资和部门编号， 一页显示一个部门的信息，每页尾，显示该页的工资之和以及受雇时间之和， 报表结尾处，显示所有雇员的工资总和以及受雇时间总和， 工资按美元计算，受雇时间按星期计算，每页的上方应有标题。 ttitle'service' breakondeptnoonpageonreport computesumofsalondeptno computesumofsalonreport computesumofservice_lengthondeptno computesumofservice_lengthonreport columnsalformat$99,999.00 columnservice_lengthformat9999 selectdeptno,enameemployee,(sysdate-hiredate)/7service_length,sal fromemp orderbydeptno; 制作报表，包括雇员姓名、总收入和受佣日期， 且：姓名的第一个字母必须大写，雇佣日期格式为MM/DD/YYYY， 总收入包括没有奖金的雇员的总收入，姓名按字母顺序排列。 col"hiredate"formatA12 col"employee"formatA10 col"compensation"format$99,999.00 selectinitcap(ename)"employee", (sal+nvl(comm,0))"compensation", to_char(hiredate,'MM/DD/YYYY')"hiredate" fromemp orderbyename; 列出有超过7个周边国家的国家名字和面积。 selectnation,area fromnation wherecodein (selectnation_code fromborder groupbynation_code havingcount(*)>7); 列出所有面积大于等于日本的岛国的国名和人口。 selectnation,population fromnation,border wherecode=nation_code(+) andnation_codeisnull andarea>= (selectarea fromnation whereupper(nation)='JAPAN'); 列出所有边界在其它国家中的国名，并且显示其边界国家名字。 breakonnation selectnation1.nation, nation2.nationborderin_country fromnationnation1,border,nationnation2 wherenation1.code=border.nation_code andborder.border_code=nation2.code orderbynation1.nation; ----------- ----------- PL/SQL 2PL/SQL的块结构和数据类型 块结构的特点 嵌套 begin ...... begin ...... exception ...... end; exception ...... end; 标识符： 不能超过30个字符 第一个字符必须为字母 其余字符可以是字母，数字，$，_，或# 不区分大小写形式 如果用双引号括起来，则字符顺序可以任意排列，并区分大小写形式 无SQL保留字 数据类型 数字型： 整数，实数，以及指数 字符串： 用单引号括起来 若在字符串表示单引号，则使用两个单引号 字符串长度为零（两个单引号之间没有字符），则表示NULL 字符： 长度为1的字符串 数据定义 语法 标识符[常数>数据类型[NOTNULL>[:=PL/SQL表达式>; ':='表示给变量赋值 数据类型包括 数字型number(7,2) 字符型char(120) 日期型date 布尔型boolean（取值为true,false或null，不存贮在数据库中） 日期型 anniversarydate:='05-JUL-95'; project_completiondate; 布尔型 over_budgetbooleannotnull:=false; availableboolean; （初始值为NULL） %type类型匹配 books_printednumber(6); books_soldbook_printed%type; manager_nameemp.ename%type; 变量赋值 变量名:=PL/SQL表达式 numvar:=5; boolvar:=true; datevar:='11-JUN-87'; 字符型、数字型表达式中的空值 null+<数字>=null（空值加数字仍是空值） null><数字>=null（空值与数字进行比较，结果仍是空值） null｜｜'字符串'='字符串'（null即''） （空值与字符串进行连接运算，结果为原字符串） 变量作用范围 标识符在宣言它的块中有效 标识符如果不在子块中重新定义，则在PL/SQL块的所有子块中同样有效 重新定义后的标识符，作用范围仅在本子块中有效 例 declare e_messchar(80); begin /*子块1*/ declare v1number(4); begin selectempnointov1fromemp wherejob='president'; exception whentoo_many_rowsthen insertintojob_errors values('morethanonepresident'); end; /*子块2*/ declare v1number(4); begin selectempnointov1fromemp wherejob='manager'; exception whentoo_many_rowsthen insertintojob_errors values('morethanonemanager'); end; exception whenothersthen e_mess:=substr(sqlerrm,1,80); insertintogeneralerrorsvalues(e_mess); end; --------- 3SQL和PL/SQL 插入 declare my_salnumber(7,2):=3040.55; my_enamechar(25):='wanda'; my_hiredatedate:='08-SEP-88'; begin insertintoemp (empno,enmae,job,hiredate,sal,deptno) values(2741,my_ename,'cabdriver',my_hiredate,my_sal,20); end; 删除 declare bad_child_typechar(20):='naughty'; begin deletefromsantas_gift_listwhere kid_rating=bad_child_type; end; 事务处理 commit[WORK>; rollback[WORK>; （关键字WORK可选，但对命令执行无任何影响） savepoint标记名;（保存当前点） 在事务中标记当前点 rollback[WORK>to[SAVEPOINT>标记名;（回退到当前保存点） 取消savepoint命令之后的所有对数据库的修改 关键字WORK和SAVEPOINT为可选项，对命令执行无任何影响 函数 PL/SQL块中可以使用SQL命令的所有函数 insertintophonebook(lastname)value(upper(my_lastname)); selectavg(sal)intoavg_salfromemp; 对于非SQL命令，可使用大多数个体函数 不能使用聚组函数和参数个数不定的函数，如 x:=sqrt(y); lastname:=upper(lastname); age_diff:=months_between(birthday1,birthday2)/12; 赋值时的数据类型转换 4种赋值形式: 变量名:=表达式 insertinto基表名values(表达式1,表达式2,...); update基表名set列名=表达式; select列名into变量名from...; 数据类型间能进行转换的有: char转成number number转成char char转成date date转成char 例 char_var:=nm_var; 数字型转换成字符型 date_var:='25-DEC-88'; 字符型转换成日期型 insertinto表名(num_col)values('604badnumber'); 错误，无法成功地转换数据类型 --------- 4条件控制 例 declare num_jobsnumber(4); begin selectcount(*)intonum_jobsfromauditions whereactorid=&&actor_idandcalled_back='yes'; ifnum_jobs>100then updateactorsetactor_rating='wordclass' whereactorid=&&actor_id; elsifnum_job=75then updateactorsetactor_rating='daytimesoaps' whereactorid=&&actor_id; else updateactorsetactor_rating='waiter' whereactorid=&&actor_id; endif; endif; commit; end; -------- 5循环 语法 loop ...... endloop; exit;（退出循环） exit[when>;（退出循环，当满足WHEN时） 例1 declare ctrnumber(3):=0; begin loop insertintotable1values('tastesgreat'); insertintotable2values('lessfilling'); ctr:=ctr+1; exitwhenctr=100; endloop; end; （注：如果ctr取为NULL，循环无法结束） 例2 FOR语法 for变量<范围>loop ...... endloop; declare my_indexchar(20):='fettucinialfredo'; bowlchar(20); begin formy_indexinreverse21..30loop insertintotemp(coll)values(my_index); /*循环次数从30到21*/ endloop; bowl:=my_index; end; 跟在inreverse后面的数字必须是从小到大的顺序，必须是整数，不能是变量或表达式 ---------- 6游标 显式游标 打开游标 open<游标名> 例 opencolor_cur; 游标属性 %notfound %found %rowcount %isopen 例 fetchmy_curintomy_var; whilemy_cur%foundloop (处理数据) fetchmy_curintomy_var; exitwhenmy_cur%rowcount=10; endloop; %notfound属性 取值情况如下： fetch操作没有返回记录，则取值为true fetch操作返回一条记录，则取值为false 对游标无fetch操作时，取值为null <游标名>%notfound 例 ifcolor_cur%notfoundthen... 注：如果没有fetch操作，则<游标名>%notfound将导致出错， 因为%notfound的初始值为NULL。 关闭游标 close<游标名> 例 closecolor_cur; 游标的FOR循环 语法 for<记录名>in<游标名>loop <一组命令> endloop; 其中： 索引是建立在每条记录的值之上的 记录名不必声明 每个值对应的是记录名，列名 初始化游标指打开游标 活动集合中的记录自动完成FETCH操作 退出循环，关闭游标 隐式游标 隐式游标是指SQL命令中用到的，没有明确定义的游标 insert,update,delete,select语句中不必明确定义游标 调用格式为SQL% 存贮有关最新一条SQL命令的处理信息 隐式游标的属性 隐式游标有四个属性 SQL%NOTFOUND SQL%FOUND SQL%ROWCOUNT：隐式游标包括的记录数 例： deletefrombaseball_teamwherebatting_avg<100; ifsql%rowcount>5thn insertintotemp values('yourteamneedshelp'); endif; SQL%ISOPEN：取值总为FALSE。SQL命令执行完毕，PL/SQL立即关闭隐式游标。 --------- 7标号 GOTO语句 用法： gotoyou_are_here; 其中you_are_here是要跳转的语句标号 标号必须在同一组命令，或是同一块中使用 正确的使用 <>（标号） x:=x+1 ifa>bthen b:=b+c; gotodinner; endif; 错误的使用 gotojail; ifa>bthen b:=b+c; <>（标号） x:=x+1; endif; 标号：解决意义模糊 标号可用于定义列值的变量 <> declare deptnonumber:=20; begin updateempsetsal=sal*1.1 wheredeptno=sample.deptno; commit; endsample; 如果不用标号和标号限制符，这条命令将修改每条记录。 ---------- 8异常处理 预定义的异常情况 任何ORACLE错误都将自动产生一个异常信息 一些异常情况已命名，如： no_data_found当SELECT语句无返回记录时产生 too_many_rows没有定义游标，而SELECT语句返回多条记录时产生 whenevernotfound无对应的记录 用户定义的异常情况 由用户自己获取 在DECLARE部分定义： declare xnumber; something_isnt_rightexception; 用户定义的异常情况遵循一般的作用范围规则 条件满足时，获取异常情况：raisesomething_isnt_right 注意：同样可以获取预定义的异常情况 exception_init语句 允许为ORACLE错误命名 调用格式： pragmaexception_init(<表达式>,); 例 declare deadlock_detectedexception; pragmaexception_init(deadlock_detected,-60); raise语句 单独使用RAISE命令，可再一次获取当前的异常情况（就象异常情况被重复处理了一样）。 在异常处理中，此语句只能单独使用。 异常处理标识符 一组用于处理异常情况的语句： exception when<表达式>or[表达式...>then <一组语句> ... whenothersthen--最后一个处理 <一组语句> end;既结束PL/SQL块部分，也结束异常处理部分 -------- 练习与答案 1: 接收contract_no和item_no值，在inventory表中查找，如果产品： 已发货，在arrival_date中赋值为今天后的7天 已订货，在arrival_date中赋值为今天后的一个月 既无订货又无发货，则在arrival_date中赋值为今天后的两个月， 并在order表中增加一条新的订单记录。 product_status的列值为'shipped'和'ordered' inventory: product_idnumber(6) product_descriptionchar(30) product_statuschar(20) std_shipping_qtynumber(3) contract_item: contract_nonumber(12) item_nonumber(6) arrival_datedate order: order_idnumber(6) product_idnumber(6) qtynumber(3) 答案： declare i_product_idinventory.product_id%type; i_product_descriptioninventory.product_description%type; i_product_statusinventory.product_status%type; i_std_shipping_qtyinventory.std_shipping_qty%type; begin selectproduct_id,product_description,product_status,std_shipping_qty intoi_product_id,i_product_description, i_product_status,i_std_shipping_qty frominventory whereproduct_id=( selectproduct_id fromcontract_item wherecontract_no=&&contractnoanditem_no=&&itemno); ifi_product_status='shipped'then updatecontract_item setarrival_date=sysdate+7 whereitem_no=&&itemnoandcontract_no=&&contractno; elsifi_product_status='ordered'then updatecontract_item setarrival_date=add_months(sysdate,1) whereitem_no=&&itemnoandcontract_no=&&contractno; else updatecontract_item setarrival_date=add_months(sysdate,2) whereitem_no=&&itemnoandcontract_no=&&contractno; insertintoorders values(100,i_product_id,i_std_shipping_qty); endif; endif; commit; end; 2: 1.找出指定部门中的所有雇员 2.用带'&'的变量提示用户输入部门编号 3.把雇员姓名及工资存入prnttable表中，基结构为： createtableprnttable (seqnumber(7),linechar(80)); 4.异常情况为，部门中奖金不为空值的雇员信息才能存入prnttable表中。 答案： declare cursoremp_curis selectename,sal,comm fromempwheredeptno=&dno; emp_recemp_cur%rowtype; null_commissionexception; begin openemp_cur; fetchemp_curintoemp_rec; while(emp_cur%found)loop ifemp_rec.commisnullthen begin closeemp_cur; raisenull_commission; end; endif; fetchemp_curintoemp_rec; endloop; closeemp_sur; exception whennull_commissionthen openemp_cur; fetchemp_curintoemp_rec; while(emp_cur%found)loop ifemp_rec.commisnotnullthen insertintotempvalues(emp_rec.sal,emp_rec.ename); endif; fetchemp_curintoemp_rec; endloop; closeemp_cur; commit; end;   Java研究组织-版权所有2002-2002