ORACLE游标、递归查询、子查询与批量更新示例

--创建临时表
create table TJ_org_NEW
(
  DEPTCODE       NVARCHAR2(255),
  ORGDESC            NVARCHAR2(255),
  ORGANTYPE      NVARCHAR2(255),
  LEVELFACT      NUMBER(6),
  PARENTDEPTCODE NVARCHAR2(255)
)
tablespace TEST
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
--初始化临时表数据
insert into TJ_ORG_NEW(DEPTCODE,ORGDESC,ORGANTYPE,LEVELFACT,PARENTDEPTCODE) select DEPTCODE,"ORG",ORGANTYPE,level,PARENTDEPTCODE from "TJ_org"
                    connect by prior DEPTCODE=PARENTDEPTCODE 
                    start with PARENTDEPTCODE=1 order by level;

/*
  使用游标实现数据的批量逻辑处理
*/
declare
   VARUSERDEPTCODE VARCHAR(255); --定义与表字段相同类型
   cursor mycursor is --定义游标
          select userdeptcode from tj_user;
         
   my_record mycursor%rowtype;  --定义游标记录类型
   Counter int :=0;
  
begin
   open mycursor;  --打开游标
   if mycursor%isopen  then  --判断打开成功
   loop --循环获取记录集
     fetch mycursor into my_record; --获取游标中的记录
         if mycursor%found then  --游标的found属性判断是否有记录
            --进行实际的业务处理Begin
            if my_record.userdeptcode=90033751 then --网省一级用户更新
              update tj_user set USERORGCODE=90033751 where userdeptcode=90033751;
              dbms_output.put_line(my_record.userdeptcode||'A');
            else --非网省一级用户更新
              update tj_user set USERORGCODE=
                   (select DEPTCODE from (select DEPTCODE,ORGDESC,ORGANTYPE,PARENTDEPTCODE,Levelfact from TJ_ORG_NEW
                                                 connect by prior PARENTDEPTCODE=DEPTCODE 
                                                 start with DEPTCODE=my_record.userdeptcode
                                                 order by Levelfact) where ORGANTYPE=1 and Levelfact=2)
                            where (userdeptcode<>90033751) and
                                  (userdeptcode in (select DEPTCODE from TJ_ORG_NEW
                                                      connect by prior PARENTDEPTCODE=DEPTCODE 
                                                      start with DEPTCODE=my_record.userdeptcode)); 
             dbms_output.put_line(my_record.userdeptcode||'B');         
            end if;
            --进行实际的业务处理End
         else
            exit;
         end if;
   end loop;
   else
     dbms_output.put_line('游标没有打开');
   end if;
  close mycursor;
end;