一、什么是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的结构
　　
　　PL/SQL的每个单元由一个或多个块(blocks)组成。这些块可以是完全独立的,或者一个块嵌套在另一个块之中。这样,一个块可表示其它块的一小部分,反过来,它恰好也是整个程序码单元的一部分。
　　
　　通常,一个块可以是无名块或者一个子程序。
　　
　　1. 无名块(anonymous)
　　
　　无名块是一种没有名字的块。这些块在运行它们的应用程序中说明，并且为了执行在运行时由PL/SQL引擎通过。无名块可以嵌入预编译程序(或OCI程序)、以及SQL*PLUS或SQL*DBA中。SQL*Forms中的触发器也由这些块组成,无名块用得较多。
　　
　　无名块的定义形式为:
　　
　　DECLARE
　　
　　--说明
　　
　　BEGIN
　　
　　--语句序列
　　
　　[EXCEPTION
　　
　　--例外处理程序]
　　
　　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
　　
　　WHERE product=’TENNIS RACKET’;
　　
　　IF qty_on_hand>0 THEN
　　
　　UPDATE inventory SET quantity=quantity-l
　　
　　WHERE product=’TENNIS RACKET’;
　　
　　INSERT INTO purchase_record
　　
　　VALUES (‘out of Tennis Rackets’,SYSDATE);
　　
　　ENDIF;
　　
　　COMMIT;
　　
　　EXCEPTION
　　
　　WHEN no_data_found THEN
　　
　　INSERT TNTO error_table
　　
　　VALUES (‘Product TENNIS RACKET not found’)
　　
　　END;
　　
　　注意关键词DECLARE、BEGIN和EXCEPTION后面不用分号跟随,但是END和全部其它PL/SQL语句要求分号。
　　
　　上面的例子说明了一个PL/SQL变量’qtyon_hand’,然后使用了一个SELECT语句。如果此变量的值为正，则修改’inventory’表。如果SELECT寻找失败,则引起叫’no-data found’的例外,控制立即转移到EXCEPTION部分,将所出问题记录到一个表中。
　　
　　2. 子程序(Subprogram)
　　
　　子程序是命名的PL/SQL块,分为过程(Procedures)和函数(Functions)两类。后者调用时,由RETURN返回一个值。因此一般可使用一个过程执行一个动作，使用一个函数计算一个值。
　　
　　SQL* Forms允许将过程和函数说明为Form的一部分,并且可从Forms的其它作用点调用。
　　
　　(1)过程
　　
　　过程是执行一种特定动作的子程序,是命名的PL/SQL块。过程有两部分:过程说明和过程体。过程的结构如下:
　　
　　PROCEDURE 过程名 (参数)
　　
　　--说明
　　
　　BEGIN
　　
　　--语句序列
　　
　　[EXCEPTION
　　
　　--例外处理程序]
　　
　　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
　　
　　INSERT INTO EMP_AUDIT VALUES(emp_id,’NO such number’);
　　
　　WHEN salary_missing THEN
　　
　　INSERT INTO EMP_AUDIT VALUES(emp_id,’SALARY IS NULL’);
　　
　　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
　　
　　--语句序列
　　
　　[EXCEPTION
　　
　　--例外处理程序]
　　
　　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<=MAX_SAL);
　　
　　END SAL_OK;
　　
　　当该函数调用时,它接收一职工的工资以及工作名称,它利用工作名称查找SALS表中的范围限制,该函数标识符SAL_OK由RETURN语句置成一个布尔值,如果工资超出范围为FALSE,否则为TRUE。
　　
　　函数调用可以是表达式的成分。
　　
　　例 2.51:
　　
　　IF SAL_OK(NEW_SAL,NEW_TITLE)THEN
　　
　　...
　　
　　END IF;
　　
　　...
　　
　　

查看本文来源

　　(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)中交互地调用包装子程序。其调用形式为：
　　
　　SQL>EXECUTE EMP_ACTIONS.HIRE_EMPLOYEE(‘TATE’,’CLERK’,...);
　　
　　注意:在执行一个包装子程序之前,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的包。如需要更多信息，请参见相应产品的引用手册。
　　
　　三、控制结构
　　
　　本节介绍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.循环语句:LOOP和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<=25000 LOOP
　　
　　...
　　
　　SELECT INTO Salary From emp WHERE...;
　　
　　total:=total+Salary;
　　
　　ENDLOOP;
　　
　　循环迭代次数依赖于条件,在循环完成之前是未知的。由于条件的测试是在循环的顶部，语句序列可以一次也不执行。
　　
　　(3) 数值FOR_LOOP循环
　　
　　数值FOR循环语句在一指定整数范围中循环,所以通过该循环的迭代次数是已知的。在第一次进入FOR循环时计算范围,之后再不重新计算。对由整数表达式1..整数表达式2定义的范围中每一整数,语句序列执行一次,每次迭代之后,循环索引增加。
　　
　　索引名为命名循环索引的标识符，索引名无需说明，它被隐式地说明为INTEGER类型的变量。该索引名的范围是该循环本身，在循环外不能存取。注意隐式说明压倒任何循环外的任何其它说明。
　　
　　整数表达式１、整数表达式２是表达式，计算出一个整数。要求整数表达式１的值不大于整数表达式２的值。
　　
　　REVERSE选择:缺省时，迭代由低界向高界处理。但使用REVERSE后,迭代是由高界向低界处理,每次迭代后,循环索引减小。
　　
　　数值FOR循环语句形式为:
　　
　　FOR计数器IN[REVERSE]低界..高界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语句无条件转移到一标号,该标号应在它的范围内是唯一的,它必须位于可执行语

查看本文来源