Free tools Windows power users keep installed
One-click scans. No signup required.
In PL/SQL, declare variables and constants before the BEGIN keyword in a block, subprogram, or package. A variable may start with a value or default to NULL; a constant must be declared with CONSTANT and an initial value.
Where declarations go in a PL/SQL block
A PL/SQL block has an optional declarative section followed by its executable section. Put declarations after DECLARE and before BEGIN. Each declaration ends with a semicolon.
DECLARE
v_count PLS_INTEGER := 0;
BEGIN
v_count := v_count + 1;
END;
/Here, v_count is declared and initialized before the executable statement increments it. In a subprogram or package, declarations likewise belong to that construct’s declarative part.
How to declare and initialize a variable
A variable declaration gives the item a name and data type. Initialization is optional: use := or DEFAULT followed by an expression when you want an initial value.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minute#1 Best Overall
DECLARE
v_name VARCHAR2(100);
v_required NUMBER NOT NULL := 1;
v_today DATE DEFAULT SYSDATE;
BEGIN
v_name := 'Ada';
END;
/If you omit initialization, the variable’s initial value is NULL. A variable declared NOT NULL must have an initialization expression.
Why an uninitialized variable stays NULL
Arithmetic involving NULL produces NULL. For example, if v_count has no initial value, v_count := v_count + 1; does not make it 1: the expression is still NULL. Initialize a counter to zero when that is the intended starting value.
How to declare a constant
A constant uses a data type like a variable but adds the CONSTANT keyword and a required initial value. Oracle describes it simply: “A constant holds a value that does not change.” Oracle PL/SQL Language Reference: Declarations.
DECLARE
c_max_days CONSTANT PLS_INTEGER := 366;
BEGIN
NULL;
END;
/Once declared, a constant cannot be reassigned. Omitting its initial value is an error; unlike a variable, it cannot begin as an implicit NULL.
Recommended Free Tools
Choosing a type: explicit types, %TYPE, and %ROWTYPE
Choose an explicit scalar type when the value is conceptually independent of a database column, or use an anchored declaration when you want its type to follow an existing declaration or table definition.
| Form | Value shape | Initialization and mutability | When to use it |
|---|---|---|---|
Explicit scalar, such as NUMBER or VARCHAR2(100) |
One scalar value | A variable may be initialized or left as NULL; it can be reassigned. |
When the value’s type should be chosen independently of a column. |
%TYPE |
One scalar value, with type and size anchored to a referenced variable or column | Behaves as a variable if declared as one; it does not inherit the referenced item’s initial value. | When a variable should stay aligned with a column or another declaration. |
%ROWTYPE |
A record with fields corresponding to a table or cursor row | Fields initially contain NULL; the record cannot be initialized in its declaration. |
When working with a row-shaped set of fields rather than one scalar. |
Use %TYPE for a column-aligned scalar
%TYPE adopts the referenced item’s data type and size. If that referenced declaration changes, the anchored declaration changes accordingly. It does not copy the referenced item’s current or initial value.
Rank #4
DECLARE
v_last_name employees.last_name%TYPE;
BEGIN
NULL;
END;
/The example declares a scalar compatible with employees.last_name. Its value is still NULL until assigned.
Use %ROWTYPE for a row-shaped record
%ROWTYPE declares a record whose fields correspond to a full row. Access the fields by name, using dot notation. The record’s fields begin as NULL, and you cannot supply an initialization expression for the %ROWTYPE variable in its declaration.
DECLARE
v_emp employees%ROWTYPE;
BEGIN
v_emp.last_name := 'Lovelace';
END;
/Scope and when declarations are initialized
A declaration is available within the scope where it appears. A local declaration in a block, subprogram, or package body is not automatically visible outside that scope. A declaration in a package specification can be accessed by code that has access to the package.
Block and subprogram variables and constants are initialized when execution enters that block or subprogram. Package-specification declarations are initialized once per session. These lifetime differences matter when deciding whether a value should be local to one execution or shared through a package during a session.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
