PL/SQL入门教程

　　PL/SQL是ORACLE的过程化语言，包括一整套的数据类型、条件结构、循环结构和异常处理结构，PL/SQL可以执行SQL语句，SQL语句中也可以使用PL/SQL函数。

　　1.2 创建PL/SQL程序块

　　DECLARE

　　…

　　BEGIN

　　…

　　EXCEPTION

　　END;

　　1.3 PL/SQL数据类型

　　例子：

　　DECLARE

　　ORDER_NO NUMBER(3);

　　CUST_NAME VARCHAR2(20);

　　ORDER_DATE DATE;

　　EMP_NO INTEGER:=25;

　　PI CONSTANT NUMBER:=3.1416;

　　BEGIN

　　NULL;

　　END;

　　1.4 处理PL/SQL的异常

　　1.4.1 PL/SQL的异常

　　例如：

　　DECLARE

　　X NUMBER;

　　BEGIN

　　X:= ‘yyyy’;–Error Here

　　EXCEPTION

　　WHEN VALUE_ERROR THEN

　　DBMS_OUTPUT.PUT_LINE(‘EXCEPTION HANDED’);

　　END;

　　实现技术：

　　EXCEPTION WHEN first_exception THEN

　　…

　　WHEN second_exception THEN

　　…

　　WHEN OTHERS THEN

　　/*THERS异常处理器必须排在最后，它处理所有没有明确列出的异常。*/

　　…

　　END;

　　1.4.2 预定义异常

　　1.4.3 自定义异常处理

　　DECLARE

　　BAD_ROWID EXCEPTION;

　　X ROWID;

　　PRAGMA EXCEPTION_INIT(BAD_ROWID,-01445);

　　BEGIN

　　SELECT ROWID INTO X FROM TAB

　　WHERE ROWNUM=1;

　　EXCEPTION WHEN BAD_ROWID THEN

　　DBMS_OUTPUT.PUT_LINE(‘CANNOT QUERY ROWID FROM THIS VIEW’);

　　END;

　　注意：-01445 因为PRAGMA EXCEPTION_INIT命令把这个变量(-01455)连接到

　　这个ORACLE错误，该语句的语法如下：

　　PRAGMA EXCEPTION_INIT(exception_name, error_number);

　　其中error_number是负数，因为错误号被认为负数，当定义错误时记住使用负号

　　1.4.4 自定义异常

　　异常不一定必须是oracle返回的系统错误，用户可以在自己的应用程序中创

　　建可触发及可处理的自定义异常

　　DECLARE

　　SALARY_CODE VARCHAR2(1);

　　INVALID_SALARY_CODE EXCEPTION;

　　BEGIN

　　SALARY_CODE:=’X’;

　　IF SALARY_CODE NOT IN(‘A’, ‘B’, ‘C’) THEN

　　RAISE INVALID_SALARY_CODE;

　　END IF;

　　EXCEPTION WHEN INVALID_SALARY_CODE THEN

　　DBMS_OUTPUT.PUT_LINE(‘INVALID SALARY CODE’);

　　END;

　　1.5 在PL/SQL中单条记录的查询

　　在PL/SQL内，有时在没有定义显式光标的情况下需要查询单条记录，并把记录的数据赋给变量。

　　DECLARE

　　ln_dno NUMBER;

　　lvs_dname VARCHAR2(40);

　　BEGIN

　　SELECT DEPT_NO,DEPT_NAME

　　INTO ln_dno,lvs_dname

　　FROM dept

　　WHERE DEPT_NO=1;

　　DBMS_OUTPUT.PUT_LINE(TO_CHAR(ln_dno)||’.’||lvs_dname);

　　EXCEPTION WHEN NO_DATA_FOUND THEN

　　DBMS_OUTPUT.PUT_LINE(‘NO DATA_FOUND’);

　　WHEN TOO_MANY_ROWS THEN

　　DBMS_OUTPUT.PUT_LINE(‘TOO_MANY_ROWS’);

　　END;

　　1.6 用光标查询多条记录

　　光标（CURSOR）是指向一个称为上下文相关区的区域的指针，这个区域在服务器的处理过程全局区（PGA）内，当服务器上执行了一个查询后，查询返回的记录集存放在上下文相关区，通过光标上的操作可以把这些记录检索到客户端的应用程序。

　　1.6.1 使用光标的基本方法

　　DECLARE

　　CURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWS

　　WHERE ROWNUM<=10

　　ORDER BY VIEW_NAME;

　　VNAME VARCHAR2(40);

　　BEGIN

　　OPEN C1;

　　FETCH C1 INTO VNAME;

　　WHILE C1%FOUND LOOP

　　DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)||’ ‘||VNAME);

　　FETCH C1 INTO VNAME;

　　END LOOP;

　　END;

　　1.6.2 使用光标FOR循环

　　DECLARE

　　CURSOR C1 IS

　　SELECT VIEW_NAME

　　FROM ALL_VIEWS

　　WHERE ROWNUM<=10

　　ORDER BY VIEW_NAME;

　　BEGIN

　　FOR I IN C1 LOOP

　　DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);

　　END LOOP;

　　END LOOP;

　　EXCEPTION WHEN OTHERS THEN

　　NULL;

　　END;

　　1.6.3 带参数的光标

　　DECLARE

　　CURSOR C1(VIEW_PATTERN VARCHAR2) IS

　　SELECT VIEW_NAME

　　FROM ALL_VIEWS

　　WHERE VIEW_NAME LIKE VIEW_PATTERN||’%’ AND

　　ROWNUM<=10

　　ORDER BY VIEW_NAME;

　　VNAME VARCHAR2(40);

　　BEGIN

　　FOR I IN C1(‘USER_AR’) LOOP

　　DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);

　　END LOOP;

　　DBMS_OUTPUT.PUT_LINE(?);

　　FOR I IN C1(‘USER’) LOOP

　　DBMS_OUTPUT.PUT_LINE(I.VIEW_NAME);

　　END LOOP;

　　EXCEPTION WHEN OTHERS THEN

　　DBMS_OUTPUT.PUT_LINE(‘AAA’);

　　END;

　　1.7 创建代表数据库记录和列的变量

　　变量名 基表名.列名%TYPE

　　DECLARE

　　D_NO DEPT.DEPT_NO%TYPE;

　　D_NAME DEPT.DEPT_NAME%TYPE;

　　BEGIN

　　SELECT DEPT_NO,DEPT_NAME INTO D_NO,D_NAME

　　FROM DEPT;

　　DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_NO));

　　EXCEPTION WHEN NO_DATA_FOUND THEN

　　NULL;

　　END;

　　变量名 基表名%ROWTYPE

　　DECLARE

　　D VEQU12%ROWTYPE;

　　BEGIN

　　SELECT ASSET12ID,ASSET12NAME

　　INTO D.ASSET12ID, D.ASSET12NAME

　　FROM VEQU12;

　　DBMS_OUTPUT.PUT_LINE(D.ASSET12ID);

　　EXCEPTION

　　WHEN NO_DATA_FOUND THEN

　　NULL;

　　WHEN TOO_MANY_ROWS THEN

　　DBMS_OUTPUT.PUT_LINE(‘TOO_MANY_ROWS’);

　　END;

　　说明：

　　当用户要创建一个变量来表示一个基表列或者要创建多个变量来代表一整条记录时，可以实际使用%TYPE属性和%ROWTYPE属性，使用%TYPE属性和%ROWTYPE属性可以保证当基表的结构或者其中某列的数据类型改变了时，用户的PL/SQL代码仍可正常工作。

　　1.9 怎样用PL/SQL表实现数组功能

　　PL/SQL表与其他过程化语言(如C语言)的一维数组类似。实现PL/SQL表需要创建一个数据类型并另外进行变量说明。

　　Type <类型名> Is

　　Table Of <数据类型>

　　Index by Binary_Integer;

　　以下为一个例子：

　　Declare

　　Type Array_type is

　　Table Of Number

　　Index by Binary_Integer;

　　My_Array Array_type;

　　Begin

　　For I In 1..10 Loop

　　My_Array(I) := I*2;

　　End Loop;

　　For I In 1..10 Loop

　　Dbms_Output.Put_line(To_char(My_Array(I)));

　　End Loop;