Bind Variable in PL/SQL

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;

Output:

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:

Use of %TYPE Attribute

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;

Example:

DECLARE
v_lname employees.last_name%type;
BEGIN
SELECT last_name INTO v_lname
FROM employees
WHERE employee_id='100';
DBMS_OUTPUT.PUT_LINE ('Last_name :'||v_lname);
END;

Output: