CodesJava

Easy learning with example program codes

oracle pl sql variable tutorial


Variable:

Variable is the name of reserved memory location. Each variable has a specific data type which determines the range of values and set of operations for that variable.

PL/SQL variables naming rules:

A variable name can’t contain more than 30 characters.
A variable name must start with an ASCII letter followed by any number, underscore (_) or dollar sign ($).
PL/SQL is case-insensitive i.e. var and VAR refer to the same variable.

How to declare variable in PL/SQL:

We have to declare a PL/SQL variable in the declaration section or in a package as a global variable. After declaration PL/SQL allocates memory for the variable and variable name is used to identify the storage location.

Syntax:

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

Where:
variable_name is a valid identifier name.
datatype is a valid PL/SQL datatype.

Initializing Variables in PL/SQL:

When we declare a variable PL/SQL assigns it NULL as default value. If we want to initialize a variable with a non-NULL value, we can do it during the declaration. We can use any one of the following methods:
1. The DEFAULT keyword
Num1 binary_integer := 0;

2. The assignment operator
siteName varchar2(20) DEFAULT ‘codesjava’;

Example:

DECLARE
   var1 integer := 20;
   var2 integer := 40;
   var3 integer;
   var4 real;
BEGIN
   var3 := var1 + var2;
   dbms_output.put_line('Value of var3: ' || var3);
   var4 := 50.0/3.0;
   dbms_output.put_line('Value of var4: ' || var4);
END;
/

Output

Value of var3: 60
Value of var4: 16.66666666666666666666666666666666666667

Variable Scope in PL/SQL:

As we discussed in earlier tutorial that PL/SQL allows the nesting of blocks i.e. blocks with blocks. Based on the nesting structure PL/SQL variables can be divide into following categories:
Local variables – Those variables which are declared in an inner block and not accessible to outer blocks are known as local variables.
Global variables – Those variables which are declared in the outer block or a package and accessible to itself and inner blocks are known as global variables.

Example:

DECLARE
   -- Global variables 
   num1 number := 10; 
   num2 number := 20; 
BEGIN 
   dbms_output.put_line('Outer Variable num1: ' || num1);
   dbms_output.put_line('Outer Variable num2: ' || num2);
   DECLARE 
      -- Local variables
      num3 number := 30; 
      num4 number := 40; 
   BEGIN 
      dbms_output.put_line('Outer variable in inner block num1: ' || num1);
      dbms_output.put_line('Outer variable in inner block num2: ' || num2);
      dbms_output.put_line('Inner Variable num3: ' || num3);
      dbms_output.put_line('Inner Variable num4: ' || num4);
   END; 
END;
/

Output:

Outer Variable num1: 10
Outer Variable num2: 20
Outer variable in inner block num1: 10
Outer variable in inner block num2: 20
Inner Variable num3: 30
Inner Variable num4: 40

 



Industrial Training

We offers Placement Oriented Training on Java, Spring, JSF, Hibernate, PHP, AngularJS, Angular 4, PLSQL, Oracle BI Publisher etc. We also provides Online training, please mail us at hr@codesjava.com.

Development

We also provides the Development services for Website Development , Java Development, PHP Development, Android App Development etc. You can contact us on hr@codesjava.com.

Copyright © 2019 CodesJava DMCA.com Protection Status SiteMap Reference: Java Wiki