Already have an account? Get multiple benefits of using own account!
Login in your account..!
Remember me
Don't have an account? Create your account in less than a minutes,
Forgot password? how can I recover my password now!
Enter right registered email to receive password!
Manipulating Individual ElementsFaraway 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:BEGININSERT INTOTABLE(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:DECLAREadjustment INTEGER DEFAULT 1;BEGINUPDATE TABLE(SELECT courses FROM departmentWHERE name = ’Psychology’)SET credits = credits + adjustmentWHERE 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:DECLAREmy_course_no NUMBER(4);my_title VARCHAR2(35);BEGINSELECT course_no, title INTO my_course_no, my_titleFROM TABLE(SELECT courses FROM departmentWHERE name = ’History’)WHERE course_no = 3105;...END;In the next illustration, you delete all 5-credit courses offered by the English Department:BEGINDELETE TABLE(SELECT courses FROM departmentWHERE 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:DECLAREmy_cost NUMBER(7,2);my_title VARCHAR2(35);BEGINSELECT cost, title INTO my_cost, my_titleFROM TABLE(SELECT projects FROM departmentWHERE 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) ASmy_projects ProjectList;BEGINSELECT projects INTO my_projects FROM departmentWHERE 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 LOOPmy_projects(i + 1) := my_projects(i);END LOOP;my_projects(position) := new_project; -- add new projectUPDATE department SET projects = my_projectsWHERE 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) ASmy_projects ProjectList;BEGINSELECT projects INTO my_projects FROM departmentWHERE 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 LOOPIF my_projects(i).project_no = proj_no THENIF new_title IS NOT NULL THENmy_projects(i).title := new_title;END IF;IF new_cost IS NOT NULL THENmy_projects(i).cost := new_cost;END IF;EXIT;END IF;END LOOP;UPDATE department SET projects = my_projectsWHERE dept_no = dept_id;END update_project;
Using EXTEND To enlarge the size of a collection, use EXTEND. This process has 3 forms. The EXTEND appends one null element to a collection. And the EXTEND(n) appends n null e
%ISOPEN The Oracle closes the SQL cursor automatically after executing its related SQL statement. As a result, the %ISOPEN forever yields FALSE.
Oracle 10G new features:- Automatic Database Diagnostic Monitor System Advancements - these methods will provides several methods for extracting reports through the Automatic
Creating and Destroying Base Tables: Example shows an SQL command to create the base table counterpart of the ENROLMENT variable Example Creating a base table. CREATE T
I want someone to write a TSQL function that returns the name of the ODBC DSN. I will use the queries below, to get information about the connection, but none of these return th
LOB Types The large object (LOB) datatypes like BFILE, BLOB, CLOB, and NCLOB store the blocks of unstructured data (like graphic images, text, video clips, and sound waveforms)
Using FOR UPDATE If you declare a cursor which will be referenced in the CURRENT OF clause of an UPDATE or DELETE statement, you should use the FOR UPDATE clause to obtain an
Expressions An expression is a randomly complex combination of the constants, variables, literals, operators, & function calls. The simplest expression is the single variabl
LAWS / RULES - Dollo's Law : Living organisms do exhibit evolutionary irreversibility or evolution is irreversible. Williston's Law
set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_divide then d
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!
whatsapp: +91-977-207-8620
Phone: +91-977-207-8620
Email: [email protected]
All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd