In this section and the following ones, we describe all the statement types that are explicitly understood by PL/oraSQL. Anything not recognized as one of these statement types is presumed to be an SQL command and is sent to the main database engine to execute.
An assignment of a value to a PL/oraSQL variable is written as:
variable
:=expression
;
As explained previously, the expression in such a statement is evaluated
by means of an SQL SELECT
command sent to the main
database engine. The expression must yield a single value (possibly
a row value, if the variable is a row or record variable). The target
variable can be a simple variable (optionally qualified with a block
name), a field of a row or record variable, or an element of an array
that is a simple variable or field.
Examples:
tax := subtotal * 0.06; my_record.user_id := 20;
The result of a SQL command yielding a single row (possibly of multiple
columns) can be assigned to a record variable, row-type variable, or list
of scalar variables. This is done by writing the base SQL command and
adding an INTO
clause. For example,
SELECTselect_expressions
INTOtarget
FROM ...; INSERT ... RETURNINGexpressions
INTOtarget
; UPDATE ... RETURNINGexpressions
INTOtarget
; DELETE ... RETURNINGexpressions
INTOtarget
;
where target
can be a record variable, a row
variable, or a comma-separated list of simple variables and
record/row fields.
PL/oraSQL variables will be
substituted into the rest of the query, and the plan is cached,
just as described above for commands that do not return rows.
This works for SELECT
,
INSERT
/UPDATE
/DELETE
with
RETURNING
, and utility commands that return row-set
results (such as EXPLAIN
).
Except for the INTO
clause, the SQL command is the same
as it would be written outside PL/oraSQL.
Note that this interpretation of SELECT
with INTO
is quite different from LightDB's regular
SELECT INTO
command, wherein the INTO
target is a newly created table. If you want to create a table from a
SELECT
result inside a
PL/oraSQL function, use the syntax
CREATE TABLE ... AS SELECT
.
If a row or a variable list is used as target, the query's result columns must exactly match the structure of the target as to number and data types, or else a run-time error occurs. When a record variable is the target, it automatically configures itself to the row type of the query result columns.
The INTO
clause can appear almost anywhere in the SQL
command. Customarily it is written either just before or just after
the list of select_expressions
in a
SELECT
command, or at the end of the command for other
command types. It is recommended that you follow this convention
in case the PL/oraSQL parser becomes
stricter in future versions.
If STRICT
is not specified in the INTO
clause, then target
will be set to the first
row returned by the query, or to nulls if the query returned no rows.
(Note that “the first row” is not
well-defined unless you've used ORDER BY
.) Any result rows
after the first row are discarded.
You can check the special SQL%FOUND
variable to
determine whether a row was returned:
DROP TABLE t1; DROP SEQUENCE t1_seq; CREATE TABLE t1 ( id NUMBER(10), description VARCHAR2(50), CONSTRAINT t1_pk PRIMARY KEY (id) ); CREATE SEQUENCE t1_seq; INSERT INTO t1 VALUES (t1_seq.nextval, 'ONE'); INSERT INTO t1 VALUES (t1_seq.nextval, 'TWO'); INSERT INTO t1 VALUES (t1_seq.nextval, 'THREE'); The RETURNING INTO clause allows us to return column values for rows affected by DML statements. The returned data could be a single column. For example, When we insert data using a sequence to generate our primary key value, we can return the primary key value as follows. DECLARE l_id t1.id%TYPE; BEGIN INSERT INTO t1 VALUES (t1_seq.nextval, 'FOUR') RETURNING id INTO l_id; COMMIT; DBMS_OUTPUT.put_line('ID=' || l_id); END; / For example, The syntax is also available for update and delete statements. DECLARE l_id t1.id%TYPE; BEGIN UPDATE t1 SET description = description WHERE description = 'FOUR' RETURNING id INTO l_id; DBMS_OUTPUT.put_line('UPDATE ID=' || l_id); DELETE FROM t1 WHERE description = 'FOUR' RETURNING id INTO l_id; DBMS_OUTPUT.put_line('DELETE ID=' || l_id); COMMIT; END; /
Oftentimes you will want to generate dynamic commands inside your
PL/oraSQL functions, that is, commands
that will involve different tables or different data types each
time they are executed. PL/oraSQL's
normal attempts to cache plans for commands will not work in such
scenarios. To handle this sort of problem, the
EXECUTE IMMEDIATE
statement is provided:
EXECUTE IMMEDIATEcommand-string
[ INTOtarget
] [ USINGexpression
[, ... ] ];
where command-string
is an expression
yielding a string (of type varchar
) containing the
command to be executed. The optional target
is a record variable, a row variable, or a comma-separated list of
simple variables and record/row fields, into which the results of
the command will be stored. The optional USING
expressions
supply values to be inserted into the command.
No substitution of PL/oraSQL variables is done on the computed command string. Any required variable values must be inserted in the command string as it is constructed; or you can use parameters as described below.
Also, there is no plan caching for commands executed via
EXECUTE IMMEDIATE
. Instead, the command is always planned
each time the statement is run. Thus the command
string can be dynamically created within the function to perform
actions on different tables and columns.
The INTO
clause specifies where the results of
a SQL command returning rows should be assigned. If a row
or variable list is provided, it must exactly match the structure
of the query's results (when a
record variable is used, it will configure itself to match the
result structure automatically). If multiple rows are returned,
only the first will be assigned to the INTO
variable. If no rows are returned, NULL is assigned to the
INTO
variable(s). If no INTO
clause is specified, the query results are discarded.
If the STRICT
option is given, an error is reported
unless the query produces exactly one row.
The command string can use parameter values, which are referenced
in the command as :inserted_by
, :inserted
, etc.
These symbols refer to values supplied in the USING
clause. This method is often preferable to inserting data values
into the command string as varchar: it avoids run-time overhead of
converting the values to varchar and back, and it is much less prone
to SQL-injection attacks since there is no need for quoting or escaping.
An example is:
EXECUTE IMMEDIATE 'SELECT count(*) FROM mytable WHERE inserted_by = :inserted_by AND inserted <= :inserted' INTO c USING checked_user, checked_date;
An EXECUTE IMMEDIATE
with a simple constant command string and some
USING
parameters, as in the first example above, is
functionally equivalent to just writing the command directly in
PL/oraSQL and allowing replacement of
PL/oraSQL variables to happen automatically.
The important difference is that EXECUTE IMMEDIATE
will re-plan
the command on each execution, generating a plan that is specific
to the current parameter values; whereas
PL/oraSQL may otherwise create a generic plan
and cache it for re-use. In situations where the best plan depends
strongly on the parameter values, it can be helpful to use
EXECUTE IMMEDIATE
to positively ensure that a generic plan is not
selected.
SELECT INTO
is not currently supported within
EXECUTE IMMEDIATE
; instead, execute a plain SELECT
command and specify INTO
as part of the EXECUTE IMMEDIATE
itself.
Sometimes a placeholder statement that does nothing is useful.
For example, it can indicate that one arm of an if/then/else
chain is deliberately empty. For this purpose, use the
NULL
statement:
NULL;
For example, the following two fragments of code are equivalent:
declare y int := 10; x int := 10; BEGIN NULL; END; /