Create a function and call it in an if statement : IF « PL SQL Statements « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Statements
3. IF

SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );

Table created.

SQL>
SQL> CREATE TABLE books (
  2    isbn      CHAR(10) PRIMARY KEY,
  3    category  VARCHAR2(20),
  4    title     VARCHAR2(100),
  5    num_pages NUMBER,
  6    price     NUMBER,
  7    copyright NUMBER(4),
  8    emp1   NUMBER,
  9    emp2   NUMBER,
 10    emp3   NUMBER
 11  );

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION Threeemp(p_ISBN IN books.isbn%TYPE)
  2    RETURN BOOLEAN AS
  3
  4    v_emp3 books.emp3%TYPE;
  5  BEGIN
  6    SELECT emp3 INTO v_emp3 FROM books WHERE isbn = p_ISBN;
  7
  8    IF v_emp3 IS NULL THEN
  9      RETURN FALSE;
 10    ELSE
 11      RETURN TRUE;
 12    END IF;
 13  END Threeemp;
 14  /

Function created.

SQL>
SQL> set serveroutput on
SQL>
SQL> BEGIN
  2    FOR v_Rec IN (SELECT ISBN, title FROM books) LOOP
  3      IF Threeemp(v_Rec.ISBN) THEN
  4        DBMS_OUTPUT.PUT_LINE('"' || v_Rec.title || '" has 3 emp');
  5      END IF;
  6    END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> drop table books;

Table dropped.

SQL> drop table emp;

Table dropped.