Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL

Re: PL/SQL

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Tue, 14 Sep 1999 10:07:44 +0200
Message-ID: <7rkvpr$bt$1@oceanite.cybercable.fr> 





Here's the doc on PL/SQL tables:



PL/SQL Tables





Description


PL/SQL tables are objects of type TABLE, which are modelled as (but not the same
as) database tables. PL/SQL tables use a primary key to give you array-like
access to rows. Like an array, a PL/SQL table is an ordered collection of
elements of the same type. Each element has a unique index number that
determines its position in the ordered collection.
However, PL/SQL tables differ from arrays in two important ways. First, arrays
have fixed lower and upper bounds, but PL/SQL tables are unbounded. So, the size
of a PL/SQL table can increase dynamically. Second, arrays require consecutive
index numbers, but PL/SQL tables do not. So, a PL/SQL table can be indexed by
any series of integers. For more information, see "PL/SQL Tables" .



To create PL/SQL tables, you must take two steps. First, you define a TABLE
type, then declare PL/SQL tables of that type.




Syntax


table_type_definition ::=



TYPE table_type_name IS TABLE OF

   {  cursor_name%ROWTYPE


    | record_type_name




    | record_name%TYPE


    | scalar_type_name


    | table_name%ROWTYPE


    | table_name.column_name%TYPE


    | variable_name%TYPE} [NOT NULL] INDEX BY BINARY_INTEGER;


plsql_table_declaration ::=




plsql_table_name table_type_name;



Keyword and Parameter Description



table_type_name


This identifies a user-defined type specifier, which is used in subsequent
declarations of PL/SQL tables.



cursor_name


This identifies an explicit cursor previously declared within the current scope.



record_type_name


This identifies a RECORD type previously defined within the current scope. For
more information, see "User-Defined Records" on page 4 - 19.



record_name


This identifies a user-defined record previously declared within the current
scope.



scalar_type_name


This identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or
VARCHAR2, which must be specified without constraints. For more information, see
"Datatypes" .



table_name


This identifies a database table (or view) that must be accessible when the
declaration is elaborated.



table_name.column_name


This identifies a database table and column that must be accessible when the
declaration is elaborated.



variable_name


This identifies a PL/SQL variable previously declared within the current scope.



%ROWTYPE



This attribute provides a record type that represents a row in a database table
or a row fetched from a previously declared cursor. Fields in the record and
corresponding columns in the row have the same names and datatypes.



%TYPE



This attribute provides the datatype of a previously declared record, database
column, or variable.



INDEX BY BINARY INTEGER



The index of a PL/SQL table must have datatype BINARY_INTEGER, which can
represent signed integers. The magnitude range of a BINARY_INTEGER value
is -2147483647 .. 2147483647.



plsql_table_name


This identifies an entire PL/SQL table.



Usage Notes


You can define TABLE types in the declarative part of any block, subprogram, or
package. To specify the element type, you can use %TYPE or %ROWTYPE.
A PL/SQL table is unbounded; its index can include any BINARY_ INTEGER value.
So, you cannot initialize a PL/SQL table in its declaration. For example, the
following declaration is illegal:




sal_tab SalTabTyp := (1500, 2750, 2000, 950, 1800);  -- illegal
The INDEX BY clause must specify datatype BINARY_INTEGER, which has a magnitude
range of -2147483647 .. 2147483647. If the element type is a record type, every
field in the record must have a scalar datatype such as CHAR, DATE, or NUMBER.



You can declare PL/SQL tables as the formal parameters of functions and
procedures. That way, you can pass PL/SQL tables to stored subprograms and from
one subprogram to another.



PL/SQL tables follow the usual scoping and instantiation rules. In a package,
PL/SQL tables are instantiated when you first reference the package and cease to
exist when you end the database session. In a block or subprogram, local PL/SQL
tables are instantiated when you enter the block or subprogram and cease to
exist when you exit.



Every PL/SQL table has the attributes EXISTS, COUNT, FIRST, LAST, PRIOR, NEXT,
and DELETE. They make PL/SQL tables easier to use and your applications easier
to maintain. For more information, see "Using PL/SQL Table Attributes" .



The first reference to an element in a PL/SQL table must be an assignment. Until
an element is assigned a value, it does not exist. If you reference a
nonexistent element, PL/SQL raises the predefined exception NO_DATA_FOUND.



To reference elements in a PL/SQL table, you specify an index number using the
following syntax:




plsql_table_name(index)


When calling a function that returns a PL/SQL table, you use the following
syntax to reference elements in the table:




function_name(parameters)(index)


If the function result is a PL/SQL table of records, you use the following
syntax to reference fields in a record:




function_name(parameters)(index).field_name
Currently, you cannot use the syntax above to call a parameterless function
because PL/SQL does not allow empty parameter lists. That is, the following
syntax is illegal:




function_name()(index)  -- illegal; empty parameter list
Instead, declare a local PL/SQL table to which you can assign the function
result, then reference the PL/SQL table directly.



You can retrieve Oracle data into a PL/SQL table in three ways: the SELECT INTO
statement lets you select a single row of data; the FETCH statement or a cursor
FOR loop lets you fetch multiple rows.



Using the SELECT INTO statement, you can select a column entry into a scalar
element. Or, you can select an entire row into a record element. Using the FETCH
statement or a cursor FOR loop, you can fetch an entire column of Oracle data
into a PL/SQL table of scalars. Or, you can fetch an entire table of Oracle data
into a PL/SQL table of records.



You cannot reference record variables in the VALUES clause. So, you cannot
insert entire records from a PL/SQL table of records into rows in a database
table.



With the Oracle Call Interface (OCI) or the Oracle Precompilers, you can bind
host arrays to PL/SQL tables declared as the formal parameters of a subprogram.
That allows you to pass host arrays to stored functions and procedures.



You can use a BINARY_INTEGER variable or compatible host variable to index the
host arrays. Given the array subscript range m .. n, the corresponding PL/SQL
table index range is always 1 .. n - m + 1. For example, if the array subscript
range is 5 .. 10, the corresponding PL/SQL table index range is 1 .. (10 - 5 +
1) or 1 .. 6.




Examples


In the following example, you define a TABLE type named SalTabTyp:



DECLARE



   TYPE SalTabTyp IS TABLE OF emp.sal%TYPE


      INDEX BY BINARY_INTEGER;



Once you define type SalTabTyp, you can declare PL/SQL tables of that type, as
follows:




sal_tab SalTabTyp;


The identifier sal_tab represents an entire PL/SQL table.



In the next example, you assign the sum of variables salary and increase to the
tenth row in PL/SQL table sal_tab:




sal_tab(10) := salary * increase;


In the following example, you select a row from the database table dept into a
record stored by the first element of the PL/SQL table dept_tab:




DECLARE



   TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE


      INDEX BY BINARY_INTEGER;



   dept_tab DeptTabTyp;


BEGIN



   /* Select entire row into record stored by first element. */
   SELECT * INTO dept_tab(1) FROM dept WHERE deptno = 10;
   IF dept_tab(1).dname = 'ACCOUNTING' THEN


      ...


   END IF;



   ...


END;



In the final example, you fetch rows from a cursor into the PL/SQL table of
records emp_tab:




DECLARE



   TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE


      INDEX BY BINARY_INTEGER;



   emp_tab EmpTabTyp;


   i BINARY_INTEGER := 0;


   CURSOR c1 IS SELECT * FROM emp;


BEGIN



   OPEN c1;


   LOOP


      i := i + 1;
      /* Fetch entire row into record stored by ith element. */
      FETCH c1 INTO emp_tab(i);
      EXIT WHEN c1%NOTFOUND;
      -- process data record




   END LOOP;



   CLOSE c1;


END;


PL/SQL Table Attributes





Description


Every PL/SQL table has the attributes EXISTS, COUNT, FIRST, LAST, PRIOR, NEXT,
and DELETE. They make PL/SQL tables easier to use and your applications easier
to maintain.


The attributes EXISTS, PRIOR, NEXT, and DELETE take parameters. Each parameter
must be an expression that yields a BINARY_INTEGER value or a value implicitly
convertible to that datatype.



DELETE acts like a procedure, which is called as a statement. The other PL/SQL
table attributes act like a function, which is called as part of an expression.
For more information, see "Using PL/SQL Table Attributes" .




Syntax


plsql_table_attribute ::=


plsql_table_name{  .COUNT
                 | .DELETE[(index[, index])]
                 | .EXISTS(index)
                 | .FIRST
                 | .LAST
                 | .NEXT(index)
                 | .PRIOR(index)}





Keyword and Parameter Description



plsql_table_name


This identifies a PL/SQL table previously declared within the current scope.



COUNT



This is a PL/SQL table attribute, which can be appended to the name of a PL/SQL
table. COUNT returns the number of elements that a PL/SQL table contains.



DELETE



This is a PL/SQL table attribute, which can be appended to the name of a PL/SQL
table. This attribute has three forms. DELETE removes all elements from a PL/SQL
table. DELETE(n) removes the nth element. If n is null, DELETE(n) does nothing.
DELETE(m, n) removes all elements in the range m .. n. If m is larger than n or
if m or n is null, DELETE(m, n) does nothing.



index


This is a numeric expression that must yield a value of type BINARY_INTEGER or a
value implicitly convertible to that datatype. For more information, see
"Datatype Conversion" .



EXISTS



This is a PL/SQL table attribute, which can be appended to the name of a PL/SQL
table. EXISTS(n) returns TRUE if the nth element in a PL/SQL table exists.
Otherwise, EXISTS(n) returns FALSE.



FIRST, LAST



These are PL/SQL table attributes, which can be appended to the name of a PL/SQL
table. FIRST and LAST return the first and last (smallest and largest) index
numbers in a PL/SQL table. If the PL/SQL table is empty, FIRST and LAST return
nulls. If the PL/SQL table contains only one element, FIRST and LAST return the
same index number.



NEXT, PRIOR



These are PL/SQL table attributes, which can be appended to the name of a PL/SQL
table. NEXT(n) returns the index number that succeeds index n in a PL/SQL table.
PRIOR(n) returns the index number that precedes index n. If n has no successor,
NEXT(n) returns a null. Likewise, if n has no predecessor, PRIOR(n) returns a
null.



Usage Notes


Currently, you cannot use PL/SQL table attributes in a SQL statement. If you
try, you get a compilation error.


DELETE lets you free the resources held by a PL/SQL table. DELETE(n) and
DELETE(m, n) let you prune a PL/SQL table. If an element to be deleted does not
exist, DELETE simply skips it; no exception is raised.



The amount of memory allocated to a PL/SQL table can increase or decrease
dynamically. As you delete elements, memory is freed page by page. If you delete
the entire PL/SQL table, all the memory is freed.



You can use EXISTS to avoid the exception NO_DATA_FOUND, which is raised when
you reference a nonexistent element.



You can use PRIOR or NEXT to traverse PL/SQL tables from which some elements
have been deleted.




Examples


In the following example, you delete elements 20 through 30 from a PL/SQL table:



ename_tab.DELETE(20, 30);


The next example shows that you can use FIRST and LAST to specify the lower and
upper bounds of a loop range provided each element in that range exists:




FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP


   ...


END LOOP;



In the following example, PL/SQL executes the assignment statement only if the
element sal_tab(i) exists:




IF sal_tab.EXISTS(i) THEN


   sal_tab(i) := sal_tab(i) + 500;


ELSE



   RAISE salary_missing;


END IF;



You can use PRIOR or NEXT to traverse PL/SQL tables from which some elements
have been deleted, as the following generic example shows:




DECLARE



   ...


   i BINARY_INTEGER;


BEGIN



   ..


   i := any_tab.FIRST;  -- get index of first element
   WHILE i IS NOT NULL LOOP

      ...  -- process any_tab(i)
      i := any_tab.NEXT(i);  -- get index of next element


   END LOOP;



END;



jmvural a écrit dans le message <37DDBE15.55D29F41_at_tiac.net>...

>I want to dynamically allocate an array within PL/SQL. Can I do this and
>can someone give me the syntax. My access to Doc is limited.
>
>Regards,
>
>JMV
>




Received on Tue Sep 14 1999 - 03:07:44 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US