Oracle PL/SQL/PL SQL/varray — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 09:59, 26 мая 2010
Содержание
- 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.
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>
Assigns a value to the indexed value.
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.
Assign values to subscripted members of the varray.
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>
Associative array example
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>
Avoid traversing an associative array where no elements are initialized.
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>
Check the size of a varray
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>
Compare two varray variables
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.
Constructs a two varrays and one nested table type in the database
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>
Control varray index with if statement
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>
Create a varray based on user defined type
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.
Create type prices with a varray of number
SQL> CREATE OR REPLACE TYPE prices AS VARRAY(20) OF
2 NUMBER(12,2)
3 /
Type created.
SQL>
SQL>
SQL>
SQL>
Creating and Using VARRAYs
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>
Declare an array initialized as a no-element collection.
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
Declare the varray with null values.
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.
Declaring a VARRAY of scalar variable
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.
Define a varray of integer with 3 rows
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>
Define a varray of twelve strings.
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>
Define a varray with a null element constructor and extends it one element at a time.
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>
Define a varray with a null element constructor and extends it one element at a time by a formula
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>
Define a varray with a three element constructor of null elements.
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>
Define a varray with a three element constructor of null elements and attempt to populate it beyond three elements.
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>
exceeded maximum VARRAY limit
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.
Extend with null element to the maximum limit size.
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>
Hard code value in varray and use for loop to insert them to a table
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>
Initialization and assignment with a numeric index value to an associative array.
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>
Initialization and assignment with a unique string index value to an associative array.
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>
Initialize the array and create two entries using the constructor
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> --
legal and illegal varray assignments.
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>
Nested varray
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>
ORA-06533: Subscript beyond count
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>
Query a stored varray.
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;
--
Reference elements in varray
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> --
Reference varray.count in for loop
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.
Store 12 months in varray of string
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>
Store pre-defined constants in VARRAY
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> --
Subscript index values begin at 1, not 0
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.
Table of numbers and varray of numbers
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>
TYPE Strings IS VARRAY(5) OF VARCHAR2(10)
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>
Use table() function to display varray type column
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.
Varray constructors.
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>
VARRAY of VARCHAR2 and Varray of number
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> --