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