What is a Variable?
A variable is like a labeled box where a programmer can stash information
for a little while. Think of it as a storage area with a specific name and a
designated purpose. Each variable in PL/SQL has a data type, defining how
much space it needs in the computer's memory.
Declaration Rules:
Keep it Concise:
- A variable name shouldn't be longer than 30 characters. It's a good practice to be brief and to the point.
- For example, ' Radius Number := 5; ' creates a variable named "Radius" with a numerical value of 5.
Declaration Steps:
- Always declare your variables before putting them to use. This happens in the declaration section of your PL/SQL block or in a package as a global variable.
Case Sensitivity Note:
- Variable names are not case-sensitive by default. However, you can't use a reserved PL/SQL keyword as a variable name.
Syntax for Declaring Variables:
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT
initial_value]
Declaration Restrictions:
No Forward References:
- You can't use a variable before telling PL/SQL about it. Declarations must come before any references.
Same Datatype Rule:
- Variables of the same datatype can't be declared together in a single statement.
Naming Rules:
1.Length Check:
Variable names should not exceed 30 characters.
2.Avoid Confusion:
Don't give your variable the same name as a table column in the block.
3.Start Right:
Variable names must begin with an ASCII letter. After that, you can use
numbers, underscores, or dollar signs.
4.Case Flexibility:
PL/SQL is not case-sensitive, so v_data and V_DATA refer to the same
variable.
Initializing Variables:
Default is NULL:
- When you declare a variable, PL/SQL automatically gives it a default value of NULL.
Methods of Initialization:
- Use the DEFAULT keyword or the assignment operator to give your variable an initial value.
- For instance, counter binary_integer := 0; or greetings varchar2(20) DEFAULT 'Hello PL/SQL';
Simple Initialization Example:
DECLARE
a INTEGER := 10;
b INTEGER := 20;
c INTEGER;
f REAL;
BEGIN
-- Calculate the sum of 'a' and 'b'
c := a + b;
dbms_output.put_line('Value of c: ' || c);
-- Calculate the result of 100.0 divided by 3.0
f := 100.0 / 3.0;
dbms_output.put_line('Value of f: ' || f);
END;
Output:
Value of c: 30
Value of f: 33.333333333333333333
Understanding Variable Scope in PL/SQL
In PL/SQL, variables can be local or global, depending on their scope
within blocks. Let's simplify the concept and explore examples of local
and global variables.
Local and Global Variables: A Simple Overview
Local Variables:
- Local variables are specific to the block they are declared in.
- They are not accessible outside the block.
Global Variables:
- Global variables are declared in the outermost block.
- They are accessible throughout the program.
Example of Local and Global Variables
DECLARE
-- Global variables
num1 NUMBER := 23;
num2 NUMBER := 24;
BEGIN
-- Output global variables
dbms_output.put_line('Global Variable or Outer
Variable num1: ' || num1);
dbms_output.put_line('Global Variable or Outer Variable
num2: ' || num2);
DECLARE
-- Local variables
num1 NUMBER := 2003;
num2 NUMBER := 2005;
BEGIN
-- Output local variables
dbms_output.put_line('Local Variable or Inner
Variable num1: ' || num1);
dbms_output.put_line('Local Variable or Inner
Variable num2: ' || num2);
END;
END;
Output:
Global variable or Outer Variable num1: 23
Global variable or Outer Variable num1:: 24
Local Variable or Inner Variable num1: 2003
Local Variable or Inner Variable num1: 2005
Variable Attributes Made Simple
%TYPE:
- Use %TYPE to declare a variable based on a table column.
- The data type of the variable will be the same as the column.
Syntax to declare Variable Using %TYPE:
variable_name table_name.column_name%TYPE;
Example Using %TYPE:
Create Employee table using sql commands:
-- Create EMP table
CREATE TABLE EMP (
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
SAL NUMBER(7, 2)
);
-- Insert some sample data
INSERT INTO EMP VALUES (7499, 'ALLEN', 1600);
INSERT INTO EMP VALUES (7521, 'WARD', 1250);
INSERT INTO EMP VALUES (7566, 'JONES', 2975);
-- Add more data as needed
-- Commit the changes
COMMIT;
PL/SQL %TYPE Program:
DECLARE
-- Declare variables using %TYPE
employee_id EMP.EMPNO%TYPE := &employee_id;
employee_name EMP.ENAME%TYPE;
employee_salary EMP.SAL%TYPE;
BEGIN
-- Retrieve employee details based on the provided
employee_id
SELECT ENAME, SAL INTO employee_name, employee_salary
FROM EMP WHERE EMPNO = employee_id;
-- Output employee details
dbms_output.put_line('Employee ID: ' || employee_id);
dbms_output.put_line('Employee Name: ' ||
employee_name);
dbms_output.put_line('Employee Salary: ' ||
employee_salary);
END;
To provide the output, I'll simulate the execution of the PL/SQL
block by assuming that you entered 7499 when prompted for
employee_id:
Assumed Output (If employee_id = 7499):
Employee ID: 7499
Employee Name: ALLEN
Employee Salary: 1600
%ROWTYPE:
- Use %ROWTYPE to declare a record representing a row in a table.
- The record's fields match the columns in the table.
Example %ROWTYPE program:
Here's a simple PL/SQL program using %ROWTYPE to insert a new record
into the EMP table.
DECLARE
-- Declare a record using %ROWTYPE
new_employee EMP%ROWTYPE;
-- Variable to store a new employee ID
new_employee_id NUMBER := 9999; -- You can adjust
this value
BEGIN
-- Assign values to the record fields
new_employee.EMPNO := new_employee_id;
new_employee.ENAME := 'NEW_EMPLOYEE';
new_employee.SAL := 2500.00; -- You can adjust
this value
-- Insert the new employee record into the EMP table
INSERT INTO EMP VALUES new_employee;
-- Commit the transaction
COMMIT;
-- Output a success message
dbms_output.put_line('New employee added
successfully!');
END;
Simulated Output:
New employee added successfully!
Output:
More topic in PL/SQL
