怎么进行PLSQL重点问题理解和实战

70次阅读

共计 3826 个字符，预计需要花费 10 分钟才能阅读完成。

本篇文章给大家分享的是有关怎么进行 PLSQL 重点问题理解和实战，丸趣 TV 小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着丸趣 TV 小编一起来看看吧。

一 ORACLE 中 PL/SQL 使用的集合变量类型有 RECORD（类）、VARRAY(sql 数组)、TABLE(嵌套表)
TABLE(嵌套表) 可以加 index 定义也可以不加，加表示 index by 是建立主键索引相当于数组，不加就是个嵌套表集合
1 TABLE(嵌套表) 定义表变量类型
type type_table_emp_empno is table of emp.empno%type index by binary_integer;–TYPE 表示表中一行中字段类型
v_empnos type_table_emp_empno;
如果用 %type 定义
定义集合变量 v_empnos 是一个有 emp.empno 字段类型的数组，自己理解是存放实际还是一个表，里面只有一个字段，且字段上有索引
对此集合变量(is table of index by) 类型的操作包括 count，delete，但不能用 trim
对 VARRAY 可以用 count，delete 和 trim
使用形式
select to_char(truck_in_out_id),
employee_id,
employee_nm,
truck_in_purpose
bulk collect into
carid,
empid,
empnm,
dest

forall i in 1 .. carid.COUNT

update cpnew.CP_VISIT_APPLY a
set a.mgr_principal_id = empid(i),
a.mgr_principal_nm = empnm(i),
a.visit_dest = dest(i)
where a.visit_apply_id = carid(i)
and a.mgr_principal_id is null;

type delArray1 is table of TEST.COURSE%ROWTYPE index by binary_integer; –ROWTYPE 表示表中一行的记录类型
cur_result delArray1;
如果用 %rowtype 定义
定义集合变量 cur_result 是一个 COURSE 表类型的集合，自己理解是按一个表存放，里面包括 COURSE 的所有字段类型，且用整形数做这个表的索引
使用形式
select * bulk collect into cur_result

forall i in 1 .. cur_result.COUNT

update cpnew.CP_VISIT_APPLY a
set a.mgr_principal_id = cur_result(i).empid,
a.mgr_principal_nm = cur_result(i).empnm,
a.visit_dest = cur_result(i).dest
where a.visit_apply_id = cur_result(i).carid
and a.mgr_principal_id is null;

实际工作中的例子
plsql 大数据量删除，修改的方法 FORALL 加 bulk collection into
create or replace procedure zl_del_UPDATEAPPLY_DEST187 as
–type ridArray is table of rowid index by binary_integer;
type delArray1 is table of varchar2(32) index by binary_integer;
type delArray2 is table of CP_2012.CP_VISIT_TRUCK_INOUT.employee_id%type index by binary_integer;
type delArray3 is table of CP_2012.CP_VISIT_TRUCK_INOUT.employee_nm%type index by binary_integer;
type delArray4 is table of CP_2012.CP_VISIT_TRUCK_INOUT.truck_in_purpose%type index by binary_integer;
// 你会发现用 %type 就得每个字段都得定义他的类型
carid delArray1;
empid delArray2;
empnm delArray3;
dest delArray4;
begin

select to_char(truck_in_out_id),
employee_id,
employee_nm,
truck_in_purpose
bulk collect into
carid,
empid,
empnm,
dest
from CP_2012.CP_VISIT_TRUCK_INOUT;
–where rownum 600001;

forall i in 1 .. carid.COUNT

update cpnew.CP_VISIT_APPLY a
set a.mgr_principal_id = empid(i),
a.mgr_principal_nm = empnm(i),
a.visit_dest = dest(i)
where a.visit_apply_id = carid(i)
and a.mgr_principal_id is null;

DBMS_OUTPUT.PUT_LINE(to_char(carid.COUNT) ||
records deleted from temp_mid_hubei_bak !!!

end;
这种方法最大缺点是 forall 里不能访问远程表，也不能用 dblink，且只能放 dml 语句不能用 dbms.putline
经过测试过发现 for 可以替代 forall 尽管时间相对慢一点，但也能接受，所以可以在 for 中用 dblink，相应语句如下：

for i in 1 .. carid.COUNT

loop
update cpnew.CP_VISIT_APPLY@LINK_213TO187_CPNEW a
set a.mgr_principal_id = empid(i),
a.mgr_principal_nm = empnm(i),
a.visit_dest = dest(i)
where a.visit_apply_id = carid(i)
and a.mgr_principal_id is null;

2 Record 变量类型:（相当于 java 的类）
定义
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;

3 VARRAY
定义和使用
CREATE OR REPLACE TYPE numbers_t IS VARRAY (5) OF NUMBER
DECLARE
l_list numbers_t:= numbers_t (1, 2, 3, 4, 5);
BEGIN
l_list.DELETE;
DBMS_OUTPUT.put_line (CASE l_list.COUNT WHEN 0 THEN Empty END);
END;
数组使用例子参考 Oracle 数组的使用 http://blog.itpub.net/12932950/viewspace-351791/

还可以对比本人之前的 blog 查看游标和 bulk collect into 的用法 http://blog.itpub.net/750077/viewspace-2075986/

二 PL/SQL 异常

异常类型 1 预定义的异常处理，2 非预定义 (Predefined)错误，3 用户定义 (User_define) 错误
一般预定义和用户定义异常使用较多
1 预定义异常如 oracle 已定义的异常
ORA-1403 No_data_found SELECT INTO 没有找到数据

使用时如果 select 没查出数据时就直接处理没找到数据的异常
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(数据库中没有编码为 ||v_empno|| 的员工
END;
2 用户自定义异常
用户先定义异常
no_result EXCEPTION;
如没有更新的数据时，抛出这个异常
UPDATE employees SET salary = salary+100 WHERE employee_id = v_empno;
IF SQL%NOTFOUND THEN
RAISE no_result;
END IF;
然后处理这个异常
EXCEPTION
WHEN no_result THEN
DBMS_OUTPUT.PUT_LINE(你的数据更新语句失败了!
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE|| — ||SQLERRM);
END;
SQLCODE，SQLERRM 是 ORACLE 函数，会打印错误代码和错误名称