调试经验——Oracle中使用游标表达式（CURSOR expression）及动态游标（SYS_REFCURSOR）

问题描述：

在Oracle示例程序库的emp表中，想要查询入职日期早于其manager的员工，如何查询？

解决方法：

可自定义一个函数，用于判断员工的入职日期是否早于其manager的入职日期。

CREATE OR REPLACE FUNCTION F_TEMP (CUR SYS_REFCURSOR, MGR_HIREDATE DATE)
   RETURN NUMBER
IS
   EMP_HIREDATE   DATE;
   BEFORE         NUMBER := 0;
   AFTER          NUMBER := 0;
BEGIN
   LOOP
      FETCH CUR INTO EMP_HIREDATE;

      EXIT WHEN CUR%NOTFOUND;

      IF EMP_HIREDATE < MGR_HIREDATE
      THEN
         AFTER := AFTER + 1;
      ELSE
         BEFORE := BEFORE + 1;
      END IF;
   END LOOP;

   CLOSE CUR;

   IF BEFORE > AFTER
   THEN
      RETURN 1;
   ELSE
      RETURN 0;
   END IF;
END;

然后，在子查询中调用这个定义好的函数（F_TEMP）。

  SELECT E1.EMPNO,
         E1.ENAME,
         E1.HIREDATE,
         E1.MGR,
         E3.ENAME "Mgr Name",
         E3.HIREDATE "Mgr's Hire Date",
         CASE WHEN E3.HIREDATE < E1.HIREDATE THEN 'No' ELSE 'Yes' END
            AS "Hired earlier than mgr?"
    FROM EMP E1, EMP E3
   WHERE     F_TEMP (CURSOR (SELECT E2.HIREDATE
                               FROM EMP E2
                              WHERE E1.MGR = E2.EMPNO),
                     E1.HIREDATE) = 1
         AND E1.MGR = E3.EMPNO
ORDER BY E1.ENAME;

查询结果：

彩蛋：

以上示例仅用于说明CURSOR表达式。

其实，对于这个需求，不使用函数，而直接用以下子查询即可实现。

SELECT E1.EMPNO,
       E1.ENAME,
       E1.HIREDATE,
       E1.MGR,
       E2.ENAME "Mgr Name",
       E2.HIREDATE "Mgr's Hire Date",
       CASE WHEN E2.HIREDATE < E1.HIREDATE THEN 'No' ELSE 'Yes' END
          AS "Hired earlier than mgr?"
  FROM EMP E1, EMP E2
 WHERE E1.HIREDATE < E2.HIREDATE AND E1.MGR = E2.EMPNO

示例中的基础数据设置，请参考：

https://blog.csdn.net/hpdlzu80100/article/details/83180714

参考文章：

https://blog.csdn.net/mydreamneverstop/article/details/78604033