PL/SQL 游标选择唯一记录并在平面文件中打印

Question

我在游标中有一组值。例如：

CURSOR c_stock_option IS
SELECT empid, '1' AS ISenrolled
FROM employee emp
UNION ALL
SELECT empid, '2' AS ISenrolled
FROM employee emp;

现在我想检查 empid 是否同时出现在第一个选择中（其中 IEnrolled =1）和第二个选择（其中 IEnrolled =2）。我只想从第一个 select where enroll=1 获取值并拒绝 where enroll=2。我只想打印符合此标准的记录。

FOR v_stock_option_record IN c_stock_option LOOP
    IF v_esppstock_recs  IN (v_stock_option_record.empid) THEN

    END IF;
    -- Participant file.
    v_member_string_1 := v_stock_option_record.empid || G_DELIMITER || --1. participant id
    v_stock_option_record.last_name || G_DELIMITER || --4. Last Name
    v_stock_option_record.first_name || G_DELIMITER || --5. First Name
END loop;

在查询的第一部分中，它选择所有已购买股票的员工（这将仅给出已购买股票的员工集合，查询的其他部分给出公司中的所有在职员工，因此位于选择的第一部分中的员工将始终位于选择的第二部分中，但是位于选择的第二部分中的员工不一定位于第一部分中，当员工出现在两个部分中时，我需要做什么。只需选择 isenrolled=1 的员工）。 下面是区分的SQL

SELECT
    empid,
    '1' AS ISenrolled
    FROM employee emp,
    hrempusf usf
    where emp.employee = usf.employee
          AND usf.field_key = 76 ---- 76 determines that employee has purchased stocks
UNION ALL
    SELECT
     empid,
    '2' AS ISenrolled
     FROM employee emp;

Answer 1

这是一种可行的方法，它只会返回 1=>IsEnrolled（如果存在），否则会返回 2 IsEnrolled。

“数据”是模仿你的两个选择语句。

with data as(
select 1 empId, 1 ISEnrolled from dual
union
select 2 empId, 1 ISEnrolled from dual
union
select 3 empId, 1 ISEnrolled from dual
union
select 4 empId, 1 ISEnrolled from dual
union
select 5 empId, 1 ISEnrolled from dual /** these 5 are mimicing your first select */
union
select 1 empId, 2 ISEnrolled from dual /** the next are the 'all' */
union
select 2 empId, 2 ISEnrolled from dual
union
select 3 empId, 2 ISEnrolled from dual
union
select 4 empId, 2 ISEnrolled from dual
union
select 5 empId, 2 ISEnrolled from dual
union
select 6 empId, 2 ISEnrolled from dual
union
select 7 empId, 2 ISEnrolled from dual
union
select 8 empId, 2 ISEnrolled from dual
union
select 9 empId, 2 ISEnrolled from dual
union
select 10 empId, 2 ISEnrolled from dual)
,
onlyOneIsEnrolled as (
  select empId, isEnrolled
    from data
    where isEnrolled = 1
) ,
notInOneIsEnrolled as(
  select empId, isEnrolled
    from data d
    where not exists(select null
                           from onlyOneIsEnrolled ooie
                            where ooie.empid = d.empId
                            )
)
select EmpId, isEnrolled
  from onlyOneIsEnrolled
  union
select EmpId, isEnrolled
  from notInOneIsEnrolled
order by isEnrolled, EmpId

这只是完成任务的一种方法，onlyOneIsEnrolled 收集所有 1，然后 notInOneIsEnrolled 获取所有不在上面的 emps，查询的最后部分将它们放在一起。

有很多方法可以完成此任务，这利用 CTE，但根据您的需要，您可以无需使用 with 子句即可完成此操作。

Answer 2

为此，您不需要复杂的 PL/SQL，只需要 LEFT OUTER JOIN。这将返回所有 EMPLOYEE 记录，无论它是否与 HREMPUSF 记录匹配。

SELECT
     empid
     , nvl2(usf.field_key ,'1', '2') AS ISenrolled
  FROM employee emp
     left outer join hrempusf usf
          on ( usf.employee = emp.employee
              and usf.field_key = 76 )

如果第一个参数不为 null，则 NVL2() 返回第二个值；如果为 null，则返回第三个参数。