Database Interaction with PL/SQL, part 3 - Using DML Commands on NESTED TABLES Within PL/SQL
(Page 5 of 5 )
Just like in SQL, we can also insert, update or delete NESTED TABLE information from within PL/SQL, but using typed variables. Let us consider the following example which updates a row in the same table.
declare
v_subjects SubjectList;
v_name employee.name%type := '&name';
begin
v_subjects := SubjectList('all databases','all languages');
update employee set Subjects = v_subjects
where name=v_name;
dbms_output.put_line('Updated succesfully...');
end;
I hope the example above is very much clear. And even in the same manner we can also insert into the same table something like the following:
declare
v_subjects SubjectList;
v_name employee.name%type := '&name';
begin
v_subjects := SubjectList('all databases','all languages');
insert into employee values(v_name, v_subjects);
dbms_output.put_line('inserted succesfully...');
end;
Actually, it is bit difficult to do some kinds of DML operations on NESTED TABLEs (and on the individual elements within the NESTED TABLE especially). Though I am not covering much of the SQL here, we can overcome the complexity with vigorous practice of SQL.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
