Variables and Data Types

Variables are named storage locations that hold values during the execution of a PL/SQL block. Declaring and using them correctly is essential for writing effective PL/SQL.

Declaring Variables

Variables are declared in the DECLARE section using this syntax:

variable_name datatype [NOT NULL] [:= initial_value];

Examples:

DECLARE
  v_name     VARCHAR2(50);
  v_age      NUMBER(3)     := 0;
  v_active   BOOLEAN       := TRUE;
  v_today    DATE          := SYSDATE;
  v_pi       CONSTANT NUMBER := 3.14159;

Common Data Types

Character Types

Type	Description
VARCHAR2(n)	Variable-length string, up to n bytes. Most common string type.
CHAR(n)	Fixed-length string, always n bytes (padded with spaces).
CLOB	Character Large Object — for very long text.

Numeric Types

Type	Description
NUMBER(p, s)	Arbitrary precision. p = total digits, s = decimal places.
INTEGER	Whole numbers (alias for NUMBER(38)).
BINARY_INTEGER	Faster integer arithmetic; often used for loop counters.

Date/Time Types

Type	Description
DATE	Stores date and time (to the second).
TIMESTAMP	Stores date and time with fractional seconds.
INTERVAL	Stores a duration (e.g., 3 days, 2 hours).

Other

Type	Description
BOOLEAN	TRUE, FALSE, or NULL. Only available in PL/SQL (not SQL).
RAW(n)	Binary data.

The := Assignment Operator

PL/SQL uses := (not =) to assign values:

v_name := 'Alice';
v_age  := v_age + 1;

The = operator is used only for comparisons (in IF statements, WHERE clauses, etc.).

The %TYPE Attribute

Instead of hard-coding a data type, you can inherit it from a table column:

DECLARE
  v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
/

If the column's type changes, your variable automatically picks it up. This is a best practice.

The %ROWTYPE Attribute

%ROWTYPE declares a record with the same structure as an entire table row:

DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  SELECT * INTO v_emp FROM employees WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ' earns ' || v_emp.salary);
END;
/

Constants

Use the CONSTANT keyword to declare a value that cannot be changed:

v_tax_rate CONSTANT NUMBER := 0.20;