Some varray examples-manipulating collections, PL-SQL Programming

Some Varray Examples

In SQL Plus, assume that you define an object type Project, as described below:

2 project_no NUMBER(2),
3 title VARCHAR2(35),
4 cost NUMBER(7,2));

After that, you define VARRAY type ProjectList that stores the Project objects:

SQL> CREATE TYPE ProjectList AS VARRAY(50) OF Project;

At last, you create relational table department that has a column of the type ProjectList, as shown:

SQL> CREATE TABLE department (
2 dept_id NUMBER(2),
3 name VARCHAR2(15),
4 budget NUMBER(11,2),
5 projects ProjectList);

Each and every item in the column projects is the varray that will store the scheduled projects for a given department. Now, you are ready to populate the relational table department. In the illustration below, notice that how the varray constructor ProjectList() gives values for the 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)));

In the illustration below, you update the list of the projects assigned to the Security Department:

new_projects ProjectList :=
ProjectList(Project(1, ’Issue New Employee Badges’, 13500),
Project(2, ’Develop New Patrol Plan’, 1250),
Project(3, ’Inspect Emergency Exits’, 1900),
Project(4, ’Upgrade Alarm System’, 3350),
Project(5, ’Analyze Local Crime Stats’, 825));
UPDATE department
SET projects = new_projects WHERE dept_id = 60;

In the next illustration, you recover all the projects for the Accounting Department into a local varray:

my_projects ProjectList;
SELECT projects INTO my_projects FROM department
WHERE dept_id = 30;

In the final illustration, you delete the Accounting Department and its project list from the table department:

DELETE FROM department WHERE dept_id = 30;

Posted Date: 10/3/2012 6:43:03 AM | Location : United States

Related Discussions:- Some varray examples-manipulating collections, Assignment Help, Ask Question on Some varray examples-manipulating collections, Get Answer, Expert's Help, Some varray examples-manipulating collections Discussions

Write discussion on Some varray examples-manipulating collections
Your posts are moderated
Related Questions
(a) What are decision support systems, and what role do they play in the business environment? (b) Data warehousing is defined as "a subject-oriented, integrated, non-volatile c

Oracle 11 G new features associated with this release:- Enhanced ILM  - Information Lifecycle Management (ILM) has been around for the almost 10 years, but Oracle has made

EXECUTE IMMEDIATE Statement   The EXECUTE IMMEDIATE statement prepare (parses) and instantly executes a dynamic SQL statement or an anonymous PL/SQL block. Syntax:

DBMS_OUTPUT: The Package DBMS_OUTPUT enables you to display output from the PL/SQL subprograms and blocks, that makes it easier to test and debug them. The procedure put_ line

Effects of NULL for Table Expression Here's an important distinction between expressions denoting tables and expressions denoting multisets of rows: a table expression cannot

Defining REF CURSOR Types To make cursor variables, you take 2 steps. At first, you define a REF CURSOR type, and then declare the cursor variables of that type. You can defin

Obtaining a natural join by specifying the common columns Synatax: SELECT * FROM IS_CALLED JOIN IS_ENROLLED_ON USING ( StudentId ) However, a named columns join doe

Semijoin and Composition - SQL For semijoin, the dyadic relational operator MATCHING, defined thus: r1 MATCHING r2, where r1 and r2 are relations such that r1 JOIN r2 is de

%NOTFOUND The %NOTFOUND is logical, opposite of the %FOUND. The %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE when the final fetch failed to return a row. I

Use the MASCOT tables CREDITRS, PORDS and PAYMENTS to write SQL queries to solve the following business problems. These tables / data are available to you via the USQ Oracle server