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;
　　
　　...
　　
　　

查看本文来源