1. Use of Variables

Variables can be used for

  1. Temporary storage of data
  2. Manipulation of stored values
  3. Reusability

Variables are mainly used for the storage of data and manipulation of stored values.
Reusability is another advantage of declaring variables.
Once declared they can be used repeatedly in an application by referring to them in SQL statements.

  1. Identifiers

Identifiers are used for

  1. Naming a Variable
  2. Providing conventions or rules for variable names
  • Must start with a letter
  • Can include letters or numbers
  • Can include special characters ($,_)
  • Must limit the length to 30 characters (A Variable Name should not exceed 30 Characters)
  • Must not be reserved keyword

Identifiers are names of variables.Variables are storage locations of data.
Data is stored in memory.Variables point to this memory location where data can be read and modified.
Identifiers are used to name PL/SQL objects (such as variables,types,cursors)

  1. Handling Variables in PL/SQL

Variables are

  1. Declared and initialized in declaration section
  2. Used and assigned new values in the executable section
  3. Passed as parameters to PL/SQL Subprograms
  4. Used to hold the output of a PL/SQL Subprogram
  1. Declaring and initializing PL/SQL Variables

Syntax :
identifier [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT expr];

Example

DECLARE
emp_hiredate DATE;
emp_deptno NUMBER(2) NOT NULL:=10;
location VARCHAR2(13):=’Atlanta’;
c_comm CONSTANT NUMBER:=1400;

Example

— PL/SQL Block —
SET SERVEROUTPUT ON

DECLARE
myname VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE(‘My Name is : ‘||myname);
myname:=’John’;
DBMS_OUTPUT.PUT_LINE(‘My Name is : ‘||myname);
END;
/

DECLARE
myname VARCHAR2(20):=’John’;
BEGIN
myname:=’Steven’;
DBMS_OUTPUT.PUT_LINE(‘My Name is : ‘||myname);
END;
/

DECLARE
myname VARCHAR2(20):=’John’;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘My Name is : ‘||myname);
myname:=’Steven’;
DBMS_OUTPUT.PUT_LINE(‘My Name is : ‘||myname);
END;
/

DECLARE
myname VARCHAR2(20):=’John’;
BEGIN
myname:=’Smith’;
DBMS_OUTPUT.PUT_LINE(‘My Name is : ‘||myname);
myname:=’Steven’;
DBMS_OUTPUT.PUT_LINE(‘My Name is : ‘||myname);
END;
/

— PL/SQL Block to demonstrate the NOT NULL for a Variable —
DECLARE
v_deptno NUMBER NOT NULL:=10;
BEGIN
v_deptno:=NULL; — Variable cannot be initialized with a NULL Value —
END;
/

— PL/SQL Block to demonstrate the CONSTANT for a Variable —
— This will error out since CONSTANT variable cannot be initialized —
DECLARE
salary_increase CONSTANT NUMBER(3):=100;
BEGIN
salary_increase:=200;
DBMS_OUTPUT.PUT_LINE(salary_increase);
END;
/

— PL/SQL Block to demonstrate the CONSTANT for a Variable —
— This will error out since CONSTANT variable cannot be initialized in BEGIN —
DECLARE
salary_increase CONSTANT NUMBER(3); — Must be set with a Value —
BEGIN
salary_increase:= 100;
dbms_output.put_line (salary_increase);
END;
/

  1. Delimiters in String Literals

Example

DECLARE
event VARCHAR2(15);
BEGIN
event:= q’!Father’s day!’;
DBMS_OUTPUT.PUT_LINE(‘3rd Sunday in June is : ‘||event);
event:= q'[Mother’s day]’;
DBMS_OUTPUT.PUT_LINE(‘2nd Sunday in May is : ‘||event);
END;
/

Without Quote Operator
DECLARE
event VARCHAR2(15);
BEGIN
event:= q'{Father’s day!}’;
DBMS_OUTPUT.PUT_LINE(‘3rd Sunday in June is : ‘||event);
event:= q'[Mother’s day]’;
DBMS_OUTPUT.PUT_LINE(‘2nd Sunday in May is : ‘||event);
END;
/

  1. Types of Variables
  1. PL/SQL Variables
  • Scalar – Holds a single value
  • Composite – Holds either single or multiple Eg Record and Table
  • Reference – Refers to an already declared variable
  • Large Objects (LOB)
  1. Non – PL/SQL Variables : Bind Variables

Guidelines for declaring and initializing PL/SQL Variables :

  1. Follow naming conventions
  2. Use meaningful names for variables
  3. Initialize variables designated as NOT NULL and CONSTANT
  4. Initialize variables with the assignment operator (:=) or the DEFAULT keyword
    myname VARCHAR2(20):=’John’;
    myname VARCHAR2(20) DEFAULT ‘John’;
  5. Declare one identifier per line for better readability and code maintenance
  6. Avoid using column names as identifiers
  1. Scalar Datatypes

Holds a single value

  1. CHAR [(maximum_length)]
  2. VARCHAR2 (maximum_length)
  3. LONG
  4. LONG RAW
  5. NUMBER
  6. BINARY_INTEGER – Values between -231 .. 231 which is much faster then INTEGER and NUMBER.
    Prior to Oracle 10g this was the only datatype.
  7. PLS_INTEGER – Same as above. New datatype introduced in Oracle 10g and used for storing signed integers.
  8. BOOLEAN – Either TRUE or FALSE
  9. BINARY_DOUBLE – Introduced in Oracle 10g for storing binary number format.This datatype will not accept the width.
  10. DATE
  11. TIMESTAMP
  12. TIMESTAMP WITH TIMEZONE
  13. TIMESTAMP WITH LOCAL TIME ZONE
  14. INTERVAL YEAR TO MONTH
  15. INTERVAL DAY TO SECOND
  1. Declaring Scalar Variables

DECLARE
vc_gender CHAR(1);
vcv_name VARCHAR2(30);
vn_salary NUMBER;
vbi_cnt_loop BINARY_INTEGER:=0;
vplsi_cnt_loop PLS_INTEGER:=0;
vb_binary_float BINARY_FLOAT;
vd_date DATE;
vt_timestamp TIMESTAMP;
vb_valid BOOLEAN NOT NULL:=TRUE;
BEGIN
vc_gender:=’M’;
vcv_name:=’Rahul’;
vn_salary:=12000;
vbi_cnt_loop:=1000000455;
vplsi_cnt_loop:=1000000455;
vb_binary_float:=12.3433333;
vd_date:=’12-JUN-2012′;
vt_timestamp:=’12-JUN-2012′;
DBMS_OUTPUT.PUT_LINE(‘Gender Variable ‘||vc_gender);
DBMS_OUTPUT.PUT_LINE(‘Name ‘||vcv_name);
DBMS_OUTPUT.PUT_LINE(‘Salary ‘||vn_salary);
DBMS_OUTPUT.PUT_LINE(‘Binary Integer Variable Value ‘||vbi_cnt_loop);
DBMS_OUTPUT.PUT_LINE(‘PLS Integer Cnt Variable Value ‘||vplsi_cnt_loop);
DBMS_OUTPUT.PUT_LINE(‘Binary Float’||vb_binary_float);
DBMS_OUTPUT.PUT_LINE(‘Date Value’||vd_date);
DBMS_OUTPUT.PUT_LINE(‘Date with TIMESTAMP Value’||vt_timestamp);
IF vb_valid = TRUE THEN
DBMS_OUTPUT.PUT_LINE(‘Boolean Variable Value is True’);
END IF;
END;
/

  1. % TYPE Attribute

The %TYPE attribute

  1. Is used to declare a variable according to :
  • A Database Column definition
  • Another declared variable (Reference Variables)
  1. Is prefixed with :
  • The Database Table and Column
  • The name of the declared variable

%TYPE attribute is used to refer to a database table column’s datatype in PL/SQL block.

The %TYPE is used when the value stored in the variable is derived from a table.
While using this you should prefix it with the database table and column name.
If you refer to a previously declared variable prefix the variable name to the attribute.

You need not change the variable declaration if the column definition changes in the table.

Syntax
identifier table.column_name%TYPE;

Example

DECLARE
v_ename emp.ename%type;
v_salary emp.sal%type;
v_avg_sal v_salary%type:=0;
BEGIN
SELECT ename,sal
INTO v_ename,v_salary
FROM emp
WHERE empno = 7369;
DBMS_OUTPUT.PUT_LINE(‘Employee Name ‘||v_ename);
DBMS_OUTPUT.PUT_LINE(‘Employee Salary ‘||v_salary);
DBMS_OUTPUT.PUT_LINE(‘Average Salary ‘||v_avg_sal);
END;
/

  1. Bind Variables

Bind Variables are :

  1. Created in the SQL environment
  2. Called as HOST variables
  3. Normally created with the keyword VARIABLE
  4. Can be accessed even after PL/SQL Block is executed
  5. Referenced with : Colon

Example

VARIABLE result NUMBER
BEGIN
SELECT (sal * 12) + NVL(COMM,0) INTO :result
FROM emp WHERE empno = 7369;
END;
/

PRINT :result

VARIABLE emp_salary NUMBER
BEGIN
SELECT sal INTO :emp_salary
FROM emp WHERE empno = 7369;
END;
/

PRINT :emp_salary

SELECT ename,empno
FROM emp
WHERE sal = :emp_salary
/

— Using AUTOPRINT —
SET AUTOPRINT ON
BEGIN
SELECT sal INTO :emp_salary
FROM emp WHERE empno = 7369;
END;
/

— Declaring More than one BIND Variable and Printing all the BIND Variable Values —
VARIABLE emp_salary NUMBER
VARIABLE emp_sal NUMBER
SET AUTOPRINT ON
BEGIN
SELECT sal INTO :emp_salary
FROM emp WHERE empno = 7369;
SELECT sal * 12 INTO :emp_sal
FROM emp WHERE empno = 7369;
END;
/

— Declaring More than one BIND Variable and Printing all the BIND Variable Values —
— Using DBMS_OUTPUT to Print the BIND Variable —
BEGIN
SELECT sal INTO :emp_salary
FROM emp WHERE empno = 7369;
SELECT sal * 12 INTO :emp_sal
FROM emp WHERE empno = 7369;
DBMS_OUTPUT.PUT_LINE(‘Salary ‘||:emp_salary);
DBMS_OUTPUT.PUT_LINE(‘Salary with Annual Salary ‘||:emp_sal);
END;
/

  1. Substitution Variables

SET AUTOPRINT ON
DECLARE
v_empno emp.empno%type:=&empno;
BEGIN
SELECT sal INTO :emp_salary
FROM emp WHERE empno = v_empno;
END;
/

SET VERIFY OFF
ACCEPT empno PROMPT ‘Please enter a valid Employee No. : ‘
SET AUTOPRINT ON
DECLARE
v_empno emp.empno%type:=&empno;
BEGIN
SELECT sal INTO :emp_salary
FROM emp WHERE empno = v_empno;
END;
/

DECLARE
v_empno emp.empno%type:=&v_empno;
BEGIN
SELECT sal INTO :emp_salary
FROM emp WHERE empno = v_empno;
END;
/

SET VERIFY OFF
ACCEPT ename PROMPT ‘Please enter a valid Employee Name : ‘
SET AUTOPRINT ON
DECLARE
v_ename emp.ename%type:=’&ename’;
BEGIN
SELECT sal INTO :emp_salary
FROM emp WHERE ename = v_ename;
END;
/

  1. Composite Data Types

A Scalar Datatype has no internal components.
A Composite Datatype has internal components that can be manipulated individually.
Composite Datatypes are of TABLE, RECORD, NESTED TABLE and VARRAY
Use the TABLE datatype to reference and manipulate collections of data as a whole object.
Use the RECORD datatype to treat related but dissimilar data as a logical unit.

© 2024 AWS. Learn from Scratch