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;