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

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

CREATE OR REPLACE PROCEDURE "PRO_INIT_SORT" AS
CURSOR 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" AS
P_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) AS
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;
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;




评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

沙漏无语

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值