What is PL/SQL table ?

 Objects  of  type  TABLE are called "PL/SQL  tables", which are modeled as (but  not the same as) database tables,  PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.  Cursors

Editorial / Best Answer

rkanth18  

  • Member Since Sep-2005 | Nov 26th, 2005


A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogenous elements, indexed by integers

One-dimensional

A PL/SQL table can have only one column. It is, in this way, similar to a one-dimensional array.

Unbounded or Unconstrained

There is no predefined limit to the number of rows in a PL/SQL table. The PL/SQL table grows dynamically as you add more rows to the table. The PL/SQL table is, in this way, very different from an array.

Related to this definition, no rows for PL/SQL tables are allocated for this structure when it is defined.

Sparse

In a PL/SQL table, a row exists in the table only when a value is assigned to that row. Rows do not have to be defined sequentially. Instead you can assign a value to any row in the table. So row 15 could have a value of `Fox' and row 15446 a value of `Red', with no other rows defined in between.

Homogeneous elements

Because a PL/SQL table can have only a single column, all rows in a PL/SQL table contain values of the same datatype. It is, therefore, homogeneous.

With PL/SQL Release 2.3, you can have PL/SQL tables of records. The resulting table is still, however, homogeneous. Each row simply contains the same set of columns.

Indexed by integers

PL/SQL tables currently support a single indexing mode: by BINARY_INTEGER. This number acts as the "primary key" of the PL/SQL table. The range of a BINARY_INTEGER is from -231-1 to 231-1, so you have an awful lot of rows with which to work

 

Showing Answers 1 - 3 of 3 Answers

kishorebabukm

  • Sep 20th, 2005
 

Pl/SQL tables are the row, column structure  as of the table but cannot perform any select query on the pl/sql tables because the data isfrom fetched  only by the indexes like

--- store empno, ename from emp table thru cursor to pl/sql tables. 

declare

type  ty_plsql is record ( empno number, ename varchar2(50));

ty_plsql1 ty_plsql;

cursor c is select empno,ename from emp;

begin

 for i in c loop

   ty_plsql(i).empno := i.empno;

   ty_plsql(i).ename := i.ename;

end loop;

end;

  Was this answer useful?  Yes

rkanth18

  • Nov 26th, 2005
 

A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogenous elements, indexed by integers

One-dimensional

A PL/SQL table can have only one column. It is, in this way, similar to a one-dimensional array.

Unbounded or Unconstrained

There is no predefined limit to the number of rows in a PL/SQL table. The PL/SQL table grows dynamically as you add more rows to the table. The PL/SQL table is, in this way, very different from an array.

Related to this definition, no rows for PL/SQL tables are allocated for this structure when it is defined.

Sparse

In a PL/SQL table, a row exists in the table only when a value is assigned to that row. Rows do not have to be defined sequentially. Instead you can assign a value to any row in the table. So row 15 could have a value of `Fox' and row 15446 a value of `Red', with no other rows defined in between.

Homogeneous elements

Because a PL/SQL table can have only a single column, all rows in a PL/SQL table contain values of the same datatype. It is, therefore, homogeneous.

With PL/SQL Release 2.3, you can have PL/SQL tables of records. The resulting table is still, however, homogeneous. Each row simply contains the same set of columns.

Indexed by integers

PL/SQL tables currently support a single indexing mode: by BINARY_INTEGER. This number acts as the "primary key" of the PL/SQL table. The range of a BINARY_INTEGER is from -231-1 to 231-1, so you have an awful lot of rows with which to work

 

rkanth18

  • Nov 26th, 2005
 

Keep the following restrictions in mind when you work with PL/SQL tables:

  • There is no concept of transaction integrity with PL/SQL tables. You cannot commit information to a PL/SQL table or roll back changes from the table.

  • You cannot SELECT from PL/SQL tables. There is no way to perform set-at-a-time processing to retrieve data from a PL/SQL table. This is a programmatic construct in a programmatic language. Instead you can use PL/SQL loops to move through the contents of a PL/SQL table, one row at a time.

  • You cannot issue DML statements (INSERTs, UPDATEs, and DELETEs) against PL/SQL tables (though PL/SQL Release 2.3 does offer a DELETE operator).

  Was this answer useful?  Yes

An associative array (or INDEX-BY table) that can be indexed by NUMBER or VARCHAR2. Elements are retrieved using number or string subscript values. Unlike with data tables, PL/SQL tables are stored in memory. PL/SQL Tables are sparse and elements are unordered.

  Was this answer useful?  Yes

Example for PL/SQL table:
==================

In the below block "typlsql" and "ty_plsql2" are PL/SQL tables

declare

type ty_plsql is record ( empno number, ename varchar2(50));

TYPE ty_plsql2 is table of ty_plsql;

ty_plsql1 ty_plsql2;

j number;

cursor c is select empno,ename from emp;

begin

ty_plsql1:=ty_plsql2(NULL,NULL);

for i in c loop

j:=1;

ty_plsql1(j).empno := i.empno;

ty_plsql1(j).ename := i.ename;

dbms_output.put_line('Print the value : '||' No '|| ty_plsql1(j).empno ||' Name '|| ty_plsql1(j).ename);

j:=j+1;

end loop;

end;

  Was this answer useful?  Yes

A PL/SQL table is a database table having one column and rows are stored not in predefined order. Associative arrays and nested tables comes under category of PL/SQL. 

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions