• Block Structures: You can send a block of statements to the database at once, reducing network traffic.
• Procedural Language Capabilities: Use IF-THEN, loops, variables, and exceptions.
• High Performance: Because PL/SQL is integrated tightly with SQL, operations perform faster.
• Error Handling: Robust exception handling blocks allow graceful error catching.

The Anatomy of a Block

A basic PL/SQL block consists of three sections: Declarative, Executable, and Exception-handling.

DECLARE
   -- Declaration section (optional)
   message VARCHAR2(20) := 'Hello World!';
BEGIN
   -- Execution section (mandatory)
   DBMS_OUTPUT.PUT_LINE(message);
EXCEPTION
   -- Exception handling section (optional)
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
/

Single-line Comments

-- This is a single line comment
v_name VARCHAR2(50);

Multi-line Comments

/* This is a multi-line comment
that spans multiple lines */
v_age NUMBER := 25;

• Maximum 30 characters long.
• Must begin with a letter.
• Can include letters, numbers, dollar signs ($), underscores (_), and number signs (#).
• Not case-sensitive (e.g., `v_name` is the same as `V_NAME`).

Syntax

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value];

Example

DECLARE
   v_emp_name VARCHAR2(50) := 'John Doe';
   c_pi CONSTANT NUMBER := 3.14159;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Name: ' || v_emp_name);
END;

Type Category	Examples
Scalar	NUMBER, CHAR, VARCHAR2, DATE, BOOLEAN
Large Object (LOB)	BFILE, BLOB, CLOB, NCLOB
Composite	RECORD, TABLE, VARRAY
Reference	REF CURSOR, REF object_type

%TYPE

Declares a variable to have the exact same data type as a database column.

DECLARE
   v_salary employees.salary%TYPE;
BEGIN
   SELECT salary INTO v_salary FROM employees WHERE employee_id = 100;
END;

%ROWTYPE

Declares a record that represents an entire row of a table.

DECLARE
   v_emp_rec employees%ROWTYPE;
BEGIN
   SELECT * INTO v_emp_rec FROM employees WHERE employee_id = 100;
   DBMS_OUTPUT.PUT_LINE(v_emp_rec.first_name);
END;

DECLARE
   v_score NUMBER := 85;
BEGIN
   IF v_score >= 90 THEN
      DBMS_OUTPUT.PUT_LINE('Grade: A');
   ELSIF v_score >= 80 THEN
      DBMS_OUTPUT.PUT_LINE('Grade: B');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Grade: C or below');
   END IF;
END;

DECLARE
   v_grade CHAR(1) := 'B';
BEGIN
   CASE v_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');
      ELSE DBMS_OUTPUT.PUT_LINE('Needs Improvement');
   END CASE;
END;

Basic Loop

DECLARE
   x NUMBER := 1;
BEGIN
   LOOP
      DBMS_OUTPUT.PUT_LINE(x);
      x := x + 1;
      EXIT WHEN x > 5;
   END LOOP;
END;

WHILE Loop

WHILE x <= 5 LOOP
   DBMS_OUTPUT.PUT_LINE(x);
   x := x + 1;
END LOOP;

FOR Loop

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

Used mainly for single-row SELECT ... INTO statements and DML operations.

BEGIN
   UPDATE employees SET salary = salary * 1.10 WHERE department_id = 10;
   IF SQL%FOUND THEN
      DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated.');
   END IF;
END;

Steps to use: Declare -> Open -> Fetch -> Close

DECLARE
   CURSOR emp_cursor IS SELECT first_name FROM employees;
   v_name employees.first_name%TYPE;
BEGIN
   OPEN emp_cursor;
   LOOP
      FETCH emp_cursor INTO v_name;
      EXIT WHEN emp_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_name);
   END LOOP;
   CLOSE emp_cursor;
END;

• %FOUND: Returns TRUE if a record was fetched successfully.
• %NOTFOUND: Returns TRUE if a record was not fetched.
• %ROWCOUNT: Returns the number of rows fetched so far (or affected by DML).
• %ISOPEN: Returns TRUE if the cursor is open.

CREATE OR REPLACE PROCEDURE give_raise (p_emp_id IN NUMBER, p_amount IN NUMBER) AS
BEGIN
   UPDATE employees
   SET salary = salary + p_amount
   WHERE employee_id = p_emp_id;
   COMMIT;
END give_raise;
/

CREATE OR REPLACE FUNCTION get_annual_salary (p_emp_id IN NUMBER) RETURN NUMBER IS
   v_annual_sal NUMBER;
BEGIN
   SELECT salary * 12 INTO v_annual_sal
   FROM employees
   WHERE employee_id = p_emp_id;
   
   RETURN v_annual_sal;
END get_annual_salary;
/

• IN (Default): Passes a value into the subprogram. Read-only inside.
• OUT: Returns a value to the caller. Must be assigned a value inside.
• IN OUT: Passes an initial value in and returns an updated value out.

• NO_DATA_FOUND: A SELECT INTO returned no rows.
• TOO_MANY_ROWS: A SELECT INTO returned more than one row.
• ZERO_DIVIDE: Attempted to divide by zero.

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee does not exist.');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);

DECLARE
   e_invalid_age EXCEPTION;
   v_age NUMBER := 15;
BEGIN
   IF v_age < 18 THEN
      RAISE e_invalid_age;
   END IF;
EXCEPTION
   WHEN e_invalid_age THEN
      DBMS_OUTPUT.PUT_LINE('Error: User must be at least 18.');
END;

Events can be INSERT, UPDATE, or DELETE. Triggers can fire BEFORE or AFTER the event.

Using :NEW and :OLD

You can access the column values before and after the change.

CREATE OR REPLACE TRIGGER trg_audit_salary
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
   IF :NEW.salary < :OLD.salary THEN
      RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be decreased!');
   END IF;
END;
/