|
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
|