Collections

SET SERVEROUTPUT ON;

DECLARE
  -- Define a varray with a maximum of 3 rows.
TYPE integer_varray IS VARRAY(3) OF INTEGER;
-- Declare the varray with null values.
varray_integer INTEGER_VARRAY := integer_varray(NULL,NULL,NULL);
BEGIN
  -- Print initialized null values.
  dbms_output.put_line('Varray initialized as nulls.');
  dbms_output.put_line('––––––––––––––--------------');
  FOR i IN 1..3
  LOOP
    dbms_output.put ('Integer Varray ['||i||'] ');
    dbms_output.put_line('['||varray_integer(i)||']');
  END LOOP;
  -- Assign values to subscripted members of the varray.
  varray_integer(1) := 11;
  varray_integer(2) := 12;
  varray_integer(3) := 13;
  -- Print initialized null values.
  DBMS_OUTPUT.PUT (CHR(10));
  -- Visual line break. dbms_output.put_line('Varray initialized as values.');
  dbms_output.put_line('––-––––––––––––--------------');
  FOR i IN 1..3
  LOOP
    dbms_output.put_line('Integer Varray ['||i||'] '|| '['||varray_integer(i)||']');
  END LOOP;
END;
/


SET SERVEROUTPUT ON;
DECLARE
  -- Define a varray of integer with 3 rows.
TYPE INTEGER_VARRAY IS VARRAY(3) OF INTEGER;
-- Declare an array initialized as a no-element collection.
varray_integer INTEGER_VARRAY := integer_varray();
BEGIN
  -- Allocate space as you increment the index.
  FOR i IN 1..3
  LOOP
    varray_integer.EXTEND;       -- Allocates space in the collection.
    varray_integer(i) := 10 + i; -- Assigns a value to the indexed value.
  END LOOP;
  -- Print initialized array.
  dbms_output.put_line('Varray initialized as values.');
  dbms_output.put_line('––––--------------––––––––––-');
  FOR i IN 1..3
  LOOP
    dbms_output.put ('Integer Varray ['||i||'] ');
    dbms_output.put_line('['||varray_integer(i)||']');
  END LOOP;
END;
/

--CREATE OR REPLACE TYPE TYPE_NAME AS {VARRAY | VARYING ARRAY} (SIZE_LIMIT) OF element_type [ NOT NULL ];

-- This is in create_varray3.sql on the publisher's web site.

CREATE OR REPLACE TYPE integer_varray AS VARRAY(3) OF INTEGER;
/

set serveroutput on;
DECLARE
  varray_integer INTEGER_VARRAY := integer_varray(NULL,NULL,NULL);
BEGIN
  -- Assign values to replace the null values.
  FOR i IN 1..3
  LOOP
    varray_integer(i) := 10 + i;
  END LOOP;
  -- Print the initialized values.
  dbms_output.put_line('Varray initialized as values.');
  dbms_output.put_line('––––––––––––––---------------');
  FOR i IN 1..3
  LOOP
    dbms_output.put ('Integer Varray ['||i||'] ');
    dbms_output.put_line('['||varray_integer(i)||']');
  END LOOP;
END;
/


CREATE OR REPLACE TYPE integer_varray AS VARRAY(100) OF INTEGER NOT NULL;
/

set serveroutput on;
DECLARE
varray_integer INTEGER_VARRAY := integer_varray();
BEGIN
FOR I IN 1..VARRAY_INTEGER.LIMIT
LOOP
varray_integer.EXTEND;
END LOOP;
dbms_output.put ('Integer Varray Initialized ');
dbms_output.put_line('['||varray_integer.COUNT||']');
END;
/

----------------------------------------------------------


CREATE OR REPLACE TYPE strings AS VARRAY(3) OF VARCHAR2(30 CHAR);
/


CREATE TABLE individuals
( individual_id INTEGER NOT NULL
, first_name VARCHAR2(30 CHAR) NOT NULL
, middle_name VARCHAR2(30 CHAR)
, last_name VARCHAR2(30 CHAR) NOT NULL
, title VARCHAR2(10 CHAR)
, CONSTRAINT INDIV_PK PRIMARY KEY(INDIVIDUAL_ID)
);

CREATE TABLE addresses
( address_id INTEGER NOT NULL
, INDIVIDUAL_ID INTEGER NOT NULL
, street_address STRINGS NOT NULL  -- use of collection datatype
, city VARCHAR2(20 CHAR) NOT NULL
, state VARCHAR2(20 CHAR) NOT NULL
, postal_code VARCHAR2(20 CHAR) NOT NULL
, country_code VARCHAR2(10 CHAR) NOT NULL
, CONSTRAINT addr_pk PRIMARY KEY(address_id)
, CONSTRAINT ADDR_INDIV_FK FOREIGN KEY(INDIVIDUAL_ID)
REFERENCES INDIVIDUALS (INDIVIDUAL_ID)
);

select * from INDIVIDUALS;

select * from ADDRESSES;

-- This is in varray_dml1.sql on the publisher's web site.
INSERT INTO individuals VALUES
( 1, 'John', 'Sidney', 'McCain', 'Mr.');


INSERT INTO addresses VALUES
( 1
, 1
, strings
('Office of Senator McCain','450 West Paseo Redondo','Suite 200')
,'Tucson'
,'AZ'
,'85701'
,'USA'
);

SELECT STREET_ADDRESS FROM addresses;


-- Create a PL/SQL table datatype.
CREATE OR REPLACE TYPE varray_nested_table IS TABLE OF VARCHAR2(30 CHAR);

-- Use SQL*Plus to format the output.
COL COLUMN_VALUE FORMAT A30 -- Print formatted elements from aggregate table.
;

--select statement
SELECT nested.column_value
FROM addresses a
, TABLE(CAST(A.STREET_ADDRESS AS VARRAY_NESTED_TABLE)) NESTED
WHERE address_id = 1;

--update statement
UPDATE addresses
SET street_address =
strings('Office of Senator McCain'
,'2400 E. Arizona Biltmore Cir.'
,'Suite 1150')
WHERE address_id = 1;


-- This is in varray_dml3.sql on the publisher's web site.
DECLARE
TYPE address_type
IS
  RECORD
  (
    address_id    INTEGER ,
    individual_id INTEGER ,
    street_address STRINGS ,
    city         VARCHAR2(20 CHAR) ,
    state        VARCHAR2(20 CHAR) ,
    postal_code  VARCHAR2(20 CHAR) ,
    COUNTRY_CODE VARCHAR2(10 CHAR) );
  address ADDRESS_TYPE;
  CURSOR get_street_address (address_id_in INTEGER)
  IS
    SELECT * FROM addresses WHERE ADDRESS_ID = ADDRESS_ID_IN;
BEGIN
  -- Access the cursor.
  OPEN get_street_address(1);
  FETCH get_street_address INTO address;
  CLOSE GET_STREET_ADDRESS;
  -- Reset the first element of the varray type variable.
  ADDRESS.STREET_ADDRESS(1) := 'Office of Senator John McCain';
  -- Update the entire varray column value.
  UPDATE addresses
  SET street_address = address.street_address
  WHERE address_id   = 1;
END;
/

-- This is in varray_dml4.sql on the publisher's web site.
INSERT INTO individuals VALUES (2, 'John', 'Sidney', 'McCain', 'Mr.');

INSERT INTO addresses VALUES
( 2
, 2
, strings('Office of Senator Kennedy')
, 'Boston'
, 'MA'
, '02203'
, 'USA'
);



-- This is in varray_dml4.sql on the publisher's web site.
DECLARE
TYPE address_type IS RECORD
( address_id INTEGER
, individual_id INTEGER
, street_address STRINGS
, city VARCHAR2(20 CHAR)
, state VARCHAR2(20 CHAR)
, postal_code VARCHAR2(20 CHAR)
, COUNTRY_CODE VARCHAR2(10 CHAR)
);

ADDRESS ADDRESS_TYPE;
-- Define a cursor to return the %ROWTYPE value.

CURSOR get_street_address
(address_id_in INTEGER) IS
SELECT *
FROM addresses
WHERE address_id = address_id_in;
BEGIN
-- Access the cursor.
OPEN get_street_address(2);
FETCH get_street_address INTO address;
CLOSE get_street_address;
-- Add elements.
address.street_address.EXTEND(2);
address.street_address(2) := 'JFK Building';
address.street_address(3) := 'Suite 2400';
-- Update the varray column value.
UPDATE addresses
SET street_address = address.street_address
WHERE address_id = 2;
END;
/