工作備份

/* 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'
UNION
SELECT   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'                              --找稽核室、法務室等
UNION
SELECT   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';