Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 38 ยท PL/SQL Basics

PL/SQL Blocks and Variables

Learn the structure of PL/SQL: anonymous blocks with DECLARE, BEGIN, EXCEPTION and END, variables and constants, data types, scope, nested blocks and DBMS_OUTPUT.

SQL is excellent at asking for sets of rows, but it has no variables, no loops and no way to react to an error. PL/SQL (Procedural Language extensions to SQL) adds all of that. It runs inside the database, next to the data, so a block of code can read rows, make decisions, change data and return a result without a network round trip for each step. Triggers, stored procedures, functions and packages are all written in PL/SQL.

From here to the end of chapter 5 the code is PL/SQL. Oracle cannot run on this site, so PL/SQL appears as read-only code with its output when we are sure of it. A few stand-ins show the same logic in SQL, where that is possible. To run the programs yourself, use the setup from Oracle tools and setup.

The block

Every PL/SQL program is made of blocks. A block has up to four parts:

PLSQL
DECLARE
  -- 1. declarations: variables, constants, cursors, local subprograms (optional)
BEGIN
  -- 2. executable statements (required)
EXCEPTION
  -- 3. error handlers (optional)
END;
/

Only BEGIN ... END; is required. This is the smallest useful program, an anonymous block (it has no name and is not stored):

PLSQL
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!');
END;
/

Run it in SQL*Plus or SQLcl with SET SERVEROUTPUT ON first and it prints Hello, PL/SQL!. The / on its own line tells the client to send the block to the server. Without SET SERVEROUTPUT ON, the text is produced but never shown.

DBMS_OUTPUT.PUT_LINE belongs to the built-in package DBMS_OUTPUT. It is meant for learning and debugging, not for user interfaces: the text is buffered and displayed only after the block ends.

flowchart TB
  A["DECLARE<br/>variables, constants"] --> B["BEGIN<br/>statements"]
  B -->|"no error"| D["END;"]
  B -->|"error raised"| C["EXCEPTION<br/>handlers"]
  C --> D

A block declares, executes, and handles errors only if something goes wrong.

Declaring variables

Variables are declared in the DECLARE section as name datatype [:= initial value];:

PLSQL
DECLARE
  v_name       VARCHAR2(50) := 'Ada';
  v_quantity   NUMBER(4)    := 3;
  v_unit_price NUMBER(8,2)  := 19.99;
  v_total      NUMBER(10,2);
  v_today      DATE         := SYSDATE;
  v_is_paid    BOOLEAN      := FALSE;
BEGIN
  v_total := v_quantity * v_unit_price;
  DBMS_OUTPUT.PUT_LINE(v_name || ' owes ' || v_total);
END;
/

This prints Ada owes 59.97.

The assignment operator is := (not =, which is comparison). DEFAULT is a synonym for := in declarations. A variable you do not initialize starts as NULL.

Common conventions: prefix variables v_, parameters p_, constants c_, so they never clash with column names. If a variable has the same name as a column in a SQL statement inside the block, the column wins, and WHERE last_name = last_name silently matches every row.

Data types

PL/SQL accepts all SQL types and adds some of its own:

Type Notes
VARCHAR2(n), CHAR(n), CLOB Text; VARCHAR2 up to 32,767 bytes in PL/SQL
NUMBER(p,s) Exact decimals
PLS_INTEGER / SIMPLE_INTEGER Whole numbers, faster than NUMBER for counters (32-bit)
BOOLEAN TRUE, FALSE or NULL; allowed in PL/SQL even before SQL got it in 23ai
DATE, TIMESTAMP As in SQL
%TYPE, %ROWTYPE Borrow a type from a column or row (next lessons)

Subtypes give a name to a constrained type: SUBTYPE percent IS NUMBER(5,2);.

Constants and NOT NULL

PLSQL
DECLARE
  c_vat_rate  CONSTANT NUMBER := 0.20;       -- cannot be changed
  v_count     PLS_INTEGER NOT NULL := 0;     -- must always have a value
BEGIN
  v_count := v_count + 1;
  -- c_vat_rate := 0.25;   would not compile: PLS-00363: expression cannot be used as an assignment target
  DBMS_OUTPUT.PUT_LINE('VAT: ' || c_vat_rate * 100 || '%, count: ' || v_count);
END;
/

The output is VAT: 20%, count: 1. A constant must be initialized in its declaration. A NOT NULL variable must be too.

Operators and expressions

PL/SQL uses the same operators as SQL: + - * /, ** for power, || for concatenation, and comparison with =, <>, <, >=, IS NULL, BETWEEN, IN, LIKE. Logic uses AND, OR and NOT with three-valued logic, so NULL = NULL is not true. Most SQL functions (UPPER, SUBSTR, ROUND, TO_CHAR, NVL) can be used directly in PL/SQL expressions; a few, such as DECODE, work only inside SQL statements.

Scope and nested blocks

A block can contain other blocks. A variable is visible in its own block and in the blocks nested inside it, never outside:

PLSQL
DECLARE
  v_outer VARCHAR2(20) := 'outer';
BEGIN
  DECLARE
    v_inner VARCHAR2(20) := 'inner';
  BEGIN
    DBMS_OUTPUT.PUT_LINE(v_outer || ' and ' || v_inner);   -- both visible
  END;
  DBMS_OUTPUT.PUT_LINE(v_outer);                            -- v_inner is gone here
END;
/

The output is outer and inner, then outer. If an inner variable has the same name as an outer one, the inner one hides it. To reach the hidden one, give the outer block a label: <<main>> DECLARE ... and write main.v_name.

Nested blocks are useful for a local exception handler around one risky statement, so the rest of the program can continue. Exceptions are covered in chapter 8.

Comments, literals and quoting

PLSQL
-- a single-line comment
/* a multi-line
   comment */
v_msg := 'It''s fine';          -- double the quote inside a string
v_msg := q'[It's fine]';        -- alternative quoting
v_when := DATE '2024-05-07';

Stand-ins you can run

The block that computes a total has a SQL twin. The WITH clause plays the role of the declarations:

SQL Stand-in: variables as a WITH clause

Press Run (or Ctrl + Enter) to see the result here.

Constants are the same idea. A scalar calculation with a fixed rate:

SQL Stand-in: a constant used in a calculation ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which section of a PL/SQL block is required?

    Only the executable section (BEGIN ... END) is mandatory. DECLARE and EXCEPTION are optional.
  2. 2. Which operator assigns a value to a PL/SQL variable?

    := is assignment. A single = is comparison.
  3. 3. A block has v_name VARCHAR2(20); and runs SELECT ... WHERE last_name = last_name where last_name is a column and also a variable. What happens?

    Inside a SQL statement, a column name takes precedence over a PL/SQL variable of the same name. Use prefixes such as v_ and p_ to avoid it.
  4. 4. What must you do before DBMS_OUTPUT.PUT_LINE text is displayed in SQL*Plus?

    The client must be told to show the buffered output with SET SERVEROUTPUT ON.
  5. 5. What is the effect of CONSTANT in a declaration?

    A constant must be initialized when declared, and any later assignment fails to compile.

Summary

  • A PL/SQL block has DECLARE, BEGIN ... END and an optional EXCEPTION section; anonymous blocks end with /.
  • Declare variables with name type := value; use CONSTANT and NOT NULL for safety.
  • Prefix names (v_, p_, c_) so they never clash with column names.
  • Variables are visible in their block and nested blocks; inner names hide outer ones.
  • DBMS_OUTPUT.PUT_LINE shows text when SERVEROUTPUT is on.

Next: PL/SQL Control Flow