Stored Procedures

A stored procedure is a named, reusable PL/SQL block saved in the database. Unlike anonymous blocks, procedures can be called by name from other PL/SQL code, SQL*Plus, or application code.

Creating a Procedure

CREATE OR REPLACE PROCEDURE procedure_name (
  parameter1 IN  datatype,
  parameter2 OUT datatype,
  parameter3 IN OUT datatype
) AS
BEGIN
  -- executable statements
EXCEPTION
  -- error handlers
END procedure_name;
/

CREATE OR REPLACE creates the procedure if it doesn't exist, or replaces it if it does — safe to run repeatedly.

Parameter Modes

Mode	Direction	Description
IN	Caller → procedure	Read-only inside the procedure (default)
OUT	Procedure → caller	Write-only; returns a value to the caller
IN OUT	Both directions	Caller passes a value; procedure can modify and return it

A Simple Example

CREATE OR REPLACE PROCEDURE greet_employee (
  p_name IN VARCHAR2
) AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END greet_employee;
/

Calling a Procedure

From PL/SQL:

BEGIN
  greet_employee('Alice');
END;
/

From SQL*Plus:

EXEC greet_employee('Alice');
-- or
EXECUTE greet_employee('Alice');

Using OUT Parameters

CREATE OR REPLACE PROCEDURE get_employee_salary (
  p_id     IN  employees.id%TYPE,
  p_salary OUT employees.salary%TYPE
) AS
BEGIN
  SELECT salary INTO p_salary FROM employees WHERE id = p_id;
END get_employee_salary;
/

-- Calling it:
DECLARE
  v_sal employees.salary%TYPE;
BEGIN
  get_employee_salary(101, v_sal);
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_sal);
END;
/

Dropping a Procedure

DROP PROCEDURE procedure_name;

Viewing Procedure Source

SELECT text FROM user_source WHERE name = 'GREET_EMPLOYEE' ORDER BY line;

Why Use Procedures?

• Reusability — write logic once, call it everywhere
• Security — grant EXECUTE on a procedure without granting table access
• Performance — compiled and cached in the shared pool
• Abstraction — hide complex implementation behind a simple interface
• Reduced network traffic — one call executes many SQL statements

Checking for Compilation Errors

If a procedure fails to compile, Oracle stores the error. View it with:

SHOW ERRORS PROCEDURE procedure_name;
-- or
SELECT * FROM user_errors WHERE name = 'PROCEDURE_NAME';