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
Named Notation The second procedure call uses the named notation. An arrow (=>) serve as the relationship operator that associates the formal parameter to the left of the arro

Example of Alternative formulation as a table constraint Example: Alternative formulation as a table constraint ALTER TABLE EXAM_MARK ADD CONSTRAINT Must_be_enrolled_to_

Using RENAME in combination with JOIN - SQL Example gives pairs of ids of students having the same name, by joining two renamings of IS_CALLED. Example gives an equivalent ex

%TYPE Attribute The %TYPE attribute gives the datatype of a record, field, nested table, database column, or the variable. You can use the %TYPE attribute as the datatype speci

Comparison Operators The Comparison operators can compare one expression to another. The outcome is always true, false, or null. Usually, you use a comparison operators in condi

Biconditional - SQL The biconditional p ↔ q can be expressed in Tutorial D by p = q and the same is true of SQL. The question then arises as to whether, in SQL, p = q is equiv

ROWNUM The ROWNUM returns a number representing the order in which a row was selected from the table. The first row selected has a ROWNUM of 1; the second row has a ROWNUM of

I need SQL to infopath data connection Project Description: Want data retrieval connection from SQL to SharePoint infopath Skills required are Sharepoint, SQL

CHECK Constraints in SQL A CHECK constraint is a table constraint defined using the key word CHECK, as already illustrated in several examples in this chapter. In particular,

LEVEL You use the LEVEL with the SELECT CONNECT BY statement to categorize rows from a database table into a tree structure. The LEVEL returns the level number of a node in a