You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
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.
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.
| 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 |
CREATE OR REPLACE PROCEDURE greet_employee (
p_name IN VARCHAR2
) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END greet_employee;
/
From PL/SQL:
BEGIN
greet_employee('Alice');
END;
/
From SQL*Plus:
EXEC greet_employee('Alice');
-- or
EXECUTE greet_employee('Alice');
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;
/
DROP PROCEDURE procedure_name;
SELECT text FROM user_source WHERE name = 'GREET_EMPLOYEE' ORDER BY line;
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';
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.