|
Declare
variables and constants in a PL/SQL declare
block.
name [CONSTANT] datatype [NOT NULL] [:= |
DEFAULT expr]
key
name :
The name of the variable
datatype :
may be scalar, composite, reference or LOB
expr
: a literal value, another variable or any
plsql expression involving operators &
functions.
A constant MUST have it's initial value in
the declaration.
Composite
datatypes are TABLE, RECORD, NESTED
TABLE and VARRAY
You can use [schema.]object%TYPE to define
variables based on actual object datatypes.
Declaring RECORD variables
A specific RECORD TYPE corresponding to a
fixed number (and datatype) of underlying
table columns can simplify the job of
defining variables.
Syntax:
TYPE type_name
IS RECORD
(field_declaration,...);
Options
'field_declaration' is defined as:
field_name {datatype
| variable%TYPE | table.column%TYPE |
table%ROWTYPE}[ [NOT NULL] {:= | DEFAULT}
expr ]
Declare %ROWTYPE%
Record variables:
DECLARE
variable_name table_name%ROWTYPE%
At runtime the
system will evaluate the number of variables
and their datatype; The columns may be based
on an underlying table or a cursor.
Declare SQL*Plus bind variables.
Syntax:
SQL > VARIABLE
g_bar VARCHAR2(30)
SQL > ACCEPT
p_foo PROMPT 'enter the value required'
You
can reference host variables in PL/SQL
statements *unless* the statement is in a
procedure, function or package. This is
done by prefixing with & (to read the
variable) or prefix with : (writing
to the variable)
Example:
-- Declare a
variable based on SQL*Plus Bind variable
v_amount
NUMBER(6,2) := &p_foo
-- Assign
value to a SQL*Plus variable from a PL/SQL
variable
:g_bar :=
v_amount *12
Declare TABLE
TYPE variables in a PL/SQL declare block.
Table variables are also known as index-by
table or array. The table variable contains
one column which must be a scalar or record
datatype plus a primary key of type
BINARY_INTEGER.
Syntax:
DECLARE
TYPE
type_name IS TABLE OF
(column_type |
variable%TYPE |
table.column%TYPE
[NOT
NULL]
INDEX BY BINARY
INTEGER;
-- Then to
declare a TABLE variable of this type:
variable_name type_name;
-- Assigning
values to a TABLE variable:
variable_name(n).field_name := 'some text';
-- Where 'n' is the index value
Using TABLE
variable Methods:
To execute these use the syntax:
table_name[ (parameters)]
EXISTS(n)
Returns TRUE if nth element of the table
exists.
COUNT
The number of elements (rows) in the plsql
table
FIRST
First and Last index no.s in the table
LAST
returns NULL if table is empty
PRIOR(n)
Returns index no that preceeds n in the
plsql table
NEXT(n)
Returns index no that succeeds n in the
plsql table
EXTEND(n,i)
Append n copies of the 'i'th element to a
plsql table i defaults to NULL n defaults to
1
TRIM(n)
Remove n elements from the end of a plsql
table n defaults to 1
DELETE(m,n)
Delete elements in range m...n (m defaults
to = n and n defaults to ALL elements |
