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:

Leave a Reply

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