Summary: in this tutorial, you will learn about PL/SQL variables and how to use them effectively.
In PL/SQL, a variable is named storage location that stores a value of a particular data type. The value of the variable changes through the program. Before using a variable, you must declare it in the declaration section of a block.
The syntax for a variable declaration is as follows:
variable_name datatype [NOT NULL] [:= initial_value];
In this syntax:
- First, specify the name of the variable. The name of the variable should be as descriptive as possible, e.g.,
- Second, choose an appropriate data type for the variable, depending on the kind of value which you want to store, for example, number, character, Boolean, and datetime.
By convention, local variable names should start with
l_ and global variable names should have a prefix of
The following example declares three variables
DECLARE l_total_sales NUMBER(15,2); l_credit_limit NUMBER (10,0); l_contact_name VARCHAR2(255); BEGIN NULL; END;
PL/SQL allows you to set a default value for a variable at the declaration time. To assign a default value to a variable, you use the assignment operator (
:=) or the
The following example declares a variable named
l_product_name with an initial value
DECLARE l_product_name VARCHAR2( 100 ) := 'Laptop'; BEGIN NULL; END;
It is equivalent to the following block:
DECLARE l_product_name VARCHAR2(100) DEFAULT 'Laptop'; BEGIN NULL; END;
In this example, instead of using the assignment operator
:= , we used the
DEFAULT keyword to initialize a variable.
NOT NULL constraint
If you impose the
NOT NULL constraint on a value, then the variable cannot accept a NULL value. Besides, a variable declared with the
NOT NULL must be initialized with a non-null value. Note that PL/SQL treats a zero-length string as a NULL value.
The following example first declares a variable named
l_shipping_status with the
NOT NULL constraint. Then, it assigns the variable a zero-length string.
DECLARE l_shipping_status VARCHAR2( 25 ) NOT NULL := 'Shipped'; BEGIN l_shipping_status := ''; END;
PL/SQL issued the following error:
ORA-06502: PL/SQL: numeric or value error
Because the variable
l_shipping_status declared with the
NOT NULL constraint, it could not accept a NULL value or zero-length string in this case.
To assign a value to a variable, you use the assignment operator (
:=), for example:
DECLARE l_customer_group VARCHAR2(100) := 'Silver'; BEGIN l_customer_group := 'Gold'; DBMS_OUTPUT.PUT_LINE(l_customer_group); END;
You can assign a value of a variable to another as shown in the following example:
DECLARE l_business_parter VARCHAR2(100) := 'Distributor'; l_lead_for VARCHAR2(100); BEGIN l_lead_for := l_business_parter; DBMS_OUTPUT.PUT_LINE(l_lead_for); END;
Typically, you declare a variable and select a value from a table column to this variable. If the data type of the table column changes, you must adjust the program to make it work with the new type.
PL/SQL allows you to declare a variable whose data type anchor to a table column or another variable. Consider the following example:
DECLARE l_customer_name customers.name%TYPE; l_credit_limit customers.credit_limit%TYPE; BEGIN SELECT name, credit_limit INTO l_customer_name, l_credit_limit FROM customers WHERE customer_id = 38; DBMS_OUTPUT.PUT_LINE(l_customer_name || ':' || l_credit_limit ); END; /
In this example:
- First, declare two variables
l_credit_limitwhose data type anchors to the
credit_limitcolumns respectively, in the declaration section of the block.
- Second, query customer name and credit limit of the customer id
38and assign these column values to the
l_credit_limitvariables in the execution block.
- Third, display the customer name and credit limit.
PL/SQL returned the following output:
This example illustrates how to declare variables that anchor to another variable:
DECLARE l_credit_limit customers.credit_limit%TYPE; l_average_credit l_credit_limit%TYPE; l_max_credit l_credit_limit%TYPE; l_min_credit l_credit_limit%TYPE; BEGIN -- get credit limits SELECT MIN(credit_limit), MAX(credit_limit), AVG(credit_limit) INTO l_min_credit, l_max_credit, l_average_credit FROM customers; SELECT credit_limit INTO l_credit_limit FROM customers WHERE customer_id = 100; -- show the credits dbms_output.put_line('Min Credit: ' || l_min_credit); dbms_output.put_line('Max Credit: ' || l_max_credit); dbms_output.put_line('Avg Credit: ' || l_average_credit); -- show customer credit dbms_output.put_line('Customer Credit: ' || l_credit_limit); END;
Here is the output:
Min Credit: 100 Max Credit: 5000 Avg Credit: 1894.67 Customer Credit: 1894.67
Now, you should know how to use PL/SQL variables in your block and manipulate them efficiently.