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
Components of an Object Type: An object type encapsulates the operations and data. Therefore, you can declare the methods and attributes in an object type specification, but no

Nested Tables versus Index-by Tables The Index-by tables and nested tables are just similar. For e.g.  They have similar structure and their individual elements are accessed in

Cursors The Oracle uses work areas to execute the SQL statements and to store process information. A PL/SQL construct known as the cursor. Let's you assume name a work area and

i NEED TO CREATE 3 guiS IN pl/sql sERVER PAGE FORM

SQL Functions The PL/SQL uses all the SQL functions involving the following aggregate functions that summarize the whole columns of the Oracle data: GROUPING, AVG, COUNT, STDDE

SSRS Report Writing Project Description: This report is part of a larger project to make a SQL Server Reporting Service (SSRS) based reporting solution. There can be more rep

ORIGIN OF EARTH - BIG-BAN G HYPOTHESIS - Origin of life is linked to origin of earth. Cosmos, the Universe originated 10-20 billion years ago by Big Bang (thermonu

Procedural Constraint Enforcement (Triggers) SQL has an alternative method of addressing database integrity, involving event-driven procedural code. The special procedures th

DBMS_PIPE: The Package DBMS_PIPE allows various sessions to communicate over the named pipes. (A pipe is a region of memory used by one of the process to pass information to

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