plsql基础教程-必看-2012

《plsql基础教程-必看-2012》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

60PL/SQL程序设计-60- 60目录第一章PL/SQL程序设计简介4§1.2SQL与PL/SQL4§1.2.1什么是PL/SQL?4§1.2.1PL/SQL的好处4§1.2.2PL/SQL可用的SQL语句5§1.3运行PL/SQL程序5第二章PL/SQL块结构和组成元素6§2.1PL/SQL块6§2.2PL/SQL结构6§2.3标识符6§2.4PL/SQL变量类型7§2.4.1变量类型7§2.4.2复合类型9§2.4.3使用%ROWTYPE11§2.4.4LOB类型*11§2.4.5Bind变量11§2.4.6INDEXBYTABLES12§2.4.7数据类型的转换*13§2.5运算符和表达式(数据定义)13§2.5.1关系运算符13§2.5.2一般运算符13§2.5.3逻辑运算符13§2.6变量赋值13§2.6.1字符及数字运算特点13§2.6.2BOOLEAN赋值13§2.6.3数据库赋值13§2.6.4可转换的类型赋值13§2.7变量作用范围及可见性13§2.8注释13§2.9简单例子13§2.9.1简单数据插入例子13§2.9.2简单数据删除例子13第三章PL/SQL流程控制语句13§3.1条件语句13§3.2CASE表达式13§3.3循环13§3.3标号和GOTO13§3.4NULL语句13第四章游标的使用13-60- 60§4.1游标概念13§4.1.1处理显式游标13§4.1.2处理隐式游标13§4.1.3游标修改和删除操作13第五章异常错误处理13§5.1异常处理概念13§5.1.1预定义的异常处理13§5.1.2非预定义的异常处理13§5.1.3用户自定义的异常处理13§5.1.4用户定义的异常处理13§5.2异常错误传播13§5.2.1在执行部分引发异常错误13§5.2.2在声明部分引发异常错误13§5.3异常错误处理编程13§5.4在PL/SQL中使用SQLCODE,SQLERRM13第六章存储函数和过程13§6.1引言13§6.2创建函数13§6.3存储过程13§6.3.1创建过程13§6.3.2调用存储过程13§6.3.3开发存储过程步骤13§6.3.4与过程相关数据字典13第七章包的创建和应用13§7.1引言13§7.2包的定义13§7.3包的开发步骤13§7.4包定义的说明13§7.5子程序重载13§7.6删除过程、函数和包13§7.7包的管理13第八章触发器13§8.1触发器类型13§8.1.1DML触发器13§8.1.2替代触发器13§8.1.3系统触发器13§8.2创建触发器13§8.2.1触发器触发次序13§8.2.2创建DML触发器13§8.2.3创建替代(Instead_of)触发器13§8.2.3创建系统事件触发器13-60- 60§8.2.4系统触发器事件属性13§8.2.5使用触发器谓词13§8.2.6重新编译触发器13§8.3删除和使能触发器13§8.4触发器和数据字典13§8.5数据库触发器的应用举例13-60- 60PL/SQL程序设计简介PL/SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对ORACLE数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据进行快速高效的处理。除此之外，可以在ORACLE数据库的某些客户端工具中，使用PL/SQL语言也是该语言的一个特点。本章的主要内容是讨论引入PL/SQL语言的必要性和该语言的主要特点，以及了解PL/SQL语言的重要性和数据库版本问题。还要介绍一些贯穿全书的更详细的高级概念，并在本章的最后就我们在本书案例中使用的数据库表的若干约定做一说明。本章主要重点：lPL/SQL概述lPL/SQL块结构lPL/SQL流程l运算符和表达式l游标l异常处理l数据库存储过程和函数l包l触发器§1.2SQL与PL/SQL§1.2.1什么是PL/SQL?PL/SQL是ProcedureLanguage&StructuredQueryLanguage的缩写。ORACLE的SQL是支持ANSI(AmericannationalStandardsInstitute)和ISO92(InternationalStandardsOrganization)标准的产品。PL/SQL是对SQL语言存储过程语言的扩展。从ORACLE6以后，ORACLE的RDBMS附带了PL/SQL。它现在已经成为一种过程处理语言，简称PL/SQL。目前的PL/SQL包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多产品（如C语言，JAVA语言等）工具中的独立引擎。可以将这两部分称为：数据库PL/SQL和工具PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。工具PL/SQL另外还增加了用于支持工具（如ORACLEForms）的句法，如：在窗体上设置按钮等。本章主要介绍数据库PL/SQL内容。§1.2.1PL/SQL的好处§1.2.1.1有利于客户/服务器环境应用的运行对于客户/服务器环境来说，真正的瓶颈是网络上。无论网络多快，只要客户端与服务器进行大量的数据交换。应用运行的效率自然就回受到影响。如果使用PL/SQL进行编程，将这种具有大量数据处理的应用放在服务器端来执行。自然就省去了数据在网上的传输时间。§1.2.1.2适合于客户环境-60- 60PL/SQL由于分为数据库PL/SQL部分和工具PL/SQL。对于客户端来说，PL/SQL可以嵌套到相应的工具中，客户端程序可以执行本地包含PL/SQL部分，也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。§1.2.2PL/SQL可用的SQL语句PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成。在PL/SQL中可以使用的SQL语句有：INSERT，UPDATE，DELETE，SELECTINTO，COMMIT，ROLLBACK，SAVEPOINT。提示：在PL/SQL中只能用SQL语句中的DML部分，不能用DDL部分，如果要在PL/SQL中使用DDL(如CREATEtable等)的话，只能以动态的方式来使用。lORACLE的PL/SQL组件在对PL/SQL程序进行解释时，同时对在其所使用的表名、列名及数据类型进行检查。lPL/SQL可以在SQL*PLUS中使用。lPL/SQL可以在高级语言中使用。lPL/SQL可以在ORACLE的开发工具中使用。l其它开发工具也可以调用PL/SQL编写的过程和函数，如PowerBuilder等都可以调用服务器端的PL/SQL过程。§1.3运行PL/SQL程序PL/SQL程序的运行是通过ORACLE中的一个引擎来进行的。这个引擎可能在ORACLE的服务器端，也可能在ORACLE应用开发的客户端。引擎执行PL/SQL中的过程性语句，然后将SQL语句发送给数据库服务器来执行。再将结果返回给执行端。-60- 60第二章PL/SQL块结构和组成元素§2.1PL/SQL块PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。PL/SQL块的结构如下：DECLARE/*声明部分:在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数*/BEGIN/*执行部分:过程及SQL语句,即程序的主要部分*/EXCEPTION/*执行异常部分:错误处理*/END;其中执行部分是必须的。PL/SQL块可以分为三类：1.无名块：动态构造，只能执行一次。2.子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。3.触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。§2.2PL/SQL结构lPL/SQL块中可以包含子块；l子块可以位于PL/SQL中的任何部分；l子块也即PL/SQL中的一条命令；§2.3标识符PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同。要求和限制有：l标识符名不能超过30字符；l第一个字符必须为字母；l不分大小写；l不能用’-‘(减号);l不能是SQL保留字。提示:一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果.-60- 60例如：下面的例子将会删除所有的纪录，而不是KING的记录；DECLAREEnamevarchar2(20):=’KING’;BEGINDELETEFROMempWHEREename=ename;END;变量命名在PL/SQL中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个系统的文档在规范上达到要求。下面是建议的命名方法：标识符命名规则例子程序变量V_nameV_name程序常量C_NameC_company_name游标变量Name_cursorEmp_cursor异常标识E_nameE_too_many表类型Name_table_typeEmp_record_type表Name_tableEmp记录类型Name_recordEmp_recordSQL*Plus替代变量P_nameP_sal绑定变量G_nameG_year_sal§2.4PL/SQL变量类型在前面的介绍中，有系统的数据类型，也可以自定义数据类型。下表是ORACLE类型和PL/SQL中的变量类型的合法使用列表：§2.4.1变量类型在ORACLE8i中可以使用的变量类型有：类型子类说明范围ORACLE限制CHARCharacterStringRowidNchar定长字符串民族语言字符集0à32767可选,确省=12000VARCHAR2Varchar,StringNVARCHAR2可变字符串民族语言字符集0à3276740004000BINARY_INTEGER带符号整数,为整数计算优化性能NUMBER(p,s)DecDoubleprecisionIntegerIntNumericRealSmallint小数,NUMBER的子类型高精度实数整数,NUMBER的子类型整数,NUMBER的子类型与NUMBER等价与NUMBER等价整数,比integer小-60- 60LONG变长字符串0->214748364732,767字节DATE日期型公元前4712年1月1日至公元后4712年12月31日BOOLEAN布尔型TRUE,FALSE,NULL不使用ROWID存放数据库行号UROWID通用行标识符，字符类型例1.插入一条记录并显示；DECLARERow_idUROWID;infoVARCHAR2(40);BEGININSERTINTOdeptVALUES(90,‘SERVICE’,‘BEIJING’)RETURNINGrowid,dname||’:’||to_char(deptno)||’:’||locINTOrow_id,info;DBMS_OUTPUT.PUT_LINE(‘ROWID:’||row_id);DBMS_OUTPUT.PUT_LINE(info);END;其中：RETURNING子句用于检索INSERT语句中所影响的数据行数，当INSERT语句使用VALUES子句插入数据时，RETURNING字句还可将列表达式、ROWID和REF值返回到输出变量中。在使用RETURNING子句是应注意以下几点限制：1．不能并行DML语句和远程对象一起使用；2．不能检索LONG类型信息；3．当通过视图向基表中插入数据时，只能与单基表视图一起使用。例2.修改一条记录并显示DECLARERow_idUROWID;infoVARCHAR2(40);BEGINUPDATEdeptSETdeptno=80WHEREDNAME=‘SERVICE’RETURNINGrowid,dname||’:’||to_char(deptno)||’:’||locINTOrow_id,info;DBMS_OUTPUT.PUT_LINE(‘ROWID:’||row_id);DBMS_OUTPUT.PUT_LINE(info);END;其中：RETURNING子句用于检索被修改行信息：当UPDATE语句修改单行数据时，RETURNING-60- 60子句可以检索被修改行的ROWID和REF值，以及行中被修改列的列表达式，并可将他们存储到PL/SQL变量或复合变量中；当UPDATE语句修改多行数据时，RETURNING子句可以将被修改行的ROWID和REF值，以及列表达式值返回到复合变量数组中。在UPDATE中使用RETURNING子句的限制与INSERT语句中对RETURNING子句的限制相同。例3.删除一条记录并显示DECLARERow_idUROWID;infoVARCHAR2(40);BEGINDELETEdeptWHEREDNAME=‘SERVICE’RETURNINGrowid,dname||’:’||to_char(deptno)||’:’||locINTOrow_id,info;DBMS_OUTPUT.PUT_LINE(‘ROWID:’||row_id);DBMS_OUTPUT.PUT_LINE(info);END;其中：RETURNING子句用于检索被修改行信息：当UPDATE语句修改单行数据时，RETURNING子句可以检索被修改行的ROWID和REF值，以及行中被修改列的列表达式，并可将他们存储到PL/SQL变量或复合变量中；当UPDATE语句修改多行数据时，RETURNING子句可以将被修改行的ROWID和REF值，以及列表达式值返回到复合变量数组中。在UPDATE中使用RETURNING子句的限制与INSERT语句中对RETURNING子句的限制相同。§2.4.2复合类型ORACLE在PL/SQL中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型---记录和表.§2.4.2.1记录类型记录类型是把逻辑相关的数据作为一个单元存储起来，它必须包括至少一个标量型或RECORD数据类型的成员，称作PL/SQLRECORD的域(FIELD)，其作用是存放互不相同但逻辑相关的信息。定义记录类型语法如下:TYPErecord_typeISRECORD(Field1type1[NOTNULL][:=exp1],Field2type2[NOTNULL][:=exp2],......Fieldntypen[NOTNULL][:=expn]);例4：-60- 60DECLARETYPEtest_recISRECORD(CodeVARCHAR2(10),NameVARCHAR2(30)NOTNULL:=’abook’);V_booktest_rec;BEGINV_book.code:=’123’;V_book.name:=’C++Programming’;DBMS_OUTPUT.PUT_LINE(v_book.code||v_book.name);END;可以用SELECT语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相配即可。§2.4.2.2使用%TYPE定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。使用%TYPE特性的优点在于：l所引用的数据库列的数据类型可以不必知道；l所引用的数据库列的数据类型可以实时改变。例5：DECLARE--用%TYPE类型定义与表相配的字段TYPEt_RecordISRECORD(T_noemp.empno%TYPE,T_nameemp.ename%TYPE,T_salemp.sal%TYPE);--声明接收数据的变量v_empt_Record;BEGINSELECTempno,ename,salINTOv_empFROMempWHEREempno=7788;DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_emp.t_no)||v_emp.t_name||TO_CHAR(v_emp.t_sal));END;例6：DECLAREv_empnoemp.empno%TYPE:=&empno;Typer_recordisrecord(v_nameemp.ename%TYPE,v_salemp.sal%TYPE,v_dateemp.hiredate%TYPE);Recr_record;-60- 60BEGINSELECTename,sal,hiredateINTORecFROMempWHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE(Rec.v_name||'---'||Rec.v_sal||'--'||Rec.v_date);END;§2.4.3使用%ROWTYPEPL/SQL提供%ROWTYPE操作符,返回一个记录类型,其数据类型和数据库表的数据结构相一致。使用%ROWTYPE特性的优点在于：l所引用的数据库中列的个数和数据类型可以不必知道；l所引用的数据库中列的个数和数据类型可以实时改变。例7：DECLAREv_empnoemp.empno%TYPE:=&empno;recemp%ROWTYPE;BEGINSELECT*INTOrecFROMempWHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工资:'||rec.sal||'工作时间:'||rec.hiredate);END;§2.4.4LOB类型*ORACLE提供了LOB(LargeOBject)类型，用于存储大的数据对象的类型。ORACLE目前主要支持BFILE,BLOB,CLOB及NCLOB类型。BFILE(Movie)存放大的二进制数据对象，这些数据文件不放在数据库里，而是放在操作系统的某个目录里，数据库的表里只存放文件的目录。BLOB(Photo)存储大的二进制数据类型。变量存储大的二进制对象的位置。大二进制对象的大小<=4GB。CLOB(Book)存储大的字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小<=4GB。NCLOB存储大的NCHAR字符数据类型。每个变量存储大字符对象的位置，该位置指到大字符数据块。大字符对象的大小<=4GB。§2.4.5Bind变量绑定变量是在主机环境中定义的变量。在PL/SQL程序中可以使用绑定变量作为他们将要使用的其它变量。为了在PL/SQL环境中声明绑定变量，使用命令VARIABLE。例如：VARIABLEreturn_codeNUMBER-60- 60VARIABLEreturn_msgVARCHAR2(20)可以通过SQL*Plus命令中的PRINT显示绑定变量的值。例如：PRINTreturn_codePRINTreturn_msg例7：VARIABLEresultNUMBERBEGINSELECT(sal*12)+nvl(comm,0)INTO:resultFROMempWHEREempno=7788;END;PRINTresult§2.4.6INDEXBYTABLES包括两个基本成分:．数据处理类型为BINARY_INTEGER主键;．标量或记录数据类型的列．TYPEtype_nameISTABLEOF{column_type|variable%TYPE|table.column%TYPE}[NOTNULL]|table%ROWTYPE[INDEXBYBINARY_INTEGER];方法描述EXISTS(n)ReturnTRUEifthenthelementinaPL/SQLtableexists;COUNTReturnsthenumberofelementsthataPL/SQLtablecurrentlycontains;FIRSTLASTReturnthefirstandlast(smallestandlastest)indexnumbersinaPL/SQLtable.ReturnsNULLifthePL/SQLtableisempty.PRIOR(n)ReturnstheindexnumberthatprecedesindexninaPL/SQLtable;NEXT(N)ReturnstheindexnumberthatsucceedsindexninaPL/SQLtable;TRIMTRIMremovesoneelementfromtheendofaPL/SQLtable.TRIM(n)removesnelementfromtheendofaPL/SQLtable.DELETEDELETEremovesallelementsfromaPL/SQLtable.DELETE(n)removesthenthelementsfromaPL/SQLtable.DELETE(m,n)removesallelementsintherangemtonfromaPL/SQLtable.例8：DECLARETYPEdept_table_typeISTABLEOFdept%ROWTYPEINDEXBYBINARY_INTEGER;my_dname_tabledept_table_type;-60- 60v_countnumber(2):=4;BEGINFORintIN1..v_countLOOPSELECT*INTOmy_dname_table(int)FROMdeptWHEREdeptno=int*10;ENDLOOP;FORintINmy_dname_table.FIRST..my_dname_table.LASTLOOPDBMS_OUTPUT.PUT_LINE(‘Departmentnumber:‘||my_dname_table(int).deptno);DBMS_OUTPUT.PUT_LINE(‘Departmentname:‘||my_dname_table(int).dname);ENDLOOP;END;§2.4.7数据类型的转换*隐式类型转换BIN_INTCHARDATELONGNUMBERPLS_INTUROWIDVARCHAR2BIN_INT üüüCHAR üDATEüüüüüLONGüüüüüüNUMBERüüüRAWüüüüüUROWIDüüüüüüVARCHAR2ü§2.5运算符和表达式(数据定义)§2.5.1关系运算符运算符意义=等于<>,!=,~=,^=不等于<小于>大于<=小于或等于>=大于或等于§2.5.2一般运算符运算符意义+加号-减号*乘号/除号:=赋值号-60- 60=>关系号..范围运算符||字符连接符§2.5.3逻辑运算符运算符意义ISNULL是空值 BETWEEN介于两者之间IN在一列值中间 AND逻辑与OR逻辑或NOT取返,如ISNOTNULL,NOTIN§2.6变量赋值在PL/SQL编程中，变量赋值是一个值得注意的地方，它的语法如下：variable:=expression;variable是一个PL/SQL变量,expression是一个PL/SQL表达式.§2.6.1字符及数字运算特点空值加数字仍是空值：NULL+<数字>=NULL空值加（连接）字符，结果为字符：NULL||<字符串>=<字符串>§2.6.2BOOLEAN赋值布尔值只有TRUE,FALSE及NULL三个值。如：DECLAREdoneBOOLEAN;/*thefollowingstatementsarelegal:*/BEGINdone:=FALSE;WHILENOTdoneLOOPNull;ENDLOOP;END;§2.6.3数据库赋值数据库赋值是通过SELECT语句来完成的，每次执行SELECT语句就赋值一次，一般要求被赋值的变量与SELECT中的列名要一一对应。如：-60- 60例9：DECLAREemp_idemp.empno%TYPE:=7788;emp_nameemp.ename%TYPE;wagesemp.sal%TYPE;BEGINSELECTename,NVL(sal,0)+NVL(comm,0)INTOemp_name,wagesFROMempWHEREempno=emp_id;Dbms_output.put_line(emp_name||’----‘||to_char(wages));END;提示：不能将SELECT语句中的列赋值给布尔变量。§2.6.4可转换的类型赋值lCHAR转换为NUMBER：使用TO_NUMBER函数来完成字符到数字的转换，如：v_total:=TO_NUMBER(‘100.0’)+sal;lNUMBER转换为CHAR：使用TO_CHAR函数可以实现数字到字符的转换，如：v_comm:=TO_CHAR(‘123.45’)||’元’;l字符转换为日期：使用TO_DATE函数可以实现字符到日期的转换，如：v_date:=TO_DATE('2001.07.03','yyyy.mm.dd');l日期转换为字符使用TO_CHAR函数可以实现日期到字符的转换，如：v_to_day:=TO_CHAR(SYSDATE,'yyyy.mm.ddhh24:mi:ss');§2.7变量作用范围及可见性在PL/SQL编程中，如果在变量的定义上没有做到统一的话，可能会隐藏一些危险的错误，这样的原因主要是变量的作用范围所致。与其它高级语言类似，PL/SQL的变量作用范围特点是：l变量的作用范围是在你所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束。l一个变量（标识）只能在你所引用的块内是可见的。l当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为它可能不用了）。l在子块中重新定义该变量后，它的作用仅在该块内。-60- 60例10：DECLAREEmesschar(80);BEGINDECLAREV1NUMBER(4);BEGINSELECTempnoINTOv1FROMempWHERELOWER(job)=’president’;DBMS_OUTPUT.PUT_LINE(V1);EXCEPTIONWhenTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(‘Morethanonepresident’);END;DECLAREV1NUMBER(4);BEGINSELECTempnoINTOv1FROMempWHERELOWER(job)=’manager’;EXCEPTIONWhenTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(‘Morethanonemanager’);END;EXCEPTIONWhenothersTHENEmess:=substr(SQLERRM,1,80);DBMS_OUTPUT.PUT_LINE(emess);END;§2.8注释在PL/SQL里，可以使用两种符号来写注释，即：l使用双‘-‘(减号)加注释PL/SQL允许用–来写注释，它的作用范围是只能在一行有效。如：V_SalNUMBER(12,2);--工资变量。l使用/**/来加一行或多行注释，如：/***********************************************//*文件名：statistcs_sal.sql*//***********************************************/提示：被解释存放在数据库中的PL/SQL-60- 60程序，一般系统自动将程序头部的注释去掉。只有在PROCEDURE之后的注释才被保留；另外程序中的空行也自动被去掉。§2.9简单例子§2.9.1简单数据插入例子例11：/*本例子仅是一个简单的插入，不是实际应用。*/DECLAREv_enameVARCHAR2(20):=‘Bill’;v_salNUMBER(7,2):=1234.56;v_deptnoNUMBER(2):=10;v_empnoNUMBER(4):=8888;BEGININSERTINTOemp(empno,ename,JOB,sal,deptno,hiredate)VALUES(v_empno,v_ename,‘Manager’,v_sal,v_deptno,TO_DATE(’1954.06.09’,’yyyy.mm.dd’));COMMIT;END;§2.9.2简单数据删除例子例12：/*本例子仅是一个简单的删除例子，不是实际应用。*/DECLAREv_empnonumber(4):=8888;BEGINDELETEFROMempWHEREempno=v_empno;COMMIT;END;-60- 60第三章PL/SQL流程控制语句介绍PL/SQL的流程控制语句,包括如下三类:l控制语句:IF语句l循环语句:LOOP语句,EXIT语句l顺序语句:GOTO语句,NULL语句§3.1条件语句IF<布尔表达式>THENPL/SQL和SQL语句ENDIF;IF<布尔表达式>THENPL/SQL和SQL语句ELSE其它语句ENDIF;IF<布尔表达式>THENPL/SQL和SQL语句ELSIF<其它布尔表达式>THEN其它语句ELSIF<其它布尔表达式>THEN其它语句ELSE其它语句ENDIF;提示:ELSIF不能写成ELSEIF例1:DECLAREv_empnoemp.empno%TYPE:=&empno;V_salaryemp.sal%TYPE;V_commentVARCHAR2(35);BEGINSELECTsalINTOv_salaryFROMempWHEREempno=v_empno;IFv_salary<1500THENV_comment:=‘Fairlyless’;ELSIFv_salary<3000THENV_comment:=‘Alittlemore’;-60- 60ELSEV_comment:=‘Lotsofsalary’;ENDIF;DBMS_OUTPUT.PUT_LINE(V_comment);END;§3.2CASE表达式CASEselectorWHENexpression1THENresult1WHENexpression2THENresult2WHENexpressionNTHENresultN[ELSEresultN+1]END;例2:DECLAREV_gradechar(1):=UPPER(‘&p_grade’);V_appraisalVARCHAR2(20);BEGINV_appraisal:=CASEv_gradeWHEN‘A’THEN‘Excellent’WHEN‘B’THEN‘VeryGood’WHEN‘C’THEN‘Good’ELSE‘Nosuchgrade’END;DBMS_OUTPUT.PUT_LINE(‘Grade:‘||v_grade||’Appraisal:‘||v_appraisal);END;§3.3循环1.简单循环LOOP要执行的语句;EXITWHEN<条件语句>/*条件满足，退出循环语句*/ENDLOOP;-60- 60例3.DECLAREintNUMBER(2):=0;BEGINLOOPint:=int+1;DBMS_OUTPUT.PUT_LINE('int的当前值为:'||int);EXITWHENint=10;ENDLOOP;END;2.WHILE循环WHILE<布尔表达式>LOOP要执行的语句;ENDLOOP;例4.DECLARExNUMBER;BEGINx:=1;WHILEx<10LOOPDBMS_OUTPUT.PUT_LINE('X的当前值为:'||x);x:=x+1;ENDLOOP;END;3.数字式循环FOR循环计数器IN[REVERSE]下限..上限LOOP要执行的语句;ENDLOOP;每循环一次，循环变量自动加1；使用关键字REVERSE，循环变量自动减1。跟在INREVERSE后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。可以使用EXIT退出循环。例5.BEGINFORintin1..10LOOPDBMS_OUTPUT.PUT_LINE('int的当前值为:'||int);ENDLOOP;END;例6.-60- 60CREATETABLEtemp_table(num_colNUMBER);DECLAREV_counterNUMBER:=10;BEGININSERTINTOtemp_table(num_col)VALUES(v_counter);FORv_counterIN20..25LOOPINSERTINTOtemp_table(num_col)VALUES(v_counter);ENDLOOP;INSERTINTOtemp_table(num_col)VALUES(v_counter);FORv_counterINREVERSE20..25LOOPINSERTINTOtemp_table(num_col)VALUES(v_counter);ENDLOOP;END;DROPTABLEtemp_table;§3.3标号和GOTOPL/SQL中GOTO语句是无条件跳转到指定的标号去的意思。语法如下：GOTOlabel;......<>　/*标号是用<<　>>括起来的标识符*/例7:DECLAREV_counterNUMBER:=1;BEGINLOOPDBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter);V_counter:=v_counter+1;IFv_counter>10THENGOTOl_ENDofLOOP;ENDIF;ENDLOOP;<>DBMS_OUTPUT.PUT_LINE('V_counter的当前值为:'||V_counter);END;§3.4NULL语句在PL/SQL程序中，可以用null语句来说明“不用做任何事情”的意思，相当于一个占位符，可以使某些语句变得有意义，提高程序的可读性。如：-60- 60DECLARE...BEGIN…IFv_numISNULLTHENGOTOprint1;ENDIF;…<>NULL;--不需要处理任何数据。END;-60- 60第四章游标的使用在PL/SQL程序中，对于处理多行记录的事务经常使用游标来实现。§4.1游标概念为了处理SQL语句，ORACLE必须分配一片叫上下文(contextarea)的区域来处理所必需的信息，其中包括要处理的行的数目，一个指向语句被分析以后的表示形式的指针以及查询的活动集(activeset)。游标是一个指向上下文的句柄(handle)或指针。通过游标，PL/SQL可以控制上下文区和处理语句时上下文区会发生些什么事情。对于不同的SQL语句，游标的使用情况不同：SQL语句游标非查询语句隐式的结果是单行的查询语句隐式的或显示的结果是多行的查询语句显示的§4.1.1处理显式游标1.显式游标处理显式游标处理需四个PL/SQL步骤:l定义游标：就是定义一个游标名，以及与其相对应的SELECT语句。格式：CURSORcursor_name[(parameter[,parameter]…)]ISselect_statement;游标参数只能为输入参数，其格式为：parameter_name[IN]datatype[{:=|DEFAULT}expression]在指定数据类型时，不能使用长度约束。如NUMBER(4)、CHAR(10)等都是错误的。l打开游标：就是执行游标所对应的SELECT语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有FORUPDATE选项，OPEN语句还将锁定数据库表中游标结果集合对应的数据行。格式：OPENcursor_name[([parameter=>]value[,[parameter=>]value]…)];在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示法。PL/SQL程序不能用OPEN语句重复打开一个游标。l提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中。格式：FETCHcursor_nameINTO{variable_list|record_variable};l对该记录进行处理；l继续处理，直到活动集合中没有记录；l关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH语句取其中数据。关闭后的游标可以使用OPEN语句重新打开。格式：CLOSEcursor_name;-60- 60注：定义的游标不能有INTO子句。例1.游标参数的传递方法。DECLAREDeptRecdept%ROWTYPE;Dept_namedept.dname%TYPE;Dept_locdept.loc%TYPE;CURSORc1ISSELECTdname,locFROMdeptWHEREdeptno<=30;CURSORc2(dept_noNUMBERDEFAULT10)ISSELECTdname,locFROMdeptWHEREdeptno<=dept_no;CURSORc3(dept_noNUMBERDEFAULT10)ISSELECT*FROMdeptWHEREdeptno<=dept_no;BEGINOPENc1;LOOPFETCHc1INTOdept_name,dept_loc;EXITWHENc1%NOTFOUND;DBMS_OUTPUT.PUT_LINE(dept_name||’---‘||dept_loc);ENDLOOP;CLOSEc1;OPENc2;LOOPFETCHc2INTOdept_name,dept_loc;EXITWHENc2%NOTFOUND;DBMS_OUTPUT.PUT_LINE(dept_name||’---‘||dept_loc);ENDLOOP;CLOSEc2;OPENc3(dept_no=>20);LOOPFETCHc3INTOdeptrec;EXITWHENc3%NOTFOUND;DBMS_OUTPUT.PUT_LINE(deptrec.deptno||’---‘||deptrec.dname||’---‘||deptrec.loc);ENDLOOP;CLOSEc3;END;2.游标属性%FOUND布尔型属性，当最近一次读记录时成功返回,则值为TRUE；%NOTFOUND布尔型属性，与%FOUND相反；%ISOPEN布尔型属性，当游标已打开时返回TRUE；-60- 60%ROWCOUNT数字型属性，返回已从游标中读取的记录数。例2：给工资低于1200的员工增加工资50。DECLAREv_empnoemp.empno%TYPE;v_salemp.sal%TYPE;CURSORcISSELECTempno,salFROMemp;BEGINOPENc;LOOPFETCHcINTOv_empno,v_sal;EXITWHENC%NOTFOUND;IFv_sal<=1200THENUPDATEempSETsal=sal+50WHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!');ENDIF;DBMS_OUTPUT.PUT_LINE('记录数:'||C%ROWCOUNT);ENDLOOP;CLOSEc;END;3.游标的FOR循环PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理，当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。格式：FORindex_variableINcursor_name[value[,value]…]LOOP--游标数据处理代码ENDLOOP;其中：index_variable为游标FOR循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标FOR循环语句中的索引变量来访问这些列数据。注：不要在程序中对游标进行人工操作；不要在程序中定义用于控制FOR循环的记录。例3：DECLARECURSORc_salISSELECTempno,ename,salFROMemp;BEGIN--隐含打开游标-60- 60FORv_salINc_salLOOP--隐含执行一个FETCH语句DBMS_OUTPUT.PUT_LINE(to_char(v_sal.empno)||’---‘||v_sal.ename||’---‘||to_char(v_sal.sal));--隐含监测c_sal%NOTFOUNDENDLOOP;--隐含关闭游标END;例4：当所声明的游标带有参数时，通过游标FOR循环语句为游标传递参数。DECLARECURSORc1(dept_noNUMBERDEFAULT10)ISSELECTdname,locFROMdeptWHEREdeptno<=dept_no;BEGINDBMS_OUTPUT.PUT_LINE(‘dept_no参数值为30：’);FORc1_recINc1(30)LOOPDBMS_OUTPUT.PUT_LINE(c1_rec.dname||’---‘||c1_rec.loc);ENDLOOP;DBMS_OUTPUT.PUT_LINE(CHR(10)||’使用默认的dept_no参数值10：’);FORc1_recINc1LOOPDBMS_OUTPUT.PUT_LINE(c1_rec.dname||’---‘||c1_rec.loc);ENDLOOP;END;例5：PL/SQL还允许在游标FOR循环语句中使用子查询来实现游标的功能。BEGINFORc1_recIN(SELECTdname,locFROMdept)LOOPDBMS_OUTPUT.PUT_LINE(c1_rec.dname||’---‘||c1_rec.loc);ENDLOOP;END;§4.1.2处理隐式游标显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由ORACLE系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL语句所包含的数据。格式调用为：SQL%注：INSERT,UPDATE,DELETE,SELECT语句中不必明确定义游标。-60- 60隐式游标属性SQL%FOUND布尔型属性,当最近一次读记录时成功返回，则值为true；SQL%NOTFOUND布尔型属性,与%found相反；SQL%ROWCOUNT数字型属性,返回已从游标中读取得记录数；SQL%ISOPEN布尔型属性,取值总是FALSE。SQL命令执行完毕立即关闭隐式游标。例6:删除EMP表中某部门的所有员工，如果该部门中已没有员工，则在DEPT表中删除该部门。DECLAREV_deptnoemp.deptno%TYPE:=&p_deptno;BEGINDELETEFROMempWHEREdeptno=v_deptno;IFSQL%NOTFOUNDTHENDELETEFROMdeptWHEREdeptno=v_deptno;ENDIF;END;§4.1.3游标修改和删除操作游标修改和删除操作是指在游标定位下，修改或删除表中指定的数据行。这时，要求游标查询语句中必须使用FORUPDATE选项，以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列。为了对正在处理(查询)的行不被另外的用户改动，ORACLE提供一个FORUPDATE子句来对所选择的行进行锁住。该需求迫使ORACLE锁定游标结果集合的行，可以防止其他事务处理更新或删除相同的行，直到您的事务处理提交或回退为止。语法：SELECT...FROM…FORUPDATE[OFcolumn[,column]…][NOWAIT]如果另一个会话已对活动集中的行加了锁，那么SELECTFORUPDATE操作一直等待到其它的会话释放这些锁后才继续自己的操作，对于这种情况，当加上NOWAIT子句时，如果这些行真的被另一个会话锁定，则OPEN立即返回并给出：ORA-0054：resourcebusyandacquirewithnowaitspecified.如果使用FORUPDATE声明游标，则可在DELETE和UPDATE语句中使用WHERECURRENTOFcursor_name子句，修改或删除游标结果集合当前行对应的数据库表中的数据行。例7：从EMP表中查询某部门的员工情况，将其工资最低定为1500；DECLAREV_deptnoemp.deptno%TYPE:=&p_deptno;CURSORemp_cursorISSELECTempno,salFROMempWHEREdeptno=v_deptnoFORUPDATEOFsalNOWAIT;BEGIN-60- 60FORemp_recordINemp_cursorLOOPIFemp_record.sal<1500THENUPDATEempSETsal=1500WHERECURRENTOFemp_cursor;ENDIF;ENDLOOP;--COMMIT;END;-60- 60第五章异常错误处理一个优秀的程序都应该能够正确处理各种出错情况，并尽可能从错误中恢复。ORACLE提供异常情况(EXCEPTION)和异常处理(EXCEPTIONHANDLER)来实现错误处理。§5.1异常处理概念异常情况处理(EXCEPTION)是用来处理正常执行过程中未预料的事件,程序块的异常处理预定义的错误和自定义错误,由于PL/SQL程序块一旦产生异常而没有指出如何处理时,程序就会自动终止整个程序运行.有三种类型的异常错误：1．预定义(Predefined)错误ORACLE预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，由ORACLE自动将其引发。2．非预定义(Predefined)错误即其他标准的ORACLE错误。对这种异常情况的处理，需要用户在程序中定义，然后由ORACLE自动将其引发。3．用户定义(User_define)错误程序执行过程中，出现编程人员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。异常处理部分一般放在PL/SQL程序体的后半部,结构为:EXCEPTIONWHENfirst_exceptionTHENWHENsecond_exceptionTHENWHENOTHERSTHENEND;异常处理可以按任意次序排列,但OTHERS必须放在最后.§5.1.1预定义的异常处理预定义说明的部分ORACLE异常错误错误号异常错误信息名称说明ORA-0001Dup_val_on_index试图破坏一个唯一性限制ORA-0051Timeout-on-resource在等待资源时发生超时ORA-0061Transaction-backed-out由于发生死锁事务被撤消ORA-1001Invalid-CURSOR试图使用一个无效的游标ORA-1012Not-logged-on没有连接到ORACLEORA-1017Login-denied无效的用户名/口令ORA-1403No_data_foundSELECTINTO没有找到数据ORA-1422Too_many_rowsSELECTINTO返回多行ORA-1476Zero-divide试图被零除ORA-1722Invalid-NUMBER转换一个数字失败ORA-6500Storage-error内存不够引发的内部错误ORA-6501Program-error内部错误ORA-6502Value-error转换或截断错误-60- 60ORA-6504Rowtype-mismatch缩主游标变量与PL/SQL变量有不兼容行类型ORA-6511CURSOR-already-OPEN试图打开一个已存在的游标ORA-6530Access-INTO-null试图为null对象的属性赋值ORA-6531Collection-is-null试图将Exists以外的集合(collection)方法应用于一个nullpl/sql表上或varray上ORA-6532Subscript-outside-limit对嵌套或varray索引得引用超出声明范围以外ORA-6533Subscript-beyond-count对嵌套或varray索引得引用大于集合中元素的个数.对这种异常情况的处理，只需在PL/SQL块的异常处理部分，直接引用相应的异常情况名，并对其完成相应的异常错误处理即可。例1：更新指定员工工资，如工资小于1500，则加100；DECLAREv_empnoemp.empno%TYPE:=&empno;v_salemp.sal%TYPE;BEGINSELECTsalINTOv_salFROMempWHEREempno=v_empno;IFv_sal<=1500THENUPDATEempSETsal=sal+100WHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已更新!');ELSEDBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'员工工资已经超过规定值!');ENDIF;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('数据库中没有编码为'||v_empno||'的员工');WHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('发生其它错误!');END;§5.1.2非预定义的异常处理对于这类异常情况的处理，首先必须对非定义的ORACLE错误进行定义。步骤如下：1.在PL/SQL块的定义部分定义异常情况：<异常情况>EXCEPTION;2.将其定义好的异常情况，与标准的ORACLE错误联系起来，使用EXCEPTION_INIT语句：PRAGMAEXCEPTION_INIT(<异常情况>,<错误代码>)；3.在PL/SQL块的异常情况处理部分对异常情况做出相应的处理。-60- 60例2：删除指定部门的记录信息，以确保该部门没有员工。INSERTINTOdeptVALUES(50,‘FINANCE’,‘CHICAGO’);DECLAREv_deptnodept.deptno%TYPE:=&deptno;e_deptno_remainingEXCEPTION;PRAGMAEXCEPTION_INIT(e_deptno_remaining,-2292);/*-2292是违反一致性约束的错误代码*/BEGINDELETEFROMdeptWHEREdeptno=v_deptno;EXCEPTIONWHENe_deptno_remainingTHENDBMS_OUTPUT.PUT_LINE('违反数据完整性约束!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('发生其它错误!');END;§5.1.3用户自定义的异常处理当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使用RAISE语句来触发。当引发一个异常错误时，控制就转向到EXCEPTION块异常错误部分，执行错误处理代码。对于这类异常情况的处理，步骤如下：1．在PL/SQL块的定义部分定义异常情况：<异常情况>EXCEPTION;2．RAISE<异常情况>；3．在PL/SQL块的异常情况处理部分对异常情况做出相应的处理。例3：更新指定员工工资，增加100；DECLAREv_empnoemp.empno%TYPE:=&empno;no_resultEXCEPTION;BEGINUPDATEempSETsal=sal+100WHEREempno=v_empno;IFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;EXCEPTIONWHENno_resultTHEN-60- 60DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('发生其它错误!');END;§5.1.4用户定义的异常处理调用DBMS_STANDARD(ORACLE提供的包)包所定义的RAISE_APPLICATION_ERROR过程，可以重新定义异常错误消息，它为应用程序提供了一种与ORACLE交互的方法。RAISE_APPLICATION_ERROR的语法如下：RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);这里的error_number是从–20,000到–20,999之间的参数，error_message是相应的提示信息(<2048字节)，keep_errors为可选，如果keep_errors=TRUE,则新错误将被添加到已经引发的错误列表中。如果keep_errors=FALSE(缺省),则新错误将替换当前的错误列表。例4：创建一个函数get_salary,该函数检索指定部门的工资总和，其中定义了-20991和-20992号错误，分别处理参数为空和非法部门代码两种错误：CREATETABLEerrlog(ErrcodeNUMBER,ErrtextCHAR(40));CREATEORREPLACEFUNCTIONget_salary(p_deptnoNUMBER)RETURNNUMBERASV_salNUMBER;BEGINIFp_deptnoISNULLTHENRAISE_APPLICATION_ERROR(-20991,’部门代码为空’);ELSIFp_deptno<0THENRAISE_APPLICATION_ERROR(-20992,’无效的部门代码’);ELSESELECTSUM(sal)INTOv_salFROMEMPWHEREdeptno=p_deptno;RETURNV_sal;ENDIF;END;DECLAREV_salaryNUMBER(7,2);V_sqlcodeNUMBER;V_sqlerrVARCHAR2(512);Null_deptnoEXCEPTION;-60- 60Invalid_deptnoEXCEPTION;PRAGMAEXCEPTION_INIT(null_deptno,-20991);PRAGMAEXCEPTION_INIT(invalid_deptno,-20992);BEGINV_salary:=get_salary(10);DBMS_OUTPUT.PUT_LINE(’10号部门工资：’||TO_CHAR(V_salary));BEGINV_salary:=get_salary(-10);EXCEPTIONWHENinvalid_deptnoTHENV_sqlcode:=SQLCODE;V_sqlerr:=SQLERRM;INSERTINTOerrlog(errcode,errtext)VALUES(v_sqlcode,v_sqlerr);COMMIT;ENDinner1;V_salary:=get_salary(20);DBMS_OUTPUT.PUT_LINE(’20号部门工资：’||TO_CHAR(V_salary));BEGINV_salary:=get_salary(NULL);ENDinner2;V_salary:=get_salary(30);DBMS_OUTPUT.PUT_LINE(’30号部门工资：’||TO_CHAR(V_salary));EXCEPTIONWHENnull_deptnoTHENV_sqlcode:=SQLCODE;V_sqlerr:=SQLERRM;INSERTINTOerrlog(errcode,errtext)VALUES(v_sqlcode,v_sqlerr);COMMIT;WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('发生其它错误!');ENDouter;§5.2异常错误传播由于异常错误可以在声明部分和执行部分以及异常错误部分出现，因而在不同部分引发的异常错误也不一样。-60- 60§5.2.1在执行部分引发异常错误当一个异常错误在执行部分引发时，有下列情况：l如果当前块对该异常错误设置了处理，则执行它并成功完成该块的执行，然后控制转给包含块。l如果没有对当前块异常错误设置定义处理器，则通过在包含块中引发它来传播异常错误。然后对该包含块执行步骤1)。§5.2.2在声明部分引发异常错误如果在声明部分引起异常情况，即在声明部分出现错误，那么该错误就能影响到其它的块。比如在有如下的PL/SQL程序：DECLAREAbcnumber(3):=’abc’;其它语句BEGIN其它语句EXCEPTIONWHENOTHERSTHEN其它语句END;例子中，由于Abcnumber(3)=’abc’;出错，尽管在EXCEPTION中说明了WHENOTHERSTHEN语句，但WHENOTHERSTHEN也不会被执行。但是如果在该错误语句块的外部有一个异常错误，则该错误能被抓住，如：BEGINDECLAREAbcnumber(3):=’abc’;其它语句BEGIN其它语句EXCEPTIONWHENOTHERSTHEN其它语句END;EXCEPTIONWHENOTHERSTHEN其它语句END;§5.3异常错误处理编程-60- 60在一般的应用处理中，建议程序人员要用异常处理，因为如果程序中不声明任何异常处理，则在程序运行出错时，程序就被终止，并且也不提示任何信息。下面是使用系统提供的异常来编程的例子。§5.4在PL/SQL中使用SQLCODE,SQLERRM由于ORACLE的错信息最大长度是512字节，为了得到完整的错误提示信息，我们可用SQLERRM和SUBSTR函数一起得到错误提示信息。SQLCODE返回错误代码数字.SQLERRM返回错误信息.如:SQLCODE=+100èSQLERRM=’no_data_found‘SQLCODE=0èSQLERRM=’normal,successfualcompletion’例5.将ORACLE错误代码及其信息存入错误代码表CREATETABLEerrors(errnumNUMBER(4),errmsgVARCHAR2(100));DECLAREerr_msgVARCHAR2(100);BEGIN/*得到所有ORACLE错误信息*/FORerr_numIN-100..0LOOPerr_msg:=SQLERRM(err_num);INSERTINTOerrorsVALUES(err_num,err_msg);ENDLOOP;END;DROPTABLEerrors;例6.查询ORACLE错误代码；BEGININSERTINTOemp(empno,ename,hiredate,deptno)VALUES(2222,‘Jerry’,SYSDATE,20);DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');INSERTINTOemp(empno,ename,hiredate,deptno)VALUES(2222,‘Jerry’,SYSDATE,20);DBMS_OUTPUT.PUT_LINE('插入数据记录成功!');EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(SQLCODE||’---‘||SQLERRM);END;-60- 60第六章存储函数和过程§6.1引言ORACLE提供可以把PL/SQL程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。在本节中，主要介绍：1．创建存储过程和函数。2．正确使用系统级的异常处理和用户定义的异常处理。3．建立和管理存储过程和函数。§6.2创建函数1.建立内嵌函数语法如下：CREATE[ORREPLACE]FUNCTIONfunction_name[(argment[{IN|INOUT}]type,argment[{IN|OUT|INOUT}]type]RETURNreturn_type{IS|AS}<类型.变量的说明>BEGINFUNCTION_bodyEXCEPTION其它语句END;例1.获取某部门的工资总和：CREATEORREPLACEFUNCTIONget_salary(Dept_noNUMBER,Emp_countOUTNUMBER)RETURNNUMBERISV_sumNUMBER;BEGINSELECTSUM(sal),count(*)INTOV_sum,emp_countFROMempWHEREdeptno=dept_no;RETURNv_sum;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');-60- 60WHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE('程序运行错误!请使用游标');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('发生其它错误!');ENDget_salary;1.内嵌函数的调用函数声明时所定义的参数称为形式参数，应用程序调用时为函数传递的参数称为实际参数。应用程序在调用函数时，可以使用以下三种方法向函数传递参数：第一种参数传递格式称为位置表示法，格式为：argument_value1[,argument_value2…]例2：计算某部门的工资总和：DECLAREV_numNUMBER;V_sumNUMBER;BEGINV_sum:=get_salary(30,v_num);DBMS_OUTPUT.PUT_LINE(’30号部门工资总和：’||v_sum||’，人数：’||v_num);END;第二种参数传递格式称为名称表示法，格式为：argument=>parameter[,…]其中：argument为形式参数，它必须与函数定义时所声明的形式参数名称相同。Parameter为实际参数。在这种格式中，形势参数与实际参数成对出现，相互间关系唯一确定，所以参数的顺序可以任意排列。例3：计算某部门的工资总和：DECLAREV_numNUMBER;V_sumNUMBER;BEGINV_sum:=get_salary(emp_count=>v_num,dept_no=>30);DBMS_OUTPUT.PUT_LINE(’30号部门工资总和：’||v_sum||’，人数：’||v_num);END;第三种参数传递格式称为混合表示法：即在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参数。采用这种参数传递方法时，使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说，无论函数具有多少个参数，只要其中有一个参数使用名称表示法，其后所有的参数都必须使用名称表示法。-60- 60例4：CREATEORREPLACEFUNCTIONdemo_fun(NameVARCHAR2,AgeINTEGER,SexVARCHAR2)RETURNVARCHAR2ASV_varVARCHAR2(32);BEGINV_var:=name||’：‘||TO_CHAR(age)||’岁，’||sex;RETURNv_var;END;DECLAREVarVARCHAR(32);BEGINVar:=demo_fun(‘user1’,30,sex=>‘男’);DBMS_OUTPUT.PUT_LINE(var);Var:=demo_fun(‘user2’,age=>40,sex=>‘男’);DBMS_OUTPUT.PUT_LINE(var);Var:=demo_fun(‘user3’,sex=>‘女’,age=>20);DBMS_OUTPUT.PUT_LINE(var);END;无论采用哪一种参数传递方法，实际参数和形式参数之间的数据传递只有两种方法：传址法和传值法。所谓传址法是指在调用函数时，将实际参数的地址指针传递给形式参数，使形式参数和实际参数指向内存中的同一区域，从而实现参数数据的传递。这种方法又称作参照法，即形式参数参照实际参数数据。输入参数均采用传址法传递数据。传值法是指将实际参数的数据拷贝到形式参数，而不是传递实际参数的地址。默认时，输出参数和输入/输出参数均采用传值法。在函数调用时，ORACLE将实际参数数据拷贝到输入/输出参数，而当函数正常运行退出时，又将输出形式参数和输入/输出形式参数数据拷贝到实际参数变量中。1.参数默认值在CREATEORREPLACEFUNCTION语句中声明函数参数时可以使用DEFAULT关键字为输入参数指定默认值。例5：CREATEORREPLACEFUNCTIONdemo_fun(NameVARCHAR2,AgeINTEGER,-60- 60SexVARCHAR2DEFAULT‘男’)RETURNVARCHAR2ASV_varVARCHAR2(32);BEGINV_var:=name||’：‘||TO_CHAR(age)||’岁，’||sex;RETURNv_var;END;具有默认值的函数创建后，在函数调用时，如果没有为具有默认值的参数提供实际参数值，函数将使用该参数的默认值。但当调用者为默认参数提供实际参数时，函数将使用实际参数值。在创建函数时，只能为输入参数设置默认值，而不能为输入/输出参数设置默认值。DECLAREVarVARCHAR(32);BEGINVar:=demo_fun(‘user1’,30);DBMS_OUTPUT.PUT_LINE(var);Var:=demo_fun(‘user2’,age=>40);DBMS_OUTPUT.PUT_LINE(var);Var:=demo_fun(‘user3’,sex=>‘女’,age=>20);DBMS_OUTPUT.PUT_LINE(var);END;§6.3存储过程§6.3.1创建过程建立存储过程在ORACLESERVER上建立存储过程,可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储过程传回参数.创建过程语法:CREATE[ORREPLACE]PROCEDUREProcedure_name[(argment[{IN|INOUT}]Type,argment[{IN|OUT|INOUT}]Type]{IS|AS}<类型.变量的说明>BEGIN<执行部分>EXCEPTION<可选的异常错误处理程序>END;-60- 60例6．用户连接登记记录；CREATEtablelogtable(useridVARCHAR2(10),logdatedate);CREATEORREPLACEPROCEDURElogexecutionISBEGININSERTINTOlogtable(userid,logdate)VALUES(USER,SYSDATE);END;例7．删除指定员工记录；CREATEORREPLACEPROCEDUREDelEmp(v_empnoINemp.empno%TYPE)ASNo_resultEXCEPTION;BEGINDELETEFROMempWHEREempno=v_empno;IFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被除名!');EXCEPTIONWHENno_resultTHENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('发生其它错误!');ENDDelEmp;§6.3.2调用存储过程存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS、ORACLE开发工具或第三方开发工具中来调用运行。ORACLE使用EXECUTE语句来实现对存储过程的调用：EXEC[UTE]Procedure_name(parameter1,parameter2…);例8：EXECUTElogexecution;例9．计算指定部门的工资总和，并统计其中的职工数量。CREATEORREPLACEPROCEDUREproc_demo(Dept_noNUMBERDEFAULT10,Sal_sumOUTNUMBER,-60- 60Emp_countOUTNUMBER)ISBEGINSELECTSUM(sal),COUNT(*)INTOsal_sum,emp_countFROMempWHEREdeptno=dept_no;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('发生其它错误!');ENDproc_demo;调用方法:DECLAREV_numNUMBER;V_sumNUMBER(8,2);BEGINProc_demo(30,v_sum,v_num);DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||’，人数：’||v_num);Proc_demo(sal_sum=>v_sum,emp_count=>v_num);DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||’，人数：’||v_num);END;在PL/SQL程序中还可以在块内建立本地函数和过程，这些函数和过程不存储在数据库中，但可以在创建它们的PL/SQL程序中被重复调用。本地函数和过程在PL/SQL块的声明部分定义，它们的语法格式与存储函数和过程相同，但不能使用CREATEORREPLACE关键字。例10：建立本地过程，用于计算指定部门的工资总和，并统计其中的职工数量；DECLAREV_numNUMBER;V_sumNUMBER(8,2);PROCEDUREproc_demo(Dept_noNUMBERDEFAULT10,Sal_sumOUTNUMBER,Emp_countOUTNUMBER)ISBEGINSELECTSUM(sal),COUNT(*)INTOsal_sum,emp_countFROMempWHEREdeptno=dept_no;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');-60- 60WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('发生其它错误!');ENDproc_demo;BEGINProc_demo(30,v_sum,v_num);DBMS_OUTPUT.PUT_LINE('30号部门工资总和：'||v_sum||’，人数：’||v_num);Proc_demo(sal_sum=>v_sum,emp_count=>v_num);DBMS_OUTPUT.PUT_LINE('10号部门工资总和：'||v_sum||’，人数：’||v_num);END;§6.3.3开发存储过程步骤开发存储过程、函数、包及触发器的步骤如下：§6.3.3.1使用文字编辑处理软件编辑存储过程源码使用文字编辑处理软件编辑存储过程源码，要用类似WORD文字处理软件进行编辑时，要将源码存为文本格式。§6.3.3.2在SQLPLUS或用调试工具将存储过程程序进行解释在SQLPLUS或用调试工具将存储过程程序进行解释；在SQL>下调试，可用START或GET等ORACLE命令来启动解释。如：SQL>STARTc:stat1.sql如果使用调式工具，可直接编辑和点击相应的按钮即可生成存储过程。§6.3.3.3调试源码直到正确我们不能保证所写的存储过程达到一次就正确。所以这里的调式是每个程序员必须进行的工作之一。在SQLPLUS下来调式主要用的方法是：l使用SHOWERROR命令来提示源码的错误位置；l使用user_errors数据字典来查看各存储过程的错误位置。§6.3.3.4授权执行权给相关的用户或角色如果调式正确的存储过程没有进行授权，那就只有建立者本人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。在SQL*PLUS下可以用GRANT命令来进行存储过程的运行授权。GRANT语法：GRANTsystem_privilege|roleTOuser|role|PUBLIC[WITHADMINOPTION]GRANTobject_privilege|ALLONschema.objectTOuser|role|PUBLIC[WITHGRANTOPTION]例子：CREATEORREPLACEPUBLICSYNONYMDBMS_JOBFORDBMS_JOB-60- 60GRANTEXECUTEONdbms_jobTOPUBLICWITHGRANTOPTION§6.3.4与过程相关数据字典USER_SOURCE,ALL_SOURCE,DBA_SOURCE,USER_ERRORS相关的权限:CREATEANYPROCEDUREDROPANYPROCEDURE在SQL*PLUS中，可以用DESCRIBE命令查看过程的名字及其参数表。DESCRIBEProcedure_name;DROPPROCEDURElogexecution;DROPPROCEDUREdelemp;DROPPROCEDUREproc_demo;DROPFUNCTIONdemo_fun;DROPFUNCTIONget_salary;-60- 60第七章包的创建和应用§7.1引言包是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合，它具有面向对象程序设计语言的特点，是对这些PL/SQL程序设计元素的封装。包类似于C++和JAVA语言中的类，其中变量相当于类中的成员变量，过程和函数相当于类方法。把相关的模块归类成为包，可使开发人员利用面向对象的方法进行存储过程的开发，从而提高系统性能。与类相同，包中的程序元素也分为公用元素和私用元素两种，这两种元素的区别是他们允许访问的程序范围不同，即它们的作用域不同。公用元素不仅可以被包中的函数、过程所调用，也可以被包外的PL/SQL程序访问，而私有元素只能被包内的函数和过程序所访问。在PL/SQL程序设计中，使用包不仅可以使程序设计模块化，对外隐藏包内所使用的信息（通过使用私用变量），而写可以提高程序的执行效率。因为，当程序首次调用包内函数或过程时，ORACLE将整个包调入内存，当再次访问包内元素时，ORACLE直接从内存中读取，而不需要进行磁盘I/O操作，从而使程序执行效率得到提高。一个包由两个分开的部分组成：包定义（PACKAGE）：包定义部分声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元素，这些元素为包的公有元素。包主体（PACKAGEBODY）：包主体则是包定义部分的具体实现，它定义了包定义部分所声明的游标和子程序，在包主体中还可以声明包的私有元素。包定义和包主体分开编译，并作为两部分分开的对象存放在数据库字典中，详见数据字典user_source,all_source,dba_source.§7.2包的定义包定义的语法如下：创建包定义:CREATE[ORREPLACE]PACKAGEpackage_name[AUTHID{CURRENT_USER|DEFINER}]{IS|AS}[公有数据类型定义[公有数据类型定义]…][公有游标声明[公有游标声明]…][公有变量、常量声明[公有变量、常量声明]…][公有子程序声明[公有子程序声明]…]END[package_name];其中：AUTHIDCURRENT_USER和AUTHIDDEFINER选项说明应用程序在调用函数时所使用的权限模式，它们与CREATEFUNCTION语句中invoker_right_clause子句的作用相同。创建包主体:CREATE[ORREPLACE]PACKAGEBODYpackage_name{IS|AS}[私有数据类型定义[私有数据类型定义]…]-60- 60[私有变量、常量声明[私有变量、常量声明]…][私有子程序声明和定义[私有子程序声明和定义]…][公有游标定义[公有游标定义]…][公有子程序定义[公有子程序定义]…]BEGINPL/SQL语句END[package_name];其中：在包主体定义公有程序时，它们必须与包定义中所声明子程序的格式完全一致。§7.3包的开发步骤与开发存储过程类似，包的开发需要几个步骤：1．将每个存储过程调式正确；2．用文本编辑软件将各个存储过程和函数集成在一起；3．按照包的定义要求将集成的文本的前面加上包定义；4．按照包的定义要求将集成的文本的前面加上包主体；5．使用SQLPLUS或开发工具进行调式。§7.4包定义的说明例1:创建的包为demo_pack,该包中包含一个记录变量DeptRec、两个函数和一个过程。CREATEORREPLACEPACKAGEdemo_packISDeptRecdept%ROWTYPE;V_sqlcodeNUMBER;V_sqlerrVARCHAR2(2048);FUNCTIONadd_dept(dept_noNUMBER,dept_nameVARCHAR2,locationVARCHAR2)RETURNNUMBER;FUNCTIONremove_dept(dept_noNUMBER)RETURNNUMBER;PROCEDUREquery_dept(dept_noINNUMBER);ENDdemo_pack;包主体的创建方法，它实现上面所声明的包定义，并在包主体中声明一个私有变量flag和一个私有函数check_dept，由于在add_dept和remove_dept等函数中需要调用check_dpet函数，所以，在定义check_dept函数之前首先对该函数进行声明，这种声明方法称作前向声明。CREATEORREPLACEPACKAGEBODYdemo_packISFlagINTEGER;FUNCTIONcheck_dept(dept_noNUMBER)RETURNINTEGER;-60- 60FUNCTIONadd_dept(dept_noNUMBER,dept_nameVARCHAR2,locationVARCHAR2)RETURNNUMBERISBEGINIFcheck_dept(dept_no)=0THENINSERTINTOdeptVALUES(dept_no,dept_name,location);RETURN1;ELSERETURN0;ENDIF;EXCEPTIONWHENOTHERSTHENV_sqlcode:=SQLCODE;V_sqlerr:=SQLERRM;RETURN-1;ENDadd_dept;FUNCTIONremove_dept(dept_noNUMBER)RETURNNUMBERISBEGINV_sqlcode:=0;V_sqlerr:=NULL;IFcheck_dept(dept_no)=1THENDELETEFROMdeptWHEREdeptno=dept_no;RETURN1;ELSERETURN0;ENDIF;EXCEPTIONWHENOTHERSTHENV_sqlcode:=SQLCODE;V_sqlerr:=SQLERRM;RETURN-1;ENDremove_dept;PROCEDUREquery_dept(dept_noINNUMBER)ISBEGINIFcheck_dept(dept_no)=1THENSELECT*INTODeptRecFROMdeptWHEREdeptno=dept_no;ENDIF;ENDquery_dept;-60- 60FUNCTIONcheck_dept(dept_noNUMBER)RETURNINTEGERISBEGINSELECTCOUNT(*)INTOflagFROMdeptWHEREdeptno=dept_no;IFflag>0THENFlag:=1;ENDIF;RETURNflag;ENDcheck_dept;BEGINV_sqlcode:=NULL;V_sqlerr:=NULL;ENDdemo_pack;对包内共有元素的调用格式为：包名.元素名称调用demo_pack包内函数对dept表进行插入、查询和修改操作，并通过demo_pack包中的记录变量DeptRec显示所查询到的数据库信息：DECLAREVarNUMBER;BEGINVar:=demo_pack.add_dept(90,’Administration’,‘Beijing’);IFvar=-1THENDBMS_OUTPUT.PUT_LINE(demo_pack.v_sqlerr);ELSIFvar=0THENDBMS_OUTPUT.PUT_LINE(‘该部门记录已经存在！’);ELSEDBMS_OUTPUT.PUT_LINE(‘添加记录成功！’);Demo_pack.query_dept(90);DBMS_OUTPUT.PUT_LINE(demo_pack.DeptRec.deptno||’---‘||demo_pack.DeptRec.dname||’---‘||demo_pack.DeptRec.loc);var:=demo_pack.remove_dept(90);IFvar=-1THENDBMS_OUTPUT.PUT_LINE(demo_pack.v_sqlerr);ELSEDBMS_OUTPUT.PUT_LINE(‘删除记录成功！’);ENDIF;ENDIF;END;例2:创建包emp_package-60- 60CREATEORREPLACEPACKAGEemp_packageISTYPEemp_table_typeISTABLEOFemp%ROWTYPEINDEXBYBINARY_INTEGER;PROCEDUREread_emp_table(p_emp_tableOUTemp_table_type);ENDemp_package;CREATEORREPLACEPACKAGEBODYemp_packageISPROCEDUREread_emp_table(p_emp_tableOUTemp_table_type)ISIBINARY_INTEGER:=0;BEGINFORemp_recordIN(SELECT*FROMemp)LOOPP_emp_table(i):=emp_record;I:=I+1;ENDLOOP;ENDread_emp_table;ENDemp_package;DECLAREE_tableemp_package.emp_table_type;BEGINEmp_package.read_emp_table(e_table);FORIINe_table.FIRST..e_table.LASTLOOPDBMS_OUTPUT.PUT_LINE(e_table(i).empno||’‘||e_table(i).ename);ENDLOOP;END;§7.5子程序重载PL/SQL允许对包内子程序和本地子程序进行重载。所谓重载时指两个或多个子程序有相同的名称，但拥有不同的参数变量、参数顺序或参数数据类型。例：CREATEORREPLACEPACKAGEdemo_pack1ISDeptRecdept%ROWTYPE;V_sqlcodeNUMBER;V_sqlerrVARCHAR2(2048);FUNCTIONquery_dept(dept_noINNUMBER)RETURNINTEGER;FUNCTIONquery_dept(dept_noINVARCHAR2)-60- 60RETURNINTEGER;ENDdemo_pack1;CREATEORREPLACEPACKAGEBODYdemo_pack1ISFUNCTIONcheck_dept(dept_noNUMBER)RETURNINTEGERISFlagINTEGER;BEGINSELECTCOUNT(*)INTOflagFROMdeptWHEREdeptno=dept_no;IFflag>0THENRETURN1;ELSERETURN0;ENDIF;ENDcheck_dept;FUNCTIONcheck_dept(dept_noVARCHAR2)RETURNINTEGERISFlagINTEGER;BEGINSELECTCOUNT(*)INTOflagFROMdeptWHEREdeptno=dept_no;IFflag>0THENRETURN1;ELSERETURN0;ENDIF;ENDcheck_dept;FUNCTIONquery_dept(dept_noINNUMBER)RETURNINTEGERISBEGINIFcheck_dept(dept_no)=1THENSELECT*INTODeptRecFROMdeptWHEREdeptno=dept_no;RETURN1;ELSERETURN0;ENDIF;ENDquery_dept;FUNCTIONquery_dept(dept_noINVARCHAR2)-60- 60RETURNINTEGERISBEGINIFcheck_dept(dept_no)=1THENSELECT*INTODeptRecFROMdeptWHEREdeptno=dept_no;RETURN1;ELSERETURN0;ENDIF;ENDquery_dept;ENDdemo_pack1;§7.6删除过程、函数和包1．删除过程我们可以DROPPROCEDURE命令对不需要的过程进行删除，语法如下：DROPPROCEDURE[user.]Procudure_name;2．删除函数我们可以DROPFUNCTION命令对不需要的函数进行删除，语法如下：DROPFUNCTION[user.]Function_name;3．删除包我们可以DROPPACKAGE命令对不需要的包进行删除，语法如下：DROPPACKAGE[BODY][user.]package_name;DROPPROCEDUREOpenCurType;DROPPACKAGEdemo_pack;DROPPACKAGEemp_package;§7.7包的管理DBA_SOURCE,USER_SOURCE,USER_ERRORS,DBA-OBJECTS-60- 60第八章触发器触发器是许多关系数据库系统都提供的一项技术。在ORACLE系统里，触发器类似过程和函数，都有声明，执行和异常处理过程的PL/SQL块。§8.1触发器类型触发器在数据库里以独立的对象存储，它与存储过程不同的是，存储过程通过其它程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。§8.1.1DML触发器ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。§8.1.2替代触发器由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。它就是ORACLE8专门为进行视图操作的一种处理方法。§8.1.3系统触发器ORACLE8i提供了第三种类型的触发器叫系统触发器。它可以在ORACLE数据库系统的事件中进行触发，如ORACLE系统的启动与关闭等。触发器组成:l触发事件：即在何种情况下触发TRIGGER;例如：INSERT,UPDATE,DELETE。l触发时间：即该TRIGGER是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER的操作顺序。l触发器本身：即该TRIGGER被触发之后的目的和意图，正是触发器本身要做的事情。例如：PL/SQL块。l触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。§8.2创建触发器创建触发器的一般语法是:CREATE[ORREPLACE]TRIGGERtrigger_name{BEFORE|AFTER|INSTEADOF}-60- 60{INSERT|DELETE|UPDATE[OFcolumn[,column…]]}ON{[schema.]table_name|[schema.]view_name}[REFERENCING{OLD[AS]old|NEW[AS]new|PARENTasparent}][FOREACHROW][WHENcondition]trigger_body;其中：BEFORE和AFTER指出触发器的触发时序分别为前触发和后触发方式，前触发是在执行触发事件之前触发当前所创建的触发器，后触发是在执行触发事件之后触发当前所创建的触发器。INSTEADOF选项使ORACLE激活触发器，而不执行触发事件。只能对视图和对象视图建立INSTEADOF触发器，而不能对表、模式和数据库建立INSTEADOF触发器。FOREACHROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOREACHROW选项时，BEFORE和AFTER触发器为语句触发器，而INSTEADOF触发器则为行触发器。REFERENCING子句说明相关名称，在行触发器的PL/SQL块和WHEN子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。WHEN子句说明触发约束条件。Condition为一个逻辑表达时，其中必须包含相关名称，而不能包含查询语句，也不能调用PL/SQL函数。WHEN子句指定的触发约束条件只能用在BEFORE和AFTER行触发器中，不能用在INSTEADOF行触发器和其它类型的触发器中。当一个基表被修改(INSERT,UPDATE,DELETE)时要执行的存储过程，执行时根据其所依附的基表改动而自动触发，因此与应用程序无关，用数据库触发器可以保证数据的一致性和完整性。每张表最多可建立12种类型的触发器，它们是:BEFOREINSERTBEFOREINSERTFOREACHROWAFTERINSERTAFTERINSERTFOREACHROWBEFOREUPDATEBEFOREUPDATEFOREACHROWAFTERUPDATEAFTERUPDATEFOREACHROWBEFOREDELETEBEFOREDELETEFOREACHROWAFTERDELETEAFTERDELETEFOREACHROW-60- 60§8.2.1触发器触发次序1.执行BEFORE语句级触发器;2.对与受语句影响的每一行：l执行BEFORE行级触发器l执行DML语句l执行AFTER行级触发器 3.执行AFTER语句级触发器§8.2.2创建DML触发器触发器名与过程名和包的名字不一样，它是单独的名字空间，因而触发器名可以和表或过程有相同的名字，但在一个模式中触发器名不能相同。触发器的限制lCREATETRIGGER语句文本的字符长度不能超过32KB；l触发器体内的SELECT语句只能为SELECT…INTO…结构，或者为定义游标所使用的SELECT语句。l触发器中不能使用数据库事务控制语句COMMIT;ROLLBACK,SVAEPOINT语句；l由触发器所调用的过程或函数也不能使用数据库事务控制语句；l触发器中不能使用LONG,LONGRAW类型；l触发器内可以参照LOB类型列的列值，但不能通过:NEW修改LOB列中的数据；l触发器所访问的表受到表的约束限制，即后面的“变化表”。问题：当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值.实现::new修饰符访问操作完成后列的值:old修饰符访问操作完成前列的值特性INSERTUPDATEDELETEOLDNULL有效有效NEW有效有效NULL例1:建立一个触发器,当职工表emp表被删除一条记录时，把被删除记录写到职工表删除日志表中去。CREATETABLEemp_hisASSELECT*FROMEMPWHERE1=2;CREATEORREPLACETRIGGERdel_empBEFOREDELETEONscott.empFOREACHROWBEGIN--将修改前数据插入到日志记录表del_emp,以供监督使用。INSERTINTOemp_his(deptno,empno,ename,job,mgr,sal,comm,hiredate)VALUES(:old.deptno,:old.empno,:old.ename,:old.job,:old.mgr,:old.sal,:old.comm,:old.hiredate);END;-60- 60DELETEempWHEREempno=7788;DROPTABLEemp_his;DROPTRIGGERdel_emp;§8.2.3创建替代(Instead_of)触发器INSTEAD_OF用于对视图的DML触发，由于视图有可能是由多个表进行联结(join)而成，因而并非是所有的联结都是可更新的。但可以按照所需的方式执行更新，例如下面情况：CREATEORREPLACEVIEWemp_viewASSELECTdeptno,count(*)total_employeer,sum(sal)total_salaryFROMempGROUPBYdeptno;在此视图中直接删除是非法：SQL>DELETEFROMemp_viewWHEREdeptno=10;DELETEFROMemp_viewWHEREdeptno=10*ERROR位于第1行:ORA-01732:此视图的数据操纵操作非法但是我们可以创建INSTEAD_OF触发器来为DELETE操作执行所需的处理，即删除EMP表中所有基准行：CREATEORREPLACETRIGGERemp_view_deleteINSTEADOFDELETEONemp_viewFOREACHROWBEGINDELETEFROMempWHEREdeptno=:old.deptno;ENDemp_view_delete;DELETEFROMemp_viewWHEREdeptno=10;DROPTRIGGERemp_view_delete;DROPVIEWemp_view;§8.2.3创建系统事件触发器ORACLE8i提供的系统事件触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语言，如CREATE、ALTER及DROP等。而数据库系统事件包括数据库服务器的启动或关闭，用户的登录与退出、数据库服务错误等。创建系统触发器的语法如下：CREATEORREPLACETRIGGER[sachema.]trigger_name{BEFORE|AFTER}{ddl_event_list|database_event_list}ON{DATABASE|[schema.]SCHEMA}[WHEN_clause]-60- 60trigger_body;其中:ddl_event_list：一个或多个DDL事件，事件间用OR分开；database_event_list：一个或多个数据库事件，事件间用OR分开；系统事件触发器既可以建立在一个模式上，又可以建立在整个数据库上。当建立在模式之上时，只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器；当建立在数据库之上时，该数据库所有用户的DDL操作和他们所导致的错误，以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器时，要求用户具有ADMINISTERDATABASETRIGGER权限。下面给出系统触发器的种类和事件出现的时机（前或后）：事件允许的时机说明启动STARTUP之后实例启动时激活关闭SHUTDOWN之前实例正常关闭时激活服务器错误SERVERERROR之后只要有错误就激活登录LOGON之后成功登录后激活注销LOGOFF之前开始注销时激活创建CREATE之前，之后在创建之前或之后激活撤消DROP之前，之后在撤消之前或之后激活变更ALTER之前，之后在变更之前或之后激活系统触发器可以在数据库级（database）或模式（schema）级进行定义。数据库级触发器在任何事件都激活触发器，即触发对象为整个数据库中所有用户产生的每个指定事件；而模式触发器只有在指定的模式所产生的的触发事件发生时才触发，默认时为当前用户模式。§8.2.4系统触发器事件属性事件属性事件Startup/ShutdownServererrorLogon/LogoffDDLDML事件名称üüüüü数据库名称ü数据库实例号ü错误号ü用户名ü模式对象类型ü模式对象名称ü列ü除DML语句的列属性外，其余事件属性值可通过调用ORACLE定义的事件属性函数来读取。函数名称数据类型说明SyseventVARCHAR2（20）激活触发器的事件名称Instance_numNUMBER数据库实例名Database_nameVARCHAR2（50）数据库名称Server_error(posi)NUMBER错误信息栈中posi指定位置中的错误号Is_servererror(err_number)BOOLEAN-60- 60检查err_number指定的错误号是否在错误信息栈中，如果在则返回TRUE，否则返回FALSE。在触发器内调用此函数可以判断是否发生指定的错误。Login_userVARCHAR2(30)登陆或注销的用户名称Dictionary_obj_typeVARCHAR2(20)DDL语句所操作的数据库对象类型Dictionary_obj_nameVARCHAR2(30)DDL语句所操作的数据库对象名称Dictionary_obj_ownerVARCHAR2(30)DDL语句所操作的数据库对象所有者名称Des_encrypted_passwordVARCHAR2(2)正在创建或修改的经过DES算法加密的用户口令§8.2.5使用触发器谓词ORACLE提供三个参数INSERTING,UPDATING,DELETING用于判断触发了哪些操作。谓词行为INSERTING如果触发语句是INSERT语句，则为TRUE,否则为FALSEUPDATING如果触发语句是UPDATE语句，则为TRUE,否则为FALSEDELETING如果触发语句是DELETE语句，则为TRUE,否则为FALSE§8.2.6重新编译触发器如果在触发器内调用其它函数或过程，当这些函数或过程被删除或修改后，触发器的状态将被标识为无效。当DML语句激活一个无效触发器时，ORACLE将重新编译触发器代码，如果编译时发现错误，这将导致DML语句执行失败。在PL/SQL程序中可以调用ALTERTRIGGER语句重新编译已经创建的触发器，格式为：ALTERTRIGGER[schema.]trigger_nameCOMPILE[DEBUG]其中：DEBUG选项要器编译器生成PL/SQL程序条使其所使用的调试代码。§8.3删除和使能触发器l删除触发器：DROPTRIGGERtrigger_name;当删除其他用户模式中的触发器名称，需要具有DROPANYTRIGGER系统权限，当删除建立在数据库上的触发器时，用户需要具有ADMINISTERDATABASETRIGGER系统权限。此外，当删除表或视图时，建立在这些对象上的触发器也随之删除。l使能触发器数据库TRIGGER的状态：有效状态(ENABLE)：当触发事件发生时，处于有效状态的数据库触发器TRIGGER将被触发。无效状态(DISABLE)：当触发事件发生时，处于无效状态的数据库触发器TRIGGER将不会被触发，此时就跟没有这个数据库触发器(TRIGGER)一样。数据库TRIGGER的这两种状态可以互相转换。格式为：ALTERTIGGERtrigger_name[DISABLE|ENABLE];-60- 60例：ALTERTRIGGERemp_view_deleteDISABLE;ALTERTRIGGER语句一次只能改变一个触发器的状态，而ALTERTABLE语句则一次能够改变与指定表相关的所有触发器的使用状态。格式为：ALTERTABLE[schema.]table_name{ENABLE|DISABLE}ALLTRIGGERS;例：使表EMP上的所有TRIGGER失效：ALTERTABLEempDISABLEALLTRIGGERS;§8.4触发器和数据字典相关数据字典：USER_TRIGGERS、ALL_TRIGGERS、DBA_TRIGGERSCOLtrigger_nameFORMATA10COLtrigger_typeFORMATA10COLtriggering_eventFORMATA10COLtable_ownerFORMATA10COLbase_object_typeFORMATA10COLreferencing_namesFORMATA10COLstatusFORMATA10COLaction_typeFORMATA10SELECTTRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,TABLE_OWNER,BASE_OBJECT_TYPE,REFERENCING_NAMES,STATUS,ACTION_TYPEFROMuser_triggers;§8.5数据库触发器的应用举例例5：利用ORACLE事件属性函数，创建一个系统事件触发器。首先创建一个事件日志表eventlog，由它存储用户在当前数据库中所创建的数据库对象，以及用户的登陆和注销、数据库的启动和关闭等事件，之后创建trig_ddl、trig_before和trig_after触发器，它们调用事件属性函数将各个事件记录到eventlog数据表中。由于在PL/SQL块中不能直接调用DDL语句，所以，利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程，由它执行DDL语句创建触发器。BEGIN--创建用于记录事件日志的数据表DBMS_UTILITY.EXEC_DDL_STATEMENT(‘CREATETABLEeventlog(EventnameVARCHAR2(20)NOTNULL,Eventdatedatedefaultsysdate,Inst_numNUMBERNULL,-60- 60Db_nameVARCHAR2(50)NULL,Srv_errorNUMBERNULL,UsernameVARCHAR2(30)NULL,Obj_typeVARCHAR2(20)NULL,Obj_nameVARCHAR2(30)NULL,Obj_ownerVARCHAR2(30)NULL)‘);--创建DDL触发器trig4_ddlDBMS_UTILITY.EXEC_DDL_STATEMENT(‘CREATEORREPLACETRIGGERtrig_ddlAFTERCREATEORALTERORDROPONDATABASEDECLAREEventVARCHAR2(20);TypVARCHAR2(20);NameVARCHAR2(30);OwnerVARCHAR2(30);BEGIN--读取DDL事件属性Event:=SYSEVENT;Typ:=DICTIONARY_OBJ_TYPE;Name:=DICTIONARY_OBJ_NAME;Owner:=DICTIONARY_OBJ_OWNER;--将事件属性插入到事件日志表中INSERTINTOscott.eventlog(eventname,obj_type,obj_name,obj_owner)VALUES(event,typ,name,owner);END;‘);--创建LOGON、STARTUP和SERVERERROR事件触发器DBMS_UTILITY.EXEC_DDL_STATEMENT(‘CREATEORREPLACETRIGGERtrig_afterAFTERLOGONORSTARTUPORSERVERERRORONDATABASEDECLAREEventVARCHAR2(20);InstanceNUMBER;Err_numNUMBER;DbnameVARCHAR2(50);UserVARCHAR2(30);BEGINEvent:=SYSEVENT;-60- 60IFevent=‘’LOGON’’THENUser:=LOGIN_USER;INSERTINTOeventlog(eventname,username)VALUES(event,user);ELSIFevent=‘’SERVERERROR’’THENErr_num:=SERVER_ERROR(1);INSERTINTOeventlog(eventname,srv_error)VALUES(event,err_num);ELSEInstance:=INSTANCE_NUM;Dbname:=DATABASE_NAME;INSERTINTOeventlog(eventname,inst_num,db_name)VALUES(event,instance,dbname);ENDIF;END;‘);--创建LOGOFF和SHUTDOWN事件触发器DBMS_UTILITY.EXEC_DDL_STATEMENT(‘CREATEORREPLACETRIGGERtrig_beforeBEFORELOGOFFORSHUTDOWNONDATABASEDECLAREEventVARCHAR2(20);InstanceNUMBER;DbnameVARCHAR2(50);UserVARCHAR2(30);BEGINEvent:=SYSEVENT;IFevent=‘’LOGOFF’’THENUser:=LOGIN_USER;INSERTINTOeventlog(eventname,username)VALUES(event,user);ELSEInstance:=INSTANCE_NUM;Dbname:=DATABASE_NAME;INSERTINTOeventlog(eventname,inst_num,db_name)VALUES(event,instance,dbname);ENDIF;END;‘);END;CREATETABLEmydata(mydateNUMBER);-60- 60CONNECTSCOTT/TIGERCOLeventnameFORMATA10COLeventdateFORMATA12COLusernameFORMATA10COLobj_typeFORMATA15COLobj_nameFORMATA15COLobj_ownerFORMATA10SELECTeventname,eventdate,obj_type,obj_name,obj_owner,username,Srv_errorFROMeventlog;DROPTRIGGERtrig_ddl;DROPTRIGGERtrig_before;DROPTRIGGERtrig_after;DROPTABLEeventlog;-60-