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; 