Testology

Chapter 2:PL/SQL Variables

PL/SQL Variables

Every PL/SQL variables has a specific data type which defines the size and physical location of the variable memory. Moreover you can specifies initial value of the variable at the time of variable declaration.

You must have to declare the PL/SQL variable in the declaration section. And variable name should not more than 31 characters. Variable name must be begin with ASCII letter. It can be either in uppercase or lowercase.

Followed by the first character are numerals, underscore, and dollar sign characters allowed.

PL/SQL is case-insensitive. It means variable name Length and length refer as the same variable.

Syntax for declaring variable

The general syntax to declaring variable:

variable_name Datatype[Size] [NOT NULL] := [ value ];

Here,variable_name is the predefined name of the variable.Data type is a valid PL/SQL data type.Size is an optional specification of data type size to hold the maximum size value.NOT NULL is an optional specification of the variable value can’t be accept NULL.value is also an optional specification, where you can initialize the initial value of variable.Each variable declaration is terminated by a semicolon.

PL/SQL variables naming rules

Like other programming languages, a variable in PL/SQL must follow the naming rules as follows:

  • The variable name must be less than 31 characters. Try to make it as meaningful as possible within 31 characters.
  • The variable name must begin with an ASCII letter. It can be either lowercase or uppercase. Notice that PL/SQL is case-insensitive, which means v_data and V_DATA refer to the same variable.
  • Followed by the first character are any number, underscore ( _), and dollar sign ( $) characters. Once again, do not make your variables hard to read and difficult to understand.

Initializing Variables in PL/SQL

When you declare a variable, its value is uninitialized and hence is NULL. If you want to initialize a variable with other value than NULL value, you can do so during the declaration.

In PL/SQL, NULLmeans an unknown value so it has some special characteristics as follows:

  • NULLis not equal to anything, even itself NULL.
  • NULLis not greater than or less than anything else, even NULL.
  • You cannot use logical operator equal ( =) or ( <>) with NULL. You must use the SQL IS NULL or IS NOT NULL to test the NULL values.

You can also specify NOT NULL constraint to avoid NULL value. If you specify the NOT NULL constraint, you must assign an initial value for that variable.

Example:

DECLARE  
   a integer := 30;  
   b integer := 40;  
   c integer;  
BEGIN  
   c := a + b;  
   dbms_output.put_line('Value of c: ' || c);   
END;

After the execution, the Output is:

Value of c: 70  

PL/SQL procedure successfully completed.

Variables Scope

PL/SQL have two type scopes local scope and global scope,

Local variables – Variables declared in inner block and can’t be referenced by the outside blocks.
Global variables – Where as variables declared in a outer block and can be referencing by itself in inner blocks.

Variable Scope Example

Example

DECLARE

    num1 number := 10;

num2 number := 20;

BEGIN

    DECLARE 

        num_addition number; 

    BEGIN 

        num_addition := num1 + num2; 

        dbms_output.put_line(‘Addition is: ‘ || num_addition);

    END;  --End of access num_addition variableEND;
/

Output is:

Addition is: 30