PL/SQL Variable

Use of Variables


Variables can be used for:

  • Temporary storage of data
  • Manipulation of stored values
  • Re-usability

Example:

SELECT first_name, last_name INTO v_fname,v_lname
FROM employees
WHERE employee_id=100;

Handling Variables in PL/SQL


  • Declared and initialized in the declarative section
  • Used and assigned new values in the executable section
  •  Passed as parameters to PL/SQL subprograms
  •  Used to hold the output of a PL/SQL subprogram

Example:Declaring Variable

DECLARE
v_hiredate DATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_locationVARCHAR2(13) := 'Atlanta';
c_comm CONSTANT NUMBER := 1400;

Example:

DECLARE
v_myName VARCHAR2(20);
BEGIN
DBMS_OUTPUT.PUT_LINE('My name is: '|| v_myName);
v_myName := 'Bhavesh';
DBMS_OUTPUT.PUT_LINE('My name is: '|| v_myName);
END;
/

Output:
My name is:
My name is: Bhavesh

Re-assigning the value of Variable


DECLARE
v_myName VARCHAR2(20):= 'Abhishek';
BEGIN
v_myName := 'Bhavesh Lakhani';
DBMS_OUTPUT.PUT_LINE('My name is: '|| v_myName);
END;

Output:
My name is: Bhavesh Lakhani

Delimiters in String Literals


DECLARE
v_festival VARCHAR2(30);
BEGIN
v_festival := q'!Independence's day!';
DBMS_OUTPUT.PUT_LINE('15th august is :'|| v_festival );
END;
/

Output:
15th august is :Independence’s day

Types of Variables


PL/SQL variables

  1. Scalar
  2. Composite
  3. Reference
  4. Large object (LOB)

Non-PL/SQL variables:

  1. Bind variables

Scalar Data Types


  • Hold a single value
  • Have no internal components

Example

DECLARE
v_emp_job VARCHAR2(9);
v_count_loop BINARY_INTEGER := 0;
v_dept_total_sal NUMBER(9,2) := 0;
v_orderdate DATE := SYSDATE + 7;
c_tax_rate CONSTANT NUMBER(3,2) := 8.25;
v_valid BOOLEAN NOT NULL := TRUE;

What is use of %TYPE Attribute


  • Is used to declare a variable according to:
  • A database column definition
  • Another declared variable
  • Is prefixed with:
    –The database table and column
    –The name of the declared variable

Example:Using database table column type

emp_lname employees.last_name%TYPE;

Example: Using another variable

balance NUMBER(7,2);
min_balance balance%TYPE := 1000;

Bind Variable


Bind variables are:

  • Created in the environment
  • Also called host variables
  • Created with the VARIABLE keyword
  • Used in SQL statements and PL/SQL blocks
  • Accessed even after the PL/SQL block is executed
  • Referenced with a preceding colon
VARIABLE b_emp_salary NUMBER
BEGIN
SELECT salary INTO :b_emp_salary
FROM employees WHERE employee_id = 100;
END;
/

Script Output:

 

print b_emp_salary;
SELECT first_name, last_name FROM employees
WHERE salary=:b_emp_salary;

Printing bind Variable Automatically:



VARIABLE b_emp_salary NUMBER
SET AUTOPRINT ON
DECLARE
v_empno NUMBER(6):=101;
BEGIN
SELECT salary INTO :b_emp_salary
FROM employees WHERE employee_id = v_empno;
END;

Output:

Leave a Reply

Your email address will not be published. Required fields are marked *