Oracle PL/SQL Tutorial/Collections/VARRAY Column
Содержание
- 1 Define function to get the first value in varray
- 2 DELETE on varray column
- 3 Display the structure of a table with a valarray column
- 4 Insert on whole VARRAYS
- 5 Modifying Varray Elements
- 6 Populating a Varray with Elements
- 7 Selecting Varray Elements
- 8 SELECT into with varray column
- 9 Set empty varray value
- 10 Stored VARRAYS
- 11 UPDATE on varray column
- 12 UPDATE on varray column in PL/SQL block
- 13 Using a Varray Type to Define a Column in a Table
Define function to get the first value in varray
SQL>
SQL> column numlist format a60
SQL>
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL>
SQL> create table e as select empno, ename, init, mgr, deptno from employees;
Table created.
SQL>
SQL>
SQL> create or replace type numberVarray as varray(4) of varchar2(20);
2 /
Type created.
SQL>
SQL>
SQL> alter table e add (numlist numberVarray);
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 NUMBERVARRAY
SQL>
SQL>
SQL> update e set numlist = numberVarray("4231","06-12345678");
10 rows updated.
SQL>
SQL>
SQL> create or replace function ext(p_varray_in numberVarray)
2 return varchar2
3 is
4 v_ext varchar2(20);
5 begin
6 v_ext := p_varray_in(1);
7 return v_ext;
8 end;
9 /
Function created.
SQL>
SQL> col ext(numlist) format a12
SQL>
SQL> select ename, init, ext(numlist) from e;
last_name INIT EXT(NUMLIST)
-------------------- ----- ------------
Jason N 4231
Jerry J 4231
Jord T 4231
Mary J 4231
Joe P 4231
Black R 4231
Red A 4231
White S 4231
Yellow C 4231
Pink J 4231
10 rows selected.
SQL>
SQL> drop table e;
Table dropped.
SQL>
SQL> drop table employees;
Table dropped.
DELETE on varray column
SQL>
SQL> CREATE OR REPLACE TYPE numberVarryType AS VARRAY(10)OF NUMBER(10);
2 /
Type created.
SQL>
SQL> CREATE TABLE address_list (
2 list_id VARCHAR2(6)PRIMARY KEY,
3 direct_addresses numberVarryType
4 );
Table created.
SQL>
SQL>
SQL> DELETE FROM address_list
2 WHERE list_id ="OFF102";
0 rows deleted.
SQL>
SQL> drop table address_list;
Table dropped.
Display the structure of a table with a valarray column
SQL>
SQL> CREATE Or Replace TYPE addressVarray AS VARRAY(2) OF VARCHAR2(50);
2 /
Type created.
SQL> CREATE TABLE employee (
2 id INTEGER PRIMARY KEY,
3 first_name VARCHAR2(10),
4 last_name VARCHAR2(10),
5 addresses addressVarray
6 );
Table created.
SQL>
SQL> DESCRIBE employee
Name Null? Type
-------------------------------------
ID NOT NULL NUMBER(38)
FIRST_NAME VARCHAR2(10)
LAST_NAME VARCHAR2(10)
ADDRESSES ADDRESSVARRAY
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
Insert on whole VARRAYS
SQL> -- INSERT
SQL>
SQL> CREATE OR REPLACE TYPE numberVarryType AS VARRAY(10)OF NUMBER(10);
2 /
Type created.
SQL>
SQL> CREATE TABLE address_list (
2 list_id VARCHAR2(6)PRIMARY KEY,
3 direct_addresses numberVarryType
4 );
Table created.
SQL>
SQL>
SQL> INSERT INTO address_list VALUES("O1",numberVarryType(1001,1002,1003,1004));
1 row created.
SQL>
SQL> DECLARE
2 v_add_varray numberVarryType :=numberVarryType(2001,2002);
3 BEGIN
4 INSERT INTO address_list VALUES ("OFF102",v_add_varray);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table address_list;
Table dropped.
SQL>
Modifying Varray Elements
The elements in a varray can only be modified as a whole.
SQL> CREATE Or Replace TYPE addressVarray AS VARRAY(2) OF VARCHAR2(50);
2 /
Type created.
SQL> CREATE TABLE employee (
2 id INTEGER PRIMARY KEY,
3 first_name VARCHAR2(10),
4 last_name VARCHAR2(10),
5 addresses addressVarray
6 );
Table created.
SQL>
SQL> DESCRIBE employee;
Name Null? Type
ID NOT NULL NUMBER(38)
FIRST_NAME VARCHAR2(10)
LAST_NAME VARCHAR2(10)
ADDRESSES ADDRESSVARRAY
SQL>
SQL> INSERT INTO employee VALUES (
2 1, "Steve", "Brown",
3 addressVarray(
4 "AAAAAAAAAAAAAAAA",
5 "BBBBBBBBBBBBBBBB"
6 )
7 );
1 row created.
SQL>
SQL> SELECT *
2 FROM employee;
ID FIRST_NAME LAST_NAME ADDRESSES
------------------------------------------------------------------------------------
1 Steve Brown ADDRESSVARRAY("AAAAAAAAAAAAAAAA", "BBBBBBBBBBBBBBBB")
SQL>
SQL>
SQL> UPDATE employee
2 SET addresses = addressVarray(
3 "CCCCCCCCCCCCCCC",
4 "BBBBBBBBBBBBBBB"
5 )
6 WHERE id = 1;
1 row updated.
SQL>
SQL> SELECT *
2 FROM employee;
ID FIRST_NAME LAST_NAME ADDRESSES
------------------------------------------------------------------------
1 Steve Brown ADDRESSVARRAY("CCCCCCCCCCCCCCC", "BBBBBBBBBBBBBBB")
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
Populating a Varray with Elements
You can populate the elements in a varray using an INSERT statement.
SQL>
SQL>
SQL> CREATE Or Replace TYPE addressVarray AS VARRAY(2) OF VARCHAR2(50);
2 /
Type created.
SQL> CREATE TABLE employee (
2 id INTEGER PRIMARY KEY,
3 first_name VARCHAR2(10),
4 last_name VARCHAR2(10),
5 addresses addressVarray
6 );
Table created.
SQL>
SQL> DESCRIBE employee;
Name Null? Type
ID NOT NULL NUMBER(38)
FIRST_NAME VARCHAR2(10)
LAST_NAME VARCHAR2(10)
ADDRESSES ADDRESSVARRAY
SQL>
SQL> INSERT INTO employee VALUES (
2 1, "Steve", "Brown",
3 addressVarray(
4 "AAAAAAAAAAAAAAAA",
5 "BBBBBBBBBBBBBBBB"
6 )
7 );
1 row created.
SQL>
SQL> SELECT *
2 FROM employee;
ID FIRST_NAME LAST_NAME ADDRESSES
----------------------------------------------------------------------------------------
1 Steve Brown ADDRESSVARRAY("AAAAAAAAAAAAAAAA", "BBBBBBBBBBBBBBBB")
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
Selecting Varray Elements
SQL>
SQL>
SQL>
SQL> CREATE Or Replace TYPE addressVarray AS VARRAY(2) OF VARCHAR2(50);
2 /
Type created.
SQL>
SQL> CREATE TABLE employee (
2 id INTEGER PRIMARY KEY,
3 first_name VARCHAR2(10),
4 last_name VARCHAR2(10),
5 addresses addressVarray
6 );
Table created.
SQL>
SQL> DESCRIBE employee
Name Null? Type
ID NOT NULL NUMBER(38)
FIRST_NAME VARCHAR2(10)
LAST_NAME VARCHAR2(10)
ADDRESSES ADDRESSVARRAY
SQL>
SQL> INSERT INTO employee VALUES (
2 1, "Steve", "Brown",
3 addressVarray(
4 "AAAAAAAAAAAAAAAA",
5 "BBBBBBBBBBBBBBBB"
6 )
7 );
1 row created.
SQL>
SQL> SELECT *
2 FROM employee;
ID FIRST_NAME LAST_NAME ADDRESSES
------------------------------------------------------------------------------------------
1 Steve Brown ADDRESSVARRAY("AAAAAAAAAAAAAAAA", "BBBBBBBBBBBBBBBB")
SQL>
SQL> SELECT addresses
2 FROM employee;
ADDRESSES
--------------------------------------------------------
ADDRESSVARRAY("AAAAAAAAAAAAAAAA", "BBBBBBBBBBBBBBBB")
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SELECT into with varray column
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE numberVarryType AS VARRAY(10)OF NUMBER(10);
2 /
Type created.
SQL>
SQL> CREATE TABLE address_list (
2 list_id VARCHAR2(6)PRIMARY KEY,
3 direct_addresses numberVarryType
4 );
Table created.
SQL>
SQL>
SQL> DECLARE
2 v_add_varray address_list.direct_addresses%TYPE;
3 BEGIN
4 SELECT direct_addresses INTO v_add_varray FROM address_list WHERE list_id ="OFF101";
5
6 FOR idx IN 1..v_add_varray.COUNT LOOP
7 DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_add_varray(idx)));
8 END LOOP;
9 EXCEPTION WHEN OTHERS THEN
10 DBMS_OUTPUT.PUT_LINE(SQLERRM);
11 END;
12 /
ORA-01403: no data found
PL/SQL procedure successfully completed.
SQL>
SQL> drop table address_list;
Table dropped.
Set empty varray value
SQL>
SQL>
SQL> column numlist format a60
SQL>
SQL>
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL>
SQL> create table e
2 as
3 select empno, ename, init, mgr, deptno
4 from employees;
Table created.
SQL>
SQL>
SQL> create or replace type numberVarray
2 as varray(4) of varchar2(20);
3 /
Type created.
SQL>
SQL>
SQL> alter table e add (numlist numberVarray);
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 NUMBERVARRAY
SQL>
SQL>
SQL>
SQL> update e
2 set numlist = numberVarray();
10 rows updated.
SQL>
SQL> drop table e;
Table dropped.
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL> drop type numberVarray;
Type dropped.
Stored VARRAYS
SQL> CREATE OR REPLACE TYPE numberVarryType AS VARRAY(10)OF NUMBER(10);
2 /
Type created.
SQL>
SQL> CREATE TABLE address_list (
2 list_id VARCHAR2(6)PRIMARY KEY,
3 direct_addresses numberVarryType
4 );
Table created.
SQL>
SQL> drop table address_list;
Table dropped.
SQL>
SQL>
UPDATE on varray column
SQL> CREATE OR REPLACE TYPE numberVarryType AS VARRAY(10)OF NUMBER(10);
2 /
Type created.
SQL>
SQL> CREATE TABLE address_list (
2 list_id VARCHAR2(6)PRIMARY KEY,
3 direct_addresses numberVarryType
4 );
Table created.
SQL>
SQL> UPDATE address_list
2 SET direct_addresses =numberVarryType(1011,1012,1013)
3 WHERE list_id ="OFF102";
0 rows updated.
SQL>
SQL>
SQL> drop table address_list;
Table dropped.
UPDATE on varray column in PL/SQL block
SQL>
SQL> CREATE OR REPLACE TYPE numberVarryType AS VARRAY(10)OF NUMBER(10);
2 /
Type created.
SQL>
SQL> CREATE TABLE address_list (
2 list_id VARCHAR2(6)PRIMARY KEY,
3 direct_addresses numberVarryType
4 );
Table created.
SQL>
SQL> DECLARE
2 v_add_varray numberVarryType :=numberVarryType(1011,1012,1013);
3 BEGIN
4 UPDATE address_list
5 SET direct_addresses =v_add_varray
6 WHERE list_id ="OFF102";
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table address_list;
Table dropped.
SQL>
SQL>
SQL>
Using a Varray Type to Define a Column in a Table
SQL>
SQL> CREATE Or Replace TYPE addressVarray AS VARRAY(2) OF VARCHAR2(50);
2 /
Type created.
SQL> CREATE TABLE employee (
2 id INTEGER PRIMARY KEY,
3 first_name VARCHAR2(10),
4 last_name VARCHAR2(10),
5 addresses addressVarray
6 );
Table created.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>