Complex oracle Data Types (2)

Oracle Data complex type (2) 4. the nested oracle table www.2cto.com is similar to the previous index table. There are some minor differences (the subscript of the nested table starts from 1). Java code declare type sal_table is table of emp. sal % type; ----- define a nested table type. Here there is no index by v_sal_table sal_table; begin v_sal_table: = sal_table (2345,234,656 ); ---- initialize the Defined variables of the sal_table type (the nested table variables must be initialized; otherwise, an error is reported); dbms_output.put_line (v_sal_table (1 )); [B] ----- the subscript starts from 1 [/B] end; 5. use the Java code www.2cto.com to create a nested table type. Of course, this table type can enable multiple columns, here we use a single column to demonstrate the create type booklist is table of varchar (20); --- after the execution, oracle generates a type-create a table Java code create table bookClass (id varchar (20), name varchar (20 ), v_booklist booklist --- the type is the nested table type created earlier) nested table v_booklist store as booklist_tab; -- specifies the nested table column. booklist_tab can be any name, this name is the name of the nested table www.2cto.com --- insert a record Java code insert into bookclass values ('2', 'art class', booklist ('sketch ', 'oil color ', 'computer plotting ') ---- traverse and display the records in the nested table in Java code declare booklist_tab booklist; begin select v_booklist into booklist_tab from bookclass where rownum = 1; for I in booklist_tab.first .. booklist_tab.last loop dbms_output.put_line (booklist_tab (I); end loop; end; ---- modify the nested table Java code update bookclass B set B. v_booklist = booklist ('C #','.. net ', 'c ++') where rownum = '1'; 6. variable array usage method Java code declare type ename is varray (5) of varchar (20); --- defines a variable array whose maximum length is 5 v_ename: = ename ('sdfs', 'fsdf ', 'sfdsdf'); ------ initialize three elements. Several elements have been initialized here, and only a few select in select t can be used below. ename into v_ename (3) from emp t where rownum = 1; dbms_output.put_line (v_ename (3); end; 7. java code declare type ename is varray (5) of varchar (20); v_ename ename: = ename ('sdfs', 'fsdf ', 'sfdsdf '); begin if v_ename.exists (1) then --- exists (n) check whether the nth element has dbms_output.put_line ('have '); dbms_output.put_line (v_ename.count ); --- count the total number of elements dbms_output.put_line (v_ename.limit); --- limit calculates the maximum number of elements dbms_output.put_line (v_ename.prior (2); --- prior (n) returns the subscript dbms_output.put_line (v_ename.next (2) of the first element of n. --- next (n) returns the subscript www.2cto.com [B]/************************** of the last element of the nth element. * ****************************** these methods include variable arrays, indexes and nested tables are supported. In addition, indexes and nested tables also support the delete, delete (n), delete (n, m) methods )), note that the subscript of undeleted elements does not change after the delete operation, delete is to leave the meta-quality to be deleted blank ******************************* * **********************/[/B] dbms_output.put_line (v_ename (3 )); else dbms_output.put_line ('no'); end if; end;