In this post we will discuss about PLSQL Variables in Oracle, Types of Variable, how to Declare variable in Oracle procedure, how to Declare variable in Oracle stored procedure, Scope of plsql Variables
Oracle PLSQL Variable
- Oracle PLSQL Variable are just like any other procedural language and it is used to store the result of a query or calculation
- Variables 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.
How to declare a variable in Oracle procedure
- All variables must be declared before they are referenced.
- Declaration involves the name of the variable followed by its data type.
- Remember that each variable must be declared on a separate line and multiple variables cannot be defined in the same statement.
- %TYPE – allows for unknown column datatypes when setting a variable. That is used to declare a variable that is of the same type as a specified table’s column. The advantage of this method of defining a variable is that, whenever the type and/or size of a column in the table is changed, it is automatically reflected in the variable declaration
- The %ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table
- BOOLEAN variables can only accept values of TRUE, FALSE, and NULL.
- ( : ) is used to prefix a bind variable
- By default, Oracle PLSQL variables are initialized as Null. Unless you explicitly assigned a value to the variable, it is undefined
- We can have Not Null on a variable, then we must assign some value to it. We can change the value of that variable but we cannot make it null
- We can initialize variables using the assignment operator (:=) or using Default
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; V_DEPT NUMBER (6) DEFAULT 160;
Examples as Full Procedure
DECLARE l_ret boolean; l_user_id number; BEGIN select user_id into l_user_id from fnd_user where user_name = '&USER_NAME'; l_ret := fnd_profile.DELETE(X_NAME => 'APPS_SERVLET_AGENT', X_LEVEL_NAME => 'USER', X_LEVEL_VALUE => l_user_id); commit; dbms_output.put_line('Profile has erased successfully'); EXCEPTION when others then dbms_output.put_line('Failed to erase the profile: '||sqlerrm); END; /
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 an inner block and cannot be referenced by outside Blocks.
Global variables – These are declared in an outer block and can be referenced by themselves and by its inner blocks.
Types of variables in plsql
1) Scalar datatype: It holds a single value. The main datatypes are the datatypes which are supported as column types in the Oracle server. PLSQL also support Boolean type
2) Composite type: These are records and allow groups of the field to be defined and manipulated in PLSQL. Composite datatypes also known as collections are table, record, Varray and nested table. Composite datatype has internal components which can be manipulated individually
Oracle PLSQL records: Check out this article on the working of oracle plsql records. Also, find out the various ways to define it and assign value to it
PLSQL Tables: Check out this post for a detailed description of PLSQL Tables.How to manipulate it and work on it in Oracle PLSQL block and benefits
What is cursor in oracle: Check out this post to get a great overview of What is cursor in oracle. Details about Cursor parameter, cursor loop, cursor for an update
PLSQL Constants : Check out this post on Oracle PLSQL Constants, How do you declare a constant variable in PL SQL, how to Declare a constant in Oracle stored procedure
Oracle PLSQL documentation
Oracle PlSQL interview question