[b]一、PL/SQL的异常[/b]
[b]1.处理异常:[/b]
例:
[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]
注意:-01445 因为PRAGMA EXCEPTION_INIT命令把这个变量(-01455)连接到
这个ORACLE错误,该语句的语法如下:
[b]4.自定义异常[/b]
[b]5.在PL/SQL中单条记录的查询[/b]
在PL/SQL内,有时在没有定义显式光标的情况下需要查询单条记录,并把记录的数据赋给变量。
[b]二、使用光标[/b]
光标(CURSOR)是指向一个称为上下文相关区的区域的指针,这个区域在服务器的处理过程全局区(PGA)内,当服务器上执行了一个查询后,查询返回的记录集存放在上下文相关区,通过光标上的操作可以把这些记录检索到客户端的应用程序。
[b]1.使用光标的基本方法[/b]
属性说明
%FOUND:布尔型属性,当最近一次该记录时成功返回,则值为TRUE
%NOTFOUND:布尔型属性,它的值总与%FOUND属性的值相反
%ISOPEN:布尔型属性,当光标是打开时返回TRUE
%ROWCOUNT:数字型属性,返回已从光标中读取的记录数
[b]2.使用光标FOR循环[/b]
[b]3.带参数的光标[/b]
[b]三、创建代表数据库记录和列的变量[/b]
[b]1.变量名 基表名.列名%TYPE[/b]
[b]2.变量名 基表名%ROWTYPE[/b]
[b]四、用PL/SQL表实现数组功能[/b]
PL/SQL表与其他过程化语言(如C语言)的一维数组类似。实现PL/SQL表需要创建一个数据类型并另外进行变量说明。
Type <类型名> Is
Table Of <数据类型>
Index by Binary_Integer;
例子:
[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 '[本地配置的数据的实例名]';
使用链接:
查询、删除和插入数据和操作本地的数据库是一样的,只不过表名需要写成“表名@database link名”。
为这个表创建一个同义词,然后直接用同义词,就如同操作本地表。
[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
[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