Cursor in if statement - Oracle PLSQL-CodePudding

How use cursor in if statements in Oracle plsql?

if parameter P_USER_ID is not null cursor = P_USER_ID else get from table all users.

If P_USER_ID is not null then
   cursor c_user is Select P_USER_ID from dual;
else 
   cursor c_user is Select * from users;
end if;

PLS-00103: Encountered the symbol "IF" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior The symbol "begin" was substituted for "IF" to continue.

CodePudding user response：

Cursor is declared in declaration section which doesn't allow IFs. But, you can do something like this:

cursor c_user is
  select p_user_id from dual  where p_user_id is not null
  union all
  select user_id   from users where p_user_id is     null;

Note that your idea of selecting * from user wouldn't really work because the same cursor (c_user) can't have just one column (your first cursor) or "many" columns (the second one).

That's why my example uses union where both select statements have to have column list which matches in number of columns and their datatypes; it also means that p_user_id and users.user_id should have the same datatype.

CodePudding user response：

You could do something like

create or replace procedure get_users( p_user_id in integer,
                                       p_rc     out sys_refcursor )
as
begin
  If P_USER_ID is not null then
    open p_rc for 'Select P_USER_ID from dual';
  else 
    open p_rc for 'Select * from users';
  end if;
end;

Be aware that having branching logic produce cursors with a different shape (different number of columns, different column names, different data types) will often make whatever code tries to use that cursor vastly more complicated. Perhaps you really want to write a single query

cursor c_user 
    is select user_id, username from users where p_user_id is null
       union all
       select p_user_id, null from dual where p_user_id is not null