CODEWITHSUNDEEP
oracle-databaseplsql
Workkkkk
Workkkkk

Reputation: 285

PL/SQL Store return variable of a function in a Boolean variable

So I have a function that accepts a student id and checks to see if the user exists. I created the function without error and I'm trying to store the return variable in a Boolean like this and print the result 

SQL> DECLARE
 2  tf BOOLEAN;
 3  BEGIN
 4    EXECUTE tf := valid_stud(5)
 5    DBMS_OUTPUT.PUT_LINE(tf);
 6  END;
 7  /

But this is getting me an error.

ERROR at line 4: ORA-06550: line 4, column 9: PLS-00103: Encountered the symbol "TF" when expecting one of the following: := . ( @ % ; immediate The symbol "." was substituted for "TF" to continue.

My function is this

CREATE OR REPLACE FUNCTION valid_stud(v_student_id NUMBER) RETURN BOOLEAN
IS
v_id NUMBER;
BEGIN
  SELECT student_id
  INTO v_id
  FROM STUDENT
  WHERE student_id = v_student_id;
  IF SQL%FOUND THEN
      RETURN TRUE;
  END IF;

EXCEPTION
    WHEN no_data_found THEN RETURN FALSE;

END valid_stud;
/

Upvotes: 0

Views: 1112

Answers (1)

Littlefoot
Littlefoot

Reputation: 143163

Here's a demonstration which shows how to do that.

Sample table:

SQL> create table student (
  2    student_id   number
  3  );

Table created.

Function: I removed unnecessary check whether SELECT returned a value; if it did, fine. If not, it'll raise an exception.

SQL> create or replace function valid_stud (
  2    v_student_id number
  3  ) return boolean is
  4    v_id   number;
  5  begin
  6    select student_id into
  7      v_id
  8    from student
  9    where student_id = v_student_id;
 10
 11    return true;
 12  exception
 13    when no_data_found then
 14      return false;
 15  end valid_stud;
 16  /

Function created.

Testing:

SQL> set serveroutput on
SQL> declare
  2    tf boolean;
  3  begin
  4    tf := valid_stud(5);
  5    if tf then
  6       dbms_output.put_line('Student exists');
  7    else
  8       dbms_output.put_line('Student does not exist');
  9    end if;
 10  end;
 11  /
Student does not exist

PL/SQL procedure successfully completed.

SQL>

Upvotes: 2

Related Questions