Oracle PLSQL Tutorial (Part 14)

This series of introductory Oracle PLSQL tutorials on is designed to show how to make effective use of PL/SQL to simplify your Oracle systems. Up 'till now in this series of PLSQLtutorials we've looked at: data types; language elements; cursors, collections, etc. (See here for previous articles). The previous tutorial looked at writing to collections in PL/SQL, so now let's look at how to populate them from the database

Populating PLSQL 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 PLSQL type as Oracle does not allow collection-type variables to be declared in 1 step (i.e. you can't just write something like this emp table of varchar2(50)
index by binary_integer)

type emp_tab is
table of varchar2(50)
index by binary_integer;

Next we declare the associative array variable itself: emp emp_tab;

With those 2 definitions in place we can now write the code to populate our PLSQLtable:

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 PLSQLassociative 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;

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 PLSQLtutorial part 12 for further discussion of PLSQLassociative arrays).

Updating Oracle From PLSQL 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 PLSQLroutine - just 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 PLSQLroutine 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 show the constructs if PLSQLrather 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 PLSQLand just issued a straight update statement like this:

update employees set salary = salary*1.1

Just because you can use PLSQL to do a particular task doesn't mean that using PLSQL is the best way to do it!

-------------

The next part of this Oracle PLSQL tutorial looks at reading/writing multilevel PLSQL collections from/to Oracle. Click here for PLSQL tutorial Part 15 (coming soon).

See Oracle PLSQL tutorial Part 13 for an introduction to multilevel PLSQL collections.

---------------------------------------

Looking for more Oracle tips and tricks? For clear, concise, technical information, to lighten the load and take you form beginner to pro just subscribe to our ezine.

Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320

Contact Us

Click here to view our privacy policy .

This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.