PL/SQL cursor with IF condition

Question

I Have below cursor in the code.

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

This curosr is getting information from EMP table.

But I need to change is as per below

• 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.

I can check the Value in the Table1 as below

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

And I want write something like

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

BEGIN
    --Stored procedure logic
END

How to do it?

Solution 1

In another way you can just keep two CURSORS for those two scenarios and OPEN them on the condition. Declaring two CURSORS will not affect to the performance; you should be careful when OPEN a CURSOR and FETCHING from it.

PROCEDURE Get_Details_On_Condition ( name_ OUT VARCHAR2, isEmp IN BOOLEAN )
IS
  CURSOR get_emp IS
     SELECT name
     FROM EMP;
  CURSOR get_std IS
     SELECT name
     FROM STUDENT;
BEGIN
   IF isEmp THEN
      OPEN get_emp ;
      FETCH get_emp INTO name_ ;
      CLOSE get_emp ;
   ELSE
      OPEN get_std ;
      FETCH get_std INTO name_ ;
      CLOSE get_std ;
   END IF;
RETURN name_;
END Get_Details_On_Condition;

Solution 2

Using if .. else construct is not proper (neither supported). You can use REF cursor to achieve the same like below.

 DECLARE type cur1 REF CURSOR;
 c1 cur1;

  BEGIN 
   IF (variable1 := 'true') THEN 
     OPEN c1 FOR 'SELECT * FROM STUDENT'; 
   ELSE 
     OPEN c1 FOR 'SELECT * FORM EMP';
 END IF ; 
  END; 

Idea taken from Oracle Community Forum Post

NOTE: I didn't included the entire code block (I mean cursor processing, closing etc) cause the main concern here is "How he will declare/define conditional cursor". So, pointed that particular in my code snippet. Since, rest of the part like processing the cursor and closing can be directly be found in Oracle specification.

For a complete code block, you can refer the answer given by Harsh

Solution 3

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;

Comments

[Shyam534]

I am getting the below exception Compilation errors for PROCEDURE SCHEMA1.IMPORT Error: PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor Line: 44 Text: DECLARE

[Maheswaran Ravisankar]

@Shyam534 When you use this code snippet in a Procedure or Function, DECLARE is not needed!

[Sebas]

Your answer was not required. It doesn't bring anything more to Rahul's answer.

[Harsh]

sebas: I did tell in my answer, it was taken from the link provided by rahul. To help shyam to take the correct code snippet to overcome his problem, I posted him the correct code snippet.

[Sebas]

I don't see the difference with Rahul's answer. You posted exactly the same thing except that you didn't use the OP's query. Edit: indeed you added the TYPE declaration... I'm not sure it justifies another answer though. I'll add a comment in the other answer for you.

[Sebas]

Rahul, it would be good if you fixed your answer as, as it stands, it is highly incomplete even though you do answer the question. I'm thinking of, as suggested partially by @Harsh, the TYPE declaration, the CLOSE statement and why not, one iteration over the recently opened cursor.

[Rahul]

@Sebas, Thanks and appreciate your edit. Yes, I didn't included the entire code block (I mean cursor processing, closing etc) cause the main concern of OP here is "How he will declare/define conditional cursor". So, pointed that particular in my code snippet. Since, rest of the part like processing the cursor and closing can be directly be found in Oracle specification.

[Rahul]

Though, what @Sebas pointed is correct (the core of the answer is same) but you have included rest of the part like TYPE declaration, closing once it's done. With that context it can't be considered as complete useless. +1 for completing the code block.

[Sebas]

Yes you are right. Would you mind removing the END statement then? Or add some comments in your code mentionning that the statement is incomplete? I am thinking of our fellow beginners using our code.

[Shyam534]

@Allan Thanks , Thats working Solution Others Thanks for your Help :-)

[Thilina Koggalage]

In your answer you should change this: IF (variable1 := 'true') THEN to: IF (variable1 = 'true') THEN as := is using for value assignments.