PL/SQL IF statement and variable declaration

Question

I am trying to make the code posted below work, but I am receiving an error about the IF statement:

DECLARE 
    a number; 
BEGIN
    SELECT Pers_API.Is_Manager_(10018) INTO a from dual;
EXCEPTION
    WHEN no_data_found THEN a := 0;
END;

IF (a >0) THEN 
    SELECT 1 from dual;
ELSE 
    SELECT 0 from dual;
END IF;

In the first block I am declaring an 'A' variable and setting it to 0 (by the way, the API call result will be 0 or 1, nothing else). The first block works fine or - at least - I do think so. But the IF block is not working and having this error:

PLS-00103:Encountered the symbol "IF"

Any help is appreciated.

Update: I have tried it this way:

DECLARE 
    a number:=0; 
BEGIN
    SELECT Pers_API.Is_Manager_(10018) INTO a from dual;

    IF (:a >0) THEN
        SELECT 1 from dual;
    ELSE 
        SELECT 0 from dual;
    END IF;
END;

I received the exception below:

ORA-01008: not all variables bound

Update

In other words, what I am trying to do is:

if Pers_API.Is_Manager_(10018) returns true (as 1), I have to do another select statement
if it's false (as 0), I have to return null.

Any other ideas are appreciated.

if i remove the colon I receive this error PLS-00428: an INTO clause expected in this this SELECT statement. the SELECT within THEN and ELSE blocks — MuhanadY
– MuhanadY, Commented Aug 14, 2018 at 14:39
You can't do this in pl\sql block SELECT 1 from dual, you have to select a value into a variable — Ilia Maskov
– Ilia Maskov, Commented Aug 14, 2018 at 14:41
how to achieve this this, In other words what am trying to do is, if Pers_API.Is_Manager_(10018) returns true (as 1) I have to do another select statement and if its false (as 0) I have to return null. any other ideas are appreciated — MuhanadY
– MuhanadY, Commented Aug 14, 2018 at 14:43
All of your selects need to select into something. (Also, you don't need to query the dual table to assign a value to a variable, as PL/SQL has an assignment operator :=, and PL/SQL doesn't need brackets around if conditions.) — William Robertson
– William Robertson, Commented Aug 14, 2018 at 16:49

Littlefoot · Accepted Answer · 2018-08-14 20:03:19Z

A few objections, if I may.

there's no need to select from dual in order to assign a value to a variable, when that value is returned by a function - simply assign it
exception handler seems to be superfluous. You said that the is_manager_ function returns 0 or 1 and nothing else. Therefore, it'll always return something, so - why do you expect that SELECT to return NO_DATA_FOUND? Besides, such a case should be handled by the function itself (i.e. you have to make sure that it returns 0 or 1 and nothing else)
the last paragraph you wrote is somewhat strange. You said that - if the function returns 1, you have to run another SELECT statement. Otherwise, you have to return null (while code you wrote, select 0 ... suggests a zero (0), not null); so, which one is true?
also, saying that you have to return something (null, 0, whatever) suggests that piece of code you wrote should be a function that returns a value. Is that so?

Code that actually compiles might look like in this example.

First, a function (based on Scott's schema) that checks whether an employee is a manager:

SQL> create or replace function is_manager_ (par_empno in number)
  2    return number
  3  is
  4    /* function checks whether PAR_EMPNO belongs to a manager and returns:
  5       - 1 - employee is     a manager
  6       - 0 - employee is NOT a manager
  7    */
  8    retval number := 0;
  9  begin
 10    select max(1)
 11      into retval
 12      from emp e
 13      where mgr = par_empno;
 14
 15    return retval;
 16  exception
 17    when no_data_found then
 18      return retval;
 19  end;
 20  /

Function created.

SQL>

Code you posted, rewritten so that it does something:

if an employee is a manager, returns his/her salary
otherwise, it does not

SQL> declare
  2    a      number := is_manager_(&&l_empno);
  3    l_sal  emp.sal%type;
  4  begin
  5    if a > 0 then
  6       select sal
  7         into l_sal
  8         from emp
  9         where empno = &&l_empno;
 10       dbms_output.put_line('Employee is a manager and his/her salary is ' || l_sal);
 11    else
 12       null;
 13       dbms_output.put_line('Employee is not a manager');
 14    end if;
 15  end;
 16  /
Enter value for l_empno: 7698
Employee is a manager and his/her salary is 2850

PL/SQL procedure successfully completed.

SQL> undefine l_empno
SQL> /
Enter value for l_empno: 7369
Employee is not a manager

PL/SQL procedure successfully completed.

SQL> undefine l_empno
SQL> /
Enter value for l_empno: -1
Employee is not a manager

PL/SQL procedure successfully completed.

SQL>

See if you can adjust it so that it fits your needs. If not, do answer to my objections posted at the beginning of this message, and we'll see what to do next.

yes you are right to have some objection about my code. that was a prototype. and yes if the result of api is 1 I have to run some select queries and 0 will return nothing.

Kaushik Nayak · Accepted Answer · 2018-08-14 19:43:05Z

It appears to me that what you are really trying to achieve here is to run a query which must use the result obtained from the package function as a condition. If so, the PL/SQL block wouldn't be needed and can be written as a basic select operation.

What is still not clear from your explanation is whether your select query SELECT 1 from dual is the actual query you are about to use in your code.Generally, in the select statement, we use CASE expression to evaluate the conditional logic, which essentially does what IF ELSE condition was supposed to do to return the desired result.The query would have a structure of this form,

SELECT
    CASE
        WHEN pers_api.is_manager_(10018) = 1 THEN some_expression
        ELSE some_other_expression --If you omit the else condition, it defaults to null when all others are false
    END
    END
as
FROM DUAL;

More specifically, if you want to have the resultant expression come from another database table, the queries can take the form

SELECT
    CASE
        WHEN pers_api.is_manager_(10018) = 1 THEN some_expression
        ELSE some_other_expression --If you omit the else condition, it defaults to null when all others are false
    END
as
FROM table_to_run_query
WHERE where_clauses = some_values;

If you say that your resultant expression from the table involves many other constructs, the challenge would be to compose it in a single query cleverly to avoid any PL/SQL at all. That would be achievable, but it will require that you explain us with some sample data and expected result, as to what exactly you want,preferably in a new question.

It would not be complete if I don't mention the REFCURSOR technique to obtain results from a select query in PL/SQL. It is either through DBMS_SQL.RETURN_RESULT(Oracle 12c and above) or using a REFCURSOR bind variable running it in SQL* Plus or as Script in other tools (F5).

VARIABLE x refcursor;
DECLARE BEGIN

    IF
        pers_api.is_manager_(10018) = 1
    THEN
        OPEN :x FOR SELECT some_columns FROM some_tables;
    ELSE
        OPEN :x FOR SELECT some_columns FROM other_tables;
    END IF;
   --DBMS_SQL.RETURN_RESULT(:x); --12c and above
END;
/

PRINT x -- 11g

thank you very much for your efforts. you light a torch on my road.

Collectives™ on Stack Overflow

PL/SQL IF statement and variable declaration

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related