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.
----------
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.
---------------
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.
-----------
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.
-------------
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.
----------
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>
------------
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;
------------
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.
-----------
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>
-----------
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.
-----------
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.
---
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.
----------
Oracle PL / SQL » PL SQL » varray
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.
----------
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.
----------------
Oracle PL / SQL » PL SQL » varray
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
---------------
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.
-----------
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.
----------
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.
-----------
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>
-----------
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.
----------
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.
---------------
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.
-----------
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.
-------------
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.
----------
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>
------------
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;
------------
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.
-----------
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>
-----------
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.
-----------
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.
---
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.
----------
Oracle PL / SQL » PL SQL » varray
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.
----------
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.
----------------
Oracle PL / SQL » PL SQL » varray
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
---------------
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.
-----------
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.
----------
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.
-----------
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>
-----------
No comments:
Post a Comment