oracle调用子存储过程+游标循环实例

原文出处：http://blog.youkuaiyun.com/estelle_belle/article/details/40823705

一，有子节点的部门的子节点的排序，调用子存储过程

[html] view plain copy

1. CREATE OR REPLACE PROCEDURE "PRO_INIT_SORT" AS  
2. CURSOR cur_department_all IS select * from tbl_department;  
3. VAR_COUNT NUMBER ;  
4. VAR_OUT_COUNT NUMBER := 0;  
5. BEGIN  
6.   FOR department_row IN cur_department_all LOOP  
7.     SELECT COUNT(1) INTO VAR_COUNT FROM tbl_department WHERE unit_id = department_row.unit_id AND department_supercode = department_row.department_code ;  
8.     --上述查询的是有所有的子节点的部门  
9.     IF VAR_COUNT != 0 THEN  
10.       --dbms_output.put_line(department_row.unit_id||'-'||department_row.department_code||'-'||var_count);  
11.       PRO_INIT_DEPARTMENT_SORT(department_row.unit_id , department_row.department_id) ;  
12.     END IF ;  
13.     VAR_OUT_COUNT := VAR_OUT_COUNT + 1 ;      
14.   END LOOP ;   
15.   DBMS_OUTPUT.PUT_LINE('总数：'||VAR_OUT_COUNT);  
16. END PRO_INIT_SORT ;  

[html]  view plain  copy

1. CREATE OR REPLACE PROCEDURE "PRO_INIT_DEPARTMENT_SORT"  
2. (  
3.   UNIT_ID IN NUMBER ,  
4.   SUPER_CODE IN NUMBER   
5. )  
6. -- 初始化TBL_DEPARTMENT表的DEPARTMENT_SORT字段 以同DEPARTMENT_SUPERCODE方式查询使用rownum值更新DEPARTMENT_SORT字段  
7. AS   
8.  -- CURSOR cur_department IS SELECT * FROM tbl_department where unit_id = TARGET_UNIT_ID and department_supercode = TARGET_SUPERCODE ORDER BY department_sort ASC;  
9. CURSOR cur_department IS SELECT rownum rn , d.* FROM tbl_department d where unit_id = UNIT_ID and department_supercode = SUPER_CODE;  
10. BEGIN  
11.   FOR department_row IN cur_department LOOP  
12.     update tbl_department set department_sort = department_row.rn where department_id = department_row.department_id ;  
13.     -- NULL ;  
14.   END LOOP ;  
15. --  NULL;  
16. END PRO_INIT_DEPARTMENT_SORT;  

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

在数据记录都不为空的时候查询出来结果上没有差别的.

但当COUNT(1)查询的那列有空的时候空的是要被去掉的不记入统计中.这样查询出来的结果是不一样的.

二，没有子节点的排序

[html]  view plain  copy

1. CREATE OR REPLACE PROCEDURE "INT_SORT_N" AS  
2. P_OUT  NUMBER;  
3. P_COUNT NUMBER:=0;  
4. CURSOR  CUR_DEPARTMENT IS SELECT T.UNIT_ID FROM TBL_DEPARTMENT T GROUP BY T.UNIT_ID ORDER BY T.UNIT_ID;  
5. BEGIN  
6.      FOR  DEP_ROW  IN CUR_DEPARTMENT  LOOP  
7.           SELECT COUNT(1) INTO P_OUT FROM TBL_DEPARTMENT T WHERE T.UNIT_ID = DEP_ROW.UNIT_ID AND T.DEPARTMENT_SUPERCODE = 0;  
8.           DBMS_OUTPUT.put_line('UID--'||DEP_ROW.UNIT_ID||'--部门--'||P_OUT);  
9.           INT_DEPARMENT_SORT(DEP_ROW.UNIT_ID);  
10.           P_COUNT := P_COUNT + 1;  
11.      END LOOP;  
12.      DBMS_OUTPUT.put_line('总数:'||P_COUNT);  
13. END;  

[html]  view plain  copy

1. create or replace procedure INT_DEPARMENT_SORT(UNIT_ID2 IN NUMBER) AS  
2. CURSOR 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;  
3. begin  
4.       FOR DEP_ROW IN DEPARTMENT_ALL LOOP  
5.           --dbms_output.put_line('---'||UNIT_ID2);  
6.           UPDATE TBL_DEPARTMENT T SET T.DEPARTMENT_SORT = DEP_ROW.RN WHERE T.DEPARTMENT_ID = DEP_ROW.DEPARTMENT_ID;         
7.       END LOOP;            
8. end INT_DEPARMENT_SORT;  

示例：

create or replace procedure p_update_planning_uses (
  v_work_flow_no varchar,
  v_planning_uses varchar,
  v_biz_id number,
  v_is_leaf number,
  v_user_id varchar
)
as
CURSOR temp_leaf IS SELECT BIZ_ID, IS_LEAF
  FROM BDIR_BD_TREE_REQ
 WHERE IS_DELETE = '0'
 AND IS_LEAF != v_is_leaf
 START WITH BIZ_ID = v_biz_id
        AND IS_LEAF = v_is_leaf
CONNECT BY PRIOR BUILDING_DIRECTORY_TREE_ID = PARENTID;

begin
FOR cur_row IN temp_leaf LOOP
   if cur_row.IS_LEAF = 3 then
     update BDIR_BUILDING_REQ set PLANNING_USES = v_planning_uses, UPDATE_TIME = sysdate, UPDATE_BY=v_user_id where BDIR_BUILDING_ID = cur_row.BIZ_ID;
     update BDIR_BD_TREE_REQ set WORK_FLOW_NO = v_work_flow_no, UPDATE_TIME = sysdate, UPDATE_BY=v_user_id where BIZ_ID = cur_row.BIZ_ID and IS_LEAF = 3;
  end if;
  if cur_row.IS_LEAF = 5 then
     update BDIR_UNIT_REQ set PLANNING_USES = v_planning_uses, UPDATE_TIME = sysdate, UPDATE_BY=v_user_id where BDIR_UNIT_ID = cur_row.BIZ_ID;
     update BDIR_BD_TREE_REQ set WORK_FLOW_NO = v_work_flow_no, UPDATE_TIME = sysdate, UPDATE_BY=v_user_id where BIZ_ID = cur_row.BIZ_ID and IS_LEAF = 5;
  end if;
  if cur_row.IS_LEAF = 6 then
     update BDIR_FLOOR_REQ set PLANNING_USES = v_planning_uses, UPDATE_TIME = sysdate, UPDATE_BY=v_user_id where FLOOR_ID = cur_row.BIZ_ID;
     update BDIR_BD_TREE_REQ set WORK_FLOW_NO = v_work_flow_no, UPDATE_TIME = sysdate, UPDATE_BY=v_user_id where BIZ_ID = cur_row.BIZ_ID and IS_LEAF = 6;
  end if;
  if cur_row.IS_LEAF = 7 then
     update BDIR_BUILDINGSHOUSE_BASE_REQ set PLANNING_USES = v_planning_uses, UPDATE_TIME = sysdate, UPDATE_BY=v_user_id where BDIR_BUILDINGS_BASE_ID = cur_row.BIZ_ID;
     update BDIR_BD_TREE_REQ set WORK_FLOW_NO = v_work_flow_no, UPDATE_TIME = sysdate, UPDATE_BY=v_user_id where BIZ_ID = cur_row.BIZ_ID and IS_LEAF = 7;
  end if;
END LOOP ;
commit;
end;

嵌套循环

create or replace procedure test_procedure is
  --a表游标定义
  cursor a_cursor is
    select id from a;
  --b表游标定义
  cursor b_cursor(aid number) is
    select id from b where b.id = aid;
begin
  for a_cur in a_cursor loop
    for b_cur in b_cursor(a_cur.id) loop
      --这里是你要执行的操作，比如insert到c
      insert into c values (b_cur.id);
      commit;
    end loop;
  end loop;