PL/SQL

Complete Syntax & Examples Reference

Exam Study Sheet | All syntax, structure & code in one place

1. PL/SQL BASICS

1.1 Block Structure

Every PL/SQL program is a block. Structure:
DECLARE
-- variable/constant/cursor declarations (optional)
BEGIN
-- executable statements (mandatory)
EXCEPTION
-- error handling (optional)
END;
/

💡 The / at the end executes the block in SQL*Plus / SQL Developer.

1.2 Variables & Data Types

Declaration
variable_name datatype [(size)] [:= initial_value];

-- Examples:
age NUMBER(4);
name VARCHAR2(30);
done BOOLEAN;
salary NUMBER(8,2) := 0;

Value Assignment — 3 Ways

-- i) Assignment operator
a := b * c;
OK := false;

-- ii) Substitute variable (runtime input)

a := &enter_number;
b := &b;

-- iii) SELECT INTO (must return exactly 1 row)

SELECT col_name INTO var_name FROM table WHERE condition;
SELECT ename INTO e FROM emp WHERE empno = 100;
SELECT sal * 0.15 INTO increased FROM emp WHERE empno = emp_id;

Constants
constant_name CONSTANT datatype := value;

-- Example:
pi CONSTANT NUMBER := 3.14;
Variable Attributes (%TYPE and %ROWTYPE)
-- %TYPE: variable takes the same type as a table column
DECLARE
eno emp.emp_no%TYPE; -- same type as emp.emp_no column

-- %ROWTYPE: variable holds an entire row of a table/cursor

DECLARE
REC EMP1%ROWTYPE;
BEGIN
SELECT * INTO REC FROM EMP WHERE EMPNO = 100;
[Link] := [Link] + [Link] + [Link];
UPDATE EMP1 SET TOTAL = [Link] WHERE EMPNO = 100;
END;

1.3 Output / Display

-- Must enable server output first:
SET SERVEROUTPUT ON;

-- Print a message:
DBMS_OUTPUT.PUT_LINE('Hello World');

-- Concatenate variable into message (|| is concat operator):

DBMS_OUTPUT.PUT_LINE('Result is: ' || sum);
DBMS_OUTPUT.PUT_LINE('Name: ' || c_name);

1.4 Comments
-- This is a single-line comment

/* This is a
multi-line comment */

2. CONTROL STRUCTURES

2.1 IF Statements
IF-THEN
IF condition THEN
statements;
END IF;

-- Example:
IF a > b THEN
DBMS_OUTPUT.PUT_LINE('a is greater');
END IF;

IF-THEN-ELSE
IF condition THEN
statements;
ELSE
 statements;
END IF;

-- Example:
IF A > B THEN
DBMS_OUTPUT.PUT_LINE('A IS GREATER');
ELSE
DBMS_OUTPUT.PUT_LINE('B IS GREATER');
END IF;

IF-THEN-ELSIF (Ladder)
IF condition1 THEN
statements;
ELSIF condition2 THEN
statements;
ELSIF condition3 THEN
statements;
ELSE
statements;
END IF;

-- Example: calculator using user choice

DECLARE
A NUMBER := &A; B NUMBER := &B; C NUMBER; X NUMBER;
BEGIN
X := &ENTER_CHOICE;
IF X=1 THEN C := A+B;
ELSIF X=2 THEN C := A-B;
ELSIF X=3 THEN C := A*B;
ELSIF X=4 THEN C := A/B;
ELSE DBMS_OUTPUT.PUT_LINE('NOT A VALID OPTION');
END IF;
DBMS_OUTPUT.PUT_LINE('RESULT IS ' || C);
END;

2.2 CASE Statement

CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE result
END CASE;

-- Example:
DECLARE
grade CHAR(1) := 'A';
BEGIN
CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Average');
ELSE DBMS_OUTPUT.PUT_LINE('Failed');
END CASE;
END;
2.3 Loops
Simple LOOP (with EXIT WHEN)
LOOP
statements;
EXIT WHEN condition; -- or: IF condition THEN EXIT; END IF;
END LOOP;

-- Example: print 1 to 10
DECLARE
i NUMBER := 1;
BEGIN
LOOP
EXIT WHEN i > 10;
DBMS_OUTPUT.PUT_LINE(i);
i := i + 1;
END LOOP;
END;

WHILE LOOP
WHILE condition LOOP
statements;
END LOOP;

-- Example: multiplication table

WHILE i <= 10 LOOP
a := n * i;
i := i + 1;
END LOOP;

FOR LOOP
FOR counter IN [REVERSE] lower_bound..upper_bound LOOP
statements;
END LOOP;

-- Forward (1 to 10):
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

-- Reverse (10 down to 1):

FOR i IN REVERSE 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

-- Multiplication table of VAR1:

DECLARE VAR1 NUMBER;
BEGIN
VAR1 := 10;
FOR VAR2 IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(VAR1 * VAR2);
END LOOP;
END;

Loop Labels (for nested loops)

<<outer>>
 LOOP
LOOP
EXIT outer WHEN condition; -- exits BOTH loops
-- EXIT WHEN condition; would exit only inner loop
END LOOP;
END LOOP outer;

3. FUNCTIONS

3.1 Syntax
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] datatype [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
-- function body
RETURN value;
END [function_name];
/

💡 Function MUST have a RETURN statement. It returns exactly ONE value.

3.2 Example — Count rows in customers table

CREATE OR REPLACE FUNCTION totalCustomers
RETURN NUMBER
IS
total NUMBER(2) := 0;
BEGIN
SELECT COUNT(*) INTO total FROM customers;
RETURN total;
END;
/

3.3 Calling a Function

-- Method 1: Store return value in variable
DECLARE
c NUMBER(2);
BEGIN
c := totalCustomers();
DBMS_OUTPUT.PUT_LINE('Total Customers: ' || c);
END;
/

-- Method 2: Use directly in SELECT

SELECT totalCustomers() FROM DUAL;

3.4 Function with IN Parameter

CREATE OR REPLACE FUNCTION get_salary(emp_id IN NUMBER)
RETURN NUMBER
IS
 sal NUMBER;
BEGIN
SELECT salary INTO sal FROM employees WHERE id = emp_id;
RETURN sal;
END;
/

4. STORED PROCEDURES

4.1 Syntax
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] datatype [, ...])]
{IS | AS}
BEGIN
-- procedure body
END procedure_name;
/

4.2 Parameter Modes

Mode Description
IN Pass value INTO procedure (default mode)

OUT Get value OUT of procedure (like return)

IN OUT Pass value in AND receive modified value back

4.3 Simple Procedure Example

CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World!');
END;
/

-- Execute it:
EXEC greetings;
-- or:
BEGIN greetings; END;
/

4.4 Procedure with IN/OUT Parameters

-- Procedure: returns multiple values (add, sub, mul, div)
CREATE OR REPLACE PROCEDURE PROCESS(
A IN NUMBER, B IN NUMBER,
C OUT NUMBER, D OUT NUMBER, E OUT NUMBER, F OUT NUMBER)
IS
BEGIN
C := A + B;
 D := A - B;
E := A * B;
F := A / B;
END;
/

-- Calling the procedure:

DECLARE
A NUMBER; B NUMBER;
C NUMBER; D NUMBER; E NUMBER; F NUMBER;
BEGIN
A := &FIRSTNUMBER;
B := &SECONDNUMBER;
PROCESS(A, B, C, D, E, F);
DBMS_OUTPUT.PUT_LINE('ADDITION IS' || C);
DBMS_OUTPUT.PUT_LINE('SUBTRACTION IS' || D);
DBMS_OUTPUT.PUT_LINE('MULTIPLICATION IS' || E);
DBMS_OUTPUT.PUT_LINE('DIVISION IS' || F);
END;

4.5 Procedure with IN OUT Parameter

CREATE OR REPLACE PROCEDURE
INCREASE_SALARY(E IN NUMBER, AMT IN NUMBER, S OUT NUMBER)
IS
BEGIN
UPDATE EMP SET SAL = SAL + AMT WHERE EMP_NO = E;
COMMIT;
SELECT SAL INTO S FROM EMP WHERE EMP_NO = E;
END;
/

4.6 Function vs Procedure — Quick Comparison

Function Procedure
Must RETURN one value May return 0 or more values (via OUT)

Called in expression or SELECT Called with EXEC or inside BEGIN...END

Cannot have DML as direct call Can perform DML (INSERT/UPDATE/DELETE)

Used to COMPUTE and return Used to PERFORM an action

5. CURSORS

5.1 Types of Cursors

Implicit Cursor Explicit Cursor
Auto-created by Oracle for DML (INSERT, UPDATE, User-defined for SELECT that returns multiple rows
DELETE) and single-row SELECT INTO

Accessed via SQL%attribute You DECLARE, OPEN, FETCH, CLOSE manually

Cannot be controlled by user Full control over row-by-row processing

5.2 Implicit Cursor Attributes
Attribute Returns
SQL%FOUND TRUE if DML/SELECT INTO affected at least 1 row

SQL%NOTFOUND TRUE if no rows affected

SQL%ROWCOUNT Number of rows affected

SQL%ISOPEN Always FALSE for implicit cursors

Implicit Cursor Examples

-- Check if update affected rows:
BEGIN
UPDATE customers SET salary = salary + 500;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('No customers updated');
ELSIF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' customers updated');
END IF;
END;

-- Check if delete worked:

BEGIN
DELETE EMP WHERE EMPNO = &EMPNO;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE('RECORD NOT DELETED');
ELSE
DBMS_OUTPUT.PUT_LINE('RECORD DELETED');
END IF;
END;

5.3 Explicit Cursor — Full Syntax

DECLARE
CURSOR cursor_name IS select_statement;
variable datatype;
rec cursor_name%ROWTYPE; -- optional record variable
BEGIN
OPEN cursor_name;
LOOP
FETCH cursor_name INTO variable [, variable2, ...];
EXIT WHEN cursor_name%NOTFOUND;
-- process row here
END LOOP;
CLOSE cursor_name;
END;

Explicit Cursor Example — Dept 10 employees

DECLARE
CURSOR C1 IS SELECT EMPNO, ENAME, JOB FROM EMP WHERE DEPTNO = 10;
REC C1%ROWTYPE; -- record holding empno, ename, job
BEGIN
OPEN C1;
LOOP
 FETCH C1 INTO REC;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO ' || [Link]);
DBMS_OUTPUT.PUT_LINE('ENAME ' || [Link]);
DBMS_OUTPUT.PUT_LINE('JOB ' || [Link]);
END LOOP;
CLOSE C1;
END;

Explicit Cursor Example — Top 5 highest paid

DECLARE
EMPNAME [Link]%TYPE;
EMPSAL [Link]%TYPE;
CURSOR TEMP1 IS SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC;
BEGIN
OPEN TEMP1;
LOOP
FETCH TEMP1 INTO EMPNAME, EMPSAL;
EXIT WHEN TEMP1%ROWCOUNT > 5 OR TEMP1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EMPNAME || ' ' || EMPSAL);
END LOOP;
CLOSE TEMP1;
END;

5.4 Cursor Attributes

Attribute Meaning
cursor%FOUND TRUE if last FETCH returned a row

cursor%NOTFOUND TRUE if last FETCH returned no row

cursor%ROWCOUNT Number of rows fetched so far

cursor%ISOPEN TRUE if cursor is currently open

5.5 Cursor FOR Loop (Shortcut — No OPEN/FETCH/CLOSE needed)

-- Syntax (cursor auto opens, fetches, closes):
FOR record_var IN cursor_name LOOP
statements;
END LOOP;

-- Example: same as Dept 10 example above, but shorter:

DECLARE
CURSOR C1 IS SELECT EMPNO, ENAME, JOB FROM EMP WHERE DEPTNO = 10;
BEGIN
FOR REC IN C1 LOOP
DBMS_OUTPUT.PUT_LINE('EMPNO ' || [Link]);
DBMS_OUTPUT.PUT_LINE('ENAME ' || [Link]);
DBMS_OUTPUT.PUT_LINE('JOB ' || [Link]);
END LOOP;
END;

-- Top 5 with FOR loop:

DECLARE
CURSOR TEMP1 IS SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC;
BEGIN
 FOR REC IN TEMP1 LOOP
EXIT WHEN TEMP1%ROWCOUNT > 5;
DBMS_OUTPUT.PUT_LINE([Link] || ' ' || [Link]);
END LOOP;
END;

💡 Cursor FOR Loop automatically declares record variable, opens, fetches, and closes the cursor.

5.6 Cursors with Parameters

-- Declare cursor with parameter:
CURSOR cursor_name (param_name datatype) IS select_statement;

-- Example: dept number passed as parameter

DECLARE
CURSOR C1(d NUMBER) IS
SELECT EMPNO, ENAME, JOB FROM EMP WHERE DEPTNO = d;
REC C1%ROWTYPE;
BEGIN
OPEN C1(&d); -- pass value at runtime
LOOP
FETCH C1 INTO REC;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO ' || [Link]);
DBMS_OUTPUT.PUT_LINE('ENAME ' || [Link]);
END LOOP;
CLOSE C1;
END;

-- Same with FOR loop:

BEGIN
FOR REC IN C1(&d) LOOP
DBMS_OUTPUT.PUT_LINE('EMPNO ' || [Link]);
END LOOP;
END;

6. EXCEPTION HANDLING

6.1 General Syntax

DECLARE
-- declarations
BEGIN
-- executable statements
EXCEPTION
WHEN exception_name1 THEN
-- handling statements
WHEN exception_name2 THEN
-- handling statements
WHEN OTHERS THEN
-- catch-all (MUST be last)
DBMS_OUTPUT.PUT_LINE('Unexpected error!');
END;

💡 WHEN OTHERS must always be the LAST exception handler. Nothing after it executes.
6.2 System-Defined Exceptions
Exception Name Error#
NO_DATA_FOUND SELECT INTO returns no rows ORA-01403

TOO_MANY_ROWS SELECT INTO returns multiple rows ORA-01422

ZERO_DIVIDE Division by zero ORA-01476

CURSOR_ALREADY_OPEN Opening an already-open cursor ORA-06511

INVALID_CURSOR Invalid cursor operation ORA-01001

System Exception Example — NO_DATA_FOUND

DECLARE
c_id [Link]%TYPE := 8;
c_name [Link]%TYPE;
c_addr [Link]%TYPE;
BEGIN
SELECT name, address INTO c_name, c_addr
FROM customers WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE('Name: ' || c_name);
DBMS_OUTPUT.PUT_LINE('Address: ' || c_addr);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such customer!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error!');
END;

System Exception Example — TOO_MANY_ROWS

DECLARE
v_lname employees.last_name%TYPE;
BEGIN
SELECT last_name INTO v_lname
FROM employees WHERE first_name = 'John';
DBMS_OUTPUT.PUT_LINE('Last name: ' || v_lname);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Multiple rows returned. Use a cursor.');
END;

6.3 User-Defined Exceptions

-- Step 1: Declare the exception in DECLARE section
DECLARE
my_exception EXCEPTION;

-- Step 2: Raise it with RAISE in BEGIN section

BEGIN
IF condition THEN
RAISE my_exception;
END IF;

-- Step 3: Handle it in EXCEPTION section

EXCEPTION
WHEN my_exception THEN
 DBMS_OUTPUT.PUT_LINE('Custom error occurred');
END;

User Exception Full Example — Division constraint

-- Divide x by y such that result >= 1 (y <= x, y != 0)
DECLARE
x INTEGER := &x;
y INTEGER := &y;
div FLOAT;
ex1 EXCEPTION; -- division by zero
ex2 EXCEPTION; -- y > x
BEGIN
IF y = 0 THEN
RAISE ex1;
ELSIF y > x THEN
RAISE ex2;
ELSE
div := x / y;
DBMS_OUTPUT.PUT_LINE('Result: ' || div);
END IF;
EXCEPTION
WHEN ex1 THEN
DBMS_OUTPUT.PUT_LINE('Error: Division by zero not allowed');
WHEN ex2 THEN
DBMS_OUTPUT.PUT_LINE('Error: y is greater than x');
END;

7. DATABASE TRIGGERS

7.1 What is a Trigger?

A trigger is a PL/SQL block that fires AUTOMATICALLY when a DML event (INSERT / UPDATE / DELETE)
occurs on a table. Unlike a procedure, it does NOT need to be called explicitly.

7.2 Full Trigger Syntax

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER}
{INSERT | UPDATE [OF column] | DELETE} [OR ...]
ON table_name
[REFERENCING {OLD AS old, NEW AS new}]
[FOR EACH ROW [WHEN condition]]
DECLARE
-- optional variable declarations
BEGIN
-- trigger body
[EXCEPTION
-- optional exception handling]
END;
/

7.3 Key Trigger Concepts

Keyword Meaning
 BEFORE Fires before the DML statement executes

AFTER Fires after the DML statement executes

FOR EACH ROW Row-level trigger: fires once per affected row

(no FOR EACH ROW) Statement-level trigger: fires once per DML
statement

:[Link] New value being inserted/updated

:[Link] Old value before update/delete

INSERTING TRUE inside trigger if event is INSERT

UPDATING TRUE inside trigger if event is UPDATE

DELETING TRUE inside trigger if event is DELETE

7.4 Trigger Examples

Ex 1 — After INSERT message
CREATE TRIGGER student_msg
ON Student
AFTER INSERT
AS
BEGIN
PRINT 'Record inserted successfully';
END;

Ex 2 — After INSERT/UPDATE/DELETE
CREATE TRIGGER student_msg
ON Student
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
PRINT 'One record is affected';
END;

Ex 3 — BEFORE trigger: force ENAME to UPPERCASE

CREATE OR REPLACE TRIGGER upper_trigger
BEFORE INSERT OR UPDATE OF ename ON emp
FOR EACH ROW
BEGIN
:[Link] := UPPER(:[Link]);
END;

Ex 4 — AFTER UPDATE: log old and new values

CREATE OR REPLACE TRIGGER EMP_UPDATE
AFTER UPDATE OF ENAME ON EMP
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('OLD NAME: ' || :[Link]);
DBMS_OUTPUT.PUT_LINE('NEW NAME: ' || :[Link]);
END;
Ex 5 — BEFORE INSERT: enforce PRIMARY KEY constraint
CREATE OR REPLACE TRIGGER PRIMARY_KEY
BEFORE INSERT ON EMPTEMP
FOR EACH ROW
DECLARE
E [Link]%TYPE;
BEGIN
IF (:[Link] IS NULL) THEN
RAISE_APPLICATION_ERROR(-20002, 'PRIMARY KEY CANNOT BE NULL');
END IF;
SELECT ENO INTO E FROM EMPTEMP WHERE ENO = :[Link];
RAISE_APPLICATION_ERROR(-20003, 'PRIMARY KEY MUST BE UNIQUE');
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL; -- no duplicate, so allow insert
END;

Ex 6 — BEFORE INSERT: enforce FOREIGN KEY constraint

CREATE OR REPLACE TRIGGER FOREIGN_KEY
BEFORE INSERT ON EMPTEMP
FOR EACH ROW
DECLARE
DNO [Link]%TYPE;
BEGIN
SELECT DEPTNO INTO DNO FROM DEPTTEMP WHERE DEPTNO = :[Link];
NULL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20004, 'FOREIGN KEY VIOLATED: VALUE NOT IN PARENT
TABLE');
END;

Ex 7 — Audit trigger: log all DML operations

-- Assumes table: AUDITOR(empno, operation)
CREATE OR REPLACE TRIGGER EMP_AUDIT
AFTER INSERT OR UPDATE OR DELETE ON EMP
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO AUDITOR VALUES(:[Link], 'INSERT');
ELSIF UPDATING THEN
INSERT INTO AUDITOR VALUES(:[Link], 'UPDATE');
ELSIF DELETING THEN
INSERT INTO AUDITOR VALUES(:[Link], 'DELETE');
END IF;
END;

8. QUICK REFERENCE — When to Use What

Use This When You Need To
FUNCTION Compute and return a single value; use in SELECT
or expressions

PROCEDURE Perform an action; return 0 or many values via OUT

 parameters

IMPLICIT CURSOR Work with single-row SELECT INTO or DML; use

SQL%FOUND etc.

EXPLICIT CURSOR Process multiple rows from a SELECT one-by-one

CURSOR FOR LOOP Iterate over multi-row SELECT; cleaner syntax, auto
open/close

CURSOR WITH PARAMS Same SELECT logic but with different filter values
each call

BEFORE TRIGGER Validate or modify data BEFORE it is written to the

table

AFTER TRIGGER Log changes or cascade updates AFTER the DML

completes

FOR EACH ROW trigger Action needed per-row (audit, transform, constraint)

Statement trigger Action needed once per transaction regardless of

rows affected

SYSTEM EXCEPTION Oracle error you expect (NO_DATA_FOUND,

TOO_MANY_ROWS etc.)

USER EXCEPTION Custom business rule violation you want to detect

and raise

Good luck in your exam!