几个存储过程的实例

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 PRO_ADD_REGIONS(R_NAME IN VARCHAR2) AS
P_COUNT NUMBER;
P_CODE NUMBER;
BEGIN
       SELECT COUNT(*) INTO P_COUNT FROM TBL_REGIONS;
       SELECT  T.REGIONS_CODE INTO P_CODE FROM TBL_REGIONS T WHERE T.REGIONS_ID = P_COUNT;
       --DBMS_OUTPUT.put_line(P_COUNT||'---'||P_CODE);
       INSERT INTO TBL_REGIONS(REGIONS_NAME,REGIONS_SORT,REGIONS_CODE,REGIONS_SUPERCODE,REGIONS_IS_LEAF)VALUES(R_NAME,P_COUNT+1,P_CODE+10000,0,1);
       COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE "PRO_DELETE_DEPARTMENT"
(
  P_DEPARTMENT_ID IN NUMBER  
) AS 
V_DEPARTMENT_SORT NUMBER ;
V_DEPARTMENT_SUPERCODE NUMBER ;
V_COUNT NUMBER;
BEGIN
  SELECT department_supercode INTO V_DEPARTMENT_SUPERCODE FROM tbl_department WHERE department_id = P_DEPARTMENT_ID ;
  SELECT department_sort INTO V_DEPARTMENT_SORT FROM tbl_department WHERE department_id = P_DEPARTMENT_ID ;
  
  --SELECT COUNT(*) INTO V_COUNT FROM tbl_person WHERE department_id = P_DEPARTMENT_ID ;
  --DBMS_OUTPUT.put_line('--人员总数---'||V_COUNT);
  
  DELETE FROM tbl_person WHERE department_id = P_DEPARTMENT_ID ;
  DELETE FROM tbl_department WHERE department_id = P_DEPARTMENT_ID ;
  
  UPDATE tbl_department SET department_sort = department_sort - 1 WHERE department_supercode = V_DEPARTMENT_SUPERCODE AND department_sort >=0 AND department_sort >= V_DEPARTMENT_SORT ; 
END PRO_DELETE_DEPARTMENT;
/

　　

CREATE OR REPLACE PROCEDURE PRO_DELETE_REGIONS(REGID IN NUMBER) AS
P_C NUMBER;
CURSOR UNIT_ALL IS SELECT * FROM TBL_UNIT U WHERE U.REGIONS_ID = REGID;
BEGIN
       FOR UNIT_ROW IN UNIT_ALL LOOP
           --DBMS_OUTPUT.put_line('---单位----'||UNIT_ROW.UNIT_ID);
           PRO_DELETE_UNIT(UNIT_ROW.UNIT_ID);           
       END LOOP;
       DELETE FROM Tbl_Regions WHERE REGIONS_ID = REGID ;  
END;
/

　　

CREATE OR REPLACE PROCEDURE "PRO_DELETE_UNIT"
(
  P_UNITID IN NUMBER  
) AS 
CURSOR  DEP_ALL IS SELECT * FROM TBL_DEPARTMENT T WHERE T.UNIT_ID =  P_UNITID ORDER BY T.DEPARTMENT_ID;
BEGIN
  --DELETE FROM tbl_department WHERE UNIT_ID = P_UNITID ;
  FOR DEP_ROW IN DEP_ALL LOOP
      --DBMS_OUTPUT.put_line('-部门--'||DEP_ROW.DEPARTMENT_ID);
      PRO_DELETE_DEPARTMENT(DEP_ROW.DEPARTMENT_ID);       
  END LOOP; 
  DELETE FROM tbl_unit WHERE UNIT_ID = P_UNITID ;  
END PRO_DELETE_UNIT;
/

　　

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;
/