Function overloading by datatype of parameters

SET SERVEROUTPUT ON

DECLARE
     FUNCTION getArea(v_radious NUMBER, v_precision NUMBER) RETURN NUMBER IS
     l_pi NUMBER := 3.14;
     BEGIN
          RETURN TRUNC((l_pi *(v_radious ** 2)),v_precision);
     END;
     FUNCTION getArea(v_radious NUMBER ,v_ignore CHAR) RETURN NUMBER IS
     BEGIN
          IF v_ignore = 'Y' THEN
             RETURN 0;
          ELSE
             RETURN (3.14 *(v_radious ** 2));
          END IF;
     END;
BEGIN
     DBMS_OUTPUT.PUT_LINE('Area of Circle is '|| getArea(6.4, 2));
     DBMS_OUTPUT.PUT_LINE('Area of Rectangle is '|| getArea(6,'N'));
END;

Function overloading by name of parameters

SET SERVEROUTPUT ON

DECLARE
     FUNCTION getArea(v_radious NUMBER, v_precision NUMBER) RETURN NUMBER IS
     l_pi NUMBER := 3.14;
     BEGIN
          RETURN TRUNC((l_pi *(v_radious ** 2)),v_precision);
     END;
     FUNCTION getArea(v_length NUMBER ,v_width NUMBER) RETURN NUMBER IS
     BEGIN
          RETURN(v_length * v_width);  
     END;
BEGIN
     DBMS_OUTPUT.PUT_LINE('Area of Circle is '|| 

                getArea(v_radious =>6.4, v_precision => 2));
     DBMS_OUTPUT.PUT_LINE('Area of Rectangle is '|| 

                getArea(v_length => 6,v_width => 4));
END;

Function overloading by number of parameters

SET SERVEROUTPUT ON

DECLARE
     FUNCTION getArea(v_radious NUMBER) RETURN NUMBER IS
     l_pi NUMBER := 3.14;
     BEGIN
          RETURN (l_pi *(v_radious ** 2));
     END;
     FUNCTION getArea(v_length NUMBER ,v_width NUMBER) RETURN NUMBER IS
     BEGIN
          RETURN(v_length * v_width);  
     END;
BEGIN
     DBMS_OUTPUT.PUT_LINE('Area of Circle is '|| getArea(6));
     DBMS_OUTPUT.PUT_LINE('Area of Rectangle is '|| getArea(6,4));
END;

FORALL with Non-Consecutive Index Values

CREATE TABLE valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2));
CREATE TABLE big_orders AS SELECT * FROM valid_orders WHERE 1 = 2;
CREATE TABLE rejected_orders AS SELECT * FROM valid_orders WHERE 2 = 3;

DECLARE
-- Make collection to hold the customer name and order amount
   TYPE cust_name_t IS TABLE OF valid_orders.cust_name%TYPE;
   TYPE order_amount_t is TABLE OF valid_orders.amount%TYPE;
  
   cust_name_tab       cust_name_t;
   order_amount_tab    order_amount_t;
-- Make another collection to point into cust_name_tab collection
   TYPE pointer_t IS TABLE OF PLS_INTEGER;
   rejected_order_tab  pointer_t := pointer_t();
   big_order_tab       pointer_t := pointer_t();   
  
   PROCEDURE setup_data is
   BEGIN
         cust_name_tab    :=  cust_name_t('Customer1','Customer2','Customer3','Customer4','Customer5');
         order_amount_tab :=  order_amount_t(500.0,0,1000,2000,NULL);
   END;
BEGIN
   setup_data();
   --  Point all big orders from main collection

   --  Delete invalid records from the main collection
   --  Point all invalid orders from main collection
   FOR i IN cust_name_tab.FIRST..cust_name_tab.LAST
   LOOP
     IF order_amount_tab(i) > 1000 THEN
         big_order_tab.EXTEND;
         big_order_tab(big_order_tab.LAST) := i;
      END IF;
     IF order_amount_tab(i) = 0 OR order_amount_tab(i) IS NULL THEN
         cust_name_tab.delete(i);
         order_amount_tab.delete(i);
         rejected_order_tab.EXTEND;
         rejected_order_tab(rejected_order_tab.LAST) := i;
      END IF; 
    
   END LOOP;  

   FORALL i IN INDICES OF cust_name_tab
       INSERT INTO valid_orders VALUES(cust_name_tab(i),order_amount_tab(i));
   setup_data();
  
   FORALL i in VALUES OF rejected_order_tab
         INSERT INTO rejected_orders VALUES(cust_name_tab(i),order_amount_tab(i));
 
   FORALL i in VALUES OF big_order_tab
         INSERT INTO big_orders VALUES(cust_name_tab(i),order_amount_tab(i));
    COMMIT;
END;

Nested Table

Nested Table - holds an arbitrary number of elements and use  sequential number as subscripts. it can be stored in a database column Collection Methods

•     EXISTS
•     COUNT
•     FIRST and LAST
•     PRIOR and NEXT
•     EXTEND
•     TRIM
•     DELETE

    DECLARE
        TYPE employee_table IS TABLE OF employees%ROWTYPE;
        employee_info  employee_table  := employee_table();

        CURSOR C1_employees_last_name is
            SELECT    employee_id,
                    RPAD(first_name,10)  first_name ,
                    RPAD(last_name,10) last_name,
                    TO_CHAR(salary,99999.99) salary
                FROM employees
                where rownum < 10;

          l_counter    BINARY_INTEGER  := 0;
    BEGIN
       
        FOR r_employees_last_name IN C1_employees_last_name
        LOOP
            employee_info.EXTEND;
            l_counter := l_counter + 1;
            employee_info(l_counter).employee_id        :=  r_employees_last_name.employee_id;
            employee_info(l_counter).salary            :=  r_employees_last_name.salary;
            employee_info(l_counter).first_name        :=  r_employees_last_name.first_name;
            employee_info(l_counter).last_name        :=  r_employees_last_name.last_name;
        END LOOP;

                -- Print Employee informations from nested table
        FOR l_counter in employee_info.FIRST.. employee_info.LAST
        LOOP
            DBMS_OUTPUT.PUT_LINE( employee_info(l_counter).employee_id||' '||
            employee_info(l_counter).first_name ||' '||
            employee_info(l_counter).last_name ||' '||
            employee_info(l_counter).salary);
        END LOOP;
       
        DBMS_OUTPUT.PUT_LINE ('No of Records ' ||' -  '||employee_info.COUNT);

        -- Change salary Locally
        FOR l_counter in employee_info.FIRST.. employee_info.LAST
        LOOP
            IF employee_info(l_counter).salary BETWEEN 0 AND 4999 THEN
                    employee_info(l_counter).salary := employee_info(l_counter).salary * 1.20;
            ELSIF employee_info(l_counter).salary BETWEEN 5000 AND 9999 THEN
                    employee_info(l_counter).salary := employee_info(l_counter).salary * 1.10;
            ELSE
                    employee_info(l_counter).salary := employee_info(l_counter).salary * 1.05;
            END IF;
           
            UPDATE employees SET salary = employee_info(l_counter).salary
                WHERE employee_id = employee_info(l_counter).employee_id;
        END LOOP;

        FOR l_counter in employee_info.FIRST.. employee_info.LAST
        LOOP
            DBMS_OUTPUT.PUT_LINE( employee_info(l_counter).employee_id||' '||
            employee_info(l_counter).first_name ||' '||
            employee_info(l_counter).last_name ||' '||
            employee_info(l_counter).salary);
        END LOOP;

        /* PRIOR and NEXT Demostration */
        FOR l_counter in employee_info.FIRST.. employee_info.LAST
        LOOP
            IF l_counter <> 1 THEN
                DBMS_OUTPUT.PUT_LINE( 'Prior emplyee id is     '||' <-- '
                ||employee_info(employee_info.PRIOR(l_counter)).employee_id);
            END IF;
            DBMS_OUTPUT.PUT_LINE( 'Current emplyee id is '||' --- ' ||employee_info(l_counter).employee_id);
            IF l_Counter <> employee_info.LAST THEN
                DBMS_OUTPUT.PUT_LINE( 'Next emplyee id is      '
                ||' --> ' ||employee_info(employee_info.NEXT(l_counter)).employee_id);
            END IF;
        END LOOP;
       
                -- Exception SUBSCRIPT_BEYOND_COUNT Demostration - Possible error 1
            DECLARE
                TYPE emp_email_table IS TABLE OF VARCHAR2(25);
                email_info emp_email_table := emp_email_table();
            BEGIN
                FOR l_counter in employee_info.FIRST.. employee_info.LAST
                LOOP
                    --email_info.EXTEND; -- Exception cause
                    SELECT email INTO email_info(l_counter)
                        FROM employees
                        WHERE EMPLOYEE_ID = employee_info(l_counter).employee_id;
                DBMS_OUTPUT.PUT_LINE( 'Empoyee No --> '||email_info(l_counter));
                END LOOP;
            EXCEPTION
                WHEN SUBSCRIPT_BEYOND_COUNT THEN
                    DBMS_OUTPUT.PUT_LINE ('Exception ****   SUBSCRIPT_BEYOND_COUNT exception raised');
            END;
       
            -- Exception SUBSCRIPT_BEYOND_COUNT Demostration - Possible error 2
            DECLARE
                TYPE emp_email_table IS TABLE OF EMPLOYEES.EMAIL%TYPE;
                email_info emp_email_table := emp_email_table();
            BEGIN
                FOR l_counter in employee_info.FIRST.. employee_info.LAST
                LOOP
                    IF NOT email_info.EXISTS(l_counter) THEN   
                        email_info.EXTEND;
                    END IF;
                    email_info(l_counter) := trunc(employee_info(l_counter).first_name)
                    ||'.'|| trunc(employee_info(l_counter).last_name)||'@yahoo.com';
                   
                DBMS_OUTPUT.PUT_LINE( 'Empoyee Email address --> '||email_info(l_counter));
                END LOOP;
            EXCEPTION
                WHEN VALUE_ERROR THEN
                    DBMS_OUTPUT.PUT_LINE ('Exception ****   Email address  is longer than 25 characters identified ');
            END;
           
            -- Exception COLLECTION_IS_NULL Demostration - Possible error 3
            DECLARE
                TYPE emp_email_table IS TABLE OF EMPLOYEES.EMAIL%TYPE;
                email_info emp_email_table;
                --:= emp_email_table();
            BEGIN
                FOR l_counter in employee_info.FIRST.. employee_info.LAST
                LOOP
                    email_info.EXTEND;
                    email_info(l_counter) := trunc(employee_info(l_counter).first_name)||'@yahoo.com';
                    DBMS_OUTPUT.PUT_LINE( 'Empoyee Email address --> '||email_info(l_counter));
                END LOOP;
            EXCEPTION
                WHEN COLLECTION_IS_NULL THEN
                    DBMS_OUTPUT.PUT_LINE ('Exception ****   COLLECTION_IS_NULL exception raised');
            END;
        ROLLBACK;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE (' OTHETS exception raised'|| SQLERRM);
    END;
    /
    198 Donald     OConnell   2600
199 Douglas    Grant      2600
200 Jennifer   Whalen     4400
201 Michael    Hartstein  13000
202 Pat        Fay        6000
203 Susan      Mavris     6500
204 Hermann    Baer       10000
205 Shelley    Higgins    12000
206 William    Gietz      8300
No of Records  -  9
198 Donald     OConnell   3120
199 Douglas    Grant      3120
200 Jennifer   Whalen     5280
201 Michael    Hartstein  13650
202 Pat        Fay        6600
203 Susan      Mavris     7150
204 Hermann    Baer       10500
205 Shelley    Higgins    12600
206 William    Gietz      9130
Current emplyee id is  --- 198
Next emplyee id is       --> 199
Prior emplyee id is      <-- 198
Current emplyee id is  --- 199
Next emplyee id is       --> 200
Prior emplyee id is      <-- 199
Current emplyee id is  --- 200
Next emplyee id is       --> 201
Prior emplyee id is      <-- 200
Current emplyee id is  --- 201
Next emplyee id is       --> 202
Prior emplyee id is      <-- 201
Current emplyee id is  --- 202
Next emplyee id is       --> 203
Prior emplyee id is      <-- 202
Current emplyee id is  --- 203
Next emplyee id is       --> 204
Prior emplyee id is      <-- 203
Current emplyee id is  --- 204
Next emplyee id is       --> 205
Prior emplyee id is      <-- 204
Current emplyee id is  --- 205
Next emplyee id is       --> 206
Prior emplyee id is      <-- 205
Current emplyee id is  --- 206
Exception ****   SUBSCRIPT_BEYOND_COUNT exception raised
Exception ****   Email address  is longer than 25 characters identified
Exception ****   COLLECTION_IS_NULL exception raised

PL/SQL procedure successfully completed.

Associated array (index-by table / PL-SQL table)

·         Unbounded set of key-value pairs. The subscript of the associated array can be integer or string. Subscripts are stored in sort order not creation order. For string subscript sort order is determined by NLS_SORT and NLS_COMP initialization parameters.

·         Associated array created empty but not null.

·         Does not need space and network operation

·         Stored data of the associated array cannot be manipulated by DML statement.

DECLARE

  -- Associative array index by string

  TYPE population  IS TABLE OF NUMBER INDEX BY VARCHAR2(25);

  -- Associative array variable 

  metro_population population;

 

  l_metro VARCHAR2(25);

 

BEGIN

   metro_population('Delhi')   := 18916890;

   metro_population('Mumbai')  := 21900967;

   metro_population('Chennai') := 7413779;

   metro_population('Kolkata') := 15644040;

  

   l_metro := metro_population.FIRST;

   WHILE l_metro IS NOT NULL

   LOOP

     DBMS_OUTPUT.PUT_LINE ('Population of '||

     l_metro||' is  '||  metro_population(l_metro));

     l_metro := metro_population.NEXT(l_metro);

   END LOOP;

END;

/

Population of Chennai is  7413779

Population of Delhi is  18916890

Population of Kolkata is  15644040

Population of Mumbai is  21900967

Collection

Internal components of the collection are called elements. Each element of the collection can be access by using unique subscripts. To create a collection type it is necessary to define a collection type and then create a variable of that type.

There are three types of collections

1.       Associated Array ( index-by table/ PL/SQL table ) à An unbounded collection

2.       VARRAY ( variable size array) à Bounded collection

3.       Nested table à Unbounded collection