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

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

一 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函数，会打印错误代码和错误名称

以上就是怎么进行PLSQL重点问题理解和实战，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注行业资讯频道。