Oracle PLSQL Variable
1) Variables and constants must be declared for use in procedural and SQL code, although the datatypes available in SQL are only a subset of those available in PL/SQL.
2) All variables and constants must be declared before they are referenced.
3) Remember that each variable must be declared on a separate line and multiple variables cannot be defined in the same statement.
4) The declarations of variables and constants are similar, but constant definitions must contain the CONSTANT keyword and must be assigned a value as part of the definition. Subsequent attempts to assign a value to a constant will result in an error
5) %TYPE – allows for unknown column datatypes when assigning a variable. That is used to declare a variable that is of the same type as a specified table’s column
6) BOOLEAN variables can only accept values of TRUE, FALSE, and NULL.
7) ( : ) is used to prefix a bind variable.
8) By default Oracle PLSQL variable are initialized as Null. Unless you explicitly assigned a value to the variable, it is undefined
9) We can have Not Null on variable, then we must assign some value to it. We can change the value of that variable but we cannot make it null
10.We can initialized variable using the assignment operator (:=) or using Define
General syntax
| variable_name datatype [NOT NULL := value ]; |
1) variable_name is the name of the variable.
2) datatype is a valid PL/SQL datatype.
Example of declaring Variables:
v_name varchar2(100); creation_date Date; v_num number default 5; v_num1 number not null := 2; v_emp varchar2(20) := Null; Var5 varchar2(20) default Null;
Example of declaring Constants:
v_global constant number := 200; Constants cannot be changed. You must initialize constants at the time of declaration.
Scope of Oracle PLSQL Variable
PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.
Local variables – These are declared in a inner block and cannot be referenced by outside Blocks.
Global variables – These are declared in a outer block and can be referenced by its itself and by its inner blocks.
Types of Variable
1) Scalar datatype: It holds single value. The main datatypes are the datatypes which are supported as column type in Oracle server. PLSQL also support Boolean type
2) Composite type: These are records and allows groups of field to be defined and manipulated in PLSQL. Composite datatypes also known as collection are table, record ,Varray and nested table. Composite datatype has internal components which can be manipulated individually
Related links
Oracle PLSQL documentation
Most commonly asked 25 Oracle PlSQL interview question
Oracle sql and plsql
how to write sql queries
Leave a Reply