Write your first PL/SQL blocks, procedures and functions in Oracle, handle exceptions and query results correctly, and avoid the mistakes that make PL/SQL slow or unsafe.
PL/SQL is Oracle's procedural extension to SQL. It adds variables, conditions, loops, exception handling and named program units (procedures, functions, packages, triggers) around ordinary SQL statements, and it runs inside the database server. The smallest complete program is an anonymous block:
SET SERVEROUTPUT ONBEGIN
DBMS_OUTPUT.PUT_LINE('Hello from PL/SQL');
END;
/
Hello from PL/SQL
PL/SQL procedure successfully completed.
The / on its own line tells SQL*Plus, SQLcl or SQL Developer to send the block to the database. SET SERVEROUTPUT ON makes the client display what DBMS_OUTPUT wrote; without it the block runs but prints nothing.
Why run code in the database
Fewer round trips. A block containing twenty SQL statements is sent to the server once, instead of twenty network calls from the application.
SQL is native. You write SELECT, INSERT, UPDATE and DELETE directly in the code, with PL/SQL variables used as bind variables automatically.
One place for data rules. Logic in a stored procedure applies to every application and tool that calls it.
Privileges. You can grant EXECUTE on a procedure without granting access to the tables it changes.
Your long query needed undo that was already overwritten. How Oracle read consistency uses undo, how to read V$UNDOSTAT, and why committing inside a loop causes it.
Every PL/SQL unit has up to three sections. Only the executable section is required.
DECLARE-- declarations (optional)
v_count PLS_INTEGER;
BEGIN-- executable statements (required)SELECTCOUNT(*) INTO v_count FROM employees;
DBMS_OUTPUT.PUT_LINE('Employees: '|| v_count);
EXCEPTION
-- error handlers (optional)WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed: '|| SQLERRM);
RAISE;
END;
/
Blocks can be nested, and an inner block can catch an error so the outer block continues.
Variables and types
DECLARE
v_name employees.full_name%TYPE; -- same type as the column
v_emp employees%ROWTYPE; -- a record with one field per column
c_raise CONSTANT NUMBER :=0.05;
v_hired DATE := SYSDATE;
v_is_sales BOOLEAN :=FALSE;
BEGINSELECT*INTO v_emp FROM employees WHERE employee_id =1;
v_name := v_emp.full_name;
v_is_sales := (v_emp.department ='SALES');
DBMS_OUTPUT.PUT_LINE(v_name ||' new salary '|| v_emp.salary * (1+ c_raise));
END;
/
Use %TYPE and %ROWTYPE wherever a variable holds column data. If the column is later widened, the code keeps working without a change.
Assignment is :=. Comparison is =. BOOLEAN has long been a PL/SQL type; from Oracle Database 23ai it is also a SQL column type.
Conditions and loops
DECLARE
v_salary employees.salary%TYPE :=61000;
v_band VARCHAR2(10);
BEGIN
IF v_salary >=75000THEN
v_band :='HIGH';
ELSIF v_salary >=55000THEN
v_band :='MID';
ELSE
v_band :='ENTRY';
END IF;
FOR i IN1 .. 3 LOOP
DBMS_OUTPUT.PUT_LINE(v_band ||' '|| i);
END LOOP;
END;
/
Note the spelling ELSIF. CASE statements and expressions, WHILE loops and basic LOOP ... EXIT WHEN ... END LOOP are also available.
Working with query results
SELECT ... INTO must return exactly one row. Zero rows raises NO_DATA_FOUND; more than one raises TOO_MANY_ROWS.
For multiple rows, use a cursor FOR loop. Oracle opens, fetches and closes the cursor for you:
BEGINFOR r IN (SELECT full_name, salary
FROM employees
WHERE department ='SALES'ORDERBY full_name)
LOOP
DBMS_OUTPUT.PUT_LINE(r.full_name ||': '|| r.salary);
END LOOP;
END;
/
Asha Rao: 52000
Ben Ortiz: 61000
After a DML statement, SQL%ROWCOUNT tells you how many rows it affected.
Exception handling
DECLARE
v_salary employees.salary%TYPE;
BEGINSELECT salary INTO v_salary FROM employees WHERE employee_id =999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such employee');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Expected one row');
END;
/
Raise your own errors with RAISE_APPLICATION_ERROR, using a number from -20000 to -20999:
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
An unhandled exception rolls back the statement that the client sent (the whole anonymous block or procedure call), but not earlier work in the same transaction.
Procedures, functions and packages
A procedure performs an action. A function returns a value and can be called from SQL.
CREATEOR REPLACE PROCEDURE raise_salary (
p_employee_id IN employees.employee_id%TYPE,
p_percent IN NUMBER
) ASBEGINUPDATE employees
SET salary = ROUND(salary * (1+ p_percent /100), 2)
WHERE employee_id = p_employee_id;
IF SQL%ROWCOUNT =0THEN
RAISE_APPLICATION_ERROR(-20002, 'Employee '|| p_employee_id ||' not found');
END IF;
END raise_salary;
/CREATEOR REPLACE FUNCTION annual_cost (
p_department IN employees.department%TYPE
) RETURN NUMBER AS
v_total NUMBER;
BEGINSELECT NVL(SUM(salary), 0) INTO v_total
FROM employees
WHERE department = p_department;
RETURN v_total;
END annual_cost;
/
Call them:
BEGIN
raise_salary(p_employee_id =>1, p_percent =>10);
COMMIT;
END;
/SELECT annual_cost('SALES') AS sales_cost FROM dual;
SALES_COST
----------
118200
The procedure does not commit. Leaving transaction control to the caller lets several calls succeed or fail together.
A package groups related procedures, functions, types and constants. It has a specification (the public interface) and a body (the implementation). Changing only the body does not invalidate code that depends on the specification, which is the main reason production code lives in packages rather than standalone procedures.
A trigger is a block that fires automatically on DML, DDL or database events. Use triggers sparingly; logic hidden in triggers is hard to trace.
Compilation errors
If you see Warning: Procedure created with compilation errors, the object exists but is invalid. Show the errors:
SHOW ERRORS
SELECT line, position, text
FROM user_errors
WHERE name ='RAISE_SALARY'ORDERBY sequence;
Find invalid objects with SELECT object_name, object_type FROM user_objects WHERE status = 'INVALID';.
Privileges
CREATE PROCEDURE lets you create procedures, functions and packages in your own schema. CREATE TRIGGER is separate.
By default a stored unit runs with the privileges of its owner (definer's rights). Add AUTHID CURRENT_USER to run with the caller's privileges instead.
Inside definer's rights code, privileges received through roles are not active. If a procedure fails with ORA-00942: table or view does not exist on a table you can query interactively, you need a direct grant on that table.
Common mistakes
Row-by-row processing. A loop that runs one UPDATE per row switches between the PL/SQL and SQL engines each time. Write a single SQL statement when you can; when you cannot, use BULK COLLECT and FORALL to process rows in batches.
WHEN OTHERS THEN NULL; silently discards every error. If you catch OTHERS, log the error and re-raise it with RAISE.
Committing inside a loop breaks atomicity and can cause ORA-01555: snapshot too old on the cursor you are reading.
Building SQL by concatenating input with EXECUTE IMMEDIATE opens you to SQL injection. Use bind variables: EXECUTE IMMEDIATE 'DELETE FROM employees WHERE employee_id = :id' USING p_id;.
Relying on DBMS_OUTPUT for logging. It is buffered and only shown after the call finishes, and only if the client asks for it. Write to a log table for anything that matters.
Comparing with NULL.v_x = NULL is never true. Use IS NULL.
Create, find, drop, disable and enable primary keys in Oracle, including composite keys, identity columns, the index behind the constraint and the ORA errors you will hit.
