Use of Varray & Nested Table in Oracle SQL

1. Code For the Storing Varray Example:

Step-01 Create type to define RECORD as Object in SQL  for holding 2 fields data.

CREATE OR REPLACE TYPE t_phone_number AS OBJECT(p_type VARCHAR2(10), p_number VARCHAR2(50));

1. CREATE OR REPLACE TYPE v_phone_numbers AS VARRAY(3) OF t_phone_number;

1. 
   

1. Step-03 Use Varray Data type in table.

1. CREATE TABLE emps_with_phones(employee_id NUMBER,

1. first_name VARCHAR2(50),

1. last_name VARCHAR2(50),

1. phone_number v_phone_numbers);

1. 
   

1. Step-04 Verify Table.

1. SELECT * FROM emps_with_phones;

1. 
   

1. Step-05 Add data to Varray

1. INSERT INTO emps_with_phones VALUES(10,'Alex','Brown',

1. v_phone_numbers(t_phone_number('HOME','111.111.1111'),

1. t_phone_number('WORK','222.222.2222'),

1. t_phone_number('MOBILE','333.333.3333')));

1. INSERT INTO emps_with_phones VALUES(11,'Bob','Green',

1. v_phone_numbers(t_phone_number('HOME','000.000.000'),

1. t_phone_number('WORK','444.444.4444')));

1. Step-06 Query the data table to verify Varray.

1. 
   

1. SELECT e.first_name,

1. last_name,

1. p.p_type,

1. p.p_number

1. FROM emps_with_phones e, table(e.phone_number) p;

Step-02 Create type as Varray to store as Object.

1.  

1. Code For the Storing Nested Table Example

1. Step-01 Define Type as table.

1. CREATE OR REPLACE TYPE n_phone_numbers AS TABLE OF t_phone_number;

1. 
   

1. Step-02 Use Table Data Type in table column.

1. CREATE TABLE emps_with_phones2(employee_id NUMBER,

1. first_name VARCHAR2(50),

1. last_name VARCHAR2(50),

1. phone_number n_phone_numbers)

1. NESTED TABLE phone_number STORE AS phone_numbers_table;

1. 
   

1. Step-03 Verify Table.

1. SELECT * FROM emps_with_phones2;

1. 
   

1. Step-04 Add data to table using Nested Table.

1. INSERT INTO emps_with_phones2

1. VALUES(10,'Alex','Brown',n_phone_numbers(t_phone_number('HOME','111.111.1111'),

1. t_phone_number('WORK','222.222.2222'),

1. t_phone_number('MOBILE','333.333.3333'))

1. );

1. INSERT INTO emps_with_phones2

1. VALUES(11,'Bob','Green',n_phone_numbers(t_phone_number('HOME','000.000.000'),

1. t_phone_number('WORK','444.444.4444'))

1. );

1. Step-05 Verify data from table.

1. SELECT e.first_name, last_name, p.p_type, p.p_number

1. FROM emps_with_phones2 e, table(e.phone_number) p;

1.  

1. Step-06 Add and Modify data to table using Nested Table.

1. INSERT INTO emps_with_phones2

1. VALUES(11,'Bob','Green',n_phone_numbers(t_phone_number('HOME','000.000.000'),

1. t_phone_number('WORK','444.444.4444'),

1. t_phone_number('WORK2','444.444.4444'),

1. t_phone_number('WORK3','444.444.4444'),

1. t_phone_number('WORK4','444.444.4444'),

1. t_phone_number('WORK5','444.444.4444'))

1. );

1. SELECT * FROM emps_with_phones2;

1.  

1. UPDATE emps_with_phones2

1. SET phone_number = n_phone_numbers(t_phone_number('HOME','000.000.000'),

1. t_phone_number('WORK','444.444.4444'),

1. t_phone_number('WORK2','444.444.4444'),

1. t_phone_number('WORK3','444.444.4444'),

1. t_phone_number('WORK4','444.444.4444'),

1. t_phone_number('WORK5','444.444.4444'))

1. WHERE employee_id = 11;

1.  

1. Step-07 Add and Modify data to table using anonymous block:

1. 
   

1. DECLARE

1. p_num n_phone_numbers;

1. BEGIN

1. SELECT phone_number

1. INTO p_num

1. FROM emps_with_phones2

1.  WHERE employee_id = 10;

1. p_num.extend;

1. p_num(5) := t_phone_number('FAX','999.99.9999');

1. 
   

1. UPDATE emps_with_phones2

1. SET phone_number = p_num

1. WHERE employee_id = 10;

1. END;

Use of Local Function | Along with Varray Data type

Here's an example of a PL/SQL procedure that takes an employee_id as input from the employees table, calculates a 10% tax on each employee's salary using a local function, and returns the before tax and after tax amounts using a VARRAY type:

Example:

CREATE OR REPLACE PROCEDURE CalculateTax(p_employee_id IN employees.employee_id%TYPE)

AS

  v_before_tax NUMBER;

  v_after_tax NUMBER;

  --use of local data type as varray--

  TYPE AmountsType is VARRAY(2) OF NUMBER;

  --use of local function to return type as varray--

      FUNCTION CalculateTaxAmount(p_salary IN NUMBER) RETURN AmountsType

      IS

            v_amounts AmountsType := AmountsType();

      BEGIN

            v_amounts.EXTEND(2);

            v_amounts(1) := p_salary;

            v_amounts(2) := p_salary * 0.9; -- 10% tax

          RETURN v_amounts;

      END CalculateTaxAmount;

 BEGIN

  SELECT salary INTO v_before_tax FROM employees 

  WHERE employee_id = p_employee_id;

--calling function inside procedure--

  v_before_tax:=CalculateTaxAmount(v_before_tax)(1);

  v_after_tax := CalculateTaxAmount(v_before_tax)(2); -- Get the after-tax amount

  DBMS_OUTPUT.PUT_LINE('Before Tax Amount: ' || v_before_tax);

  DBMS_OUTPUT.PUT_LINE('After Tax Amount: ' || v_after_tax);

END CalculateTax;

--call the procedure:

begin

  CalculateTax(100); --employee_id=100 and salary is 24000

end;

--output:

Before Tax Amount: 24000

After Tax Amount: 21600