《PLSQL程序设计培训教程.ppt》由会员分享，可在线阅读，更多相关《PLSQL程序设计培训教程.ppt（104页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、PLSQL程序设计培训教程1-2SQL语句每次只能执行一条，不适应开发功能完备的语句每次只能执行一条，不适应开发功能完备的数据库应用程序，数据库应用程序，Oracle提供了自己的过程化语言提供了自己的过程化语言PL/SQL。本章主要内容包括：本章主要内容包括：PL/SQL基础基础 PL/SQL控制结构控制结构 PL/SQL记录和表记录和表 游标游标 过程与函数过程与函数 触发器触发器 异常处理异常处理1-31-41-51-65.1.3 表达式表达式主要指赋值表达式，语法如下：主要指赋值表达式，语法如下：变量：变量：=表达式，例如：表达式，例如：Date_of_today:=sysdate;As
2、tring:=farewell|to arms;Area:=pi*radius*2;表达式中的常用符号表达式中的常用符号数学运算符数学运算符:+、-、*、/、*布尔运算符布尔运算符:=、=其他符号其他符号:.、|、&、“、”1-75.1.4 PL/SQL程序块结构程序块结构可选的块声明、块体部分和可选的异常处理部分可选的块声明、块体部分和可选的异常处理部分:DECLARE-块声明部分块声明部分BEGIN-块体的可执行部分块体的可执行部分EXCEPTION-异常处理部分异常处理部分END;1-8一个一个PL/SQL块的简单例子块的简单例子SET SERVEROUTPUT ONDECLARE Pi
3、 CONSTANT REAL:=3.1415926;Circumference REAL;Area REAL;radius REAL:=&Radius;BEGIN Circumference:=Pi*radius*2.0;Area:=Pi*radius*2;DBMS_OUTPUT.enable;1-9DBMS_OUTPUT.put_line(Radius=|TO_CHAR(radius)|,Circumference=|TO_CHAR(Circumference)|,Area=|TO_CHAR(Area);END;1-10图图5.2显示了显示了PL/SQL引擎在引擎在Oracle服务器中的位置
4、服务器中的位置Oracle服务器服务器PL/SQL引擎引擎PL/SQL块过程化语句执行器SQL语句执行器图图5.21-11不论使用哪种工具，例如不论使用哪种工具，例如SQL*Plus或或SQL*Plus WorkSheet，该工具必须向该工具必须向Oracle服务器提交服务器提交PL/SQL源代码，源代码，PL/SQL引擎扫描、分析并编译代引擎扫描、分析并编译代码，然后预备执行编译后的代码。在执行期间，将码，然后预备执行编译后的代码。在执行期间，将所有的所有的SQL语句传递给语句传递给SQL语句执行器组件执行。语句执行器组件执行。SQL语句执行器执行语句执行器执行SQL或或DML语句。语句。P
5、L/SQL引引擎可以使用由查询提取的数据集进行更进一步的处擎可以使用由查询提取的数据集进行更进一步的处理。理。使用使用PL/SQL块后不必每执行一条块后不必每执行一条SQL语句都要经语句都要经过一次网络，因而相对于逐条发送一组过一次网络，因而相对于逐条发送一组SQL语句，语句，大大降低了网络传输量。大大降低了网络传输量。1-12另外，在另外，在PL/SQL块中，可以把块中，可以把SQL/DML语句当作语句当作一个事务对待。如果整个事务成功，那么对数据库一个事务对待。如果整个事务成功，那么对数据库的全部改动会被提交。如果事务的任何部分失败，的全部改动会被提交。如果事务的任何部分失败，整个事务会被
6、回滚。因为整个事务会被回滚。因为PL/SQL块中可以包含复块中可以包含复杂的逻辑，所以在服务器上执行，于是客户程序的杂的逻辑，所以在服务器上执行，于是客户程序的大小和复杂程度也降低了。大小和复杂程度也降低了。1-13PL/SQL块能执行复杂的逻辑和错误处理。一个客块能执行复杂的逻辑和错误处理。一个客户应用中可以嵌入一个简单的匿名或未命名的块户应用中可以嵌入一个简单的匿名或未命名的块（没有使用名字标记的（没有使用名字标记的PL/SQL代码块）并激活这代码块）并激活这些些PL/SQL块。这种能力通常称为远程过程调用块。这种能力通常称为远程过程调用（RPC）。）。PL/SQL块还可以调用其他块还可以
7、调用其他PL/SQL块。块。因为这些因为这些PL/SQL块已经被编译并被开发者很好地块已经被编译并被开发者很好地调试过，所以它们提供了显著的性能改进。并且通调试过，所以它们提供了显著的性能改进。并且通过为其他应用或模块提供可重复使用的块，减少了过为其他应用或模块提供可重复使用的块，减少了应用开发。应用开发。1-14当编译任意当编译任意PL/SQL程序时，不管是命名块还是未程序时，不管是命名块还是未命名块的代码，源代码和对象代码都被告诉缓存在命名块的代码，源代码和对象代码都被告诉缓存在共享共享SQL区中。分配给一个区中。分配给一个PL/SQL块的空间叫做块的空间叫做一个游标。服务器使用最近最少使
8、用算法，在共享一个游标。服务器使用最近最少使用算法，在共享SQL区保存缓存的程序直到它过期失效。在区保存缓存的程序直到它过期失效。在PL/SQL块中的任何块中的任何SQL语句都被给予各自的共享语句都被给予各自的共享SQL区。当一个命名子程序被编译时，它的源代码区。当一个命名子程序被编译时，它的源代码还被保存到数据字典中。包含在一个子程序中的代还被保存到数据字典中。包含在一个子程序中的代码是可重入的，也就是说，它在所有连接的用户间码是可重入的，也就是说，它在所有连接的用户间是共享的。是共享的。1-15当提交一个未命名的当提交一个未命名的PL/SQL块给服务器执行时，块给服务器执行时，服务器通过比
9、较源代码文本，决定在高速缓存中是服务器通过比较源代码文本，决定在高速缓存中是否有该程序块。如果代码文本字符之间（包括大小否有该程序块。如果代码文本字符之间（包括大小写）完全相同，则能够简单执行缓存的分析代码。写）完全相同，则能够简单执行缓存的分析代码。否则，必须首先分析新的语句。通过共享可执行代否则，必须首先分析新的语句。通过共享可执行代码，一个基于服务器的应用能够真正地实现内存节码，一个基于服务器的应用能够真正地实现内存节省，这在拥有数百个连接用户时尤其重要。省，这在拥有数百个连接用户时尤其重要。1-165.2 PL/SQL控制结构控制结构控制结构是所有程序设计语言的核心。控制结构是所有程序
10、设计语言的核心。PL/SQL程序程序可以使用顺序结构、选择结构、可以使用顺序结构、选择结构、NULL结构和循环结结构和循环结构来控制程序流。构来控制程序流。5.2.1 顺序结构顺序结构5.2.2 选择结构选择结构:1.If-then(允许嵌套允许嵌套)If var1var2 then DBMS_OUTPUT.put_line(var1 is larger than var2);end if;1-17在在PL/SQL中实现中实现if逻辑有两条规则：逻辑有两条规则：规则规则1 每个每个if语句都有自己的语句都有自己的then，以以if开始的语开始的语句行后面不跟语句结束符（句行后面不跟语句结束符（
11、;）。）。规则规则2 每个每个if语句块以相应的语句块以相应的end if结束。结束。2 if-then-else（也可以嵌套）也可以嵌套）If var1var2 then DBMS_OUTPUT.put_line(var1 is larger than var2);elseDBMS_OUTPUT.put_line(var1 is not larger than var2);end if;1-18规则规则3每个每个if语句有且只有一个语句有且只有一个else。规则规则4Else语句行后面不跟语句结束符。语句行后面不跟语句结束符。3 if-then-elsif这种结构用于替代嵌套这种结构用于替代
12、嵌套if-then-else结构。例子如结构。例子如下页所示。下页所示。1-19If var1var2 thenDBMS_OUTPUT.put_line(var1 is larger than var2);elsif var1=var2 thenDBMS_OUTPUT.put_line(var1 is equal to var2);elseDBMS_OUTPUT.put_line(var1 is smaller than var2);End if;规则规则5elsif无匹配的无匹配的end if。1-205.2.3 NULL结构结构空操作语句。空操作语句。If mark60 thennull;
13、else insert into student_course values();end if;1-215.2.4 循环结构循环结构1 LOOP-EXIT-END循环，由循环，由3部分组成，其语法部分组成，其语法如下：如下：loop 执行语句执行语句1；执行语句执行语句2；end loop一般这种循环结构的终止条件通过在执行语句中加一般这种循环结构的终止条件通过在执行语句中加入入EXIT或或EXIT WHEN来实现。例如下页的例子来实现。例如下页的例子1-22 SET SERVEROUTPUT ONDECLARE Ctr INTEGER:=0;BEGIN DBMS_OUTPUT.enable;
14、loop DBMS_OUTPUT.put(Ctr|);Ctr:=Ctr+1;EXIT WHEN Ctr=10;end loop;DBMS_OUTPUT.put_line(Loop Exited);END;1-232 WHILE-LOOP-END循环循环语法如下：语法如下：while 布尔表达式布尔表达式 loop 执行语句执行语句1；执行语句执行语句2；end loop;1-24一个一个WHILE循环的例子循环的例子 SET SERVEROUTPUT ONDECLARE Ctr INTEGER:=0;BEGIN while Ctr10 loop DBMS_OUTPUT.put(Ctr|);Ct
15、r:=Ctr+1;end loop;DBMS_OUTPUT.put_line(Loop Exited);END;1-253 FOR-IN-LOOP-END循环循环语法格式为：语法格式为：for 计数循环变量计数循环变量 in reverse 起始值起始值.终止值终止值 loop 执行语句执行语句1；执行语句执行语句2；end loop;1-26一个一个FOR循环结构的例子：循环结构的例子：SET SERVEROUTPUT ONBEGIN DBMS_OUTPUT.enable;for ctr in 0.9 loop DBMS_OUTPUT.put(Ctr|);end loop;DBMS_OUTP
16、UT.put_line(Loop Exited);END;1-275.3 PL/SQL记录和表记录和表用户自定义类型就是记录类型，用户自定义类型就是记录类型，PL/SQL记录是用户自记录是用户自定义类型，定义类型，PL/SQL表是由用户定义类型来说明的表，表是由用户定义类型来说明的表，本节介绍如何利用本节介绍如何利用%TYPE属性和属性和%ROWTYPE属性、属性、怎样声明和使用基本的记录类型和怎样声明和使用基本的记录类型和PL/SQL表。表。%TYPE和和%ROWTYPE属性的区别在于：前者用于基属性的区别在于：前者用于基本数据类型，而后者则用于记录类型和表结构。本数据类型，而后者则用于记录
17、类型和表结构。5.3.1 使用使用%TYPE%TYPE属性用于声明变量、常量、记录中的字段以及属性用于声明变量、常量、记录中的字段以及数据库列，表属性或其它程序结构相匹配的记录变量数据库列，表属性或其它程序结构相匹配的记录变量1-28使用使用%TYPE的一个例子：的一个例子：DECLAREStudent_No INTEGER;Course_No Stduent_No%TYPE;可以看出，使用可以看出，使用%TYPE可以获取它修饰的变量的数可以获取它修饰的变量的数据类型从而作为别的变量的数据类型。据类型从而作为别的变量的数据类型。1-295.3.2 记录类型记录类型声明记录类型的一般语句：声明记
18、录类型的一般语句：TYPE 记录类型记录类型 IS RECORD（字段字段1 数据类型数据类型 NOT NULL DEFAULT ：=表表达式达式），字段，字段2)1-30一个声明记录的例子一个声明记录的例子SET SERVEROUTPUT ONDECLARE TYPE StudentRecord IS RECORD(StudentNo INTERGER,Name VARCHAR2(10),Gender CHAR(1),HomeTown VARCHAR2(20),Study VARCHAR2(10),Activity Study%TYPE );Student1 StudentRecord;1-
19、315.3.3 使用使用%ROWTYPE%ROWTYPE属性声明用于记录类型和表结构。属性声明用于记录类型和表结构。5.3.4 表表PL/SQL表是一个由用户定义类型来说明的表。其语法表是一个由用户定义类型来说明的表。其语法格式如下：格式如下：TYPE PL/SQL 表名表名 IS TABLE OF 数据类型，例如数据类型，例如DECLARETYPE StudentName IS TABLE OF StudentInfo.Name%TYPE;1-325.4 游标游标PL/SQL用游标来管理用游标来管理SQL的的SELECT语句。它是为语句。它是为处理这些语句而分配的一个具有结构的内存区。游标处
20、理这些语句而分配的一个具有结构的内存区。游标定义类似于其他定义类似于其他PL/SQL变量，并且必须遵守同样的命变量，并且必须遵守同样的命名规则。名规则。1-335.4.1 游标基本操作游标基本操作游标是一个对象，能够提供行级的游标是一个对象，能够提供行级的SQL语句控制。语句控制。游标声明不同于变量，而是用于实现游标的一个内游标声明不同于变量，而是用于实现游标的一个内存区域的句柄。游标声明仅仅定义了将提交给存区域的句柄。游标声明仅仅定义了将提交给SQL语句执行器哪些查询，在可执行代码中的程序控制语句执行器哪些查询，在可执行代码中的程序控制下进行查询的管理。游标可以表示任意合法的下进行查询的管理
21、。游标可以表示任意合法的SQL SELECT语句。游标通常是任何语句。游标通常是任何PL/SQL应用的基应用的基本构建块，它们为数据库中存储的数据集合上的操本构建块，它们为数据库中存储的数据集合上的操作提供了循环机制。如果还需要更新，使用作提供了循环机制。如果还需要更新，使用FOR UPDATE子句。子句。1-341 声明游标声明游标游标的声明包括两个部分：游标名称和这个游标所游标的声明包括两个部分：游标名称和这个游标所用到的用到的SQL语句。语句。PL/SQL语言中语法格式为：语言中语法格式为：CURSOR 游标名称游标名称 IS 查询语句查询语句(SELECT语句语句);例如，例如，CUR
22、SOR Student_list IS SELECT StudentNo FROM StudentInfo ORDER BY StudentNo;如同其它变量的声明一样，声明游标的这一段代码如同其它变量的声明一样，声明游标的这一段代码是不执行的，不能将是不执行的，不能将DEBUG时的断点设在这一代时的断点设在这一代码行上，也不能用码行上，也不能用IFEND IF语句来声明两个同名语句来声明两个同名的游标。的游标。1-352 打开游标打开游标要使用一个已经声明的游标，需要先打开它。可以要使用一个已经声明的游标，需要先打开它。可以使用使用PL/SQL的的OPEN语句，其语法如下：语句，其语法如下：
23、OPEN 游标名称；游标名称；当打开一个游标时，和该游标相关的当打开一个游标时，和该游标相关的SQL语句就会语句就会传递到传递到Oracle平台上，并得到执行，例如打开上文平台上，并得到执行，例如打开上文的游标；的游标；OPEN Student_list;1-363 提取数据提取数据使用使用FETCH语句从游标中取出数据。该语句每次语句从游标中取出数据。该语句每次返回一行数据。每次取数据时，返回一行数据。每次取数据时，PL/SQL都将指向都将指向通过游标查询要返回行的下一行数据。其语法为：通过游标查询要返回行的下一行数据。其语法为：FETCH 游标名称游标名称INTO 变量变量1，变量，变量2
24、，;其中，变量用于存储查询结果。例如，要将上文中其中，变量用于存储查询结果。例如，要将上文中的游标的游标Student_list中的数据存入记录型变量中的数据存入记录型变量StudentA中，使用如下语句：中，使用如下语句：FETCH Student_listINTO StudentA;1-37使用使用FETCH语句时需注意以下几点：语句时需注意以下几点：对游标执行对游标执行FETCH语句，可以从工作区中的第一语句，可以从工作区中的第一条记录开始通过拨动指针顺次取出全部记录。条记录开始通过拨动指针顺次取出全部记录。工作区内的游标指针只能向下移动，不能回退，要工作区内的游标指针只能向下移动，不能
25、回退，要想回退，必须关闭游标后再重新打开。想回退，必须关闭游标后再重新打开。在使用在使用FETCH语句之前，必须先打开游标，这样语句之前，必须先打开游标，这样才能保证工作区内有数据。才能保证工作区内有数据。INTO子句中的变量个数、顺序、类型必须与工作子句中的变量个数、顺序、类型必须与工作区中每行记录的字段数、顺序及数据类型一一对应区中每行记录的字段数、顺序及数据类型一一对应1-384 关闭游标关闭游标使用完游标后应将其关闭，来释放占用的资源。其使用完游标后应将其关闭，来释放占用的资源。其语法为：语法为：CLOSE 游标名称游标名称例如，关闭游标例如，关闭游标Student_list的操作如下
26、：的操作如下：CLOSE Student_list;1-39一个完整使用游标的例子：一个完整使用游标的例子：DECLARECURSOR Student_list IS SELECT StudentNo FROM StudentInfo ORDER BY StudentNo;StudentA StudentInfo%ROWTYPE;BEGINOPEN Student_list;1-40loop FETCH Student_list INTO StudentA;EXIT WHEN Student_list%NOTFOUND;end loop;CLOSE Student_list;END;其中利用了
27、游标的其中利用了游标的%NOTFOUND属性来确定是否所有属性来确定是否所有的信息都已从工作区中取出。的信息都已从工作区中取出。1-415.4.2 游标的属性操作游标的属性操作下表对游标的各个属性进行了介绍下表对游标的各个属性进行了介绍属性属性返回值返回值说明说明ISOPENTRUE/FALSE指出游标是关闭的还是指出游标是关闭的还是打开的打开的FOUNDTRUE/FALSE指出是否发现一条记录指出是否发现一条记录NOTFOUNDTRUE/FALSE指出是否没发现一条记指出是否没发现一条记录录ROWCOUNTNUMBER每次提取记录的序数值每次提取记录的序数值（第一，第二，第三，（第一，第二，
28、第三，）属性使用的格式为：游标名属性使用的格式为：游标名属性名属性名，如：，如：If(Student_list%ISOPEN)then CLOSE Student_list;end if;1-425.4.3 参数化游标和隐式游标参数化游标和隐式游标上几节介绍的是普通形式的游标，不含有任何参数，上几节介绍的是普通形式的游标，不含有任何参数，使用前需要声明和打开，使用后需要关闭。下面介使用前需要声明和打开，使用后需要关闭。下面介绍两种特殊形式的游标。绍两种特殊形式的游标。参数化游标：参数化游标：参数化游标是需要一个或多个参数的游标，在运行参数化游标是需要一个或多个参数的游标，在运行时时PL/SQL
29、程序用它来定义游标的记录选择标准。程序用它来定义游标的记录选择标准。其声明语法如下：其声明语法如下：CURSOR 游标名称（字段游标名称（字段1 IN 数据类型数据类型：=|DEFAULT 初始化值初始化值),)RETURN 返回值返回值类型类型 IS 查询语句（查询语句（SELECT语句）；语句）；1-43对于隐式游标，用户无需进行声明、打开及关闭，对于隐式游标，用户无需进行声明、打开及关闭，只要简单地编码只要简单地编码SELECT语句并让语句并让PL/SQL根据需根据需要处理游标即可。与循环结合的隐式游标将允许用要处理游标即可。与循环结合的隐式游标将允许用户每次处理一行。当户每次处理一行。
30、当SELECT语句预计只返回一行语句预计只返回一行时，最适于采用隐式游标。如：时，最适于采用隐式游标。如：SELECT StudentNoINTO curStudentFROM StudentRecordWHERE Name=李新李新；1-44使用隐式游标要注意以下几点：使用隐式游标要注意以下几点：每个隐式游标必须有一个每个隐式游标必须有一个INTO。和显式游标一样，带有关键字和显式游标一样，带有关键字INTO，接收数据的接收数据的变量时，数据类型要与表列的一致。变量时，数据类型要与表列的一致。隐式游标一次仅返回一行，使用时必须检查异常。隐式游标一次仅返回一行，使用时必须检查异常。5.4.4
31、游标变量游标变量 所有所有PL/SQL里对游标的访问都是通过游标变量实里对游标的访问都是通过游标变量实现的，它总是特殊的数据类型现的，它总是特殊的数据类型:refcursor.1-451 创建一个游标变量创建一个游标变量创建一个游标变量除了可以把它声明为一个类型为创建一个游标变量除了可以把它声明为一个类型为refcursor的量。另外一个方法是使用游标声明语的量。另外一个方法是使用游标声明语法，通常是下面这样：法，通常是下面这样：游标变量名游标变量名 CURSOR （参数参数）IS SELECT 查查询语句；询语句；如果有参数，那么它是一个逗号分隔的如果有参数，那么它是一个逗号分隔的name
32、datatype配对的列表，它们定义那些将会用参数值配对的列表，它们定义那些将会用参数值替换掉的所给出查询中的名字。实际用于代换这些替换掉的所给出查询中的名字。实际用于代换这些名字的数值将在游标打开之后声明。例如：名字的数值将在游标打开之后声明。例如：1-46DECLARE curs1 refcursor;curs2 CURSOR IS SELECT*from tenk1;curs3 CURSOR(key int)IS SELECT*from tenk1 where unique1=key;所有这所有这3个变量都是个变量都是refcursor类型，但是第一个可类型，但是第一个可以用于任何查询，
33、而第二个已经绑定了一个声明完以用于任何查询，而第二个已经绑定了一个声明完整的查询，最后一个是绑定了一个带参数的查询整的查询，最后一个是绑定了一个带参数的查询（key将在游标被打开时被替换成一个整数）。变将在游标被打开时被替换成一个整数）。变量量curs1可以称之为未绑定的，因为它没有和任何可以称之为未绑定的，因为它没有和任何查询相绑定。查询相绑定。1-472 打开游标变量打开游标变量在使用游标检索行之前，必须先打开它（这是和在使用游标检索行之前，必须先打开它（这是和SQL命令命令DECLARE CURSOR相等的操作）。相等的操作）。PL/SQL有有4种形式的种形式的OPEN语句，两种用于未绑
34、定的语句，两种用于未绑定的游标变量，另外两种用于绑定的游标变量：游标变量，另外两种用于绑定的游标变量：OPEN FOR SELECT:OPEN 未绑定的游标变量未绑定的游标变量 FOR SELECT 查询语句；查询语句；该游标变量打开，并且执行给出的查询。游标不能是该游标变量打开，并且执行给出的查询。游标不能是已经打开的，并且它必须是一个声明为未绑定的游标已经打开的，并且它必须是一个声明为未绑定的游标（也就是说，声明为一个简单的（也就是说，声明为一个简单的refcursor变量）。变量）。SELECT查询是和其他在查询是和其他在PL/SQL里的里的SELECT查询查询平等对待的：先代换平等对待
35、的：先代换PL/SQL的变量名，而且查询规的变量名，而且查询规划为将来可能的复用缓存起来。例如：划为将来可能的复用缓存起来。例如：1-48OPEN curs1 IS SELECT*FROM foo WHERE key=mykey;OPEN FOR EXECUTEOPEN 未绑定的游标变量未绑定的游标变量 IS EXECUTE SELECT 查询语句；查询语句；打开游标变量并且执行给出的查询。游标不能是已打开游标变量并且执行给出的查询。游标不能是已打开的，并且必须声明为一个未绑定的游标（也就打开的，并且必须声明为一个未绑定的游标（也就是说，是一个简单的是说，是一个简单的refcursor变量）。
36、查询是用变量）。查询是用和那些用于和那些用于EXECUTE命令一样的方法声明的字串命令一样的方法声明的字串表达式，这样，就给予查询可以在两次运行间发生表达式，这样，就给予查询可以在两次运行间发生变化的灵活性。变化的灵活性。OPEN curs1 IS EXECUTE“SELECT*FROM”|quote_ident($1);1-49OPEN 一个绑定的查询一个绑定的查询OPEN 绑定的游标变量绑定的游标变量（参数值参数值）；这种形式的这种形式的OPEN用于打开一个游标变量，该游标用于打开一个游标变量，该游标变量的查询是在声明时和它绑定在一起的。游标不变量的查询是在声明时和它绑定在一起的。游标不能
37、是已经打开的。而且仅当该游标声明为接受参数能是已经打开的。而且仅当该游标声明为接受参数时，语句中才需要出现一个实际参数值表达式的列时，语句中才需要出现一个实际参数值表达式的列表。这些值将代换到查询中。一个绑定的游标的查表。这些值将代换到查询中。一个绑定的游标的查询计划总是认为可缓冲的询计划总是认为可缓冲的这种情况下没有等效这种情况下没有等效的的EXECUTE。OPEN curs2;OPEN curs3(42);1-503 使用游标变量使用游标变量用户一旦打开了一个游标，就可以进行如下操作：用户一旦打开了一个游标，就可以进行如下操作：这些操作不需要发生和打开该游标开始操作的同一这些操作不需要发生
38、和打开该游标开始操作的同一个函数里。用户可以从函数中返回一个个函数里。用户可以从函数中返回一个refcursor数值，然后让调用者操作该游标（在内部，数值，然后让调用者操作该游标（在内部，refcursor值只是一个包含用于该游标查询的信使值只是一个包含用于该游标查询的信使的字串名。这个名字可以传来传去，可以赋予其他的字串名。这个名字可以传来传去，可以赋予其他refcursor变量，也不用担心扰乱信使）。变量，也不用担心扰乱信使）。所有的信使在事务的结尾都会隐含地关闭。因此一所有的信使在事务的结尾都会隐含地关闭。因此一个个refcursor值只能在该事务结束前用于引用一个值只能在该事务结束前用
39、于引用一个打开的游标。其语法如下：打开的游标。其语法如下：FETCH 游标变量游标变量 INTO 目标目标 t;目标可以是一个行变量、一个记录变量，或者是一目标可以是一个行变量、一个记录变量，或者是一个逗号分隔的普通变量列表。个逗号分隔的普通变量列表。1-514 关闭游标变量关闭游标变量其语法为：其语法为：CLOSE cursor;CLOSE关闭一个打开的游标变量。这样就可以在关闭一个打开的游标变量。这样就可以在事务结束之前释放资源，或者释放掉该游标变量，事务结束之前释放资源，或者释放掉该游标变量，可以稍后再次打开。可以稍后再次打开。1-525.5 过程和函数过程和函数PL/SQL中的块主要有
40、命名块和匿名块两种类型。本章中的块主要有命名块和匿名块两种类型。本章前面的例子都是匿名块。两者之间区别在于：匿名块前面的例子都是匿名块。两者之间区别在于：匿名块没有自己的名称，以没有自己的名称，以DECLARE或或BEGIN开始，每次开始，每次使用时都要进行编译，另外，该类块不在数据库中存使用时都要进行编译，另外，该类块不在数据库中存储并且直接从其他的储并且直接从其他的PL/SQL块中调用；块中调用；1-53而命名块没有这些限制，可以存储在数据库中并在适而命名块没有这些限制，可以存储在数据库中并在适当的时候运行。命名块包括子程序、包和触发器等，当的时候运行。命名块包括子程序、包和触发器等，本节
41、集中讨论子程序，子程序就是有名称的本节集中讨论子程序，子程序就是有名称的PL/SQL程程序，包括过程与函数，可以接收参数并可被重复调用。序，包括过程与函数，可以接收参数并可被重复调用。还可以把过程和函数作为应用逻辑编译成二进制形式存还可以把过程和函数作为应用逻辑编译成二进制形式存储在储在Oracle数据库中，作为命名的模式对象。通过在数数据库中，作为命名的模式对象。通过在数据库中集成通用的过程和函数，任何应用程序都可以调据库中集成通用的过程和函数，任何应用程序都可以调用它们。用它们。1-545.5.1 过程创建和调用过程创建和调用语法如下：语法如下：CREATE OR REPLACE PROC
42、EDURE 过程名称过程名称（参数参数 IN|OUT|IN OUT 类型类型，参数参数 IN|OUT|IN OUT 类型类型）AUTHID CURRENT_USER|DESIGNER IS|AS 过程体过程体 1-55过程体是构成该过程代码的过程体是构成该过程代码的PL/SQL块。有关过程块。有关过程的参数和关键字的含义详见的参数和关键字的含义详见5.5.2节。节。在在CREATE关键字后加上关键字后加上OR REPLACE关键字是关键字是为了允许将撤消和重建这两步操作合并为一个操作。为了允许将撤消和重建这两步操作合并为一个操作。因为在创建一个过程时，有可能这个过程已经存在。因为在创建一个过程
43、时，有可能这个过程已经存在。为了修改过程的代码，首先必须将该过程撤消，然为了修改过程的代码，首先必须将该过程撤消，然后再重建。由于这种操作已经是开发过程的标准方后再重建。由于这种操作已经是开发过程的标准方式，所以关键字式，所以关键字OR REPLACE允许将撤消和重建允许将撤消和重建这两步操作合并为一个操作。如果过程存在，首先这两步操作合并为一个操作。如果过程存在，首先撤消该过程，而不给出任何警告提示。可以使用命撤消该过程，而不给出任何警告提示。可以使用命令令DROP PROCEDURE来撤消一个过程。如果该来撤消一个过程。如果该过程已经不存在，可以直接创建它，如果该过程已过程已经不存在，可以
44、直接创建它，如果该过程已经存在而没有关键字经存在而没有关键字OR REPLACE，则则CREATE语句将返回一条语句将返回一条Oracle错误信息。错误信息。1-56和其他的和其他的CREATE语句一样，创建过程是一种语句一样，创建过程是一种DDL操作。因此，在过程创建前和创建后，都要执行一操作。因此，在过程创建前和创建后，都要执行一条隐式的条隐式的COMMIT命令。这种操作可以通过使用关命令。这种操作可以通过使用关键字键字IS或或AS来实现，这两个关键字是等价的。来实现，这两个关键字是等价的。1-57过程体是一种带有声明部分、可执行语句部分和异过程体是一种带有声明部分、可执行语句部分和异常部
45、分的常部分的PL/SQL块，完整的过程结构如下所示：块，完整的过程结构如下所示：CREATE OR REPLACE PROCEDURE 过程名称过程名称参数列参数列AS/*过程体的声明部分过程体的声明部分*/BEGIN/*过程体的可执行部分过程体的可执行部分*/EXCEPTION/*过程体的异常部分过程体的异常部分*/END 过程名称过程名称1-58一个创建过程的简单例子，用于打印当前时间：一个创建过程的简单例子，用于打印当前时间：CREATE OR REPLACE PROCEDURE print_current_time ASCURTIME VARCHAR2(20);BEGIN SELECT
46、 TO_CHAR(sysdate,yyyy/mm/dd hh24:mi:ss)INTO CURTIME FROM dual;DBMS_OUTPUT.put_line(当前时间是：当前时间是：|CHR（9）|CURTIME)；END print_current_time;1-59在在PL/SQL中，调用过程有两种方式：中，调用过程有两种方式：直接利用直接利用EXECUTE命令，其格式为：命令，其格式为：EXECUTE 过程名称过程名称（参数值（参数值1，参数值，参数值2，）;例如，例如，EXECUTE print_current_time;在在PL/SQL块中调用，其调用方式为：块中调用，其调用
47、方式为：Print_current_time;1-605.5.2 过程参数设置与传递过程参数设置与传递下面是一个带参数过程的简单例子：下面是一个带参数过程的简单例子：CREATE OR REPLACE PROCEDURE print_parameter(p_Parameter IN VARCHAR2 DEFAULT NULL)ASBEGIN IF(p_Parameter IS NULL)THEN DBMS_OUTPUT.put_line(你没有输入参数你没有输入参数)；ELSE DBMS_OUTPUT.put_line(你输入的参数是：你输入的参数是：|CHR(9)|p_Parameter);
48、调用该过程：调用该过程：EXECUTE print_parameter(abc);1-611 参数模式参数模式形参可以有形参可以有3种模式：种模式：IN、OUT和和IN OUT。默认为默认为IN模式模式说明说明IN当调用过程时，实参的值将传入该过程。在该过当调用过程时，实参的值将传入该过程。在该过程内部，形参类似程内部，形参类似PL/SQL使用的常数，即该值使用的常数，即该值具有只读属性，不能对其修改。当该过程结束时，具有只读属性，不能对其修改。当该过程结束时，控制将返回到调用环境，这时对应的实参没有改控制将返回到调用环境，这时对应的实参没有改变。变。OUT当过程被调用时，实参具有的任何值都将
49、被忽略。当过程被调用时，实参具有的任何值都将被忽略。在该过程内部，形参的作用类似于没有初始化的在该过程内部，形参的作用类似于没有初始化的PL/SQL变量，其值为空（变量，其值为空（NULL）。）。该变量具有该变量具有读写属性，当该过程结束时，控制将返回调用环读写属性，当该过程结束时，控制将返回调用环境，形参的内容将赋予对应的实参。境，形参的内容将赋予对应的实参。IN OUT该模式是模式该模式是模式IN和和OUT的组合。调用过程时，实的组合。调用过程时，实参的值将被传递到该过程中，在该过程内部，形参的值将被传递到该过程中，在该过程内部，形参相当于初始化的变量，并具有读写属性。当该参相当于初始化的
50、变量，并具有读写属性。当该过程结束时，控制将返回到调用环境，形参的内过程结束时，控制将返回到调用环境，形参的内容赋予实参。容赋予实参。1-622 使用使用AUTHID选项创建存储过程选项创建存储过程当声明存储过程时，可以使用当声明存储过程时，可以使用AUTHID CURRENT_USER或或AUTHID DESIGNER为默认选为默认选项来指定项来指定Oracle在执行过程时使用的权限域。在执行过程时使用的权限域。如果使用如果使用AUTHID CURRENT_USER选项创建存储过选项创建存储过程，程，Oracle使用调用该过程的用户权限域执行该过程使用调用该过程的用户权限域执行该过程（调用时