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

一、PL/SQL的异常

1.处理异常：

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;

2.预定义异常:
异常名称                   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      试图被零除

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

4.自定义异常

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;

5.在PL/SQL中单条记录的查询
    在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;

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

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;

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

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

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.变量名 基表名.列名%TYPE

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;

2.变量名 基表名%ROWTYPE

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;

四、用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;
End;

五、数据链路(database link)
    通过创建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 -- 跟前面结果一样

六、导入导出存储过程
(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