/* FORMATTED ON 2011/3/8 上午 10:36:27 (QP5 V5.115.810.9015) */DECLARE V_DEPTNAME VARCHAR2(100);
CURSOR DEPTNO_CURSOR IS SELECT DEPTNO FROM ESS_DEPARTMENT_DEPTNO_DEPTNAME;BEGIN FOR C IN DEPTNO_CURSOR LOOP SELECT SHORT_NAME INTO V_DEPTNAME FROM PS_DEPARTMENT@ESS_TO_PAPR WHERE DEPTNO = C.DEPTNO;
UPDATE ESS_DEPARTMENT_DEPTNO_DEPTNAME SET DEPTNAME = V_DEPTNAME WHERE DEPTNO = C.DEPTNO; END LOOP;END;
---查詢部級部門和只屬於中心的處級部門
/* Formatted on 2011/3/9 下午 02:55:33 (QP5 v5.115.810.9015) */SELECT DEPTNO, SHORT_NAME, 0, 0, 0, TO_CHAR (SYSDATE, 'YYYYMM'), TO_CHAR (SYSDATE, 'YYYYQ'), SYSDATE, 0, 0 FROM PS_DEPARTMENT@ESS_TO_PAPR WHERE GRADE = '5' AND STATUS = 'O' AND COM = 'M'UNIONSELECT DEPTNO, SHORT_NAME, 0, 0, 0, TO_CHAR (SYSDATE, 'YYYYMM'), TO_CHAR (SYSDATE, 'YYYYQ'), SYSDATE, 0, 0 FROM PS_DEPARTMENT@ESS_TO_PAPR D1, (SELECT O.CHILD FROM PS_DEPARTMENT@ESS_TO_PAPR D, PS_ORGANIZATION@ESS_TO_PAPR O WHERE D.DEPTNO = O.PARENT -- AND CHILD = '26AZ0000' AND D.COM = 'M' AND (O.CLOSE_DATE IS NULL) AND D.GRADE = 2.7) A WHERE D1.DEPTNO = A.CHILD AND SHORT_NAME NOT LIKE '%管理室%' AND D1.GRADE = 7 AND D1.STATUS = 'O' --找稽核室、法務室等UNIONSELECT DEPTNO, SHORT_NAME, 0, 0, 0, TO_CHAR (SYSDATE, 'YYYYMM'), TO_CHAR (SYSDATE, 'YYYYQ'), SYSDATE, 0, 0 FROM PS_DEPARTMENT@ESS_TO_PAPR D1, (SELECT O.CHILD FROM PS_DEPARTMENT@ESS_TO_PAPR D, PS_ORGANIZATION@ESS_TO_PAPR O WHERE D.DEPTNO = O.PARENT -- AND CHILD = '26AZ0000' AND D.COM = 'M' AND (O.CLOSE_DATE IS NULL) AND D.GRADE = 3) A WHERE D1.DEPTNO = A.CHILD AND SHORT_NAME NOT LIKE '%管理室%' AND D1.GRADE = 7 AND D1.STATUS = 'O';
