Re: pl/sql table

From: Ivan Samuelson <bolski_at_indy.net>
Date: 1996/07/14
Message-ID: <31e98192.392882_at_news.indy.net>

On Thu, 11 Jul 1996 08:54:30 GMT, jingram_at_teleport.com (Jonathan W. Ingram) wrote:

>Before I could do anything about it, Ayokunle Giwa <akgiwa_at_cnct.com>
>wrote:
>
>>Hi all,
>> I'd really appreciate someone out there explaining to me the basics
>>of a pl/sql table its uses and methods .........Thanx ....Ayokunle Giwa
>
>It's very simple. A PL/SQL table is like an array. The only
>difference is that an array is actually useful :-) The only time I've
>had opportunity to use PL/SQL tables, I had to use (and keep track of,
>oh vey!) 2 dozen of them. As much as I like PL/SQL, it has a long way
>to go when it comes to array-like processing.

Actually, Jonathan, PL/SQL tables are a lot more useful than you might think. True, a PL/SQL table is LIKE a 1-dimensional array. However, unlike an array, it is a sparse structure. You only use the memory that you need. It's unlimited in it's size. Well, it IS limited by the amount of memory you have.

If you need to hold 100 items, then you must declare an array of that size with your normal programming langauges (such as C or Pascal or Basic). However, there really is no way to search the array other than a sequential search UNLESS you use hashing or a binary search.

With a PL/SQL table, the index is a binary index. The indexes do NOT have to be in sequential order. I can declare a PL/SQL table as such:

     TYPE error_table_typ IS TABLE of NUMBER(7)
          INDEX BY BINARY_INTEGER;

     my_table error_table_typ;

I could then store numbers as follows:

	my_table(1) := 100;
        my_table(100) := 205;
        my_table(150) := 1;

All that is in memory are these three "records". That's it. Now, if I wanted to store them this way in an array in C or Pascal, I would have to create an array of at least 150 elements. Of course, some of you may be thinking, "WHY would I want to store elements out of order?" Well, a PL/SQL table works great if you have data you have to constantly verify against. Selecting against a PL/SQL table is a LOT faster than having to constantly go against a table in the database. Loading the table into a PL/SQL table and then using the PL/SQL table to verify your data is much faster. THIS is where a PL/SQL table comes in handy.

Let's say you have a table that has an ID to indentify a business. You want the business name. The ID's are NOT neccessarily in a sequential order. But, they ARE numbers. Here's the table:

        COMPANY_TABLE

	company_id_nbr    NUMBER(8);
        company_name_txt VARCHAR2(200);

We create a PL/SQL table as follows:

     DECLARE
	TYPE company_name_table_typ IS TABLE of VARCHAR2(200)
	     INDEX BY BINARY_INTEGER;

	my_table company_name_table_typ;

        CURSOR my_cursor IS
           SELECT company_id_nbr, company_name_txt
             FROM company_table;

        my_cursor_rec my_cursor%ROWTYPE;

     BEGIN
        OPEN my_cursor;
        FETCH my_cursor
         INTO my_cursor_rec;

        WHILE my_cursor%FOUND
        LOOP
           my_table(my_cursor_rec.company_id_nbr) :=
              my_cursor_rec.company_name_txt;

           FETCH my_cursor
            INTO my_cursor_rec;
        END LOOP;
        CLOSE my_cursor;
     END;

Put this code into a procedure before you're main code runs (make the PL/SQL tables global, of course). Then, if we ever need to verify the company name and all we have is the id, we can then do the following:

	DECLARE
           name_txt VARCHAR2(200);
           id_nbr   NUMBER(8);
        BEGIN
           name_txt := my_table(id_nbr);

           ...

        EXCEPTION
           WHEN no_data_found
           THEN
              /* This will happen if the record does NOT exists */
              /* in the PL/SQL table. */

        END;

This is MUCH faster than going against the table in the database directly. THIS is the beauty of PL/SQL tables. At first, I too felt they were useless. Now, I've gone back to many programs I've written and rewrote them to use PL/SQL tables with this kind of programming. I have noticed a 25%+ increase in the speed in the verification routines of my programs, which also decreases the amount of time my programs run (especially my overnight processes that are huge).

With PL/SQL 2.3, you will be able to have PL/SQL tables with more than one column which means you can create a PL/SQL table that is a direct copy of a record of a table in the database. Until then, if you use PL/SQL version before 2.3, then you won't be able to have more than one column per PL/SQL table. But, you just have to create a PL/SQL table for each column that has a different type and index each table by the same index. It's that simple.

Hope this helps the first poster and sheds some light for you, Jonathan. I felt the same way you did until I read about PL/SQL tables in the Nutshell book Oracle PL/SQL Programming by Steven Feuerstein. I whole-heartidly endorse this book as THE book for all Oracle developers. It's helped me out of many situations.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Ivan Samuelson, Consultant       *   LET 'ER RIP! GO HORSE!
Profound Consulting              *   bolski_at_indy.net
http://www.indy.net/~profound    *   http://chaos.taylored.com/home/bolski/

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Received on Sun Jul 14 1996 - 00:00:00 CEST