Bind Variables

Suppose that you want to be to display the variables you  use in your PL/SQL subprograms in SQL*Plus or you can use the same variables in multiple subprograms.

If you declare a variable in a PL/SQL , We  cannot display that variable in SQL*Plus.Use a bind variable in PL/SQL to access the variable from SQL*Plus.

Bind variables are variables you generate  in SQL*Plus and then reference in PL/SQL. If you create a bind variable in SQL*Plus, you can use this variable as you would a declared variable in your PL/SQL subprogram and then access the variable from SQL*Plus.

Example 1:

VARIABLE G_NAME VARCHAR2(20)
VARIABLE G_SAL NUMBER

BEGIN
  SELECT LAST_NAME,SALARY
  INTO   :G_NAME,:G_SAL
  FROM   EMPLOYEES
  WHERE  EMPLOYEE_ID=&EMPNO;
END;
/
PRINT G_NAME
PRINT G_SAL


Example 2:

VARIABLE G_NAME VARCHAR2(20)
VARIABLE G_SAL NUMBER

BEGIN
  SELECT LAST_NAME,SALARY
  INTO   :G_NAME,:G_SAL
  FROM   EMPLOYEES
  WHERE  EMPLOYEE_ID=&EMPNO;
  DBMS_OUTPUT.PUT_LINE(:G_NAME||' GETS '||:G_SAL);
END;
/
PRINT G_NAME
PRINT G_SAL


Example 3:

DECLARE
  V_SAL NUMBER;
  V_FLAG BOOLEAN;
BEGIN
  SELECT SALARY INTO V_SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=&EMPNO;

  IF V_SAL >= 10000 THEN
     V_FLAG := TRUE;
  ELSE
     V_FLAG := FALSE;
  END IF;

  IF V_FLAG=TRUE THEN
     DBMS_OUTPUT.PUT_LINE('HIGH GRADE');
  ELSE
     DBMS_OUTPUT.PUT_LINE('LOWER GRADE');
  END IF;
END;


Example 4:-


DECLARE
  V_SAL NUMBER;
  V_FLAG BOOLEAN;
BEGIN
  SELECT SALARY INTO V_SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=&EMPNO;

  V_FLAG := (V_SAL>=10000);

  IF V_FLAG=TRUE THEN
     DBMS_OUTPUT.PUT_LINE('HIGH GRADE');
  ELSE
     DBMS_OUTPUT.PUT_LINE('LOWER GRADE');
  END IF;
END;