PL/SQL
Complete Quick-Reference Notes
Oracle Procedural Language Extension to SQL
1. What is PL/SQL?
PL/SQL (Procedural Language / SQL) is Oracle's procedural extension to SQL. It combines SQL's
data-manipulation power with procedural constructs such as variables, loops, conditions, and exception
handling — all executed inside the Oracle database engine.
• Supports variables, constants, data types
• Provides control structures: IF, LOOP, FOR, WHILE
• Enables modular code via procedures, functions, and packages
• Includes robust exception-handling mechanism
• Executes entirely on the server — reduces network traffic
2. Basic Block Structure
Every PL/SQL program is built from blocks:
DECLARE
-- variable and type declarations
BEGIN
-- executable statements (SQL + procedural code)
EXCEPTION
-- error / exception handlers
END;
DECLARE and EXCEPTION sections are optional; BEGIN…END is mandatory.
3. Data Types
Type Description
NUMBER Numeric values (integer & decimal)
VARCHAR2(n) Variable-length character string
CHAR(n) Fixed-length character string
DATE Date and time
BOOLEAN TRUE / FALSE / NULL
CLOB / BLOB Large character / binary objects
v%TYPE Inherits the data type of a column or variable
row%ROWTYPE Inherits the entire row structure of a table or cursor
v_sal [Link]%TYPE; -- same type as [Link] column
v_emp emp%ROWTYPE; -- entire row of emp table
4. Variables & Constants
DECLARE
v_name VARCHAR2(50) := 'John'; -- variable with default
v_age NUMBER := 25;
v_count NUMBER; -- defaults to NULL
c_pi CONSTANT NUMBER := 3.14159; -- constant
BEGIN
v_count := v_count + 1;
END;
5. Control Structures
IF – ELSIF – ELSE
IF x > 100 THEN
DBMS_OUTPUT.PUT_LINE('High');
ELSIF x > 50 THEN
DBMS_OUTPUT.PUT_LINE('Medium');
ELSE
DBMS_OUTPUT.PUT_LINE('Low');
END IF;
LOOP / EXIT WHEN
LOOP
i := i + 1;
EXIT WHEN i > 10;
END LOOP;
FOR Loop
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
WHILE Loop
WHILE v_count < 100 LOOP
v_count := v_count + 1;
END LOOP;
6. Cursors
Implicit Cursor — auto-created for single-row SELECT INTO:
SELECT sal INTO v_sal FROM emp WHERE empno = 101;
Explicit Cursor
CURSOR c_emp IS SELECT * FROM emp WHERE deptno = 10;
OPEN c_emp;
FETCH c_emp INTO v_emp;
CLOSE c_emp;
Cursor FOR Loop (preferred — opens, fetches, and closes automatically):
FOR rec IN (SELECT * FROM emp) LOOP
DBMS_OUTPUT.PUT_LINE([Link] || ' earns ' || [Link]);
END LOOP;
Cursor attributes: %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN
7. Exception Handling
Built-in Exceptions
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = 999;
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Not found');
WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Too many rows');
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
User-Defined Exception
DECLARE
e_low_salary EXCEPTION;
BEGIN
IF v_sal < 1000 THEN RAISE e_low_salary; END IF;
EXCEPTION
WHEN e_low_salary THEN DBMS_OUTPUT.PUT_LINE('Salary too low!');
END;
8. Procedures & Functions
Stored Procedure
CREATE OR REPLACE PROCEDURE greet(p_name IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END greet;
-- Calling it:
EXEC greet('Alice');
Function (must return a value)
CREATE OR REPLACE FUNCTION add_nums(a IN NUMBER, b IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN a + b;
END add_nums;
-- Usage:
v_result := add_nums(10, 20);
Parameter modes: IN (read-only, default) · OUT (write-only) · IN OUT (read-write)
9. Triggers
CREATE OR REPLACE TRIGGER trg_emp_audit
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
BEGIN
:NEW.updated_date := SYSDATE;
:NEW.updated_by := USER;
END;
• :NEW — new row values (INSERT / UPDATE)
• :OLD — old row values (UPDATE / DELETE)
• Trigger types: BEFORE / AFTER · ROW / STATEMENT level
10. Packages
Package Specification (public interface):
CREATE OR REPLACE PACKAGE pkg_emp AS
PROCEDURE show_emp (p_id IN NUMBER);
FUNCTION get_salary(p_id IN NUMBER) RETURN NUMBER;
END pkg_emp;
Package Body (implementation):
CREATE OR REPLACE PACKAGE BODY pkg_emp AS
PROCEDURE show_emp(p_id IN NUMBER) IS
BEGIN
-- implementation
END show_emp;
FUNCTION get_salary(p_id IN NUMBER) RETURN NUMBER IS
v_sal NUMBER;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = p_id;
RETURN v_sal;
END get_salary;
END pkg_emp;
11. Collections
Type Description
VARRAY Fixed-size, ordered array; stored in DB
Nested Table Unbounded ordered list; stored in DB
Associative Array Key-value pairs (INDEX BY); memory-only
Associative Array Example
DECLARE
TYPE t_names IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
v_names t_names;
BEGIN
v_names(1) := 'Oracle';
v_names(2) := 'PL/SQL';
DBMS_OUTPUT.PUT_LINE(v_names(1)); -- Oracle
END;
12. Useful Built-in Functions & Procedures
Function / Procedure Purpose
DBMS_OUTPUT.PUT_LINE(x) Print output to console
SYSDATE Current date and time
SYSTIMESTAMP Current timestamp with timezone
SQLERRM Text of last error message
SQLCODE Numeric code of last error
NVL(x, y) Replace NULL x with y
NVL2(x, a, b) If x NOT NULL → a, else → b
COALESCE(a,b,...) First non-NULL value in list
TO_DATE(str, fmt) Convert string to DATE
TO_CHAR(val, fmt) Convert value to string
UPPER / LOWER / INITCAP String case conversion
TRIM / LTRIM / RTRIM Remove leading/trailing spaces
LENGTH(str) Length of string
SUBSTR(str, pos, len) Substring extraction
INSTR(str, sub) Position of substring
ROUND / TRUNC / CEIL Numeric rounding functions
13. Key Points to Remember
• PL/SQL is block-structured and case-insensitive.
• Always handle exceptions — unhandled errors abort the block.
• Use %TYPE and %ROWTYPE instead of hard-coding types for maintainability.
• Prefer Cursor FOR loops over manual OPEN / FETCH / CLOSE.
• Use Packages to group related procedures and functions — improves performance via package-level
caching.
• Avoid DDL statements (CREATE, DROP) inside PL/SQL; use EXECUTE IMMEDIATE if needed.
• COMMIT and ROLLBACK control transaction boundaries within PL/SQL.
• Use BULK COLLECT and FORALL for high-performance bulk DML operations.
PL/SQL Quick-Reference Notes · Oracle Database · All sections compiled for study and revision.