Table of Contents

Stored Subprograms – Overview
Creating Stored Procedures
Parameter Modes: IN, OUT, IN OUT
Creating Stored Functions
Using Functions in SQL Queries
Local Subprograms
Overloading
Managing Subprograms
Autonomous Transactions
Summary and Outlook

Oracle PL/SQL – Procedures and Functions

Subject: Information Technology – Database Systems
Grade: 12th Grade – HTL Computer Science
Prerequisites: PL/SQL Introduction, Control Structures, Cursors, Exceptions
Author: HTL Pinkafeld – IF/IT

1. Stored Subprograms – Overview

1.1 Advantages over Anonymous Blocks

Feature	Anonymous Block	Stored Procedure/Function
Storage	Not stored	Stored in database
Reusability	No	Yes – callable from anywhere
Compilation	Every execution	Once (pre-compiled)
Security	No GRANT possible	EXECUTE privilege grantable
Parameters	Not possible	IN, OUT, IN OUT
Return value	Not possible	Function returns a value

1.2 Procedure vs. Function

Feature	Procedure	Function
Keyword	`PROCEDURE`	`FUNCTION`
Return value	None (only OUT params)	Exactly one value (`RETURN`)
Called	As a statement	In expressions, SQL
Typical use	Execute actions	Compute and return a value

2. Creating Stored Procedures

2.1 Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name
    [(parameter1 [mode] data_type, ...)]
IS  -- or AS
    -- Declaration section (no DECLARE keyword!)
BEGIN
    -- Execution section
EXCEPTION
    -- Error handling (optional)
END [procedure_name];
/

2.2 Simple Procedure without Parameters

CREATE OR REPLACE PROCEDURE show_departments IS
BEGIN
    FOR r IN (SELECT deptno, dname, loc FROM dept ORDER BY deptno) LOOP
        DBMS_OUTPUT.PUT_LINE(
            LPAD(r.deptno, 3) || ' | ' || RPAD(r.dname, 14) || ' | ' || r.loc
        );
    END LOOP;
END show_departments;
/

-- Call:
BEGIN
    show_departments;
END;
/

2.3 OR REPLACE

The OR REPLACE keyword overwrites an existing procedure without an error. Without it, Oracle throws an error if the procedure already exists.

3. Parameter Modes: IN, OUT, IN OUT

3.1 IN Parameter (Default)

IN is the default mode. The value is passed at call time and cannot be changed inside the procedure:

CREATE OR REPLACE PROCEDURE give_raise (
    p_empno   IN emp.empno%TYPE,
    p_percent IN NUMBER
) IS
    v_old_sal emp.sal%TYPE;
BEGIN
    SELECT sal INTO v_old_sal FROM emp WHERE empno = p_empno;

    UPDATE emp
    SET    sal = sal * (1 + p_percent / 100)
    WHERE  empno = p_empno;

    DBMS_OUTPUT.PUT_LINE(
        'Employee ' || p_empno || ': ' || v_old_sal ||
        ' → ' || ROUND(v_old_sal * (1 + p_percent/100), 2)
    );
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found.');
END give_raise;
/

-- Call:
BEGIN
    give_raise(7369, 10);   -- 10% raise for employee 7369
END;
/

3.2 OUT Parameter

OUT parameters return values to the caller. They may not be read inside the procedure (only written):

CREATE OR REPLACE PROCEDURE get_emp_info (
    p_empno    IN  emp.empno%TYPE,
    p_name     OUT emp.ename%TYPE,
    p_salary   OUT emp.sal%TYPE,
    p_found    OUT BOOLEAN
) IS
BEGIN
    SELECT ename, sal
    INTO   p_name, p_salary
    FROM   emp
    WHERE  empno = p_empno;
    p_found := TRUE;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_name    := NULL;
        p_salary  := NULL;
        p_found   := FALSE;
END get_emp_info;
/

-- Call:
DECLARE
    v_name  VARCHAR2(10);
    v_sal   NUMBER;
    v_ok    BOOLEAN;
BEGIN
    get_emp_info(7839, v_name, v_sal, v_ok);
    IF v_ok THEN
        DBMS_OUTPUT.PUT_LINE(v_name || ' earns ' || v_sal);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Not found.');
    END IF;
END;
/

3.3 IN OUT Parameter

IN OUT parameters can be both read and written:

CREATE OR REPLACE PROCEDURE normalize_name (
    p_name IN OUT VARCHAR2
) IS
BEGIN
    p_name := INITCAP(TRIM(p_name));
END normalize_name;
/

DECLARE
    v_n VARCHAR2(50) := '  JOHN DOE  ';
BEGIN
    DBMS_OUTPUT.PUT_LINE('Before: [' || v_n || ']');
    normalize_name(v_n);
    DBMS_OUTPUT.PUT_LINE('After:  [' || v_n || ']');
END;
/

4. Creating Stored Functions

4.1 Syntax

CREATE [OR REPLACE] FUNCTION function_name
    [(parameter [IN] data_type, ...)]
RETURN return_type
IS  -- or AS
    -- Declarations
BEGIN
    -- Logic
    RETURN expression;
EXCEPTION
    ...
END [function_name];
/

4.2 Simple Function

CREATE OR REPLACE FUNCTION annual_salary (
    p_sal  emp.sal%TYPE,
    p_comm emp.comm%TYPE DEFAULT 0
) RETURN NUMBER
IS
    v_comm NUMBER := NVL(p_comm, 0);
BEGIN
    RETURN (p_sal + v_comm) * 12;
END annual_salary;
/

-- Call in PL/SQL:
DECLARE
    v_ann NUMBER;
BEGIN
    v_ann := annual_salary(3000, 500);
    DBMS_OUTPUT.PUT_LINE('Annual salary: ' || v_ann);
END;
/

4.3 Function with Error Handling

CREATE OR REPLACE FUNCTION get_dept_name (
    p_deptno dept.deptno%TYPE
) RETURN VARCHAR2
IS
    v_name dept.dname%TYPE;
BEGIN
    SELECT dname INTO v_name FROM dept WHERE deptno = p_deptno;
    RETURN v_name;
EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN 'Unknown Department';
    WHEN OTHERS        THEN RETURN 'Error: ' || SQLERRM;
END get_dept_name;
/

5. Using Functions in SQL Queries

5.1 Function in SELECT

Custom functions can be used directly in SQL queries – just like built-in functions:

SELECT empno,
       ename,
       sal,
       NVL(comm, 0)              AS commission,
       annual_salary(sal, comm)  AS annual_pay,
       get_dept_name(deptno)     AS department
FROM   emp
ORDER  BY annual_pay DESC;

5.2 Functions in WHERE and ORDER BY

-- Function in WHERE:
SELECT ename, sal
FROM   emp
WHERE  annual_salary(sal, comm) > 40000;

-- Function in ORDER BY:
SELECT ename, deptno
FROM   emp
ORDER  BY get_dept_name(deptno), ename;

5.3 Restrictions for SQL Functions

For a function to be usable in SQL, it must follow these rules:

No DML (no INSERT/UPDATE/DELETE), unless using PRAGMA AUTONOMOUS_TRANSACTION
No COMMIT/ROLLBACK inside the function
No OUT parameters for functions used in SQL
Deterministic: Same input → same output (use DETERMINISTIC for optimizer hints)

6. Local Subprograms

Procedures and functions can be defined locally within a block. They are only visible within that block:

DECLARE
    -- Local function (must come AFTER all variable declarations)
    FUNCTION tax (p_salary NUMBER) RETURN NUMBER IS
    BEGIN
        CASE
            WHEN p_salary <= 1800 THEN RETURN 0;
            WHEN p_salary <= 3000 THEN RETURN p_salary * 0.20;
            WHEN p_salary <= 5000 THEN RETURN p_salary * 0.32;
            ELSE                       RETURN p_salary * 0.42;
        END CASE;
    END tax;

    PROCEDURE show_tax (p_name VARCHAR2, p_sal NUMBER) IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE(
            RPAD(p_name, 12) || 'Salary: ' || LPAD(p_sal, 6) ||
            ' | Tax: ' || LPAD(ROUND(tax(p_sal)), 6)
        );
    END show_tax;

BEGIN
    FOR r IN (SELECT ename, sal FROM emp WHERE deptno = 10) LOOP
        show_tax(r.ename, r.sal);
    END LOOP;
END;
/

7. Overloading

Within a package (or local declaration section), multiple subprograms with the same name but different parameters can coexist:

DECLARE
    FUNCTION format_val (p_value NUMBER) RETURN VARCHAR2 IS
    BEGIN RETURN TO_CHAR(p_value, 'FM999,990.00') || ' USD'; END;

    FUNCTION format_val (p_value DATE) RETURN VARCHAR2 IS
    BEGIN RETURN TO_CHAR(p_value, 'DD/MM/YYYY'); END;

    FUNCTION format_val (p_value VARCHAR2) RETURN VARCHAR2 IS
    BEGIN RETURN '"' || TRIM(p_value) || '"'; END;

BEGIN
    DBMS_OUTPUT.PUT_LINE(format_val(1234.5));
    DBMS_OUTPUT.PUT_LINE(format_val(SYSDATE));
    DBMS_OUTPUT.PUT_LINE(format_val('  Hello  '));
END;
/

8. Managing Subprograms

8.1 Data Dictionary

-- Show all own procedures/functions:
SELECT object_name, object_type, status, last_ddl_time
FROM   user_objects
WHERE  object_type IN ('PROCEDURE', 'FUNCTION')
ORDER  BY object_type, object_name;

-- Show source code:
SELECT line, text
FROM   user_source
WHERE  name = 'ANNUAL_SALARY'
AND    type = 'FUNCTION'
ORDER  BY line;

-- Show compile errors:
SELECT line, position, text
FROM   user_errors
WHERE  name = 'GIVE_RAISE'
ORDER  BY sequence;

8.2 Grant Privileges

GRANT EXECUTE ON annual_salary TO scott;
GRANT EXECUTE ON give_raise TO hr_app;
REVOKE EXECUTE ON annual_salary FROM scott;

9. Autonomous Transactions

9.1 PRAGMA AUTONOMOUS_TRANSACTION

An autonomous transaction runs independently of the calling transaction. Useful for logging (must survive even a ROLLBACK):

CREATE OR REPLACE PROCEDURE log_action (
    p_action  VARCHAR2,
    p_user    VARCHAR2 DEFAULT USER,
    p_info    VARCHAR2 DEFAULT NULL
) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO audit_log (action, usr, ts, info)
    VALUES (p_action, p_user, SYSTIMESTAMP, p_info);
    COMMIT;  -- Commits only THIS transaction – main TX unaffected
END log_action;
/

BEGIN
    log_action('DELETE', USER, 'Attempt to delete dept 10');
    DELETE FROM dept WHERE deptno = 10;  -- Fails (FK violation)
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- Main transaction rolled back
        -- But: log_action was COMMITTED and remains!
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

10. Summary and Outlook

10.1 Procedure vs. Function – Decision Guide

Question	Recommendation
Is a value returned?	Function
Should it be used in SQL?	Function
Multiple output values needed?	Procedure with OUT parameters
Execute actions (DML)?	Procedure
Logging (independent)?	Procedure with AUTONOMOUS_TRANSACTION

10.2 Best Practices

✅ Use OR REPLACE in definitions
✅ Repeat subprogram name after END
✅ Use %TYPE and %ROWTYPE for parameters
✅ Exception handling in every subprogram
✅ Functions in SQL: deterministic and without DML
✅ Communicate errors with RAISE_APPLICATION_ERROR

10.3 Outlook

The next chapter covers Packages – bundling related procedures, functions, types, and variables into a logical unit:

Package Specification vs. Package Body
Package variables (session state)
Overloading in packages
Oracle standard packages (DBMS_OUTPUT, DBMS_UTILITY, UTL_FILE, …)