Sem título

Página 1

Working with Collections inside SQL

when collection declared at schema level then it refers to SQL layer.

only nested and varrays.

TABLE : collection --> relational table

Multiset : relational table--> collection

TABLE:-

CREATE OR REPLACE TYPE name_t AS TABLE OF VARCHAR2(100);

DECLARE

1_NAMES name_t ;

BEGIN

FOR VAR IN (SELECT COLUMN_VALUE ||'(FROM plSQL)' LAST_NAME FROM TABLE(1_NAMES) UNION SELECT LAST_NAME FROM EMPLOYEES)

LOOP

DBMS_OUTPUT.PUT_LINE(REC.LAST_NAME);

END LOOP;

--- AT SCHEMA LEVEL ONLY

iNSTEAD OF

{ DECLARE TYPE employee_type IS RECORD -- AT LOCAL LEVEL. (employee_id number(5), employee_first_name varchar2(25), employee_last_name employee.last_name%type, employee_dept employee.dept%type); employee_salary employee.salary%type; employee_rec employee_type);

DECLARE employee_rec employee%ROWTYPE; }

2.

CREATE OR REPLACE TYPE name_t AS TABLE OF VARCHAR2(100);

DECLARE

1_NAMES name_t ;

BEGIN

UPDATE TABLE (SELECT 1_names from employees where surname = 'sinha') set column_value =' singh '

where colunm_value = 'sinha';

Página 2

MULTISET

OPPOSITE OF TABLE

CONVERTS A SET OF TABLE ,VIEW,QUERY INTO VARRAY OR NESTED TABLE.

DECLARE

CURSOR BIRD_CURS IS

SELECT b.genus,b.spices,cast(multiset(select bh.country from birds_habitats bh where bh.genus=b.genus) as country_tab_t) from birds;

bird_row bird_curs%rowtype;

begin

open birds_curs;

fetch birds into bird_row;

end;