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;









Understanding Associative Arrays (Index-By Tables)


Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.


Giving a value using a key for the first time adds that key to the associative array. Following assignments using the same key update the same entry. It is important to choose a key that is unique, either by using the primary key from a SQL table, or by concatenating strings collected to form a unique value.


For example, here is the declaration of an associative array type, and two arrays of that type, using keys that are strings:


DECLARE
TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population population_type;
continent_population population_type;
howmany NUMBER;
which VARCHAR2(64)


BEGIN
country_population('Greenland') := 100000;
country_population('Iceland') := 750000;
howmany := country_population('Greenland');


continent_population('Australia') := 30000000;
continent_population('Antarctica') := 1000; -- Creates new entry
continent_population('Antarctica') := 1001; -- Replaces previous
value
which := continent_population.FIRST; -- Returns 'Antarctica'
-- as that comes first alphabetically.
which := continent_population.LAST; -- Returns 'Australia'
howmany := continent_population(continent_population.LAST);
-- Returns the value corresponding to the last key, in this
-- case the population of Australia.
END;
/











DECLARE
TYPE number_index_by_string IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
TYPE string_index_by_number IS TABLE OF dept.loc%TYPE INDEX BY PLS_INTEGER;


v_country_codes NUMBER_INDEX_BY_STRING;
v_countries STRING_INDEX_BY_NUMBER;
BEGIN
v_country_codes('Ukraine') := 380;
v_country_codes('UAE') := 971;
v_country_codes('UK') := 44;
v_country_codes('USA') := 1;


v_countries(380) := 'Ukraine';
v_countries(971) := 'UAE';
v_countries(44) := 'UK';
v_countries(1) := 'USA';






END;