Oracle PL/SQL/PL SQL/varray
Содержание
- 1 assignments to varray elements, and the ORA-6532 and ORA-6533 errors.
- 2 Assigns a value to the indexed value.
- 3 Assign values to subscripted members of the varray.
- 4 Associative array example
- 5 Avoid traversing an associative array where no elements are initialized.
- 6 Check the size of a varray
- 7 Compare two varray variables
- 8 Constructs a two varrays and one nested table type in the database
- 9 Control varray index with if statement
- 10 Create a varray based on user defined type
- 11 Create type prices with a varray of number
- 12 Creating and Using VARRAYs
- 13 Declare an array initialized as a no-element collection.
- 14 Declare the varray with null values.
- 15 Declaring a VARRAY of scalar variable
- 16 Define a varray of integer with 3 rows
- 17 Define a varray of twelve strings.
- 18 Define a varray with a null element constructor and extends it one element at a time.
- 19 Define a varray with a null element constructor and extends it one element at a time by a formula
- 20 Define a varray with a three element constructor of null elements.
- 21 Define a varray with a three element constructor of null elements and attempt to populate it beyond three elements.
- 22 exceeded maximum VARRAY limit
- 23 Extend with null element to the maximum limit size.
- 24 Hard code value in varray and use for loop to insert them to a table
- 25 Initialization and assignment with a numeric index value to an associative array.
- 26 Initialization and assignment with a unique string index value to an associative array.
- 27 Initialize the array and create two entries using the constructor
- 28 legal and illegal varray assignments.
- 29 Nested varray
- 30 ORA-06533: Subscript beyond count
- 31 Query a stored varray.
- 32 Reference elements in varray
- 33 Reference varray.count in for loop
- 34 Store 12 months in varray of string
- 35 Store pre-defined constants in VARRAY
- 36 Subscript index values begin at 1, not 0
- 37 Table of numbers and varray of numbers
- 38 TYPE Strings IS VARRAY(5) OF VARCHAR2(10)
- 39 Use table() function to display varray type column
- 40 Varray constructors.
- 41 VARRAY of VARCHAR2 and Varray of number
assignments to varray elements, and the ORA-6532 and ORA-6533 errors.
<source lang="sql">
SQL> SQL> SQL> DECLARE
2 TYPE Strings IS VARRAY(5) OF VARCHAR2(10); 3 v_List Strings := 4 Strings("One", "Two", "Three", "Four"); 5 BEGIN 6 v_List(2) := "TWO"; 7 8 v_List.EXTEND; 9 v_List(5) := "Five"; 10 11 -- Attempt to extend the varray to 6 elements. This will 12 -- raise ORA-6532 . 13 v_list.EXTEND; 14 END; 15 /
DECLARE
ERROR at line 1: ORA-06532: Subscript outside of limit ORA-06512: at line 13
SQL> SQL> SQL> SQL>
</source>
Assigns a value to the indexed value.
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE integer_varray IS VARRAY(3) OF INTEGER; 3 intArray INTEGER_VARRAY := integer_varray(); 4 BEGIN 5 FOR i IN 1..3 LOOP 6 intArray.EXTEND; 7 intArray(i) := 10 + i; 8 END LOOP; 9 10 FOR i IN 1..3 LOOP 11 dbms_output.put ("Integer Varray :"||i); 12 dbms_output.put_line(":"||intArray(i)); 13 END LOOP; 14 END; 15 /
Integer Varray :1:11 Integer Varray :2:12 Integer Varray :3:13 PL/SQL procedure successfully completed.
</source>
Assign values to subscripted members of the varray.
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE integer_varray IS VARRAY(3) OF INTEGER; 3 4 intArray INTEGER_VARRAY := integer_varray(NULL,NULL,NULL); 5 BEGIN 6 intArray(1) := 11; 7 intArray(2) := 12; 8 intArray(3) := 13; 9 10 dbms_output.put_line("Varray initialized as values."); 11 FOR i IN 1..3 LOOP 12 dbms_output.put_line("Integer Varray :"||i||":" ||intArray(i)); 13 END LOOP; 14 END; 15 /
Varray initialized as values. Integer Varray :1:11 Integer Varray :2:12 Integer Varray :3:13 PL/SQL procedure successfully completed. SQL> SQL> SQL>
</source>
Associative array example
<source lang="sql">
SQL> CREATE OR REPLACE PROCEDURE myProcedure AS
2 TYPE numTable IS TABLE OF NUMBER INDEX BY VARCHAR2(15); 3 carray numTable; 4 BEGIN 5 carray("J") := 1; 6 carray("S") := 2; 7 carray("F") := 3; 8 carray("C") := 4; 9 10 DBMS_OUTPUT.PUT_LINE("carray[""J""] = " || carray("J")); 11 DBMS_OUTPUT.PUT_LINE("carray[""S""] = " || carray("S")); 12 DBMS_OUTPUT.PUT_LINE("carray[""F""] = " || carray("F")); 13 DBMS_OUTPUT.PUT_LINE("carray[""C""] = " || carray("C")); 14 END myProcedure; 15 /
Procedure created. SQL>
</source>
Avoid traversing an associative array where no elements are initialized.
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE
2 3 4 TYPE charArrayType IS TABLE OF VARCHAR2(5 CHAR) INDEX BY BINARY_INTEGER; 5 6 7 charArray charArrayType; 8 9 BEGIN 10 11 IF charArray.COUNT <> 0 THEN 12 13 DBMS_OUTPUT.PUT_LINE(charArray(1)); 14 ELSE 15 16 DBMS_OUTPUT.PUT_LINE("empty."); 17 18 END IF; 19 20 END; 21 /
empty. PL/SQL procedure successfully completed. SQL> SQL>
</source>
Check the size of a varray
<source lang="sql">
SQL> SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;
2 /
Type created. SQL> SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS
2 returnValue VARCHAR2(2000); 3 BEGIN 4 FOR i IN set_in.FIRST..set_in.LAST LOOP 5 IF i = set_in.FIRST THEN 6 IF set_in.COUNT = 1 THEN 7 returnValue := set_in(i); 8 ELSE 9 returnValue := ":"||set_in(i); 10 END IF; 11 ELSIF i <> set_in.LAST THEN 12 returnValue := returnValue||", "||set_in(i); 13 ELSE 14 returnValue := returnValue||", "||set_in(i)||")"; 15 END IF; 16 END LOOP; 17 RETURN returnValue; 18 END format_list; 19 /
Function created. SQL>
</source>
Compare two varray variables
<source lang="sql">
SQL> CREATE or replace TYPE addressType AS OBJECT (
2 street VARCHAR2(15), 3 city VARCHAR2(15), 4 state CHAR(2), 5 zip VARCHAR2(5) 6 ); 7 /
Type created. SQL> SQL> CREATE or replace TYPE addressTypeVArray AS VARRAY(2) OF VARCHAR2(50);
2 /
SQL> SQL> CREATE or replace TYPE addressTypeNestedTable AS TABLE OF addressType;
2 /
Type created. SQL> SQL> -- SQL> CREATE GLOBAL TEMPORARY TABLE empTempTable (
2 id INTEGER PRIMARY KEY, 3 fname VARCHAR2(10), 4 lname VARCHAR2(10), 5 addresses addressTypeVArray 6 );
Table created. SQL> SQL> CREATE TABLE empTable (
2 id INTEGER PRIMARY KEY, 3 fname VARCHAR2(10), 4 lname VARCHAR2(10), 5 addresses addressTypeNestedTable 6 ) 7 NESTED TABLE 8 addresses 9 STORE AS 10 nested_addresses2 TABLESPACE users;
Table created. SQL> SQL> SQL> -- equal/not equal example SQL> CREATE OR REPLACE PROCEDURE equal_example AS
2 TYPE charTable IS TABLE OF VARCHAR2(10); 3 emp1 charTable; 4 emp2 charTable; 5 emp3 charTable; 6 result BOOLEAN; 7 BEGIN 8 emp1 := charTable("A", "B", "C"); 9 emp2 := charTable("A", "B", "C"); 10 emp3 := charTable("B", "C", "D"); 11 12 result := emp1 = emp2; 13 IF result THEN 14 DBMS_OUTPUT.PUT_LINE("emp1 equal to emp2"); 15 END IF; 16 17 END equal_example; 18 /
Procedure created. SQL> SQL> drop type addressType force; Type dropped. SQL> drop type addressTypeVArray force; Type dropped. SQL> drop TYPE addressTypeNestedTable force; Type dropped. SQL> drop TABLE empTable; Table dropped.
</source>
Constructs a two varrays and one nested table type in the database
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> SQL> CREATE OR REPLACE TYPE unitType
2 AS VARRAY(13) OF VARCHAR2(5 CHAR); 3 /
Type created. SQL> SQL> SQL> CREATE OR REPLACE TYPE categoryType
2 AS VARRAY(4) OF VARCHAR2(8 CHAR); 3 /
Type created. SQL> SQL> SQL> CREATE OR REPLACE TYPE charArrayType
2 AS TABLE OF VARCHAR2(17 CHAR); 3 /
Type created. SQL> SQL> DECLARE
2 3 counter INTEGER := 0; 4 5 6 suits categoryType :=categoryType("A","B","C","D"); 7 8 9 units unitType :=unitType("Ace","Two","Three","Four","Five","Six","Seven"); 10 11 12 charArray charArrayType := charArrayType(); 13 14 BEGIN 15 FOR i IN 1..suits.COUNT LOOP 16 FOR j IN 1..units.COUNT LOOP 17 18 counter := counter + 1; 19 charArray.EXTEND; 20 21 22 charArray(counter) := units(j)||" of "||suits(i); 23 24 END LOOP; 25 26 END LOOP; 27 28 FOR i IN 1..counter LOOP 29 dbms_output.put_line("["||charArray(i)||"]"); 30 END LOOP; 31 32 END; 33 /
[Ace of A] [Two of A] [Three of A] [Four of A] [Five of A] [Six of A] [Seven of A] [Ace of B] [Two of B] [Three of B] [Four of B] [Five of B] [Six of B] [Seven of B] [Ace of C] [Two of C] [Three of C] [Four of C] [Five of C] [Six of C] [Seven of C] [Ace of D] [Two of D] [Three of D] [Four of D] [Five of D] [Six of D] [Seven of D] PL/SQL procedure successfully completed. SQL>
</source>
Control varray index with if statement
<source lang="sql">
SQL> SQL> create table product(
2 product_id integer primary key 3 ,price number(7,2) 4 ,description varchar2(75) 5 ,onhand number(5,0) 6 ,reorder number(5,0) 7 ,supplier_no integer 8 );
Table created. SQL> -- product Table Inserts: SQL> insert into product(product_id, price, description, onhand, reorder)values (1,2.50,"PC",100,20); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (2,23.00,"Disk",null,null); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (3,null,"Monitor",null,null); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (4,1.50,"Mouse",50,10); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (5,10.50,"Vase",100,20); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (6,45.00,"Keyboard",null,null); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (7,19.99,"Cable",3,5); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (8,4.50,"Notebook",null,null); 1 row created. SQL> SQL> CREATE SEQUENCE Product_seq
2 INCREMENT BY 1 3 START WITH 9 4 NOMAXVALUE 5 NOCYCLE;
Sequence created. SQL> SQL> CREATE OR REPLACE PROCEDURE p_add_prod (v_supplier IN number, v_ctr IN number)
2 AS 3 i number := 1; 4 idIndex number := 1; 5 newOrder product.reorder%TYPE; 6 7 TYPE names IS VARRAY(10) OF VARCHAR2(75); 8 v_names names := names("A","B","C","D","E","F","G","H","I","J"); 9 10 TYPE Prod_Prices IS VARRAY(10) OF NUMBER(7,2); 11 v_prices Prod_prices := Prod_prices(2,2.25,3,4.2,6,12.4,11.7,9.25,5,7.5); 12 13 TYPE Prod_Onhand IS VARRAY(10) OF NUMBER; 14 v_onhand Prod_Onhand := Prod_Onhand(70,20,10,40,30,50,60,80,90,55); 15 16 begin 17 18 WHILE i <= v_ctr LOOP 19 IF idIndex > 10 THEN 20 idIndex := 1; 21 END IF; 22 23 IF v_onhand(idIndex) >= 30 THEN 24 newOrder := v_onhand(idIndex) - 1; 25 ELSE 26 newOrder := v_onhand(idIndex) + 5; 27 END IF; 28 29 INSERT INTO product (PRODUCT_ID, PRICE, DESCRIPTION, ONHAND, REORDER, SUPPLIER_NO) 30 VALUES (product_seq.NEXTVAL, v_prices(idIndex), v_names(idIndex), v_onhand(idIndex), newOrder, v_supplier); 31 32 i := i + 1 ; 33 idIndex := idIndex + 1; 34 35 END LOOP; 36 end; 37 /
Procedure created. SQL> SQL> SQL> drop table product; Table dropped. SQL> drop sequence product_seq; Sequence dropped. SQL> SQL>
</source>
Create a varray based on user defined type
<source lang="sql">
SQL> SQL> SQL> create type employee_type as object (
2 employee_id number, 3 first_name varchar2(30), 4 last_name varchar2(30) 5 ); 6 /
Type created. SQL> SQL> create type employee_list_type as varray(50) of employee_type
2 /
Type created. SQL> SQL> SQL> create table departments (
2 department_id number, 3 department_name varchar2(30), 4 manager employee_type, 5 employees employee_list_type ) 6 /
Table created. SQL> SQL> SQL> SQL> drop table departments; Table dropped. SQL> drop type employee_list_type; Type dropped. SQL> drop type employee_type; Type dropped.
</source>
Create type prices with a varray of number
<source lang="sql">
SQL> CREATE OR REPLACE TYPE prices AS VARRAY(20) OF
2 NUMBER(12,2) 3 /
Type created. SQL> SQL> SQL> SQL>
</source>
Creating and Using VARRAYs
<source lang="sql">
SQL> SQL> create type employee_type as object (
2 employee_id number, 3 first_name varchar2(30), 4 last_name varchar2(30) 5 ); 6 /
Type created. SQL> SQL> create type employee_list_type as varray(50) of employee_type;
2 /
Type created. SQL> create table departments (
2 department_id number, 3 department_name varchar2(30), 4 manager employee_type, 5 employees employee_list_type );
Table created. SQL> SQL> insert into departments ( department_id,
2 department_name, 3 manager, 4 employees ) 5 values ( 10, 6 "Accounting", 7 employee_type( 1, "Danielle", "Steeger" ), 8 employee_list_type( 9 employee_type( 2, "Madison", "Sis" ), 10 employee_type( 3, "Robert", "Cabove" ), 11 employee_type( 4, "Michelle", "Sechrist" )) 12 );
1 row created. SQL> SQL> SQL> insert into departments ( department_id,
2 department_name, 3 manager, 4 employees ) 5 values ( 20, 6 "Research", 7 employee_type( 11, "Ricky", "Lil" ), 8 employee_list_type( 9 employee_type( 12, "Ricky", "Ricardo" ), 10 employee_type( 13, "Lucy", "Ricardo" ), 11 employee_type( 14, "Fred", "Mertz" ), 12 employee_type( 15, "Ethel", "Mertz" )) 13 );
1 row created. SQL> SQL> column department_name format a13 SQL> column employees format a63 word_wrapped SQL> select department_name, employees
2 from departments;
DEPARTMENT_NA EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
---------------------------------------------------------------
Accounting EMPLOYEE_LIST_TYPE(EMPLOYEE_TYPE(2, "Madison", "Sis"),
EMPLOYEE_TYPE(3, "Robert", "Cabove"), EMPLOYEE_TYPE(4, "Michelle", "Sechrist"))
Research EMPLOYEE_LIST_TYPE(EMPLOYEE_TYPE(12, "Ricky", "Ricardo"),
EMPLOYEE_TYPE(13, "Lucy", "Ricardo"), EMPLOYEE_TYPE(14, "Fred", "Mertz"), EMPLOYEE_TYPE(15, "Ethel", "Mertz"))
SQL> SQL> SQL> drop table departments; Table dropped. SQL> SQL> drop type employee_list_type; Type dropped. SQL> SQL> drop type employee_type; Type dropped. SQL> SQL>
</source>
Declare an array initialized as a no-element collection.
<source lang="sql">
SQL> SQL> SQL> DECLARE
2 TYPE integer_varray IS VARRAY(3) OF INTEGER; 3 intArray INTEGER_VARRAY := integer_varray(); 4 BEGIN 5 FOR i IN 1..3 LOOP 6 dbms_output.put ("Integer Varray:"||i); 7 dbms_output.put_line(":"||intArray(i)); 8 END LOOP; 9 END; 10 /
DECLARE
ERROR at line 1: ORA-06533: Subscript beyond count ORA-06512: at line 7
</source>
Declare the varray with null values.
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE integer_varray IS VARRAY(3) OF INTEGER; 3 intArray INTEGER_VARRAY := integer_varray(NULL,NULL,NULL); 4 BEGIN 5 dbms_output.put_line("Varray initialized as nulls."); 6 FOR i IN 1..3 LOOP 7 dbms_output.put ("Integer Varray:"||i); 8 dbms_output.put_line(":"||intArray(i)); 9 END LOOP; 10 11 END; 12 /
Varray initialized as nulls. Integer Varray:1: Integer Varray:2: Integer Varray:3: PL/SQL procedure successfully completed.
</source>
Declaring a VARRAY of scalar variable
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE number_varray IS VARRAY(10) OF NUMBER; 3 list NUMBER_VARRAY := number_varray(1,2,3,4,5,6,7,8,NULL,NULL); 4 BEGIN 5 FOR i IN 1..list.LIMIT LOOP 6 dbms_output.put("["||list(i)||"]"); 7 END LOOP; 8 dbms_output.new_line; 9 END; 10 /
[1][2][3][4][5][6][7][8][][] PL/SQL procedure successfully completed.
</source>
Define a varray of integer with 3 rows
<source lang="sql">
SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE
2 3 4 TYPE integer_varray IS VARRAY(3) OF INTEGER; 5 6 7 intArray INTEGER_VARRAY := 8 integer_varray(NULL,NULL,NULL); 9 10 BEGIN 11 12 13 FOR i IN 1..3 LOOP 14 15 dbms_output.put ("Integer Varray :"||i); 16 dbms_output.put_line(":"||intArray(i)); 17 18 END LOOP; 19 20 intArray(1) := 11; 21 intArray(2) := 12; 22 intArray(3) := 13; 23 24 25 FOR i IN 1..3 LOOP 26 dbms_output.put_line("Integer Varray :"||i||", "||intArray(i)); 27 END LOOP; 28 29 END; 30 /
Integer Varray :1: Integer Varray :2: Integer Varray :3: Integer Varray :1, 11 Integer Varray :2, 12 Integer Varray :3, 13 PL/SQL procedure successfully completed. SQL>
</source>
Define a varray of twelve strings.
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR); 3 4 BEGIN 5 null; 6 END; 7 /
PL/SQL procedure successfully completed. SQL>
</source>
Define a varray with a null element constructor and extends it one element at a time.
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE
2 3 TYPE integer_varray IS VARRAY(3) OF INTEGER; 4 5 intArray INTEGER_VARRAY := integer_varray(); 6 7 BEGIN 8 9 FOR i IN 1..3 LOOP 10 11 intArray.EXTEND; 12 13 intArray(i) := 10 + i; 14 15 END LOOP; 16 17 FOR i IN 1..3 LOOP 18 19 dbms_output.put ("Integer Varray :"||i); 20 dbms_output.put_line(":"||intArray(i)); 21 22 END LOOP; 23 24 END; 25 /
Integer Varray :1:11 Integer Varray :2:12 Integer Varray :3:13 PL/SQL procedure successfully completed. SQL> SQL>
</source>
Define a varray with a null element constructor and extends it one element at a time by a formula
<source lang="sql">
SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> SQL> CREATE OR REPLACE TYPE integer_varray
2 AS VARRAY(100) OF INTEGER NOT NULL; 3 /
Type created. SQL> SQL> DECLARE
2 3 4 intArray INTEGER_VARRAY := integer_varray(); 5 6 BEGIN 7 8 9 FOR i IN 1..intArray.LIMIT LOOP 10 11 12 intArray.EXTEND; 13 14 END LOOP; 15 16 dbms_output.put_line(intArray.COUNT); 17 18 END; 19 /
100 PL/SQL procedure successfully completed. SQL>
</source>
Define a varray with a three element constructor of null elements.
<source lang="sql">
SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE
2 3 4 TYPE integer_varray IS VARRAY(3) OF INTEGER; 5 6 7 intArray INTEGER_VARRAY := 8 integer_varray(NULL,NULL,NULL); 9 10 BEGIN 11 12 13 FOR i IN 1..3 LOOP 14 15 dbms_output.put ("Integer Varray :"||i); 16 dbms_output.put_line(":"||intArray(i)); 17 18 END LOOP; 19 20 intArray(1) := 11; 21 intArray(2) := 12; 22 intArray(3) := 13; 23 24 25 FOR i IN 1..3 LOOP 26 dbms_output.put_line("Integer Varray :"||i||", "||intArray(i)); 27 END LOOP; 28 29 END; 30 /
Integer Varray :1: Integer Varray :2: Integer Varray :3: Integer Varray :1, 11 Integer Varray :2, 12 Integer Varray :3, 13 PL/SQL procedure successfully completed. SQL>
</source>
Define a varray with a three element constructor of null elements and attempt to populate it beyond three elements.
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> SQL> CREATE OR REPLACE TYPE integer_varray
2 AS VARRAY(3) OF INTEGER; 3 /
Type created. SQL> SQL> DECLARE
2 3 4 intArray INTEGER_VARRAY := integer_varray(NULL,NULL,NULL); 5 6 BEGIN 7 8 FOR i IN 1..3 LOOP 9 10 intArray(i) := 10 + i; 11 12 END LOOP; 13 14 15 16 FOR i IN 1..3 LOOP 17 18 dbms_output.put ("Integer Varray ["||i||"] "); 19 dbms_output.put_line("["||intArray(i)||"]"); 20 21 END LOOP; 22 23 END; 24 /
Integer Varray [1] [11] Integer Varray [2] [12] Integer Varray [3] [13] PL/SQL procedure successfully completed. SQL>
</source>
exceeded maximum VARRAY limit
<source lang="sql">
SQL> CREATE or replace TYPE phoneVArray AS VARRAY(3) OF VARCHAR2(14);
2 /
Type created. SQL> SQL> CREATE or replace TYPE addressType AS OBJECT (
2 street VARCHAR2(15), 3 city VARCHAR2(15), 4 state CHAR(2), 5 zip VARCHAR2(5), 6 phone_numbers phoneVArray 7 ); 8 /
Type created. SQL> SQL> CREATE or replace TYPE addressTypeNestedTable AS TABLE OF addressType;
2 /
Type created. SQL> SQL> SQL> -- create the tables SQL> SQL> CREATE TABLE emp (
2 id INTEGER PRIMARY KEY, 3 fname VARCHAR2(10), 4 lname VARCHAR2(10), 5 addresses addressTypeNestedTable 6 ) 7 NESTED TABLE 8 addresses 9 STORE AS 10 nested_addresses;
Table created. SQL> SQL> INSERT INTO emp VALUES (
2 1, "Steve", "Brown", 3 addressTypeNestedTable( 4 addressType("2 Street", "town", "MA", "12345", 5 phoneVArray( 6 "(800)-555-1211", 7 "(800)-555-1213" 8 ) 9 ), 10 addressType("4 Street", "L Town", "CA", "54321", 11 phoneVArray( 12 "(800)-555-1211", 13 "(800)-555-1212", 14 "(800)-555-1213", 15 "(800)-555-1214" 16 ) 17 ) 18 ) 19 ); phoneVArray( *
ERROR at line 11: ORA-22909: exceeded maximum VARRAY limit
SQL> SQL> SQL> drop type addressTypeNestedTable force; Type dropped. SQL> drop type addressType force; Type dropped. SQL> drop type phoneVArray force; Type dropped. SQL> drop TABLE emp; Table dropped.
</source>
Extend with null element to the maximum limit size.
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE number_varray IS VARRAY(5) OF INTEGER; 3 number_list NUMBER_VARRAY := number_varray(1,2,3); 4 5 PROCEDURE print_list(list_in NUMBER_VARRAY) IS 6 BEGIN 7 FOR i IN list_in.FIRST..list_in.COUNT LOOP 8 DBMS_OUTPUT.PUT_LINE("List Index ["||i||"] "||"List Value ["||list_in(i)||"]"); 9 END LOOP; 10 END print_list; 11 BEGIN 12 print_list(number_list); 13 14 number_list.EXTEND(number_list.LIMIT - number_list.LAST); 15 16 print_list(number_list); 17 END; 18 /
List Index [1] List Value [1] List Index [2] List Value [2] List Index [3] List Value [3] List Index [1] List Value [1] List Index [2] List Value [2] List Index [3] List Value [3] List Index [4] List Value [] List Index [5] List Value [] PL/SQL procedure successfully completed. SQL>
</source>
Hard code value in varray and use for loop to insert them to a table
<source lang="sql">
SQL> create table gift(
2 gift_id integer primary key 3 ,price number(7,2) 4 ,description varchar2(75) 5 ,onhand number(5,0) 6 ,reorder number(5,0) 7 ,supplier_no integer 8 );
Table created. SQL> -- gift Table Inserts: SQL> insert into gift(gift_id, price, description, onhand, reorder)values (1,2.50,"Happy Birthday",100,20); 1 row created. SQL> insert into gift(gift_id, price, description, onhand, reorder)values (2,23.00,"Happy Birthday",null,null); 1 row created. SQL> insert into gift(gift_id, price, description, onhand, reorder)values (3,null,"Happy New Year",null,null); 1 row created. SQL> insert into gift(gift_id, price, description, onhand, reorder)values (4,1.50,"Happy New Year",50,10); 1 row created. SQL> SQL> SQL> CREATE SEQUENCE gift_seq
2 INCREMENT BY 1 3 START WITH 9 4 NOMAXVALUE 5 NOCYCLE;
Sequence created. SQL> SQL> CREATE OR REPLACE PROCEDURE p_add_prod (v_supplier IN number, v_ctr IN number)
2 AS 3 i number := 1; 4 idIndex number := 1; 5 newOrder gift.reorder%TYPE; 6 7 TYPE names IS VARRAY(10) OF VARCHAR2(75); 8 v_names names := names("A","B","C","D","E","F","G","H","I","J"); 9 10 TYPE Prod_Prices IS VARRAY(10) OF NUMBER(7,2); 11 v_prices Prod_prices := Prod_prices(2,2.25,3,4.2,6,12.4,11.7,9.25,5,7.5); 12 13 TYPE Prod_Onhand IS VARRAY(10) OF NUMBER; 14 v_onhand Prod_Onhand := Prod_Onhand(70,20,10,40,30,50,60,80,90,55); 15 16 begin 17 18 WHILE i <= v_ctr LOOP 19 IF idIndex > 10 THEN 20 idIndex := 1; 21 END IF; 22 23 IF v_onhand(idIndex) >= 30 THEN 24 newOrder := v_onhand(idIndex) - 10; 25 ELSE 26 newOrder := v_onhand(idIndex) - 5; 27 END IF; 28 29 INSERT INTO gift (gift_ID, PRICE, DESCRIPTION, ONHAND, REORDER, SUPPLIER_NO) 30 VALUES (gift_seq.NEXTVAL, v_prices(idIndex), v_names(idIndex), v_onhand(idIndex), newOrder, v_supplier); 31 32 i := i + 1 ; 33 idIndex := idIndex + 1; 34 35 END LOOP; 36 end; 37 /
Procedure created. SQL> SQL> show errors No errors. SQL> SQL> drop SEQUENCE gift_seq; Sequence dropped. SQL> SQL> drop table gift; Table dropped. SQL> SQL>
</source>
Initialization and assignment with a numeric index value to an associative array.
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE
2 3 4 TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR); 5 6 7 TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR) 8 INDEX BY VARCHAR2(9 CHAR); 9 10 11 month MONTHS_VARRAY := months_varray("January","February","March","April","May","June","July","August"); 12 13 14 calendar CALENDAR_TABLE; 15 16 BEGIN 17 18 19 IF calendar.COUNT = 0 THEN 20 21 22 FOR i IN month.FIRST..month.LAST LOOP 23 24 25 26 calendar(month(i)) := ""; 27 28 29 DBMS_OUTPUT.PUT_LINE("Index :"||month(i)||" is "||i); 30 31 END LOOP; 32 33 34 FOR i IN calendar.FIRST..calendar.LAST LOOP 35 36 DBMS_OUTPUT.PUT_LINE("Index :"||i||" is "||calendar(i)); 37 38 END LOOP; 39 40 END IF; 41 42 END; 43 /
Index :January is 1 Index :February is 2 Index :March is 3 Index :April is 4 Index :May is 5 Index :June is 6 Index :July is 7 Index :August is 8
SQL>
</source>
Initialization and assignment with a unique string index value to an associative array.
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> DECLARE
2 3 current VARCHAR2(9 CHAR); 4 element INTEGER; 5 6 TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR); 7 8 TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR)INDEX BY VARCHAR2(9 CHAR); 9 10 month MONTHS_VARRAY := months_varray("January","February","March","April","May","June","July","August"); 11 12 calendar CALENDAR_TABLE; 13 14 BEGIN 15 16 IF calendar.COUNT = 0 THEN 17 18 FOR i IN month.FIRST..month.LAST LOOP 19 20 calendar(month(i)) := TO_CHAR(i); 21 22 DBMS_OUTPUT.PUT_LINE("Index :"||month(i)||" is "||i); 23 24 END LOOP; 25 FOR i IN 1..calendar.COUNT LOOP 26 27 IF i = 1 THEN 28 29 current := calendar.FIRST; 30 31 element := calendar(current); 32 33 ELSE 34 35 IF calendar.NEXT(current) IS NOT NULL THEN 36 37 current := calendar.NEXT(current); 38 39 element := calendar(current); 40 41 ELSE 42 43 EXIT; 44 45 END IF; 46 47 END IF; 48 49 DBMS_OUTPUT.PUT_LINE("Index :"||current||" is "||element); 50 51 END LOOP; 52 53 END IF; 54 55 END; 56 /
Index :January is 1 Index :February is 2 Index :March is 3 Index :April is 4 Index :May is 5 Index :June is 6 Index :July is 7 Index :August is 8 Index :April is 4 Index :August is 8 Index :February is 2 Index :January is 1 Index :July is 7 Index :June is 6 Index :March is 3 Index :May is 5 PL/SQL procedure successfully completed. SQL>
</source>
Initialize the array and create two entries using the constructor
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 TYPE dept_array IS VARRAY(100) OF VARCHAR2(30); 3 4 depts dept_array; 5 inx1 PLS_INTEGER; 6 7 BEGIN 8 depts := dept_array ("Dept One","Dept Two"); 9 10 FOR inx1 IN 1..2 LOOP 11 DBMS_OUTPUT.PUT_LINE(depts(inx1)); 12 END LOOP; 13 END; 14 /
Dept One Dept Two PL/SQL procedure successfully completed. SQL> SQL> SQL> --
</source>
legal and illegal varray assignments.
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE Strings IS VARRAY(5) OF VARCHAR2(10); 3 4 v_List Strings := Strings("One", "Two", "Three"); 5 BEGIN 6 v_List(2) := "TWO"; 7 8 -- Subscript beyond count, raises ORA-6533. 9 v_List(4) := "!!!"; 10 END; 11 /
DECLARE
ERROR at line 1: ORA-06533: Subscript beyond count ORA-06512: at line 9
SQL> SQL>
</source>
Nested varray
<source lang="sql">
SQL> SQL> SQL> DECLARE
2 TYPE x IS VARRAY(30) OF INTEGER; 3 TYPE y IS VARRAY(20) OF x; 4 5 xArray x := x(234,2,3,4); 6 yArray y := y( x(0), xArray, x(4,5,3,4) ); 7 BEGIN 8 dbms_output.put_line(yArray(3)(2) ); 9 END; 10 /
5 PL/SQL procedure successfully completed. SQL>
</source>
ORA-06533: Subscript beyond count
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE Strings IS VARRAY(5) OF VARCHAR2(10); 3 4 v_List Strings := Strings("S", "D", "U"); 5 BEGIN 6 v_List(2) := "DAVID"; 7 8 v_List(4) := "!!!"; 9 END; 10 /
DECLARE
ERROR at line 1: ORA-06512: at line 8
SQL>
</source>
Query a stored varray.
<source lang="sql">
SQL> SQL> CREATE OR REPLACE TYPE BookList AS VARRAY(10) OF NUMBER(4);
2 /
Type created. SQL> SQL> SQL> CREATE TABLE class_material (
2 department CHAR(3), 3 course NUMBER(3), 4 required_reading BookList 5 );
Table created. SQL> SQL> CREATE TABLE books (
2 catalog_number NUMBER(4) PRIMARY KEY, 3 title VARCHAR2(40), 4 author1 VARCHAR2(40), 5 author2 VARCHAR2(40), 6 author3 VARCHAR2(40), 7 author4 VARCHAR2(40) 8 );
Table created. SQL> SQL> INSERT INTO books (catalog_number, title, author1)
2 VALUES (1000, "Oracle8i Advanced PL/SQL Programming", "Urman, Scott");
1 row created. SQL> SQL> INSERT INTO books (catalog_number, title, author1, author2, author3)
2 VALUES (1001, "Oracle8i: A Beginner""s Guide", "Abbey, Michael", "Corey, Michael J.", "Abramson, Ian");
1 row created. SQL> SQL> INSERT INTO books (catalog_number, title, author1, author2, author3, author4)
2 VALUES (1002, "Oracle8 Tuning", "Corey, Michael J.", "Abbey, Michael", "Dechichio, Daniel J.", "Abramson, Ian");
1 row created. SQL> SQL> INSERT INTO books (catalog_number, title, author1, author2)
2 VALUES (2001, "A History of the World", "Arlington, Arlene", "Verity, Victor");
1 row created. SQL> SQL> INSERT INTO books (catalog_number, title, author1)
2 VALUES (3001, "Bach and the Modern World", "Foo, Fred");
1 row created. SQL> SQL> INSERT INTO books (catalog_number, title, author1)
2 VALUES (3002, "Introduction to the Piano", "Morenson, Mary");
1 row created. SQL> CREATE OR REPLACE PROCEDURE PrintRequired(
2 p_Department IN class_material.department%TYPE, 3 p_Course IN class_material.course%TYPE) IS 4 5 v_Books class_material.required_reading%TYPE; 6 v_Title books.title%TYPE; 7 BEGIN 8 SELECT required_reading 9 INTO v_Books 10 FROM class_material 11 WHERE department = p_Department 12 AND course = p_Course; 13 14 DBMS_OUTPUT.PUT("Required reading for " || RTRIM(p_Department)); 15 DBMS_OUTPUT.PUT_LINE(" " || p_Course || ":"); 16 17 FOR v_Index IN 1..v_Books.COUNT LOOP 18 SELECT title 19 INTO v_Title 20 FROM books 21 WHERE catalog_number = v_Books(v_Index); 22 DBMS_OUTPUT.PUT_LINE( 23 " " || v_Books(v_Index) || ": " || v_Title); 24 END LOOP; 25 END PrintRequired; 26 /
Procedure created. SQL> SQL> DECLARE
2 CURSOR c_Courses IS 3 SELECT department, course 4 FROM class_material 5 ORDER BY department; 6 BEGIN 7 FOR v_Rec IN c_Courses LOOP 8 PrintRequired(v_Rec.department, v_Rec.course); 9 END LOOP; 10 END; 11 /
PL/SQL procedure successfully completed. SQL> SQL> drop table class_material; Table dropped. SQL> SQL> drop table books; Table dropped. SQL> SQL> --Overloading packaged subprograms based on object types. CREATE OR REPLACE TYPE t11 AS OBJECT (
f NUMBER
) / CREATE OR REPLACE TYPE t21 AS OBJECT (
f NUMBER
) / CREATE OR REPLACE PACKAGE Overload AS
PROCEDURE Proc(p_Parameter1 IN t11); PROCEDURE Proc(p_Parameter1 IN t21);
END Overload; / CREATE OR REPLACE PACKAGE BODY Overload AS
PROCEDURE Proc(p_Parameter1 IN t11) IS BEGIN DBMS_OUTPUT.PUT_LINE("Proc(t11): " || p_Parameter1.f); END Proc; PROCEDURE Proc(p_Parameter1 IN t21) IS BEGIN DBMS_OUTPUT.PUT_LINE("Proc(t21): " || p_Parameter1.f); END Proc;
END Overload; / set serveroutput on DECLARE
v_Obj1 t11 := t11(1); v_Obj2 t21 := t21(2);
BEGIN
Overload.Proc(v_Obj1); Overload.Proc(v_Obj2);
END; / drop type t11; drop type t21; --
</source>
Reference elements in varray
<source lang="sql">
SQL> SQL> create table department
2 ( dept_id number(2), 3 dept_name varchar2(14), 4 no_of_emps varchar2(13) 5 ) 6 /
Table created. SQL> SQL> INSERT INTO department VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO department VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO department VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO department VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> DECLARE
2 CURSOR all_depts IS SELECT * FROM department ORDER BY dept_name; 3 4 TYPE dept_array IS VARRAY(100) OF department%ROWTYPE; 5 6 depts dept_array; 7 inx1 PLS_INTEGER; 8 inx2 PLS_INTEGER; 9 BEGIN 10 inx1 := 0; 11 12 depts := dept_array (); 13 14 FOR dept IN all_depts LOOP 15 inx1 := inx1 + 1; 16 depts.extend(); 17 depts(inx1).dept_id := dept.dept_id; 18 depts(inx1).dept_name := dept.dept_name; 19 depts(inx1).no_of_emps := dept.no_of_emps; 20 END LOOP; 21 22 FOR inx2 IN 1..depts.count LOOP 23 DBMS_OUTPUT.PUT_LINE ( 24 depts(inx2).dept_id || 25 " " || depts(inx2).dept_name); 26 END LOOP; 27 END; 28 /
10 ACCOUNTING 40 OPERATIONS 20 RESEARCH 30 SALES PL/SQL procedure successfully completed. SQL> SQL> drop table department; Table dropped. SQL> SQL> --
</source>
Reference varray.count in for loop
<source lang="sql">
SQL> CREATE OR REPLACE TYPE unitType AS VARRAY(13) OF VARCHAR2(5 CHAR);
2 /
Type created. SQL> CREATE OR REPLACE TYPE categoryType AS VARRAY(4) OF VARCHAR2(8 CHAR);
2 /
Type created. SQL> CREATE OR REPLACE TYPE charArrayType AS TABLE OF VARCHAR2(17 CHAR);
2 /
Type created. SQL> SQL> DECLARE
2 counter INTEGER := 0; 3 4 suits categoryType :=categoryType("A","B","C","D"); 5 units unitType :=unitType("1","2","3","4","Five","Six","Seven"); 6 7 charArray charArrayType := charArrayType(); 8 BEGIN 9 10 FOR i IN 1..suits.COUNT LOOP 11 FOR j IN 1..units.COUNT LOOP 12 counter := counter + 1; 13 charArray.EXTEND; 14 charArray(counter) := units(j)||" of "||suits(i); 15 END LOOP; 16 END LOOP; 17 18 END; 19 /
PL/SQL procedure successfully completed.
</source>
Store 12 months in varray of string
<source lang="sql">
SQL> DECLARE
2 TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR); 3 4 TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR) INDEX BY BINARY_INTEGER; 5 6 month MONTHS_VARRAY := months_varray("January","February","March","April","May","June","July","August","September","October","November","December"); 7 8 calendar CALENDAR_TABLE; 9 BEGIN 10 IF calendar.COUNT = 0 THEN 11 FOR i IN month.FIRST..month.LAST LOOP 12 calendar(i) := ""; 13 DBMS_OUTPUT.PUT_LINE(i||" is "||calendar(i)); 14 calendar(i) := month(i); 15 END LOOP; 16 17 18 END IF; 19 END; 20 /
1 is 2 is 3 is 4 is 5 is 6 is 7 is 8 is 9 is 10 is 11 is 12 is PL/SQL procedure successfully completed. SQL>
</source>
Store pre-defined constants in VARRAY
<source lang="sql">
SQL> SQL> create table ord(
2 order_no integer 3 ,cust_no integer 4 ,order_date date not null 5 ,total_order_price number(7,2) 6 ,deliver_date date 7 ,deliver_time varchar2(7) 8 ,payment_method varchar2(2) 9 ,emp_no number(3,0) 10 ,deliver_name varchar2(35) 11 ,gift_message varchar2(100) 12 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE p_add_orders (v_ord_ctr IN number, v_item_ctr IN number,
2 v_cust_no IN number, v_emp_no IN number) 3 AS 4 v_loop number := 1; 5 v_type_ctr number := 1; 6 v_curr_order ord.order_no%TYPE; 7 8 TYPE PayMethods IS VARRAY(10) OF VARCHAR2(2); 9 v_paymethods PayMethods := PayMethods("VS","CA","VG","AX","CK","MC","DI","CA","CK","VS"); 10 11 TYPE Orderdates IS VARRAY(10) OF DATE; 12 v_odates Orderdates := Orderdates(add_months(sysdate, -45), 13 add_months(sysdate, -14), 14 add_months(sysdate, -22), 15 add_months(sysdate, -38), 16 add_months(sysdate, -46), 17 add_months(sysdate, -59), 18 add_months(sysdate, -19), 19 add_months(sysdate, -11), 20 add_months(sysdate, -74), 21 add_months(sysdate, -6)); 22 begin 23 WHILE v_loop <= v_ord_ctr LOOP 24 IF v_type_ctr > 10 THEN 25 v_type_ctr := 1; 26 END IF; 27 28 INSERT INTO ord (ORDER_NO, CUST_NO, ORDER_DATE, TOTAL_ORDER_PRICE, DELIVER_DATE, 29 PAYMENT_METHOD, EMP_NO) 30 VALUES (999, v_cust_no, v_odates(v_type_ctr), 0, v_odates(v_type_ctr) + 10, 31 v_paymethods(v_type_ctr), v_emp_no ); 32 33 SELECT 11111 34 INTO v_curr_order 35 FROM dual ; 36 37 v_loop := v_loop + 1 ; 38 v_type_ctr := v_type_ctr + 1 ; 39 END LOOP; 40 end; 41 /
Procedure created. SQL> SQL> show error No errors. SQL> SQL> SQL> drop table ord; Table dropped. SQL> SQL> SQL> --
</source>
Subscript index values begin at 1, not 0
<source lang="sql">
SQL> SQL> DECLARE
2 3 TYPE integer_varray IS VARRAY(3) OF INTEGER; 4 5 6 intArray INTEGER_VARRAY := integer_varray(NULL,NULL,NULL); 7 BEGIN 8 9 10 FOR i IN 1..3 LOOP 11 dbms_output.put ("Integer Varray:"||i); 12 dbms_output.put_line(":"||intArray(i)); 13 END LOOP; 14 15 END; 16 /
Integer Varray:1: Integer Varray:2: Integer Varray:3: PL/SQL procedure successfully completed.
</source>
Table of numbers and varray of numbers
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE t_IndexBy IS TABLE OF NUMBER 3 INDEX BY BINARY_INTEGER; 4 TYPE t_Nested IS TABLE OF NUMBER; 5 TYPE t_Varray IS VARRAY(10) OF NUMBER; 6 7 v_IndexBy t_IndexBy; 8 v_Nested t_Nested; 9 v_Varray t_Varray; 10 BEGIN 11 v_IndexBy(1) := 1; 12 v_IndexBy(2) := 2; 13 v_Nested := t_Nested(1, 2, 3, 4, 5); 14 v_Varray := t_Varray(1, 2); 15 END; 16 /
PL/SQL procedure successfully completed. SQL> SQL>
</source>
TYPE Strings IS VARRAY(5) OF VARCHAR2(10)
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE Strings IS VARRAY(5) OF VARCHAR2(10); 3 v_List Strings := Strings("One", "Two", "Three", "Four"); 4 BEGIN 5 v_List(2) := "TWO"; 6 7 v_List.EXTEND; 8 v_List(5) := "Five"; 9 10 v_list.EXTEND; 11 END; 12 /
DECLARE
ERROR at line 1: ORA-06532: Subscript outside of limit ORA-06512: at line 10
SQL> SQL>
</source>
Use table() function to display varray type column
<source lang="sql">
SQL> SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> SQL> SQL> create table e
2 as 3 select empno, ename, init, mgr, deptno 4 from emp;
Table created. SQL> SQL> SQL> create or replace type numberlist_t
2 as varray(4) of varchar2(20); 3 /
Type created. SQL> SQL> column numlist format a60 SQL> SQL> alter table e add (numlist numberlist_t); Table altered. SQL> SQL> describe e
Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NUMBER(4) ENAME VARCHAR2(8) INIT VARCHAR2(5) MGR NUMBER(4) DEPTNO NUMBER(2) NUMLIST NUMBERLIST_T
SQL> SQL> SQL> break on empno SQL> SQL> select empno, n.*
2 from e 3 , TABLE(e.numlist) n;
no rows selected SQL> SQL> SQL> SQL> drop table e; Table dropped. SQL> drop table emp; Table dropped.
</source>
Varray constructors.
<source lang="sql">
SQL> SQL> DECLARE
2 TYPE Numbers IS VARRAY(20) OF NUMBER(3); 3 v_NullList Numbers; 4 v_List1 Numbers := Numbers(1, 2); 5 6 v_List2 Numbers := Numbers(NULL); 7 BEGIN 8 IF v_NullList IS NULL THEN 9 DBMS_OUTPUT.PUT_LINE("v_NullList is NULL"); 10 END IF; 11 12 IF v_List2(1) IS NULL THEN 13 DBMS_OUTPUT.PUT_LINE("v_List2(1) is NULL"); 14 END IF; 15 END; 16 /
v_NullList is NULL v_List2(1) is NULL PL/SQL procedure successfully completed. SQL> SQL>
</source>
VARRAY of VARCHAR2 and Varray of number
<source lang="sql">
SQL> SQL> create table product(
2 product_id integer primary key 3 ,price number(7,2) 4 ,description varchar2(75) 5 ,onhand number(5,0) 6 ,reorder number(5,0) 7 ,supplier_no integer 8 );
Table created. SQL> insert into product(product_id, price, description, onhand, reorder)values (1,2.50,"Oracle",100,20); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (2,23.00,"SQL Server",null,null); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (3,null,"MySQL",null,null); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (4,1.50,"DB2",50,10); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (5,10.50,"Java",100,20); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (6,45.00,"C++",null,null); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (7,19.99,"Javascript",3,5); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (8,4.50,"Ruby",null,null); 1 row created. SQL> SQL> CREATE OR REPLACE PROCEDURE p_add_prod (v_supplier IN number, v_ctr IN number)
2 AS 3 v_loop number := 1; 4 v_type_ctr number := 1; 5 v_reorder product.reorder%TYPE; 6 7 TYPE Prod_Names IS VARRAY(10) OF VARCHAR2(75); 8 v_names Prod_Names := Prod_Names("Widget ","Gadget"); 9 10 TYPE Prod_Prices IS VARRAY(10) OF NUMBER(7,2); 11 v_prices Prod_prices := Prod_prices(2,2.25,3,4.2,6,12.4,11.7,9.25,5,7.5); 12 13 TYPE Prod_Onhand IS VARRAY(10) OF NUMBER; 14 v_onhand Prod_Onhand := Prod_Onhand(70,20,10,40,30,50,60,80,90,55); 15 16 begin 17 WHILE v_loop <= v_ctr LOOP 18 IF v_type_ctr > 10 THEN 19 v_type_ctr := 1; 20 END IF; 21 IF v_onhand(v_type_ctr) >= 30 THEN 22 v_reorder := v_onhand(v_type_ctr) - 10; 23 ELSE 24 v_reorder := v_onhand(v_type_ctr) - 5; 25 END IF; 26 27 INSERT INTO product (PRODUCT_ID, PRICE, DESCRIPTION, ONHAND, REORDER, SUPPLIER_NO) 28 VALUES (11111, v_prices(v_type_ctr), v_names(v_type_ctr), 29 v_onhand(v_type_ctr), v_reorder, v_supplier); 30 31 v_loop := v_loop + 1 ; 32 v_type_ctr := v_type_ctr + 1; 33 34 END LOOP; 35 end; 36 /
Procedure created. SQL> show error No errors. SQL> SQL> exec p_add_prod(1,2); BEGIN p_add_prod(1,2); END;
ERROR at line 1: ORA-00001: unique constraint (sqle.SYS_C006114) violated ORA-06512: at "sqle.P_ADD_PROD", line 27 ORA-06512: at line 1
SQL> SQL> select * from product; PRODUCT_ID PRICE DESCRIPTION ONHAND REORDER SUPPLIER_NO
---------- --------------------------------------------------------------------------- ---------- ---------- -----------
1 2.5 Oracle 100 20 2 23 SQL Server 3 MySQL 4 1.5 DB2 50 10 5 10.5 Java 100 20 6 45 C++ 7 19.99 Javascript 3 5 8 4.5 Ruby
8 rows selected. SQL> SQL> SQL> drop table product; Table dropped. SQL> SQL> --
</source>