oracle調用子預存程序+遊標迴圈執行個體

標籤：blog   ar   使用   java   for   strong   資料   div   on   

一，有子節點的部門的子節點的排序，調用子預存程序

CREATE OR REPLACE PROCEDURE "PRO_INIT_SORT" ASCURSOR cur_department_all IS select * from tbl_department;VAR_COUNT NUMBER ;VAR_OUT_COUNT NUMBER := 0;BEGIN  FOR department_row IN cur_department_all LOOP    SELECT COUNT(1) INTO VAR_COUNT FROM tbl_department WHERE unit_id = department_row.unit_id AND department_supercode = department_row.department_code ;    --上述查詢的是有所有的子節點的部門    IF VAR_COUNT != 0 THEN      --dbms_output.put_line(department_row.unit_id||‘-‘||department_row.department_code||‘-‘||var_count);      PRO_INIT_DEPARTMENT_SORT(department_row.unit_id , department_row.department_id) ;    END IF ;    VAR_OUT_COUNT := VAR_OUT_COUNT + 1 ;      END LOOP ;   DBMS_OUTPUT.PUT_LINE(‘總數：‘||VAR_OUT_COUNT);END PRO_INIT_SORT ;

CREATE OR REPLACE PROCEDURE "PRO_INIT_DEPARTMENT_SORT"(  UNIT_ID IN NUMBER ,  SUPER_CODE IN NUMBER )-- 初始化TBL_DEPARTMENT表的DEPARTMENT_SORT欄位 以同DEPARTMENT_SUPERCODE方式查詢使用rownum值更新DEPARTMENT_SORT欄位AS  -- CURSOR cur_department IS SELECT * FROM tbl_department where unit_id = TARGET_UNIT_ID and department_supercode = TARGET_SUPERCODE ORDER BY department_sort ASC;CURSOR cur_department IS SELECT rownum rn , d.* FROM tbl_department d where unit_id = UNIT_ID and department_supercode = SUPER_CODE;BEGIN  FOR department_row IN cur_department LOOP    update tbl_department set department_sort = department_row.rn where department_id = department_row.department_id ;    -- NULL ;  END LOOP ;--  NULL;END PRO_INIT_DEPARTMENT_SORT;

　　注意：COUNT(1)和COUNT(*)

在資料記錄都不為空白的時候查詢出來結果上沒有差別的.

但當COUNT(1)查詢的那列有空的時候空的是要被去掉的不記入統計中.這樣查詢出來的結果是不一樣的.

二，沒有子節點的排序

CREATE OR REPLACE PROCEDURE "INT_SORT_N" ASP_OUT  NUMBER;P_COUNT NUMBER:=0;CURSOR  CUR_DEPARTMENT IS SELECT T.UNIT_ID FROM TBL_DEPARTMENT T GROUP BY T.UNIT_ID ORDER BY T.UNIT_ID;BEGIN     FOR  DEP_ROW  IN CUR_DEPARTMENT  LOOP          SELECT COUNT(1) INTO P_OUT FROM TBL_DEPARTMENT T WHERE T.UNIT_ID = DEP_ROW.UNIT_ID AND T.DEPARTMENT_SUPERCODE = 0;          DBMS_OUTPUT.put_line(‘UID--‘||DEP_ROW.UNIT_ID||‘--部門--‘||P_OUT);          INT_DEPARMENT_SORT(DEP_ROW.UNIT_ID);          P_COUNT := P_COUNT + 1;     END LOOP;     DBMS_OUTPUT.put_line(‘總數:‘||P_COUNT);END;

　　

create or replace procedure INT_DEPARMENT_SORT(UNIT_ID2 IN NUMBER) ASCURSOR DEPARTMENT_ALL IS SELECT rownum rn,T.* FROM TBL_DEPARTMENT T WHERE T.UNIT_ID = UNIT_ID2 AND T.DEPARTMENT_SUPERCODE = 0 ORDER BY T.DEPARTMENT_ID;begin      FOR DEP_ROW IN DEPARTMENT_ALL LOOP          --dbms_output.put_line(‘---‘||UNIT_ID2);          UPDATE TBL_DEPARTMENT T SET T.DEPARTMENT_SORT = DEP_ROW.RN WHERE T.DEPARTMENT_ID = DEP_ROW.DEPARTMENT_ID;             END LOOP;          end INT_DEPARMENT_SORT;

　　

oracle調用子預存程序+遊標迴圈執行個體