flzhang的ITPUB博客

暂无签名

  • 博客访问: 656552
  • 博文数量: 94
  • 用 户 组: 普通用户
  • 注册时间: 2014-03-26 21:05
ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(94)

文章存档

2017年(42)

2016年(18)

2015年(19)

2014年(15)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
PLSQL重点问题理解和实战 2017-11-29 10:25:58

分类: Oracle

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

参考 ORACLE PL/SQL编程之五:异常错误处理 https://www.cnblogs.com/huyong/archive/2011/05/06/2038743.html


后续持续更新
......

阅读(2072) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册