| General Information |
| A varray is a variable element array object. Memory to store varrays is taken from the PGA. |
| Related Data Dictionary Objects |
| col$ |
tab$ |
plitblm |
| dba_source |
all_source |
user_source |
| dba_types |
all_types |
user_types |
| dba_tables |
all_tables |
user_tables |
| dba_tab_cols |
all_tab_cols |
user_tab_cols |
| dba_nested_tables |
all_nested_tables |
user_nested_tables |
| dba_varrays |
all_varrays |
user_varrays |
|
| System Privileges |
| CREATE TYPE |
CREATE ANY TYPE |
DROP ANY TYPE |
|
| |
| Non-Object SQL |
| Define VARRAY as a TYPE |
CREATE OR REPLACE TYPE uwvarray AS VARRAY(5) OF NUMBER;
/
desc uwvarray
CREATE TABLE varraytab (
oddt_col VARCHAR2(20),
udva_col uwvarray);
desc varraytab |
| |
| Object SQL |
| Define Object as a TYPE |
CREATE OR REPLACE TYPE Project AUTHID DEFINER AS OBJECT (
project_no NUMBER(2),
title VARCHAR2(35),
cost NUMBER(7,2));
/
desc project
SELECT type_name, typecode, instantiable
FROM user_types; |
| Define VARRAY of object type |
CREATE OR REPLACE TYPE ProjectList AS VARRAY(50) OF
Project;
/
desc projectlist
SELECT type_name, typecode, instantiable
FROM user_types; |
| Create table based on VARRAY |
-- Create relational table department, which has a column of type ProjectList
CREATE TABLE department (
dept_id NUMBER(2),
dname VARCHAR2(15),
budget NUMBER(11,2),
projects ProjectList);
desc department
set describe depth all linenum on indent on
desc department |
| Inserting into the VARRAY containing table |
-- Each item in column projects is a varray that will store the projects
scheduled for a given department. Next populate relational table department. Then the varray constructor ProjectList() provides values for column projects
INSERT INTO department
VALUES(30, 'Accounting', 1205700,
ProjectList (Project(1, 'Design New Expense Report', 3250),
Project(2, 'Outsource Payroll', 12350),
Project(3, 'Evaluate Merger Proposal', 2750),
Project(4, 'Audit Accounts Payable', 1425)));
INSERT INTO department
VALUES(50, 'Maintenance', 925300,
ProjectList (Project(1, 'Repair Leak in Roof', 2850),
Project(2, 'Install New Door Locks', 1700),
Project(3, 'Wash Front Windows', 975),
Project(4, 'Repair Faulty Wiring', 1350),
Project(5, 'Winterize Cooling System', 1125)));
INSERT INTO department
VALUES(60, 'Security', 750400,
ProjectList (Project(1, 'Issue New Employee Badges', 13500),
Project(2, 'Find Missing IC Chips', 2750),
Project(3, 'Upgrade Alarm System', 3350),
Project(4, 'Inspect Emergency Exits', 1900)));
COMMIT;
SELECT * FROM department; |
| Delete Record with VARRAY |
-- In the final example, delete the Accounting Department and its project list from table department
DELETE FROM department WHERE dept_id = 30;
SELECT * FROM department; |
| |
| PL/SQL |
| Delete Record with VARRAY |
CREATE OR REPLACE TYPE Project IS OBJECT (
project_no NUMBER(2),
title VARCHAR2(35),
cost NUMBER(7,2));
/
DECLARE
TYPE ProjectList IS VARRAY(50) OF Project;
demo_t ProjectList;
BEGIN
NULL;
END;
/
-- the following generates an error
DECLARE
TYPE ProjectList IS VARRAY(50) OF Project;
demo_t ProjectList;
BEGIN
demo_t(1) := Project(1,'One',1);
END;
/
set serveroutput on
DECLARE
TYPE ProjectList IS VARRAY(50) OF Project;
demo_t ProjectList;
BEGIN
demo_t := ProjectList(NULL,NULL,NULL);
demo_t(1) := Project(1,'One',1);
demo_t(2) := Project(2,'Two',2);
dbms_output.put_line(demo_t(2).title); -- value of varray element
dbms_output.put_line(demo_t.COUNT); -- current count of elements in varray
dbms_output.put_line(demo_t.LIMIT); -- max elements in varray
IF demo_t.EXISTS(2) THEN
dbms_output.put_line('TRUE');
END IF;
END;
/ |
|
