PL/SQL Blocks

The Anatomy of a PL/SQL Block

DECLARE
  v_name  VARCHAR2(100);
BEGIN
  SELECT name INTO v_name FROM customers WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE('Customer: ' || v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No customer found');
END;
/

• DECLARE: variables and types (optional).
• BEGIN ... END: the executable statements.
• EXCEPTION: error handlers (optional).
• / runs the block in SQL*Plus.

Enabling Output

SET SERVEROUTPUT ON;

Working With Variables

DECLARE
  v_total NUMBER := 0;
BEGIN
  FOR r IN (SELECT total FROM orders) LOOP
    v_total := v_total + r.total;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Sum: ' || v_total);
END;
/

Control Structures

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

Key Points

• Blocks are DECLARE / BEGIN / EXCEPTION / END.
• Use DBMS_OUTPUT with SERVEROUTPUT ON to see text.
• IF/ELSIF, loops and cursors give PL/SQL real power.
• EXCEPTION WHEN ... handles errors in one place.