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
Advantages of PL/SQL The PL/SQL is a high-performance transaction processing, completely portable language that offers the following advantages as shown: 1) Support for SQL

Write a pl/sql block that declares and uses cursors with parameters. In a loop, use a cursor to retrieve the department number and the department name from the departments table

Example of Cast Operator So long as CAST is used as shown, we could obtain the total marks for each exam in similar fashion, using SUM (Mark) AS TotalMarks. However, this giv

PITS Depressions in secondary cell wall is called pit. A pit present on the free cell wall surface without its partner is called Blind pit. It consists of 2 parts -

Change Sql file into CSV for product registration on Magento Project Description: I have a set of files that are in Sql format and could like for a developer to help me with

Introduction Oracle 9i - it was made public in the year 2001 with over 400 features, and graphics, it has merged the traditional business with modern internet application

FOR-LOOP While the number of iterations through a WHILE loop is unknown till the loop completes, then the number of iterations through a FOR loop is known before the loop is ent

Data Types in SQL - Integer INTEGER or  synonymously INT, for integers within a certain range. SQL additionally has types SMALLINT and BIGINT for certain ranges of integers. T

Committing and Rolling Back The COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. As the figure shows, if one transaction ends,

Deriving Predicates from Predicates in SQL The corresponding section in the theory book describes how predicates can be derived from predicates using (a) the logical connectiv