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:

SQL> CREATE TYPE Project AS OBJECT (
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:


BEGIN
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)));
END;


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



DECLARE
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));
BEGIN
UPDATE department
SET projects = new_projects WHERE dept_id = 60;
END;


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


DECLARE
my_projects ProjectList;
BEGIN
SELECT projects INTO my_projects FROM department
WHERE dept_id = 30;
...
END;


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

BEGIN
DELETE FROM department WHERE dept_id = 30;
END;

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
3CX PBX sync Windows Service Project Description: !! You require access to a commercial version of the 3CX PBX system in order to be able to program the API !! !! You requ

Collections:   The collection is an ordered group of elements, all of similar type (for illustration, the grades for a class of students). Each element has an exclusive subsc

LOOP Statements The LOOP statements execute a series of statements at multiple times. The loops enclose the series of statements that is to be repeated. The PL/SQL provides typ

Declaring Objects: You can use the object types wherever built-in types like CHAR or NUMBER can be used. In the block below, you can declare object r of type Rational. Then, yo

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

Declaring Cursor Variables Once a REF CURSOR type is define by you, and then you can declare the cursor variables of that type in any PL/SQL block or subprogram. In the exampl

Effects of NULL for Multiple Assignments - SQL If the row expression given as the source for a multiple assignment evaluates to NULL, then NULL is assigned to each target. If

Use of Table Expressions - Expressing Constraint Conditions With the exception of key constraints, the examples in the theory book all explicitly reference at least one relvar

Iterative Control: LOOP  Statements The LOOP statement executes a series of statements multiple times. There are 3 forms of LOOP statements: LOOP, WHILE-LOOP, & FOR-LOOP. LOOP

Sequential Control Dissimilar to the IF and LOOP statements, the GOTO and NULL statements are not important to the PL/SQL programming. The configuration of PL/SQL is such that th