Mega Code Archive
Create a function and call it in an if statement
<![CDATA[
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> 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.
]]>