具有IF条件的PL/SQL游标 [英] PL/SQL cursor with IF condition

问题描述

我在代码中有下面的光标.

I Have below cursor in the code.

   CURSOR cur1
   IS
      SELECT a, b, c, d,
        FROM EMP;
BEGIN
    --Stored procedure logic
END

此curosr正在从EMP表获取信息.

This curosr is getting information from EMP table.

但是我需要按照以下内容进行更改

But I need to change is as per below

• 有一个包含键值对的表(Table1).
• 如果Table1值为TRUE，则应使用STUDENT表创建光标
• 如果table1值为FALSE，则应该使用EMP表创建光标.

• There is a table (Table1) with Key Value pairs.
• If the Table1 value is TRUE then the cursor should be created with STUDENT table
• If the table1 value is FALSE then the cursor should be created with EMP table.

我可以检查Table1中的值，如下所示

I can check the Value in the Table1 as below

select t.value into variable1 from Table1 t where s.key='xxxxx';

我想写类似的东西

IF variable1 := 'true'
 curosr created with STUDENT
 ELSE
  curosr created with EMP
END IF

BEGIN
    --Stored procedure logic
END

该怎么做?

推荐答案

我希望在不使用动态SQL的情况下解决此问题.如果两个表的处理结果的代码相同，则可以合理地假设各列也相同(或等效).我倾向于使用UNION和子查询来解决此问题:

I would prefer to solve this without using dynamic SQL. If the code to process the results is the same for both tables, then it is reasonable to assume that the columns are the same (or equivalent) as well. My inclination would be to solve this using UNION and sub-queries:

DECLARE
   CURSOR cur1 IS
      SELECT a, b, c, d
      FROM   emp
      WHERE  NOT EXISTS
                (SELECT *
                 FROM   table1
                 WHERE  s.key = 'xxxxx' AND t.VALUE = 'true')
      UNION ALL
      SELECT a, b, c, d
      FROM   student
      WHERE  EXISTS
                (SELECT *
                 FROM   table1
                 WHERE  s.key = 'xxxxx' AND t.VALUE = 'true');
BEGIN
  --Stored procedure logic
END;

这篇关于具有IF条件的PL/SQL游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！