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

Assignment Help:

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;


Related Discussions:- Manipulating individual elements - pl/sql

Data types in sql - integer, Data Types in SQL - Integer INTEGER or  s...

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

Sql outer join, SQL outer join SELECT * FROM IS_CALLED NATURAL LEFT...

SQL outer join SELECT * FROM IS_CALLED NATURAL LEFT JOIN IS_ENROLLED_ON Note that adding LEFT to an invocation of CROSS JOIN has no effect unless the right-hand operand

Advantages of wrapping, Advantages of Wrapping   The PL/SQL Wrapper co...

Advantages of Wrapping   The PL/SQL Wrapper convert the PL/SQL source code into a transitional form of the object code. By hiding the application internals, the Wrapper secure

Rollback behavior - bulk bind performance improvement, Rollback Behavior ...

Rollback Behavior When a FORALL statement fails, the database changes are rolled back to an implicit savepoint marked before each of the SQL statement execution. The Changes t

Currval and nextval - sql pseudocolumns, CURRVAL and NEXTVAL The serie...

CURRVAL and NEXTVAL The series is a schema object which generates the sequential numbers. Whenever you form a sequence, you can specify its primary value and an increment. T

Updating by insertion, Updating by insertion Syntax : INSERT IN...

Updating by insertion Syntax : INSERT INTO ENROLMENT VALUES (SID ('S4'), 'Devinder', CID ('C1'));

Commit statement in pl sql, COMMIT Statement The COMMIT statement expli...

COMMIT Statement The COMMIT statement explicitly makes everlasting changes to the database during the present transaction. The Changes made to the database are not considered e

Theory of eternity of life - origin of life, THEO R Y OF ETERNITY OF LIFE...

THEO R Y OF ETERNITY OF LIFE (PRAYER - 1880) - The theory of eternity of life, also called the steady-state theory , states that life has ever been in existence as at presen

Using rollback - , Using ROLLBACK The ROLLBACK statements end the pres...

Using ROLLBACK The ROLLBACK statements end the present transaction and undo any change made during the transaction. The Rolling back is helpful for two reasons. Firstly, if yo

Effects of null in table literal, Effects of NULL in Table Literal Whe...

Effects of NULL in Table Literal When a VALUES expression appears as the source value for an SQL INSERT statement, the key word NULL can appear as a field value, such that for

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd