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