PL/SQL的异常处理/光标/数据链路/导入导出存储过程

[b]一、PL/SQL的异常[/b]

[b]1.处理异常：[/b]

EXCEPTION WHEN first_exception THEN
   …
   WHEN second_exception THEN
   …
   WHEN OTHERS THEN 
  /*OTHERS异常处理器必须排在最后，它处理所有没有明确列出的异常。*/
  …
END;

例：

DECLARE
    X NUMBER;
BEGIN
    X:= 'yyyy';--Error Here
EXCEPTION   WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('EXCEPTION HANDED');
END;

[b]2.预定义异常:[/b]
异常名称 ORACLE错误 说明
CURSOR_ALREADY_OPEN ORA-6511 试图打开一个已打开的光标
DUP_VAL_ON_INDEX ORA-0001 试图破坏一个唯一性限制
INVALID_CURSOR ORA-1001 试图使用一个无效的光标
INVALID_NUMBER ORA-1722 试图对非数字值进行数字操作
LOGIN_DENIED ORA-1017 无效的用户名或者口令
NO_DATA_FOUND ORA-1403 查询未找到数据
NOT_LOGGED_ON ORA-1012 还未连接就试图数据库操作
PROGRAM_ERROR ORA-6501 内部错误
ROWTYPE_MISMATCH ORA-6504 主变量和光标的类型不兼容
STORAGE_ERROR ORA-6500 内部错误
TIMEOUT_ON_RESOURCE ORA-0051 发生超时
TOO_MANY_ROWS ORA-1422 SELECT INTD命令返回的多行
TRANSACTION_BACKED_OUT ORA-006 由于死锁提交被退回
VALUE_ERROR ORA-6502 转换或者裁剪错误
ZERO_DIVIDE ORA-1476 试图被零除

[b]3.自定义异常处理[/b]

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是负数，因为错误号被认为负数，当定义错误时记住使用负号。

[b]4.自定义异常[/b]

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;

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

DECLARE
    ln_dno NUMBER;
    lvs_dname VARCHAR2(40);
BEGIN
    SELECT user_id,user_name
        INTO ln_dno,lvs_dname
    FROM t_user_table
    WHERE Rownum=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;

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

[b]1.使用光标的基本方法[/b]

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;

属性说明
%FOUND:布尔型属性，当最近一次该记录时成功返回，则值为TRUE
%NOTFOUND:布尔型属性，它的值总与%FOUND属性的值相反
%ISOPEN:布尔型属性，当光标是打开时返回TRUE
%ROWCOUNT:数字型属性，返回已从光标中读取的记录数

[b]2.使用光标FOR循环[/b]

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;
EXCEPTION WHEN OTHERS THEN
      NULL;
END;

[b]3.带参数的光标[/b]

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;

[b]三、创建代表数据库记录和列的变量[/b]

[b]1.变量名 基表名.列名%TYPE[/b]

DECLARE
    V_NO  t_User_Table.User_Id%TYPE;
    V_NAME  t_User_Table.User_Name%TYPE;
BEGIN
    SELECT  User_Id,User_Name INTO V_NO,V_NAME
    FROM  t_User_Table
    WHERE Rownum=1;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(V_NO) || ':' || V_NAME);
EXCEPTION WHEN NO_DATA_FOUND THEN
    NULL;
END;

[b]2.变量名 基表名%ROWTYPE[/b]

DECLARE
    D t_user_table%ROWTYPE;
BEGIN
    SELECT user_id,user_name
        INTO D.user_id, D.user_name
        FROM t_user_table
        WHERE rownum=1;
    DBMS_OUTPUT.PUT_LINE(D.user_id || ':' || D.user_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
    NULL;
WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
END;

[b]四、用PL/SQL表实现数组功能[/b]
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;
End;

[b]五、数据链路(database link)[/b]
通过创建database link实现ORACLE跨数据库查询的方法。
(1)配置本地数据库服务器的tnsnames.ora文件($ORACLE_HOME/network/admin/tnsnames.ora)
EA_OA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 195.2.199.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = EA_OA)
)
)
创建链接:注意：这里不会验证用户名密码的正确性
CREATE DATABASE LINK [数据库链接名] CONNECT TO [用户名] IDENTIFIED BY [密码] USING '[本地配置的数据的实例名]';

create public database link TO_EA_OA 
    connect to admin
　　   identified by 123456 using 'EA_OA';

使用链接：
查询、删除和插入数据和操作本地的数据库是一样的，只不过表名需要写成“表名@database link名”。

   select * from oa_user_basic@TO_EA_OA;

为这个表创建一个同义词，然后直接用同义词，就如同操作本地表。

   create public SYNONYM oa_user for oa_user_basic@TO_EA_OA;
   select * from oa_user -- 跟前面结果一样

[b]六、导入导出存储过程[/b]
(1)PL/SQL Stored Procedure Export
Tools > Export User Objects > Select Package And Package Body > Export
(2)PL/SQL Stored Procedure Import
New > Command Window > @d:\my_proc.sql