PL/SQL if inside declaration

Question

I'm wondering if there's any way to have an if statement inside the declaration of a PL/SQL procedure. E.g:

procedure testing (no IN NUMBER, name IN VARCHAR2) IS
    if no = 0 then 
      cursor c is 
          select * from table where name = name;
    else 
      cursor c is
          select * from table where name = name;
    end if;
BEGIN 
   --work with cursor c
END testing;

This is more or less the intention of it.

No... you can nest PL/SQL blocks though and so have a declare inside a begin. — Ben, Aug 27 '12 at 11:51

score 1 · Answer 1 · answered Aug 27 '12 at 14:12

No, you can only have variables declaration and initialization in the declarative part.

You can use a cursor variable (REF CURSOR type) and open the cursor from the executable block:

procedure testing (no IN NUMBER, name IN VARCHAR2) IS
    TYPE YOUR_CURSOR_TYPE IS REF CURSOR;
    c YOUR_CURSOR_TYPE;
BEGIN 
   --work with cursor c
   if no = 0 then 
      OPEN c FOR 
          select * from table where name = name;
    else 
      open c FOR
          select * from table where name = name;
    end if;

    ... do something with c 
END testing;

Depending on the columns of your select clause you must decide whether using a strongly typed or weakly typed cursor variable.

Cursor Variables

Alexander Tokarev · Answer 2 · 2012-08-27T12:49:10.160

0

Pardon, haven't read the question correctly. You can't do IF before BEGIN.

If C is ref cursor you could open it either for select * from table or select name from table as well but the intention of it isn't clear enough because you should have the possibility to deal with diffident field list in both cases.

The example from Oracle: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/sqloperations.htm#BABFEJED

edited Aug 27 '12 at 12:49

answered Aug 27 '12 at 12:37

Alexander Tokarev

1,000
7
16

PL/SQL if inside declaration

2 Answers2