What Is A PL/SQL Variable?

What Is A PL/SQL Variable?

October 20, 2020
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

tutorial pl sql tutorial database programming database developer database administrator sql database plsql tutorial plsql sql programming language learning pl sql learning PLS-00371: at most one declaration for is permitted personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

tutorial pl sql tutorial database programming database developer database administrator sql database plsql tutorial plsql sql programming language learning pl sql learning PLS-00371: at most one declaration for is permitted personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

tutorial pl sql tutorial database programming database developer database administrator sql database plsql tutorial plsql sql programming language learning pl sql learning PLS-00371: at most one declaration for is permitted personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

tutorial pl sql tutorial database programming database developer database administrator sql database plsql tutorial plsql sql programming language learning pl sql learning PLS-00371: at most one declaration for is permitted personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

tutorial pl sql tutorial database programming database developer database administrator sql database plsql tutorial plsql sql programming language learning pl sql learning PLS-00371: at most one declaration for is permitted personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

tutorial pl sql tutorial database programming database developer database administrator sql database plsql tutorial plsql sql programming language learning pl sql learning PLS-00371: at most one declaration for is permitted personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

tutorial pl sql tutorial database programming database developer database administrator sql database plsql tutorial plsql sql programming language learning pl sql learning PLS-00371: at most one declaration for is permitted personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

tutorial pl sql tutorial database programming database developer database administrator sql database plsql tutorial plsql sql programming language learning pl sql learning PLS-00371: at most one declaration for is permitted personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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.

Next: How To Write PL/SQL Body Code

See Also:

Leave a Reply

Your email address will not be published. Required fields are marked *

*