PL/SQL
一、什么是PL/SQL?
PL/SQL(Procedural Language/SQL)
是对SQL的扩充,它吸收了近年来编程语言的许多最高设计特点:如数据封装性、信息隐蔽性、重载和例外处理等。它允许SQL的数据操纵语言和查询语句包含在块结构(block_structured)和代码过程语言中,使PL/SQL成为一个功能强大的事务处理语言。
PL/SQL的优点如下:
1.块结构(Block Structure)
PL/SQL是块结构语言,意味着程序可以分成若干逻辑块,各自包含那个单元里要求的逻辑语言资源。可以对块宣布本地变量，在块中使用这些变量,可在它们应用的块中特别地处理错误条件(叫做Exceptions)
2. 流程控制
条件语句、循环和分支可用来控制程序的过程流，以决定是否或何时执行SQL或其它行动。这些特点允许ORACLE工具(诸如SQL*Forms)去分组联系在一起的命令组和通过PL/SQL控制它们的执行。这样可以避免置许多命令为单独的触发器步骤或者嵌套外部编程语言中的SQL语句的要求。
3. 可移植性
因为PL/SQL是ORACLE的主语言,故程序可移植至支持ORACLE和PL/SQL的任何操作系统平台上。
4.集成性
PL/SQL在RDBMS(存贮过程、触发器、包)和ORACLE工具中扮演了日益增长的中心角色。PL/SQL的变量和型与SQL的变量和型兼容(与自己用在数据库列中的型也兼容)。因此，PL/SQL是连接数据库技术和过程编程能力之间间隙的方便的桥梁。
5. 改进了性能
PL/SQL的使用可以帮助改进应用程序的性能。在没有PL/SQL时,ORACLE每次只处理一个SQL语句,而在具有PL/SQL时,一个完整的语句块一次发送到ORACLE,可明显地减少与ORACLE之间的通信和调用。提高了效益。效益的差异取决于PL/SQL使用的什么环境。这些效益稍后讨论
二、PL/SQL的结构
WHERE product=
’TENNIS RACKET’
;
WHERE product=
’TENNIS RACKET’
;
VALUES (
‘out of Tennis Rackets’
,SYSDATE);
VALUES (
‘Product TENNIS RACKET not found’
)
上面的例子说明了一个PL/SQL变量
’qtyon_hand’,然后使用了一个SELECT语句。如果此变量的值为正，则修改’inventory’表。如果SELECT寻找失败,则引起叫’no-data found’的例外,控制立即转移到EXCEPTION部分,将所出问题记录到一个表中。
INSERT INTO EMP_AUDIT VALUES(emp_id,
’NO such number’
);
INSERT INTO EMP_AUDIT VALUES(emp_id,
’SALARY IS NULL’
);
emp_rec.ename:=
’JOHNSON’
;
SQL>EXECUTE EMP_ACTIONS.HIRE_EMPLOYEE(
‘TATE’,’CLERK’
,...);
PL/SQL的每个单元由一个或多个块(blocks)组成。这些块可以是完全独立的,或者一个块嵌套在另一个块之中。这样,一个块可表示其它块的一小部分,反过来,它恰好也是整个程序码单元的一部分。
通常,一个块可以是无名块或者一个子程序。
1. 无名块(anonymous)
无名块是一种没有名字的块。这些块在运行它们的应用程序中说明，并且为了执行在运行时由PL/SQL引擎通过。无名块可以嵌入预编译程序(或OCI程序)、以及SQL*PLUS或SQL*DBA中。SQL*Forms中的触发器也由这些块组成,无名块用得较多。
无名块的定义形式为:
DECLARE
--说明
BEGIN
--语句序列
END；
一个PL/SQL块由三部分组成:说明部分,可执行部分和例外处理部分。
在说明部分中允许说明变量和常量等PL/SQL对象,这些对象在块中引用,或在嵌套的子块中引用。说明部分是可选的。在执行部分可使用SQL的DML语句,事务控制语句,还可使用控制结构如条件控制、迭代控制和顺序控制，这是PL/SQL对SQL的最重要的扩展。在PL/SQL程序中可以方便地发现和处理预定义的或用户定义的称为例外的出错条件(警告或出错条件),当发生错误时,引起(raise)一个例外,正常的执行被停止,控制转移到例外处理程序。例外处理程序也是可选的。
例2.47: PL/SQL无名块结构的例子
DECLARE
qty_on_hand NUMBER(5);
BEGIN
SELECT quantity INTO qty_on_hand FROM inventory
IF qty_on_hand>0 THEN
UPDATE inventory SET quantity=quantity-l
INSERT INTO purchase_record
ENDIF;
COMMIT;
EXCEPTION
WHEN no_data_found THEN
INSERT TNTO error_table
END;
注意关键词DECLARE、BEGIN和EXCEPTION后面不用分号跟随,但是END和全部其它PL/SQL语句要求分号。
2. 子程序(Subprogram)
子程序是命名的PL/SQL块,分为过程(Procedures)和函数(Functions)两类。后者调用时,由RETURN返回一个值。因此一般可使用一个过程执行一个动作，使用一个函数计算一个值。
SQL* Forms允许将过程和函数说明为Form的一部分,并且可从Forms的其它作用点调用。
(1)过程
过程是执行一种特定动作的子程序,是命名的PL/SQL块。过程有两部分:过程说明和过程体。过程的结构如下:
PROCEDURE 过程名 (参数)
--说明
BEGIN
--语句序列
END[过程名];
其中参数 格式为:
参数名 IN 类型名
OUT :=值。
IN OUT
过程说明指定过程名或参数表,参数说明为选择项。过程体由三部分组成：申明部分、可执行部分和可选项例外处理部分。申明部分包含类型、光标、常量、变量、例外和子程序的说明，这些对象是局部的，当退出过程时它们不再存在。可执行部分由赋值语句、控制语句和操纵ORACLE数据语句组成。例外处理部分包括例外处理程序。
参数方式(IN,OUT,INOUT)定义了形式参数的行为。IN参数可将值传送给被调用的子程序。OUT参数将值返回给子程序的调用者。INOUT参数可将初始值传送给被调用的子程序,并将修改的值返回调用者。在过程内,IN参数起像常量一样的作用,它不能被赋值;IN OUT 参数,它起像初始化的变量的作用,可被赋值,它的值可以赋给其它变量。OUT参数起像一个未初始化的变量的作用,它的值不可赋给其它变量或重新赋给自己。在退出过程之前，要显式地将值赋给全部OUT形式参数。IN参数可初始化为缺省值。
例 2.48: OUT参数的例子:
PROCEDURE reconcile(acctno out INTEGER) IS
BEGIN
...
END reconcile;
例 2.49: 增加职工工资的过程:
PROCEDURE RAISE_SALARY(emp_id INTEGER,INCREASE REAL)IS
CURRENT_SALARY REAL;
SALARY_MISSING EXCEPTION;
BEGIN
SELECT SAL INTO CURRENT_SALARY FROM EMP
WHERE EPNO=emp_id;
IF CORRENT_SALARY IS NULL THEN
RAISE SALARY_MISSING;
ELSE
UPDATE EMP SET SAL=SAL+INCREASE
WHERE empno=emp_id;
ENDIF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
WHEN salary_missing THEN
END RAISE_SALARY;
当调用该过程时,该过程接收一个职工号和一个工资增加数,使用该职工号选择EMP表中的当前工资。如果该EMP无此职工号或当前工资为NULL,则引起例外。否则修改工资。
过程调用为-PL/SQL语句,针对上述例子其调用语句形式为:
RAISE_SALARY (1002,600)
也可为:
RAISE_SALARY(INCREASE=>600,EMP_ID=>1002)
(2) 函数
函数(function)为一命名的程序单位,可带参数,并返回一个计算值。函数和过程其结构是同样的,除了函数有一个RETURN子句外。函数的结构如下:
FUNCTION 函数名(变元)
RETURN 类型名 IS
--说明
BEGIN
--语句序列
END [函数名];
其中变元格式为:
变元名 IN 类型名
OUT :=值。
IN OUT
说明:函数中关键字和参数的含义同过程。RETURN 子句用于指定结果值的数据类型。
例 2.50: 决定一个职工工资是否超出范围的函数:
FUNCTION SAL_OK(SALARY REAL,TITLE CHAR)
RETURN BOOEAN IS
MAX_SAL REAL;
MIN_SAL REAL;
BEGIN
SELECT LOSAL,HISAL INTO MIN_SAL,AMX_SAL
FROM SALS WHERE JOB=TITLE;
RETURN(SALARY>=MIN_SAL)AND (SALARY
END SAL_OK;
当该函数调用时,它接收一职工的工资以及工作名称,它利用工作名称查找SALS表中的范围限制,该函数标识符SAL_OK由RETURN语句置成一个布尔值,如果工资超出范围为FALSE,否则为TRUE。
函数调用可以是表达式的成分。
例 2.51:
IF SAL_OK(NEW_SAL,NEW_TITLE)THEN
...
END IF;
...
PROMOTABLE:=SAL_OK(NEW_SAL,NEW_TITLE)AND(RATING>3);
注意:调用用户定义的函数可在过程性语句中使用,便不能在SQL语句中使用。
在函数中使用RETURN语句,该语句的作用是:立即完成子程序的执行并将控制返回给调用者。然后从调用者程序中紧接调用该子程序处执行。一个子程序可包含多个RETURN子句。对于过程,RETURN语句不能包含表达式;对于函数,RETURN语句必须包含一个表达式,该表达式是一个任意复杂表达式,在 RETURN语句执行时计算,该值赋给函数标识符。
3. 包 (package)
(1)包的意义
PL/SQL为了满足程序模块化的需要,除了块(block)和子程序结构外,还引入了包的构造。
包是一种数据库对象,将逻辑上相关的PL/SQL类型、对象和子程序组合成一个更大的单位。包有两个部分:包说明(specification)和包体 (body)。说明部分是为应用程序的接口，它申明类型、常量、例外、游标和可用的子程序。体定义游标和子程序，实现说明。应用程序仅对包说明中的申明是可见的和可存取。如果ORACLE具有Procedure选件,包可以编译、存贮在ORACLE数据库中,其内容可为许多应用共享。当用户第一次调用一包装的子程序时,整个包装入到内存,所以在以后对包中子程序调用时,不再需要I/O操作,故包可提高效率和改进性能。
PL/SQL的包具有信息隐蔽性(information hiding),仅在算法和数据结构设计有关层可见。在前面讲到的过程中，可将过程说明和过程体组成一个程序单位。也可将过程说明与它的过程体分开，在这种情况下，将过程放置在一个包中，可隐蔽实现的细节。也可在包中定义过程，而该过程在包说明中没有定义过程说明，这样定义的过程仅在包内使用。
(2)包的定义
包的定义形式如下:
包说明:
PACKAGE 包名 IS
--变量说明;
--游标说明;
--例外说明;
--记录说明;
--Plsql表说明;
--过程说明;
--函数说明;
END[包名];
包体:
PACKAGE BODY 包名 IS
--变量名说明;
--游标说明;
--游标申明;
--例外说明;
--记录说明;
--plsql说明;
--过程体;
--函数体;
BEGIN
--语句序列
END[包名];
其中,包名为命名包的标识符。
包不能嵌入在PL/SQL块或子程序中,然而使用支持PL/SQL的任何工具可定义包。要使包为全体使用,必须用CREATE命令建立,存贮在一个 ORACLE数据库中。可在SQL*PLUS、SQL*DBA或从ORACLE预编译宿主程序中利用CREATE PACKAGE和CREATE PACKAGE BODY 语句建立。
例 2.52: 建立包:
.包说明建立
CREATE PACKAGE emp_actions AS
TYPE EMPRECTYP IS RECORD(emp_id INTEGER,salary REAL);
CURSOR desc_salary(emp_id NUMBER)RETURN EMPRECTYP;
PROCEDURE hire_employee
(ename CHAR,
job CHAR,
sal NUMBER,
mgr NUMBER,
comm NUMBER,
deptno NUMBER;
PROCEDURE fire_employee(emp_id NUMBER);
END emp_actions;
(A):其中语句TYPE EMPRECTYP IS RECORD(emp_id. INTEGER,Salary REAL)是指用户定义EMPRECTYP为RECORD(记录)类型对象。用户定义记录
的一般格式是:
TYPY 类型名 IS RECORD
(字段名1{字段类型|变量名%TYPE|表名.列名%TYPE|表名%ROWTYPE},
字段名2{字段类型|变量名%TYPE|表名.列名%TYPE|表名%ROWTYPE},...);其中:类型名为类型说明符,可在记录说明中使用。字段
类型为PL/SQL允许的任何数据类型,包括RECORD类型和TABLE(表)类型。可用%TYPE或%ROWTYPE属性指定一字段的数据类型。
利用%TYPE属性可提供变量,常量或数据库列的数据类型。例如变量my_dname是同SCOTT的DEPT表的DNAME列类型相同,则可如下定义:
my_dname SCOTT.dept.danme%TYPE ,%ROWTYPE属性提供一记录类型,它表示一表(或视图)中的一行。记录可存贮由表所选择的一整行或者由
一游标所获取的一整行。例如：
DECLARE
emp_rec emp% ROWTYPE;
CURSOR C1 IS SELECT deptno,dname,loc FROM dept;
dept_rec C1%ROWTYPE;
其中 emp_rec记录可存贮从表EMP中选择的行,dept_rec记录可存贮由C1游标所获取的行。这样定义的记录中的字段与行中列有相同的名和相同数据类型。记录中的字段可以下列形式引用：
记录名.字段名
(B):其中语句CURSOR desc_salary(emp_id NUMBER) RETURN EMPRECTYP定义了一个游标desc_salary。
游标(CURSOR)详见本节最后的内容。
．包体建立
CREATE PACKAGE BODY emp_actions AS
CURSOR desc_salary(emp_id NUMBER)RETURN EMPRECTYP
IS SELECT EMPNO,SAL FROM emp ORDER BY SAL DESC;
PROCEDURE hire_employee
(ename CHAR,
job CHAR,
sal NUMBER,
mgr NUMBER,
comm NUMBER,
deptno NUMBER)IS
BEGIN
INSERT INTO EMP VALUES(EMPNO_SEQ.NEXTVAL,
ename,job,mgr,SYSDATE,sal,comm,deptno);
END hire_employee;
PROCEDURE fire_employee(emp_id NUMBER) IS
BEGIN
DELETE FROM EMP WHERE EMPNO=emp_id;
END fire_employee;
END emp_actions;
注意:hire_employee过程使用参数,数据库序列empno_seq、函数SYSDATE插入一个新职工记录,其职工号由序列产生,聘用日期由函数
SYSDATE生成。
在包说明中,说明对所有应用是可见的而且可存取。在包体中，实现细节是隐藏的，不可存取。所以改变包体不会引起调用程序的重新编译。
在设计一个应用时，最初只需要定义包说明的接口信息。在没有包体时可编制包说明的程序并进行编译。一旦包说明已经被编译，引用该包的存贮子程序同样编译。在完成应用之前，可不必定义包体。调试、提高或替换一个包体不必修改包说明，以致不需重新编译调用程序。在包说明中所说明的游标和子程序必须在包体中定义。
(3)对包装子程序的调用
包装的子程序可被数据库触发器、其它的存贮子程序、ORACLE预编译应用程序、OCI应用程序或ORACLE工具(如SQL*PLUS)调用,其调用形式如下:
．从另一存贮子程序调用
存贮子程序可调用包装子程序。
例2.53: 调用包EMP_ACTIONS中的包装过程HIRE_EMPLOYEE,其形式为:
EMP_ACTIONS.HIRE_EMPLOYEE(name,title,...);
．从应用程序中调用
一个ORACLE预编译的应用程序或OCI应用程序可用无名PL/SQL块调用包装子程序。
例2.54: 在ORACLE预编译应用程序对包装过程HIRE_EMPLOYEE调用,形式为:
EXEC SQL EXECUTE
BEGIN
EMP_ACTIONS.HIRE_EMPLOYEE(:name,:title,...);
END;
END_EXEC;
其中实在参数name、title为宿主变量。
．从ORACLE工具中调用
可从ORACLE工具(如SQL*PLUS、SQL*FORMS和SQL*DBA)中交互地调用包装子程序。其调用形式为：
注意:在执行一个包装子程序之前,ORACLE标志一个隐式的保留点。如果子程序由于具有未处理例外而失败时，在返回到宿主环境之前，ORACLE将回滚到该保留点上.
在一个包装子程序中可包含任何SQL语句和PL/SQL语句。然而，凡参与分布工事务、数据库触发器、SQL*FORM应用程序的子程序不能调用包含有COMMIT、ROLLBACK、SAVEPOINT语句的包装子程序。
．远程存取
可利用下列语法调用存贮在远程ORACLE数据库中的包装子程序:
包名.子程序名@DB链名(参数1,参数2,...)
ORACLE在数据字典中分别存贮包说明和包体。其它对象仅可引用包说明中全局包对象，重新定义包体中的对象时不会引起ORACLE使从属对象无效。
(4) STANDARD包和产品特有的包
名为STANDARD的包定义了PL/SQL环境。该包说明全局的申明类型、例外和子程序，可自动地适用于每一个PL/SQL程序。STANDARD包的内容对应用是直接可见的。许多内部函数是被重载的。
为了方便建立基于PL/SQL的应用程序,ORACLE SERVER和几种ORACLE工具都有产品特有的包,例SQL*FORMS提供名为SQLFORMS的包。如需要更多信息，请参见相应产品的引用手册。
三、控制结构
在ORACLE中,使用了称之为
“专用SQL区”(Private SQL Area)的工作区,用于执行SQL语句和存贮处理信息。在PL/SQL中,有一种叫作游标(CURSOR)的专用SQL构造,可以快速存取它所存贮的信息。PL/SQL有两种类型的游标:显式游标和隐式游标。PL/SQL为全部SQL DML语句(包括返回单行的查询)隐式地定义一个游标。而对于返回多行的查询，为了单个地处理每一行，必须显式地定义一个游标。
OPEN C1 (
‘ATTLEY’
,300);
OPEN C1(
‘THURSTON’
,MY_COMM);
隐式游标与显式游标有些不同。ORACLE在处理每一个不与显式说明游标相关的SQL语句时,隐式地打开一个游标。PL/SQL可以用
“SQL”引用最近的隐式游标。在程序中不能用OPENFETCH和CLOSE语句控制隐式游标,但可以利用光标属性存取最近执行的SQL语句的有关信息。
本节介绍PL/SQL程序的控制流结构。根据结构化程序设计理论，任何程序可同三种基本控制结构组成：分支结构、循环结构和顺序结构。PL/SQL也用相应的语句来支持这三种控制结构。
1.条件控制IF语句
IF语句是条件执行语句。IF语句有三种形式:IF_THEN,IF_THEN_ELSE和IF_THEN_ELSIF。
(1) IF_THEN形式
它是IF语句最简单形式,将一条件与一语句序列相联。当条件为TRUE时,执行语句序列。
例 2.55:
IF X>Y THEN high:=X;ENDIF
(2) IF_THEN_ELSE形式
该种IF语句形式比简单形式增加关键字ELSE,后跟另一语句序列。形式为：
IF 条件 THEN
语句序列1;
ELSE
语句序列2;
ENDIF;
ELSE子句中语句序列仅当条件计算为FALSE或NULL时执行。在THEN和ELSE子句中可包含IF语句,即IF语句可以嵌套。
(3) IF_THEN_ELSIF形式
该形式利用ELSIF关键字引入附加条件。形式为：
IF 条件1 THEN
语句序列1;
ELSIF 条件2 THEN
语句序列2;
ELSE
语句序列3;
ENDIF;
当条件1计算得FALSE或NULL时,ELSIF子句测试条件2,为TRUE时,则执行语句序列2。IF语句可以有任何数目的ELSIF语句,而最后的 ELSE子句是可选项。在此种情况下，每一条件对应一语句序列，条件由顶向底计算。任何一个条件计算为TRUE时,执行相对应的语句序列。如果所有条件计算为FALSE或NULL,则执行ELSE子句中的序列。
例 2.56:
IF X>Y THEN
high:=X;
ELSIF X=Y THEN
B:=FALSE;
ELSE
C:=NULL;
ENDIF;
其中,B和C是布尔数据类型(BOOLEAN)。布尔数据类型用于存贮TRUE、FALSE或NULL(空值)。它没有参数，仅可将三种值赋给一个布尔变量，不能将TRUE、FALSE值插入到数据库的列,也不能从数据库的列中选择或获取列值到BOOLEAN变量。
2.循环语句OOP和EXIT语句
(1) 基本循环
基本循环(或无限循环)是循环语句的最简单的形式,它由关键字LOOP和ENDLOOP之间的语句序列组成。每一次循环迭代，语句序列执行一次，然后控制重新由循环的顶部开始。可利用EXIT、GOTO或RAISE语句退出循环。基本循环形式为：
LOOP
语句序列;
EXIT WHEN 布尔表达式;
ENDLOOP;
其中EXIT语句用于退出循环。EXIT语句有两种形式:无条件的EXIT和条件的EXIT[标号名]WHEN。[标号名]选项中的标号名为所要退出的循环标识。使用标号名不仅可退出当前循环，而且可退出由标号名所标识的循环。
不能用EXIT语句退出一个PL/SQL块。
(2) WHILE_LOOP循环
WHILE_LOOP循环语句将一条件与一由关键字LOOP和ENDLOOP包装的语句序列相联系。在每次循环开始前，计算条件，如果该条件计算得到 TRUE,执行语句序列,然后控制重新回到循环顶部。如果条件计算得到FALSE或NULL,结束循环。在循环中引起例外时也结束循环。该语句的形式为：
WHILE 条件 LOOP
语句序列,
END LOOP;
例 2.57:
WHILE total
...
SELECT INTO Salary From emp WHERE...;
total:=total+Salary;
ENDLOOP;
循环迭代次数依赖于条件,在循环完成之前是未知的。由于条件的测试是在循环的顶部，语句序列可以一次也不执行。
(3) 数值FOR_LOOP循环
数值FOR循环语句在一指定整数范围中循环,所以通过该循环的迭代次数是已知的。在第一次进入FOR循环时计算范围,之后再不重新计算。对由整数表达式1..整数表达式2定义的范围中每一整数,语句序列执行一次,每次迭代之后,循环索引增加。
索引名为命名循环索引的标识符，索引名无需说明，它被隐式地说明为INTEGER类型的变量。该索引名的范围是该循环本身，在循环外不能存取。注意隐式说明压倒任何循环外的任何其它说明。
整数表达式１、整数表达式２是表达式，计算出一个整数。要求整数表达式１的值不大于整数表达式２的值。
REVERSE选择:缺省时，迭代由低界向高界处理。但使用REVERSE后,迭代是由高界向低界处理,每次迭代后,循环索引减小。
数值FOR循环语句形式为:
FOR计数器IN低界..高界LOOP
语句序列;
ENDLOOP;
例 2.58:
FOR I IN 1..3 LOOP --将值1,2,3赋给I
语句序列; --执行三次
ENDLOOP;
3. 顺序控制
GOTO语句和NULL语句对PL/SQL程序设计不像IF语句和循环语句那么重要,但有时PL/SQL结构还是需要。
(1) GOTO语句
GOTO语句无条件转移到一标号,该标号应在它的范围内是唯一的,它必须位于可执行语句或一PL/SQL块之前。执行时，GOTO语句将控制转移到该标号所标注的语句或块。
注意：GOTO语句不能转移到IF语句、循环语句或子块中。
在当前块中，利用GOTO语句可转移到该块的另一个地方或者转移到包装它的块,但不能转移到例外处理子程序。在例外处理程序中，GOTO语句可转移包装它的块,但不能转移到当前块中。
例 2.59:
BEGIN
...
GOTO INSERT_ROW
...
>
INSERT INTO MP VALUES...
END;
(2) NULL 语句
该语句不作什么,仅将控制传递给下一语句。使用它的目的是为了提高PL/SQL程序的可读性。
四、 游标(CURSOR)
在PL/SQL块子程序或包的说明部分定义游标,给游标命名,并指定一查询。之后可用三种命令：OPEN、FETCH和CLOSE控制游标。
1. 游标申明与包游标
(1) 游标申明
游标申明,命名游标并将它与一查询联系起来。其定义形式如下：
CURSOR 游标名[(参数[,参数]...)] IS
这里:参数表示为:变量名数据类型[{:=LDEFAULT}]值]
其中:游标名为命名游标的标识符。参数名为命名游标形式参数的标识符。数据类型：指定游标参数的数据类型。SELECT:为选择语句,是与指定的游标名相联系的一查询,该语句类似于SELECT INTO语句,但它不包含INTO子句。
例 2.60： 定义C1游标:
CURSOR C1 (median IN NUMBER) IS
SELECT JOB,ENAME FROM EMP WHERE Sai>median;
(2)包游标
在包中,由于包分成两部分:包说明和包体,因此在包中的游标也分成两部分:游标说明(cursor specification)和游标体(cursor body)。
例 2.61： 包游标定义
在包说明中游标说明:
CREATE PACKAGE EMP_ACTIONS AS
/*包游标说明*/
CURSOR C1 RETURN EMP%ROWTYPE;
...
END EMP_ACTIONS;
在包体中定义游标体:
CREAT PACKAGE BODY EMP_ACTIONS IS
/*定义包游标体*/
CURSOR C1 RETURN EMP%ROWTYPE
SELECT * FROM EMP WHERE SAL >3000;
...
END EMP_ACTIONS;
在这种情况下,改变游标体时不需修改游标说明。在游标说明中没有SELECT语句,RETURN子句定义了结果值的数据类型。可使用％ROWTYPE属性、％TYPE属性定义返回行、返回结果值的类型。
在游标体中必须有一个SELECT语句和相应游标说明中一样的RETURN子句,在SELECT语句中的选择项表的数目和类型必须与RETURN子句相匹配。
2. 打开一游标
打开游标语句执行查询,标识活动集(active set)。该活动集是由满足查询检索标准的全部行组成。打开游标语句形式如下：
OPEN C1;
在OPEN 语句执行时,活动集中的行没有被检索,而由FETCH语句检索这些行。
在OPEN语句中可将参数传送给游标,见下例:
例 2.62:
如游标说明为:
CURSOR C1 (my_ename CHAR,My_COMM NUMBER) IS
SELECT...
可用下列打开游标语句:
注意:在最后一个OPEN语句中引用的变量与游标定义中参数有相同名字,游标说明中使用my_comm是指形参my_comm,而在说明外边使用,它指 PL/SQL变量my_comm,游标形式参数必须是IN参数。OPEN语句中实在参数与形式参数之间联系的方法除了位置对应表示法外,还有命名表示法: 形参=>实参。
3. 获取活动集中的行
FETCH语句检索活动集中的行,每次一行。每执行一次FETCH,游标前进到活动集中下一行。FETCH语句的实例如:
FETCH C1 INTO My_empno,my_ename,my_deptno;
由与游标相联的查询返回的每一例值,在INTO表中必须有相应的变量,而且其数据类型必须兼容。
例 2.63:
DECLARE
MY_SAL EMP.SAL%TYPE;
MY_JOB EMP.JOB%TYPE;
FACTOR INTEGER:=2;
CURSOR C1 IS
SELECT FACTOR *SAL FROM EMP WHERE JOB=MY_JOB;
BEGIN
...
OPEN C1;--此时FACTOR=2
LOOP
FETCH C1 INTO MMMY_SAL;
EXIT WHEN C1%NOTFOUND;
...
factor:=factor+1;--不影响FETCH结果
ENDLOOP;
END;
注意:这个例子中每次检查的SAL被乘以2,即使每次FETCH 后factor被增加。因为OPEN 时决定活动集。
4.关闭游标
CLOSE语句关闭游标,使活动集成为未定义。CLOSE语句的实例形为:
CLOSE C1;
一旦关闭游标后,可重新打开它。如果在已关闭的游标上执行任何其它操作,将引起预定义例外INVALID_CURSOR,相当于ORACLE错误ORA_01001。
5. 游标的属性
每个显式定义的游标有四种属性：％NOTFOUND、％FOUND、％ROWCOUNT和％ISOPEN。注意仅在过程性语句中可使用游标属性,而在SQL语句中不能使用。
(1)％NOTFOUND属性
如果最后FETCH语句执行时返回一行,则％NOTFOUND计算行FALSE;如果最后FETCH语句没返回行,则％NOTFOUND计算行TRUE。可利用％NOTFOUND退出循环。
例2.64:
LOOP
FETCH C1 INTO My_ename,My_deptno;
EXIT WHEN C1％NOTFOUND;
...
ENDLOOP;
当FETCH语句没有获取行时,C1％NOFOUND为TRUE,退出当前循环。
(2)％FOUND属性
游标的％FOUND属性为%NOTFOUND的逻辑非。如果最后FETCH语句执行时返回行。%FOUND为TRUE;如果未返回行,%FOUND则为FALSE。
(3)%ROWCOUNT属性
游标的%ROWCOUNT属性返回游标打开后至今,由FETCH语句已获取的行数。
(4)%ISOPEN属性
如果游标是打开的,则该游标的%ISOPEN计算得TRUE值;如果未打开,其%ISOPEN为FALSE。
SQL游标也有四个属性:%NOTFOUND、%FOUND、%ROWCOUNT和%ISOPEN。SQL游标属性总是涉及最后执行的SQL语句,这些语句包括INSERT、UPDATE、DELETE和SELECT INTO语句。隐式游标的四个属性与显式游标属性类似,仅在此叙述其不同之处。
%ROWCOUNT属性返回由INSERT、UPDATE或DELETE所影响的行数以及由SELECT INTO所选择的行数。注意SELECT INTO返回的行数不能多于一行,如果多于一行,将引起预定义例外TOO_MANY_ROWS。
%ISOPEN属性:ORACLE在执行每一个相关的SQL语句后,自动地关闭SQL游标,所以SQL游标的%ISOPEN属性总是FALSE。
6.游标循环
可以利用循环和游标实现数据库数据的循环处理。游标FOR循环可简化上述处理程序。游标FOR循环隐式地说明它的循环索引为一个记录,并打开游标,重复地获取活动集中的行放置到记录的字段中,在所有行处理完后或退出循环时关闭游标。其语法与前面章节讲的FOR循环相同。
例 2.65： 利用光标FOR循环实现本节的游标应用：
DECLARE
RESULT TEMP.COL%TYPE;
CURSOR C1 IS
SELECT n1,n2,n3 FROM DATA_TABLE
WHERE EXper_num=1;
BEGIN
FOR C1rec IN C1 LOOP
RESULT:=C1rec.n2/(C1rec.n1+C1rec.n3);
INSERT INTO TEMP VALUES(RESULT,NULL,NULL);
END LOOP;
COMMIT;
END;
其中FOR循环索引C1rec隐式地说明为一记录,它的字段可存贮由游标C1所获取的全部列值,即它等于下列显式说明:i
C1rec C1%ROWTYPE;
该记录仅定义在循环内部。循环中的语句序列对满足游标的查询的每一行执行一次。当离开循环时，自动地关闭游标。
哇，看完了
页:
[1]