Bind Variables & Associative arrays

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;