In this blog, we’ll discuss Variables in Pl/SQL. A variable is nothing but a reputation given to a cargo area that our programs can manipulate. Each variable in PL/SQL features a specific data type, which determines the dimensions and therefore the layout of the variable’s memory; the range of values which will be stored within that memory and the set of operations which will be applied to the variable.
The name of a PL/SQL variable consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and will not exceed 30 characters. By default, variable names aren’t case-sensitive. you can’t use a reserved PL/SQL keyword as a variable name.
PL/SQL programing language allows to define various sorts of variables, like date time data types, records, collections, etc. which we’ll cover in subsequent chapters. For this chapter, allow us to study only basic variable types.
Variable Declaration in PL/SQL
PL/SQL variables must be declared within the declaration section or during a package as a worldwide variable. once you declare a variable, PL/SQL allocates memory for the variable’s value and therefore the storage location is identified by the variable name.
The syntax for declaring a variable is −
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
Where, variable_name may be a valid identifier in PL/SQL, datatype must be a legitimate PL/SQL data type or any user defined data type which we have already got discussed within the last chapter. Some valid variable declarations along side their definition are shown below −
sales number(10, 2);
pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);
When you provide a size, scale or precision limit with the info type, it’s called a constrained declaration. Constrained declarations require less memory than unconstrained declarations. for instance −
sales number(10, 2);
name varchar2(25);
address varchar2(100);
Initializing Variables in PL/SQL
Whenever you declare a variable, PL/SQL assigns it a default value of NULL. If you would like to initialize a variable with a worth aside from the NULL value, you’ll do so during the declaration, using either of the subsequent −
• The DEFAULT keyword
• The assignment operator
For example −
counter binary_integer := 0;
greetings varchar2(20) DEFAULT ‘Have an honest Day’;
You can also specify that a variable shouldn’t have a NULL value using the NOT NULL constraint. If you employ the NOT NULL constraint, you want to explicitly assign an initial value for that variable.
It is an honest programming practice to initialize variables properly otherwise, sometimes programs would produce unexpected results. Try the subsequent example which makes use of varied sorts of variables −

DECLARE 
a integer := 10; 
b integer := 20; 
c integer; 
f real; 
BEGIN 
c := a + b; 
dbms_output.put_line('Value of c: ' || c); 
f := 70.0/3.0; 
dbms_output.put_line('Value of f: ' || f); 
END; 
When the above code is executed, it produces the subsequent result −
Value of c: 30 
Value of f: 23.333333333333333333

PL/SQL procedure successfully completed.
Variable Scope in PL/SQL
PL/SQL allows the nesting of blocks, i.e., each program block may contain another inner block. If a variable is said within an inner block, it’s not accessible to the outer block. However, if a variable is said and accessible to an outer block, it’s also accessible to all or any nested inner blocks. There are two sorts of variable scope −
• Local variables − Variables declared in an inner block and not accessible to outer blocks.
• Global variables − Variables declared within the outermost block or a package.
Following example shows the usage of Local and Global variables in its simple form −

DECLARE 
-- Global variables 
num1 number := 95; 
num2 number := 85; 
BEGIN 
dbms_output.put_line('Outer Variable num1: ' || num1); 
dbms_output.put_line('Outer Variable num2: ' || num2); 
DECLARE 
-- Local variables 
num1 number := 195; 
num2 number := 185; 
BEGIN 
dbms_output.put_line('Inner Variable num1: ' || num1); 
dbms_output.put_line('Inner Variable num2: ' || num2); 
END; 
END; 
/

When the above code is executed, it produces the subsequent result −
Outer Variable num1: 95
Outer Variable num2: 85
Inner Variable num1: 195
Inner Variable num2: 185

PL/SQL procedure successfully completed.
Assigning SQL Query Results to PL/SQL Variables
You can use the SELECT INTO statement of SQL to assign values to PL/SQL variables. for every item within the SELECT list, there must be a corresponding, type-compatible variable within the INTO list. the subsequent example illustrates the concept. allow us to create a table named CUSTOMERS −

(For SQL statements, please ask the SQL tutorial)
CREATE TABLE CUSTOMERS( 
ID INT NOT NULL, 
NAME VARCHAR (20) NOT NULL, 
AGE INT NOT NULL, 
ADDRESS CHAR (25), 
SALARY DECIMAL (18, 2), 
PRIMARY KEY (ID) 
);

Table Created
Let us now insert some values within the table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

The following program assigns values from the above table to PL/SQL variables using the SELECT INTO clause of SQL −

DECLARE 
c_id customers.id%type := 1; 
c_name customers.name%type; 
c_addr customers.address%type; 
c_sal customers.salary%type; 
BEGIN 
SELECT name, address, salary INTO c_name, c_addr, c_sal 
FROM customers 
WHERE id = c_id; 
dbms_output.put_line 
('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal); 
END; 
/

When the above code is executed, it produces the subsequent result −
Customer Ramesh from Ahmedabad earns 2000

PL/SQL procedure completed successfully
So, this brings us to the end of blog. This Tecklearn ‘Variables in PL-SQL’ blog helps you with commonly asked questions if you are looking out for a job in Oracle Pl-SQL. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Oracle PL SQL Training

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?

• The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
• PL-SQL has a market share of 23% globally.
• IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle SQL
• Database Models
• RDBMS
• Components of SQL
• DataTypes
• DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
• Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
• DML Commands-Insert, Update, Delete
• Order by Clause
• Group Functions
• SET Operators- Union All, Union, Intersect, Minus
• TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
• Types of Views
• Synonyms
• Types of Synonyms
• Indexes
• Types of Indexes
Using Subqueries to Solve Queries
• Subqueries
• Co-Related Subquery
OLAP Functions
• OLAP Features
• Roll Up
• Model Clause
• Dimension Modelling
Conditional Statement
• Block
• Variable Attributes
• Nested Blocks
• Conditional Control Statements
• Iterative Controls (Loop)
Cursor Management
• Types of Cursor
• Writing Explicit cursors
• Explicit cursor functions
• Advance Explicit cursor
• Cursor with parameters
Exception Handling
• Handling Exception
• Handling Exception with PL/SQL Predefined Exceptions,
• User Defined Exceptions
• Non-Predefined Error
• Function for trapping Exception
• Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
• Important Features of Sub-Programs
• Procedure
• Functions
Trigger Management
• Introduction to Triggers
• Types of Triggers
• Compound Triggers
Oracle Job Scheduling
Large Object Functions
• Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting

464