Oracle PL/SQL/Stored Procedure Function/Procedure Call

A PL/SQL procedure with no parameter

   
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE InsertIntoTemp AS
  2    v_Num1      NUMBER := 1;
  3    v_Num2      NUMBER := 2;
  4    v_String1   VARCHAR2(50) := "Hello World!";
  5    v_String2   VARCHAR2(50) := "-- This message brought to you by PL/SQL!";
  6    v_OutputStr VARCHAR2(50);
  7  BEGIN
  8    INSERT INTO MyTable (num_col, char_col)
  9      VALUES (v_Num1, v_String1);
 10    INSERT INTO MyTable (num_col, char_col)
 11      VALUES (v_Num2, v_String2);
 12
 13    SELECT char_col
 14      INTO v_OutputStr
 15     FROM MyTable
 16     WHERE num_col = v_Num1;
 17    DBMS_OUTPUT.PUT_LINE(v_OutputStr);
 18
 19    SELECT char_col
 20      INTO v_OutputStr
 21     FROM MyTable
 22     WHERE num_col = v_Num2;
 23    DBMS_OUTPUT.PUT_LINE(v_OutputStr);
 24
 25    ROLLBACK;
 26
 27  END InsertIntoTemp;
 28  /
Procedure created.
SQL>
SQL> BEGIN
  2    InsertIntoTemp;
  3  END;
  4  /
Hello World!
-- This message brought to you by PL/SQL!
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from MyTable;
no rows selected
SQL>
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>

Call a stored procedure in a PL/SQL block

   
SQL>
SQL>
SQL>  create or replace procedure my_proc as
  2    begin
  3      dbms_output.put_line ( "Hello World" );
  4    end my_proc;
  5    /
Procedure created.
SQL>
SQL>
SQL>
SQL>  set serverout on
SQL>
SQL>  begin
  2      my_proc;
  3    end;
  4    /
Hello World
PL/SQL procedure successfully completed.
SQL>

Call a stored procedure then other statements

   
SQL>
SQL> create or replace procedure swap(
  2    p_parm1 in out number,
  3    p_parm2 in out number ) as
  4    l_temp number;
  5  begin
  6    l_temp := p_parm1;
  7    p_parm1 := p_parm2;
  8    p_parm2 := l_temp;
  9  end swap;
 10  /
Procedure created.
SQL>
SQL> set serverout on
SQL>
SQL> declare
  2   l_num1 number := 100;
  3   l_num2 number := 101;
  4  begin
  5   swap( l_num1, l_num2 );
  6   dbms_output.put_line( "l_num1 = " || l_num1 );
  7   dbms_output.put_line( "l_num2 = " || l_num2 );
  8  end;
  9  /
l_num1 = 101
l_num2 = 100
PL/SQL procedure successfully completed.
SQL>
SQL>

Calling ParameterLength illegally (ORA-6502)...

   
SQL>
SQL> CREATE OR REPLACE PROCEDURE ParameterLength (
  2    p_Parameter1 IN OUT VARCHAR2,
  3    p_Parameter2 IN OUT NUMBER) AS
  4  BEGIN
  5    p_Parameter1 := "abcdefghijklmno";
  6    p_Parameter2 := 12.3;
  7  END ParameterLength;
  8  /
Procedure created.
SQL>
SQL> DECLARE
  2    v_Variable1 VARCHAR2(10);
  3    v_Variable2 NUMBER(7,3);
  4  BEGIN
  5    ParameterLength(v_Variable1, v_Variable2);
  6  END;
  7  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "sqle.PARAMETERLENGTH", line 5
ORA-06512: at line 5

SQL>

Default parameter value

   
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE DefaultTest (
  2    p_ParameterA NUMBER DEFAULT 10,
  3    p_ParameterB VARCHAR2 DEFAULT "abcdef",
  4    p_ParameterC DATE DEFAULT SYSDATE) AS
  5  BEGIN
  6    DBMS_OUTPUT.PUT_LINE(
  7      "A: " || p_ParameterA ||
  8      "  B: " || p_ParameterB ||
  9      "  C: " || TO_CHAR(p_ParameterC, "DD-MON-YYYY"));
 10  END DefaultTest;
 11  /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> BEGIN
  2    DefaultTest(p_ParameterA => 7, p_ParameterC => "30-DEC-95");
  3  END;
  4  /
A: 7  B: abcdef  C: 30-DEC-1995
PL/SQL procedure successfully completed.
SQL>

Dependency Example

   
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL> CREATE TABLE session (
  2    department       CHAR(3),
  3    course           NUMBER(3),
  4    description      VARCHAR2(2000),
  5    max_lecturer     NUMBER(3),
  6    current_lecturer NUMBER(3),
  7    num_credits      NUMBER(1),
  8    room_id          NUMBER(5)
  9    );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
  2              VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION AlmostFull (
  2    p_Department session.department%TYPE,
  3    p_Course     session.course%TYPE)
  4    RETURN BOOLEAN IS
  5
  6    studentCount NUMBER;
  7    studentMax     NUMBER;
  8    v_ReturnValue     BOOLEAN;
  9    v_FullPercent     CONSTANT NUMBER := 80;
 10  BEGIN
 11    SELECT current_lecturer, max_lecturer
 12      INTO studentCount, studentMax
 13      FROM session
 14      WHERE department = p_Department
 15      AND course = p_Course;
 16
 17    IF (studentCount / studentMax * 100) >= v_FullPercent THEN
 18      v_ReturnValue := TRUE;
 19    ELSE
 20      v_ReturnValue := FALSE;
 21    END IF;
 22
 23    RETURN v_ReturnValue;
 24  END AlmostFull;
 25  /
Function created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE markFullSession AS
  2    CURSOR c_session IS
  3      SELECT department, course
  4        FROM session;
  5  BEGIN
  6    FOR v_ClassRecord IN c_session LOOP
  7      IF AlmostFull(v_ClassRecord.department, v_ClassRecord.course) THEN
  8        INSERT INTO MyTable (char_col) VALUES
  9          (v_ClassRecord.department || " " || v_ClassRecord.course ||
 10           " is almost full!");
 11      END IF;
 12    END LOOP;
 13  END markFullSession;
 14  /
Procedure created.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL> drop table session;
Table dropped.
SQL>

Exceptions in Subprograms

   
SQL>
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE RaiseError (
  2    p_Raise IN BOOLEAN := TRUE,
  3    p_ParameterA OUT NUMBER) AS
  4  BEGIN
  5    p_ParameterA := 7;
  6
  7    IF p_Raise THEN
  8      RAISE DUP_VAL_ON_INDEX;
  9    ELSE
 10      RETURN;
 11    END IF;
 12  END RaiseError;
 13  /
Procedure created.
SQL> DECLARE
  2    v_TempVar NUMBER := 1;
  3  BEGIN
  4    INSERT INTO MyTable (num_col, char_col)
  5      VALUES (v_TempVar, "Initial value");
  6    RaiseError(FALSE, v_TempVar);
  7
  8    INSERT INTO MyTable (num_col, char_col)
  9      VALUES (v_TempVar, "Value after successful call");
 10
 11    v_TempVar := 2;
 12    INSERT INTO MyTable (num_col, char_col)
 13      VALUES (v_TempVar, "Value before 2nd call");
 14    RaiseError(TRUE, v_TempVar);
 15  EXCEPTION
 16    WHEN OTHERS THEN
 17      INSERT INTO MyTable (num_col, char_col)
 18        VALUES (v_TempVar, "Value after unsuccessful call");
 19  END;
 20  /
PL/SQL procedure successfully completed.
SQL>
SQL> show error
No errors.
SQL>
SQL>
SQL> select * from MyTable;
   NUM_COL CHAR_COL
---------- ------------------------------------------------------------
         1 Initial value
         7 Value after successful call
         2 Value before 2nd call
         2 Value after unsuccessful call
SQL>
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>

Exception throwed out of procedure call

   
SQL>
SQL> CREATE TABLE lecturer (
  2    id               NUMBER(5) PRIMARY KEY,
  3    first_name       VARCHAR2(20),
  4    last_name        VARCHAR2(20),
  5    major            VARCHAR2(30),
  6    current_credits  NUMBER(3)
  7    );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
  2                VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
  2                VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE AddNewStudent (
  2    p_FirstName  lecturer.first_name%TYPE,
  3    p_LastName   lecturer.last_name%TYPE,
  4    p_Major      lecturer.major%TYPE DEFAULT "Economics") AS
  5  BEGIN
  6    INSERT INTO lecturer VALUES (20001, p_FirstName, p_LastName, p_Major, 0);
  7  END AddNewStudent;
  8  /
Procedure created.
SQL>
SQL> BEGIN
  2    AddNewStudent("S", "S");
  3  END;
  4  /
PL/SQL procedure successfully completed.
SQL>
SQL> BEGIN
  2    AddNewStudent(p_FirstName => "Veronica",
  3                  p_LastName => "Vassily");
  4  END;
  5  /
BEGIN
*
ERROR at line 1:
ORA-00001: unique constraint (sqle.SYS_C006785) violated
ORA-06512: at "sqle.ADDNEWSTUDENT", line 6
ORA-06512: at line 2

SQL>
SQL> drop table lecturer;
Table dropped.
SQL>

Forward Declarations

   
SQL> DECLARE
  2    v_TempVal BINARY_INTEGER := 5;
  3
  4    PROCEDURE B(p_Counter IN OUT BINARY_INTEGER);
  5
  6    PROCEDURE A(p_Counter IN OUT BINARY_INTEGER) IS
  7    BEGIN
  8      IF p_Counter > 0 THEN
  9        B(p_Counter);
 10        p_Counter := p_Counter - 1;
 11      END IF;
 12    END A;
 13
 14    PROCEDURE B(p_Counter IN OUT BINARY_INTEGER) IS
 15    BEGIN
 16      p_Counter := p_Counter - 1;
 17      A(p_Counter);
 18    END B;
 19  BEGIN
 20    B(v_TempVal);
 21  END;
 22  /
PL/SQL procedure successfully completed.
SQL>
SQL>

Pass parameter by data type

   
SQL>
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE DefaultTest (
  2    p_ParameterA NUMBER DEFAULT 10,
  3    p_ParameterB VARCHAR2 DEFAULT "abcdef",
  4    p_ParameterC DATE DEFAULT SYSDATE) AS
  5  BEGIN
  6    DBMS_OUTPUT.PUT_LINE(
  7      "A: " || p_ParameterA ||
  8      "  B: " || p_ParameterB ||
  9      "  C: " || TO_CHAR(p_ParameterC, "DD-MON-YYYY"));
 10  END DefaultTest;
 11  /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL>
SQL> BEGIN
  2    DefaultTest(7);
  3  END;
  4  /
A: 7  B: abcdef  C: 18-JUN-2008
PL/SQL procedure successfully completed.
SQL>

This script demonstrates how to create and call a simple procedure.

   
SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE AddNewAuthor(p_ID emp.ID%TYPE,p_FirstName emp.fname%TYPE,p_LastName emp.lname%TYPE) AS
  2  BEGIN
  3    INSERT INTO emp (id, fname, lname) VALUES (p_ID, p_FirstName, p_LastName);
  4  END AddNewAuthor;
  5  /
Procedure created.
SQL>
SQL>
SQL> DECLARE
  2    
  3    v_NewFirstName emp.fname%TYPE := "C";
  4    v_NewLastName emp.lname%TYPE := "C";
  5    v_NewAuthorID emp.ID%TYPE := 100;
  6  BEGIN
  7    AddNewAuthor(v_NewAuthorID, v_NewFirstName, v_NewLastName);
  8  END;
  9  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>

Use named notation

   
SQL> CREATE OR REPLACE PROCEDURE CallMe(pA VARCHAR2,pB NUMBER,pC BOOLEAN,pD DATE) AS
  2  BEGIN
  3    NULL;
  4  END CallMe;
  5  /
SP2-0804: Procedure created with compilation warnings
SQL>
SQL> DECLARE
  2    v1 VARCHAR2(10);
  3    v2 NUMBER(7,6);
  4    v3 BOOLEAN;
  5    v4 DATE;
  6  BEGIN
  7    CallMe(pA => v1, pB => v2,pC => v3,pD => v4);
  8  END;
  9  /
PL/SQL procedure successfully completed.
SQL>
SQL>

Use named notation, but with a different order of the formal parameters

   
SQL>
SQL> -- First create a procedure with 4 parameters
SQL> CREATE OR REPLACE PROCEDURE CallMe(pA VARCHAR2,pB NUMBER,pC BOOLEAN,pD DATE) AS
  2  BEGIN
  3    NULL;
  4  END CallMe;
  5  /
SP2-0804: Procedure created with compilation warnings

SQL> DECLARE
  2    v1 VARCHAR2(10);
  3    v2 NUMBER(7,6);
  4    v3 BOOLEAN;
  5    v4 DATE;
  6  BEGIN
  7    CallMe(pB => v2, pC => v3,pD => v4,pA => v1);
  8  END;
  9  /
PL/SQL procedure successfully completed.
SQL>