PL/SQL Tutorial #14 - Interaction of

Collections With the Database

This series of introductory Oracle PL/SQL tutorials is designed to show how to to build your applications better. faster and smarter. This series of PL/SQL tutorials has covered data types; language elements; cursors and collections. See here for previous articles. This tutorial demonstrates the population of collections from the database and updating the database with the contents of the collection.

Populating PL/SQL

Collections From

Oracle

To demonstrate how to do this, let's start with a very simple example - an associative array which is a one-dimensional collection of employee names. First we need to create a user-defined PL/SQL type as Oracle does not allow collection-type variables to be declared in 1 step - i.e. you can't just write something like: emp table of varchar2(50) index by binary_integer; Here is our type declaration: type emp_tab is table of varchar2(50)                        index by binary_integer; Next we declare the associative array variable:  emp emp_tab; With those 2 definitions in place we can now write the code to populate our PL/SQL table from Oracle: for emp_rec in (select last_name from employees) loop     emp(1) := emp_rec.last_name; end loop; As you can see, the code to populate the collection is very simple - just an assignment statement inside a cursor for loop. In this case, it's a bit too simple! Every name retrieved from the database is stored in the same cell, which means that only the last employee name retrieved from the database is accessible in this PL/SQL associative array. Fortunately, we can remedy this quickly and easily by declaring another variable to use as an index into the array. Here is (the whole of) our modified code: declare    type emp_tab is table of varchar2(50) index by binary_integer;    emp emp_tab; -- associative array variable    i   binary_integer; -- array index variable begin    i := 1;    for emp_rec in (select last_name from employees) loop        emp(i) := emp_rec.last_name;        i := i + 1;    end loop; end; Notice that we started filling our PLSQL table at cell #1 but we could just have easily started at -1 or 100 or in fact any number within the range of valid binary integers (refer to PL/SQL tutorial #12 for further discussion of associative arrays).

Updating Oracle From PL/SQL Collections

Now that we've seen how to load data from the database into our collection, we can look at how to write data from a PL/SQL collection back into the Oracle database. To do this we only need a minor modification to our code - we just need to add an update statement at the appropriate point. update employees set last_name = emp(2) where last_name = emp(1); To make this more sensible and easier to understand, let's change our collection to store employee salaries rather than names and use the employee id as the index in to our table to save us from having to store the employee id. Our PL/SQL code now looks like this: declare    type sal_tab is table of varchar2(50) index by binary_integer;    sal sal_tab; begin    /* first get the salary for each employee */   for emp_rec in   (select salary,emp_id from employees order by emp_id)    loop        sal(emp_rec.emp_id) := emp_rec.salary;    end loop;    /* now let's give everyone a 10% rise! */    for this_emp in sal.first..sal.last loop        sal(this_emp) := sal(this_emp)*1.1;        update employees set salary = sal(this_emp)        where emp_id = this_emp;    end loop; end; One word of caution though. This is a very simple example designed to demonstrate the constructs if PL/SQL rather than how to update a table in the Oracle database. In this particular example, our little program would run a lot faster and be much easier to maintain if we dispensed with all the PL/SQL and just issued a straight update statement in SQL like this:        update employees set salary = salary*1.1 Just because you can use PL/SQL to do a particular task doesn't mean that using PL/SQL is the best way to do it! Also note that this is not the most efficient nor succinct way to populate a collection with a large volume of data read from the database or to write a large collection back to Oracle. The Bulk Collect and For All  clauses are designed for that and these will be covered in a future tutorial. The next tutorial looks at the use of reading/writing multilevel PL/SQL collections from/to Oracle (coming soon). PL/SQL tutorial #13- multi-level collections PL/SQL tutorial #15 - read from/write to Oracle  --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our scheduled Oracle training courses, or let us know your requirements. Looking for more Oracle tips and tricks? For no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our newsletter. Jam-packed full of tips and tricks, it will help you make your Oracle systems faster and more reliable and save you hours searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox. Smartsoft Computing Ltd, Bristol, England  Need help with your Oracle systems? Contact Us View our privacy policy This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. © Copyright Smartsoft Computing Ltd. All rights reserved.