PL/SQL obsługuje 3 typy kolekcji

  1. ASSOCIATIVE ARRAY
  2. VARRAY
  3. NESTED TABLE

DECLARE TYPE t_employees_type IS TABLE OF employees%rowtype; t_employees t_employees_type; BEGIN SELECT * BULK COLLECT INTO t_employees FROM employees ;

FOR i IN t_employees.FIRST..t_employees.LAST LOOP dbms_output.put_line (t_employees (i).last_name); – dbms_output.put_line (t_employees (i).); END LOOP; END; /

DECLARE CURSOR no_ids_cur IS SELECT last_name, salary FROM employees;

l_employee no_ids_cur%ROWTYPE; BEGIN OPEN no_ids_cur; FETCH no_ids_cur INTO l_employee; CLOSE no_ids_cur;

       dbms_output.put_line (l_employee.last_name);

END;

/

DECLARE TYPE at_employees_type IS TABLE OF employees%rowtype INDEX BY PLS_INTEGER; TYPE nt_employees_type IS TABLE OF employees%rowtype; TYPE vt_employees_type IS VARRAY(10) OF employees%rowtype;

at_employees at_employees_type; nt_employees nt_employees_type; vt_employees vt_employees_type;

CURSOR c_employees IS SELECT * FROM employees; BEGIN

SELECT * BULK COLLECT INTO at_employees FROM employees;

SELECT * BULK COLLECT INTO nt_employees FROM employees;

SELECT * BULK COLLECT INTO vt_employees FROM employees;
END;

DECLARE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100); happyfamily list_of_names_t := list_of_names_t (); children list_of_names_t := list_of_names_t (); parents list_of_names_t := list_of_names_t (); BEGIN happyfamily.EXTEND (4); happyfamily (1) := ‘Veva’; happyfamily (2) := ‘Chris’; happyfamily (3) := ‘Eli’; happyfamily (4) := ‘Steven’;

  children.EXTEND;
  children (children.LAST) := ‘Chris’;
  children.EXTEND;
  children (children.LAST) := ‘Eli’;
 
  parents := happyfamily MULTISET EXCEPT children;
 
  FOR l_row IN 1 .. parents.COUNT
  LOOP
     DBMS_OUTPUT.put_line (parents (l_row));
  END LOOP;    END;

create or replace PROCEDURE add_meeting_attendees (in_meeting_id IN meeting_attendees.meeting_id%TYPE ,in_employee_ids IN VARCHAR2) IS co_emp_delimiter CONSTANT CHAR(1) := ‘:’; co_lower_bound CONSTANT SIMPLE_INTEGER := 1; TYPE t_employee_ids_type IS TABLE OF employees.id%TYPE; l_employee_ids t_employee_ids_type; BEGIN SELECT regexp_substr (in_employee_ids ,’[^’ || co_emp_delimiter || ‘]+’ ,1 ,level ) AS employee_ids BULK COLLECT INTO l_employee_ids FROM dual CONNECT BY level <= regexp_count (in_employee_ids ,’[^’ || co_emp_delimiter || ‘]+’); «insert_employees» FORALL i IN co_lower_bound..l_employee_ids.COUNT() INSERT /*+ ignore_row_on_dupkey_index(meetings_attendees, meat_uk) */ INTO meetings_attendees (meeting_id ,employee_id ,app_session)
VALUES (in_meeting_id ,l_employee_ids(i) ,v(‘APP_SESSION’)); END;

DECLARE TYPE t_employees_type IS TABLE OF employees%rowtype; t_employees t_employees_type; BEGIN SELECT * BULK COLLECT INTO t_employees FROM employees ;

FOR i IN t_employees.FIRST..t_employees.LAST LOOP dbms_output.put_line (t_employees (i).last_name); – dbms_output.put_line (t_employees (i).); END LOOP; END; /