ORACLE的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的结构

　　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;

　　...

　　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}[NOT NULL],

　　字段名2{字段类型|变量名%TYPE|表名.列名%TYPE|表名%ROWTYPE}[NOT NULL],...);其中:类型名为类型说明符,可在记录说明中使用。字段

　　类型为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游标所获取的行。这样定义的记录中的字段与行中列有相同的名和相同数据类型。记录中的字段可以下列形式引用:

　　记录名.字段名

　　emp_rec.ename:=’JOHNSON’;