Procedure is used to execute the block of SQL statements multiple times in the database server. It works similar to the function or procedure of any programming language. The way of creating, calling and removing procedure in a Oracle database has been shown in this tutorial.

Syntax:

CREATE [OR REPLACE] PROCEDURE Procedure_Name
    [(p_1 IN/OUT datatype [,p_2 IN/OUT datatype] ... [,p_n IN/OUT datatype])]
AS | IS
    [Variable declaration]
BEGIN
    executable_section

[EXCEPTION
    exception_section]

END [procedure_name];

You can create a new procedure that does not exist or create a procedure by replacing an existing procedure. The IN/OUT parameters is defined after the CREATE OR REPLACE statement. The internal variables of the procedure is declared after the AS statement. The SQL statements of the procedure is declared after the BEGIN statement. The EXCEPTION part is used for handling error after calling the procedure.

Example-1: Create procedure without input parameter

create or replace PROCEDURE COUNT_STD
IS
    DEPT STD_INFO.DEPARTMENT%type;
    TOTAL INTEGER;
BEGIN    
    SELECT STD_INFO.DEPARTMENT, count(STD_INFO.ID)
    INTO DEPT, TOTAL
    FROM STD_INFO
    WHERE STD_INFO.DEPARTMENT = 'CSE'
    GROUP BY STD_INFO.DEPARTMENT;    
    DBMS_OUTPUT.PUT_LINE ('Department' || '     ' || 'Total Students');
    DBMS_OUTPUT.PUT_LINE (DEPT || '     ' || TOTAL);
END;    

The following output will appear if the procedure is created successfully in the system.

Run the following statements to check the output of the procedure.

SET SERVEROUTPUT ON;
CALL COUNT_STD();

The following output will appear based on the table data after calling the procedure.

Example-2: Create procedure with input parameter

create or replace PROCEDURE search_std
(SID IN STD_BASICS.ID%TYPE) 
IS
    n STD_BASICS.NAME%TYPE;
    b STD_BASICS.BATCH%TYPE;
    s STD_BASICS.SEMESTER%TYPE;
BEGIN    
    SELECT NAME, BATCH, SEMESTER
    INTO n, b, s
    FROM STD_BASICS
    WHERE STD_BASICS.ID = SID;
    DBMS_OUTPUT.PUT_LINE (n || ',' || b || ',' || s);
END;

The following output will appear if the procedure is created successfully in the system.

Run the following statements to check the output of the procedure.

SET SERVEROUTPUT ON;
CALL SEARCH_STD(4);

The following output will appear if the id value, 4 exists in the std_basic table.

Example-3: Create procedure with the cursor

create or replace PROCEDURE read_std_list
IS
   CURSOR std_list IS
      SELECT *
      FROM STD_BASICS
      WHERE BATCH = 57;
BEGIN
   FOR std_row
   IN std_list
   LOOP
      DBMS_OUTPUT.put_line (std_row.name || ',' || std_row.batch || ',' || std_row.semester);
   END LOOP;
END;

The following output will appear if the procedure is created successfully in the system.

Run the following statements to check the output of the procedure.

SET SERVEROUTPUT ON;
CALL READ_STD_LIST();

The following output will appear based on the table data.

Example-3: Drop Procedure

The ‘DROP’ statement is used to delete any procedure from the database.

DROP PROCEDURE procedure_name;