JL Computer Consultancy

Historically PL/SQL has had limited value for large-scale row handling because of its inherent inefficiency and its limitation of single-row processing. This changed with the arrival of array processing in Oracle Version 8.0 (and has already changed again in 8.1)

This article is a brief demonstration of using array processing in PL/SQL, taking the example from the dbmssql script ($ORACLE_HOME/rdsbm/admin/dbmssql.sql) as a basis, and producing an array based equivalent.

rem
复制

rem     Script:        pl_array.sql
复制

rem     Author:        Jonathan Lewis
复制

rem     Dated:         12th-April-1999
复制

rem     Purpose:       Simple demo of pl/sql arrays in Oracle 8.0
复制

rem
复制

rem            This example is (vaguely) web-search related,
复制

rem            demonstrating how to select rows and insert them
复制

rem            into a holding table in order, but numbering them
复制

rem            as they are inserted so that subsequent web-calls
复制

rem            can ask for 'rows 12-22'.
复制

rem
复制

rem
复制

rem     A table to use as the sample Ddta
复制

rem
复制

create table ob1 
复制

unrecoverable
复制

as
复制

        select  name, 0 line_no 
复制

        from    sys.obj$ 
复制

        where   rownum <= 25;
复制

rem
复制

rem     rownum < 1 : my favourite way of creating an empty table
复制

rem
复制

create table ob2 as
复制

        select  *
复制

        from    ob1
复制

        where   rownum < 1;
复制

DECLARE
复制

        src_cur        pls_integer;
复制

        dest_cur       pls_integer;
复制

        src_rows       pls_integer;
复制

        dest_rows      pls_integer;
复制

 
复制

        -- Two of the new datatypes in the dbms_sql package
复制

        -- An array of varchar2, and an array of number
复制

 
复制

        name_tbl       dbms_sql.varchar2_table;
复制

        line_tbl       dbms_sql.number_table;
复制

        array_size     number := 10;
复制

begin
复制

        for ct in 1..array_size loop
复制

               line_tbl(ct) := ct;
复制

        end loop;
复制

        src_cur := dbms_sql.open_cursor;
复制

        dbms_sql.parse(
复制

                       src_cur, 
复制

                       'select name from ob1 order by name', 
复制

                       dbms_sql.V7
复制

        );
复制

        dest_cur := dbms_sql.open_cursor;
复制

        dbms_sql.parse(
复制

                       dest_cur,
复制

                       'insert into ob2(name,line_no) values(:v1,:v2)',
复制

                       dbms_sql.v7
复制

        );
复制

        --  Prepare the data from the source cursor
复制

        src_rows := dbms_sql.execute(src_cur);
复制

        loop
复制

        -- Associate the name_tbl array with column 1 of the source cursor,
复制

        -- and specify 'rows' 1 to 10 (array_size) as the boundaries of the
复制

        -- section of the array that we want to use for the fetch.
复制

 
复制

               dbms_sql.define_array(src_cur, 1, name_tbl, array_size, 1);
复制

               src_rows := dbms_sql.fetch_rows(src_cur);
复制

        -- Exit if there were no more rows to fetch
复制

               if (src_rows = 0) then
复制

                       exit;
复制

               end if;
复制

        -- Move the values from the 1st column of the cursor
复制

        -- into the name_tbl array
复制

               dbms_sql.column_value(src_cur, 1, name_tbl);
复制

        -- Associate the name_tbl array with bind-variable v1 of dest_cur,
复制

        -- and limit the use of the array to be from 1 to the number of 
复制

        -- rows fetched by the source cursor.
复制

        -- Repeat to associate the line_tbl array with bind variable v2.
复制

               dbms_sql.bind_array(dest_cur,'v1',name_tbl,1,src_rows);
复制

               dbms_sql.bind_array(dest_cur,'v2',line_tbl,1,src_rows);
复制

               dest_rows:=dbms_sql.execute(dest_cur);
复制

        -- If the number of rows fetched filled the space we 
复制

        -- specified, then there may be more to fetch, but if
复制

        -- not, then we want to drop out now.
复制

               if (src_rows < array_size) then
复制

                       exit;
复制

               end if;
复制

        --  Bump up the line_tbl values by the size of the array we are
复制

        --  using so that each row inserted gets a proper sequence number
复制

               for ct in 1..array_size loop
复制

                       line_tbl(ct) := line_tbl(ct) + array_size;
复制

               end loop;
复制

        end loop;
复制

        dbms_sql.close_cursor(src_cur);
复制

        dbms_sql.close_cursor(dest_cur);
复制

end;
复制

.
复制

/
复制

rem
复制

rem     Take a look at the results
复制

rem
复制

select * from ob2;
复制