Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 38 ยท Cursors, Procedures and Functions

Stored Procedures in Oracle PL/SQL

Create, call, change and drop Oracle stored procedures. Learn IN, OUT and IN OUT parameters, default values, named notation, invoker rights and compile errors.

An anonymous PL/SQL block disappears as soon as it runs. A stored procedure is a named block saved inside the database. You write it once, Oracle compiles it, and any application or other PL/SQL code can call it by name. Because it runs next to the data, a procedure that performs five statements costs one network round trip instead of five, and the business rule it contains lives in one place.

A procedure does something (insert a row, send a message, change a status) and does not return a value through RETURN. If you need a value you can use inside SQL, write a function instead.

Oracle cannot run on this site. The PL/SQL below is read-only; the runnable examples are Python and SQLite stand-ins that model parameter passing and a procedure's effect on a table.

Creating a procedure

SQL
CREATE OR REPLACE PROCEDURE give_raise (
  p_employee_id IN employees.employee_id%TYPE,
  p_percent     IN NUMBER DEFAULT 5
)
IS
  v_old employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_old FROM employees WHERE employee_id = p_employee_id;

  UPDATE employees
     SET salary = salary * (1 + p_percent / 100)
   WHERE employee_id = p_employee_id;

  DBMS_OUTPUT.PUT_LINE('Salary ' || v_old || ' raised by ' || p_percent || '%');
END give_raise;
/

The parts, in order: CREATE OR REPLACE PROCEDURE name, an optional parameter list in parentheses, IS (or AS), a declaration section with no DECLARE keyword, then BEGIN ... END. The closing / on its own line tells SQL*Plus and SQL Developer to send the block. OR REPLACE swaps the code without losing the grants other users hold on it, which is why you should prefer it to drop-and-create.

Use %TYPE to tie parameter and variable types to a column. If the column later changes from NUMBER(8,2) to NUMBER(10,2), your code follows.

If you see Warning: Procedure created with compilation errors, run SHOW ERRORS (SQL*Plus) or query USER_ERRORS for the line and message. A procedure with errors is stored but marked INVALID.

Calling a procedure

SQL
-- from SQL*Plus / SQL Developer
EXEC give_raise(101, 10)

-- from PL/SQL
BEGIN
  give_raise(p_employee_id => 101, p_percent => 10);
END;
/

Both positional (give_raise(101, 10)) and named (p_percent => 10) notation work, and you may mix them if the positional ones come first. Named notation lets you skip defaulted parameters (give_raise(101) uses 5%) and keeps calls readable as parameter lists grow.

Parameter modes

Each parameter has a mode that says which way data flows:

Mode Direction Notes
IN (default) caller to procedure Read-only inside. Can be a literal or expression. May have a DEFAULT.
OUT procedure to caller Starts as NULL; the caller must pass a variable.
IN OUT both ways Caller passes a variable; the procedure may read and change it.
SQL
CREATE OR REPLACE PROCEDURE employee_summary (
  p_id     IN  employees.employee_id%TYPE,
  p_name   OUT VARCHAR2,
  p_salary IN OUT NUMBER
)
IS
BEGIN
  SELECT first_name INTO p_name FROM employees WHERE employee_id = p_id;
  p_salary := p_salary * 1.1;          -- reads the incoming value, writes a new one
END;
/

DECLARE
  v_name   VARCHAR2(50);
  v_salary NUMBER := 4000;
BEGIN
  employee_summary(101, v_name, v_salary);
  DBMS_OUTPUT.PUT_LINE(v_name || ' ' || v_salary);   -- e.g. Ada 4400
END;
/

By default, OUT and IN OUT parameters are passed by value: Oracle works on a copy and writes it back when the procedure ends successfully. If the procedure raises an unhandled exception, the caller's variable keeps its old value. The NOCOPY hint asks for pass-by-reference, which helps with large collections but changes that safety guarantee.

The Python stand-in below models the copy-in, copy-out rule so you can see when the caller's variable changes.

Python IN, OUT and IN OUT (Python stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

What a procedure does to the data

The effect of give_raise is easy to model in SQL. This SQLite version uses a fixed UPDATE with the same arithmetic, and the SELECT shows the change:

SQL The effect of give_raise (SQL stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Rights, dependencies and housekeeping

By default a procedure runs with the privileges of its owner (definer rights). Users only need EXECUTE on the procedure, not access to the tables it touches, which makes procedures a clean way to expose controlled operations. Add AUTHID CURRENT_USER after the procedure name to run with the caller's rights instead, which is what you want for utility code that should respect each caller's own permissions.

SQL
GRANT EXECUTE ON give_raise TO hr_clerk;

SELECT object_name, status FROM user_objects WHERE object_type = 'PROCEDURE';

DROP PROCEDURE give_raise;

Oracle tracks dependencies: if you drop or change a table that give_raise uses, the procedure becomes INVALID and Oracle recompiles it automatically on the next call. You can force it with ALTER PROCEDURE give_raise COMPILE.

Good habits: keep procedures small and single-purpose, name parameters with a p_ prefix and locals with v_ so they never clash with column names (a parameter named salary would shadow the column in WHERE salary = salary), and do not COMMIT inside a procedure unless that is its documented job. Let the caller decide where the transaction ends.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens to an OUT parameter when a procedure starts?

    An OUT parameter has no incoming value. It starts as NULL and the procedure must assign it.
  2. 2. Why use CREATE OR REPLACE instead of DROP followed by CREATE?

    Dropping removes the privileges other users were granted; replacing keeps them.
  3. 3. Which call is valid for give_raise(p_employee_id, p_percent DEFAULT 5) if you only want the default percent?

    Parameters with a DEFAULT can be omitted from the end of a positional call or skipped with named notation.
  4. 4. A procedure is created with compilation errors. How do you find the cause?

    Oracle stores the procedure as INVALID and records line numbers and messages in USER_ERRORS.

Summary

  • A stored procedure is a named, compiled PL/SQL block that performs an action and is called with EXEC or from other PL/SQL.
  • Parameters have modes IN, OUT and IN OUT; use positional or named notation, and defaults for optional inputs.
  • OUT values are copied back only if the procedure finishes normally.
  • Definer rights (default) let users run code without table access; AUTHID CURRENT_USER switches to invoker rights.
  • Check USER_ERRORS for compile problems and keep transaction control with the caller.

Next: Stored Functions and Parameter Modes