What Is A PL/SQL Variable?
March 27, 2023 | Oracle DBA |
This online PL/SQL programming language tutorial will to explain you a fundamental of PL/SQL variables and why do we need them.
To start with PL/SQL variables we need to understand where do keep them. All variables are defined in the DECLARE part of a PL/SQL block and when you declaring a variable you actually reserving a storage space in a computer memory. Every variable type is needing a different amount of space storage to be allocated for example if you declaring a number type for 1 number digit then value can be only from -9 to -1 or from 0 to 9 and if the variable is a date type to keep one date value the computer needs a space to store a number of year, a number of month, a number of day, a number of hours, a number of minutes etc. In this case a date variable needs more memory space to be allocated.
A Few Performance Tips:
- Do not keep variables declared if they are not in use.
- Re-use the existing variables where is possible.
- Use global variables if they are taking much space for example types like CLOB and BLOB.
- Do not forget to empty CLOB and BLOB data types them after you stop using them, because they are keeping the space allocated through all session and it may slow down your processes.
A PL/SQL variable should always be declared before using it in the declaration part and it needs a name to point to correct memory sector where the space is reserved. The variable name should be unique in the same declaration part. The next PL/SQL tutorial example shows how you can declare variables to an empty PL/SQL block.
DECLARE v_number NUMBER(17); v_name VARCHAR2(20); v_exists BOOLEAN; v_salary NUMBER(6,2); v_commentary VARCHAR2(2000) := 'PL/SQL Tutorial'; v_amount PLS_INTEGER := 1100; BEGIN NULL; END;
On the last example you can see that two last variables are declared differently – they have a value assigned. You can assign a initial value in the declaration part and it can be overwritten in the code with a new value.
The next declaration type is constant variables and the constant variables are important if you need to keep the initial value in the PL/SQL variable. This way declared variable helps you to avoid a mistake to rewrite the initial value where it is not allowed. The initial value needs to be assigned to variable when it is getting declared. The following sample shows how to declare a constant variable.
DECLARE v_number CONSTANT NUMBER(17) := 1; v_name CONSTANT VARCHAR2(20) := 'John'; v_exists CONSTANT BOOLEAN := true; v_salary CONSTANT NUMBER(6,2) := 2532.11; v_commentary CONSTANT VARCHAR2(2000) := 'PL/SQL Tutorial'; v_amount CONSTANT PLS_INTEGER := 1100; BEGIN NULL; END;
P.S. All variables declared as constant are read-only now.
Another way to avoid programming mistakes with declaring variables is to set a variable NOT NULL. This constraint will raise an error if somewhere in the code something is trying to assign a variable null (empty) value. To declare a NOT NULL variable you need to assign the initial value in the declaration part.
DECLARE v_number NUMBER(17) NOT NULL := 1; v_name VARCHAR2(20) NOT NULL := 'John'; v_exists BOOLEAN NOT NULL := true; v_salary NUMBER(6,2) NOT NULL := 2532.11; v_commentary VARCHAR2(2000) NOT NULL := 'PL/SQL Tutorial'; v_amount PLS_INTEGER NOT NULL := 1100; BEGIN NULL; END;
When you need to declare many variables with exactly the same type then the easiest way to do it is to use %TYPE attribute. The attribute works like a link and it referencing to the variable that has been declared before it. Variables v_2 and v_3 has no idea what could be the data type, so while v_1 is declared they are just taking over from it the same data type. To see how it works lets declare 3 number variables and last two are using the %TYPE attribute.
DECLARE v_1 NUMBER; v_2 v_1%TYPE; v_3 v_1%TYPE; BEGIN NULL; END;
Now we realised that the type should a text, so change them all to VARCHAR2 we need to amend only the v_1 variable data type. To see how it is done, take a look on the next example:
DECLARE v_1 VARCHAR2(50); v_2 v_1%TYPE; v_3 v_1%TYPE; BEGIN NULL; END;
With one change we changed all the variables to VARCHAR2.
Through all the examples we have been using only one declaration block, but you can write declaration part into executable area too. We should call them as sub-declarations. The next PL/SQL tutorial sample will show you how to declare more declarations into executable area and this widely used in database programming to make your programming code easier to follow.
DECLARE
v_number NUMBER(17);
v_name VARCHAR2(20);
BEGIN
DECLARE
v_exists BOOLEAN;
v_salary NUMBER(6,2);
BEGIN
NULL;
END;
DECLARE
v_number NUMBER(17);
v_name VARCHAR2(20);
BEGIN
NULL;
END;
END;
When you are looking at the example above you’ll notice that the lowest sub-declaration has the variables declared with the same name as the main declaration. You can declare variables with the same name in the code, but not in the same declaration. It is also important to know that the lowest sub-executable block will have variables v_number and v_name with new values because the code will overwrite the variable name with the local sub-declaration and after the process is done in the sub-block and leaves it the v_number and v_name variable will have been assigned back the last main block values as they were before entering into the sub-block.
Important to Know:
About declaring two variables with the same name. The PL/SQL anonymous block will not raise an error until you would not use none of them. See the following Oracle DBA example:
DECLARE
v_1 VARCHAR2(50);
v_1 NUMBER;
BEGIN
dbms_output.put_line('Hello PL/SQL Tutorial!');
END;
And now lets use the same example assigning a value to the declared variable:
DECLARE
v_1 VARCHAR2(50);
v_1 NUMBER;
BEGIN
v_1 := 1;
dbms_output.put_line('Hello PL/SQL Tutorial!');
END;
The error PLS-00371: at most one declaration for ‘V_1’ is permitted is saying quite clearly that only one v_1 variable name is permitted. You should be very careful declaring your variables and while your variable names are clear enough there shouldn’t be any duplicated declarations.