Manipulating individual elements - pl/sql, PL-SQL Programming

Manipulating Individual Elements

Faraway you have manipulated an entire collection. Within the SQL, to manipulate the individual elements of the collection, and then use the operator TABLE. The operand of TABLE is a subquery which returns a single column value for you to manipulate. That the value is the nested table or the varray.
In the illustration below, you add a row to the History Department nested table stored in the column courses:



BEGIN
INSERT INTO
TABLE(SELECT courses FROM department WHERE name = ’History’)
VALUES(3340, ’Modern China’, 4);
END;


In the next illustration, you revise the number of credits for two courses offered by the Psychology Department:

DECLARE
adjustment INTEGER DEFAULT 1;
BEGIN
UPDATE TABLE(SELECT courses FROM department
WHERE name = ’Psychology’)
SET credits = credits + adjustment
WHERE course_no IN (2200, 3540);
END;

In the following illustration, you retrieve the number and the title of a specific course offered by the History Department:

DECLARE
my_course_no NUMBER(4);
my_title VARCHAR2(35);
BEGIN
SELECT course_no, title INTO my_course_no, my_title
FROM TABLE(SELECT courses FROM department
WHERE name = ’History’)
WHERE course_no = 3105;
...
END;


In the next illustration, you delete all 5-credit courses offered by the English Department:

BEGIN
DELETE TABLE(SELECT courses FROM department
WHERE name = ’English’)
WHERE credits = 5;
END;



In the following illustration, you recover the title and cost of the Maintenance Department’s fourth project from the varray column projects:

DECLARE
my_cost NUMBER(7,2);
my_title VARCHAR2(35);
BEGIN
SELECT cost, title INTO my_cost, my_title
FROM TABLE(SELECT projects FROM department
WHERE dept_id = 50)
WHERE project_no = 4;
...
END;


Presently, you cannot reference the individual elements of a varray in an UPDATE, INSERT, or DELETE statement. And hence, you should use the PL/SQL procedural statements. In the illustration below, the stored procedure add_project inserts a new project into the department’s project list at a given position a shown:

CREATE PROCEDURE add_project (
dept_no IN NUMBER,
new_project IN Project,
position IN NUMBER) AS
my_projects ProjectList;
BEGIN

SELECT projects INTO my_projects FROM department
WHERE dept_no = dept_id FOR UPDATE OF projects;
my_projects.EXTEND; -- make room for new project
/* Move varray elements forward. */
FOR i IN REVERSE position..my_projects.LAST - 1 LOOP
my_projects(i + 1) := my_projects(i);
END LOOP;
my_projects(position) := new_project; -- add new project
UPDATE department SET projects = my_projects
WHERE dept_no = dept_id;
END add_project;

The stored procedure updates below for a given project is:

CREATE PROCEDURE update_project (
dept_no IN NUMBER,
proj_no IN NUMBER,
new_title IN VARCHAR2 DEFAULT NULL,
new_cost IN NUMBER DEFAULT NULL) AS
my_projects ProjectList;
BEGIN
SELECT projects INTO my_projects FROM department
WHERE dept_no = dept_id FOR UPDATE OF projects;
/* Find project, update it, then exit loop immediately. */
FOR i IN my_projects.FIRST..my_projects.LAST LOOP
IF my_projects(i).project_no = proj_no THEN
IF new_title IS NOT NULL THEN
my_projects(i).title := new_title;
END IF;
IF new_cost IS NOT NULL THEN
my_projects(i).cost := new_cost;
END IF;
EXIT;
END IF;
END LOOP;
UPDATE department SET projects = my_projects
WHERE dept_no = dept_id;
END update_project;

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







Related Discussions:- Manipulating individual elements - pl/sql, Assignment Help, Ask Question on Manipulating individual elements - pl/sql, Get Answer, Expert's Help, Manipulating individual elements - pl/sql Discussions

Write discussion on Manipulating individual elements - pl/sql
Your posts are moderated
Related Questions
Bulk Binds advantages In the Embedded Oracle RDBMS, the PL/SQL engines accept any valid PL/SQL subprogram or block. As the figure shows, the PL/SQL engine executes all procedur

Data Types in SQL - XML, Array, Row BINARY LARGE OBJECT for arbitrarily large bit strings. XML for XML documents and fragments. ARRAY types for arrays.

Multiset types - SQL An SQL multiset is what in mathematics is also known as a bag-something like a set except that the same element can appear more than once. The body of an

Table Literals - SQL One might expect SQL to support table literals in the manner illustrated in Example 2.2, but in fact that is not a legal SQL expression. Example: Not a

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

Opening a Cursor Variable The OPEN-FOR statement relates a cursor variable with the multi-row query, executes the query, and then identifies the result set. The syntax for ope

Recursion The Recursion is a powerful method for simplify the design of the algorithms. Principally, the recursion means the self-reference. In the recursive mathematical serie

Quantification in SQL To quantify something, as the theory book has it, is to state its quantity, to say how many of it there are. For example, in Tutorial D the expression CO

Example of NOT EXISTS in SQL Example: Use of NOT EXISTS CREATE ASSERTION Must_be_enrolled_to_take_exam_alternative1 CHECK ( NOT EXISTS (SELECT StudentId, CourseId

Fetching with a Cursor The FETCH statements retrieve the rows in the result set one at a time. After each and every fetch, the cursor advance to the next row in the result set