PL/SQL Stored Procedures

Stored procedures are called blocks that allow grouping and organization of SQL and PL/SQL commands.


Stored Procedure


CREATE OR REPLACE PROCEDURE proc_name
   (paramater_name datatype, ...)
IS
BEGIN
   -- pl/sql statement
END;

Parameters are used to transfer data between values and call the procedure.
Parameters can have one of 3 ways: IN, OUT and IN OUT.

Example Stored Procedure:
CREATE OR REPLACE PROCEDURE upd_address
   (p_id IN NUMBER,
   p_address IN VARCHAR2)
IS
BEGIN
   UPDATE students
   SET address= p_address
   WHERE student_id = p_id;
END;