PL/SQL Block Structure, Variables, Records, Collections, Control Flow, and Scope

Learning outcomes

ServiceHub needs to validate a batch request, derive several scalar values, call SQL conditionally, and emit a concise diagnostic message. One developer writes procedural logic into an oversized SQL expression; another loops through thousands of rows merely to update one column. PL/SQL is useful for orchestration, state, branching, exception handling, and reusable server-side APIs—but SQL remains the baseline for operations over sets of rows.

1. Every PL/SQL block has an executable core

A PL/SQL block has an optional DECLARE section, a required BEGIN ... END executable section, and an optional EXCEPTION section. Anonymous blocks are submitted by a client and are not stored as named schema objects; stored procedures, functions, package bodies, and triggers contain named PL/SQL blocks compiled into the database.

SET SERVEROUTPUT ONDECLARE  l_open_count PLS_INTEGER;BEGIN  SELECT COUNT(*)  INTO l_open_count  FROM servicehub_plsql_case  WHERE status_code = 'OPEN';  DBMS_OUTPUT.PUT_LINE('open cases=' || l_open_count);EXCEPTION  WHEN OTHERS THEN    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);    RAISE;END;/

DBMS_OUTPUT is a teaching/debugging channel, not a production logging subsystem. SQLcl/SQL*Plus must enable SERVEROUTPUT to display its buffered lines.

2. Anchor types to the data model

%TYPE derives a PL/SQL variable's type from a column or another PL/SQL variable. %ROWTYPE creates a record whose fields match a table, cursor, or view row. Anchoring avoids duplicating type declarations and lets compatible schema changes flow into recompiled PL/SQL.

DECLARE  l_case_id servicehub_plsql_case.case_id%TYPE := 1;  l_row     servicehub_plsql_case%ROWTYPE;BEGIN  SELECT *  INTO l_row  FROM servicehub_plsql_case  WHERE case_id = l_case_id;  DBMS_OUTPUT.PUT_LINE(    l_row.case_id || ':' || l_row.status_code || ':' || l_row.amount  );END;/

Anchoring does not make schema changes risk-free. Dropping or renaming columns, changing semantic meaning, or modifying package specifications can still invalidate dependent code and require testing.

3. Records group fields; collections group elements

A PL/SQL record is a composite value with named fields. A collection is an ordered or keyed group of elements. PL/SQL supports associative arrays, nested tables, and varrays. Associative arrays are especially useful for session-local keyed working state.

DECLARE  TYPE t_summary IS RECORD (    status_code servicehub_plsql_case.status_code%TYPE,    case_count  PLS_INTEGER,    total_amt   NUMBER  );  TYPE t_count_by_status IS TABLE OF PLS_INTEGER    INDEX BY VARCHAR2(12);  l_counts t_count_by_status;BEGIN  l_counts('OPEN') := 0;  l_counts('CLOSED') := 0;  FOR r IN (    SELECT status_code, COUNT(*) AS case_count    FROM servicehub_plsql_case    GROUP BY status_code  ) LOOP    l_counts(r.status_code) := r.case_count;  END LOOP;  DBMS_OUTPUT.PUT_LINE('OPEN=' || l_counts('OPEN'));END;/

The SQL query performs the grouping as a set. PL/SQL merely consumes already-aggregated rows.

4. Control flow is for decisions and orchestration

Use IF/ELSIF/ELSE and CASE for branches, and basic/WHILE/FOR loops for genuine iteration. Prefer a cursor FOR loop over manual open/fetch/close when you simply need to process rows sequentially; Oracle manages the cursor lifecycle for the loop.

DECLARE  l_total NUMBER;BEGIN  SELECT SUM(amount)  INTO l_total  FROM servicehub_plsql_case  WHERE status_code = 'OPEN';  IF NVL(l_total,0) > 10000 THEN    DBMS_OUTPUT.PUT_LINE('manual review required');  ELSIF NVL(l_total,0) > 0 THEN    DBMS_OUTPUT.PUT_LINE('normal queue');  ELSE    DBMS_OUTPUT.PUT_LINE('no open monetary exposure');  END IF;END;/

5. Lexical scope: inner declarations can hide outer names

PL/SQL uses lexical scope. An identifier declared in an inner block can hide an outer identifier with the same name. This is legal, but careless shadowing makes incident debugging difficult. Labels can qualify variables when demonstration is necessary, but production naming conventions should usually avoid the ambiguity.

<<outer_block>>DECLARE  l_status VARCHAR2(12) := 'OPEN';BEGIN  DECLARE    l_status VARCHAR2(12) := 'CLOSED';  BEGIN    DBMS_OUTPUT.PUT_LINE('inner=' || l_status);    DBMS_OUTPUT.PUT_LINE('outer=' || outer_block.l_status);  END;END outer_block;/

6. Deliberately wrong: SELECT INTO when the query can return many rows

SELECT ... INTO in PL/SQL is a single-row contract. Zero rows raises NO_DATA_FOUND; more than one row raises TOO_MANY_ROWS (ORA-01422). Adding an arbitrary row limit only hides the modeling error unless one arbitrary row is genuinely acceptable.

DECLARE  l_case_id NUMBER;BEGIN  SELECT case_id  INTO l_case_id  FROM servicehub_plsql_case  WHERE status_code = 'OPEN';END;/-- With multiple OPEN rows: ORA-01422 / TOO_MANY_ROWS

Repair the contract: use an aggregate for one aggregate value, a cursor/collection for multiple rows, or a genuine unique predicate when the business model promises one row.

7. Set-based SQL remains the baseline

Do not loop through rows merely to apply the same relational transformation to each row. The SQL engine can update the qualifying set directly and optimize the access path as a whole.

BEGIN  FOR r IN (    SELECT case_id    FROM servicehub_plsql_case    WHERE status_code = 'OPEN'  ) LOOP    UPDATE servicehub_plsql_case    SET amount = amount * 1.02    WHERE case_id = r.case_id;  END LOOP;END;/

UPDATE servicehub_plsql_caseSET amount = amount * 1.02WHERE status_code = 'OPEN';ROLLBACK;

Later lessons introduce bulk PL/SQL for cases where procedural per-item logic is unavoidable. Bulk processing is an optimization over row-by-row procedural SQL—not a reason to replace one correct SQL statement.

8. Reproducible lab

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_plsql_case PURGE';EXCEPTION  WHEN OTHERS THEN    IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_plsql_case (  case_id      NUMBER PRIMARY KEY,  status_code  VARCHAR2(12) NOT NULL,  amount       NUMBER(10,2) NOT NULL);INSERT INTO servicehub_plsql_case VALUES (1,'OPEN',125);INSERT INTO servicehub_plsql_case VALUES (2,'OPEN',275);INSERT INTO servicehub_plsql_case VALUES (3,'CLOSED',90);COMMIT;

DECLARE  TYPE t_amounts IS TABLE OF servicehub_plsql_case.amount%TYPE    INDEX BY PLS_INTEGER;  l_row      servicehub_plsql_case%ROWTYPE;  l_amounts  t_amounts;  l_total    NUMBER := 0;BEGIN  SELECT *  INTO l_row  FROM servicehub_plsql_case  WHERE case_id = 1;  SELECT amount  BULK COLLECT INTO l_amounts  FROM servicehub_plsql_case  WHERE status_code = 'OPEN'  ORDER BY case_id;  FOR i IN 1..l_amounts.COUNT LOOP    l_total := l_total + l_amounts(i);  END LOOP;  DBMS_OUTPUT.PUT_LINE('first status=' || l_row.status_code);  DBMS_OUTPUT.PUT_LINE('open total=' || l_total);END;/

DROP TABLE servicehub_plsql_case PURGE;

9. Production judgment

Use PL/SQL where server-side procedural state, branching, exception handling, encapsulation, or multi-step orchestration is valuable. Keep large relational transformations in SQL whenever practical. Anchor types where it improves dependency alignment, keep lexical scope shallow, and make row-count contracts explicit.

No special edition, option, pack, restart, parameter, or COMPATIBLE change is required. Lesson 2 turns blocks into reusable procedures/functions and adds a critical boundary: functions called from SQL must obey SQL-callability and side-effect rules.

Authoritative references

• PL/SQL Language Fundamentals — blocks, variables, scope and control structures
• PL/SQL Collections and Records — records and collection concepts
• SELECT INTO Statement — single-row query semantics
• Cursors Overview — implicit and explicit cursor concepts
• Bulk SQL and Bulk Binding — set/bulk boundary preview