HomeOracle Page 5 - Database Interaction with PL/SQL, part 3
Using DML Commands on NESTED TABLES Within PL/SQL - Oracle
Jagadish Chatarji has been writing about database interactions with Oracle PL/SQL. The last part started on TYPE, RECORD, and TABLE declarations of PL/SQL. This one now goes further into TABLE, RECORD, and using them together. It will also introduce NESTED TABLES.
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:
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.