4.4 Basic Statements

Table of Contents Previous Next


4 Stored Procedure Language : 4.4 Basic Statements

4.4.1 NULL
The simplest statement is the NULL statement. This statement is an executable statement that does nothing.
The NULL statement can act as a placeholder where an executable statement is required such as in a branch of an IF-THEN-ELSE statement.
The assignment statement sets a variable or a formal parameter of mode OUT or IN OUT specified on the left side of the assignment, :=, to the evaluated expression specified on the right side of the assignment.
variable := expression;
variable is an identifier for a previously declared variable, OUT formal parameter, or IN OUT formal parameter.
expression is an expression that produces a single value. The value produced by the expression must have a compatible data type with that of variable.
The SELECT INTO statement is an SPL variation of the SQL SELECT command, the differences being:
That SELECT INTO is designed to assign the results to variables or records where they can then be used in SPL program statements.
The accessible result set of SELECT INTO is at most one row.
Other than the above, all of the clauses of the SELECT command such as WHERE, ORDER BY, GROUP BY, HAVING, etc. are valid for SELECT INTO. The following are the two variations of SELECT INTO.
SELECT select_expressions INTO target FROM ...;
target is a comma-separated list of simple variables. select_expressions and the remainder of the statement are the same as for the SELECT command. The selected values must exactly match in data type, number, and order the structure of the target or a runtime error occurs.
SELECT * INTO record FROM table ...;
record is a record variable that has previously been declared.
Note: In either cases, where no row is returned or more than one row is returned, SPL throws an exception.
Note: There is a variation of SELECT INTO using the BULK COLLECT clause that allows a result set of more than one row that is returned into a collection. See Section 4.12.4.1 for more information on using the BULK COLLECT clause with the SELECT INTO statement.
You can use the WHEN NO_DATA_FOUND clause in an EXCEPTION block to determine whether the assignment was successful (that is, at least one row was returned by the query).
This version of the emp_sal_query procedure uses the variation of SELECT INTO that returns the result set into a record. Also note the addition of the EXCEPTION block containing the WHEN NO_DATA_FOUND conditional expression.
Another conditional clause of use in the EXCEPTION section with SELECT INTO is the TOO_MANY_ROWS exception. If more than one row is selected by the SELECT INTO statement an exception is thrown by SPL.
When the following block is executed, the TOO_MANY_ROWS exception is thrown since there are many employees in the specified department.
Note: See Section 4.5.7 or more information on exception handling.
4.4.4 INSERT
The INSERT command available in the SQL language can also be used in SPL programs.
An expression in the SPL language can be used wherever an expression is allowed in the SQL INSERT command. Thus, SPL variables and parameters can be used to supply values to the insert operation.
If an exception occurs all database changes made in the procedure are automatically rolled back. In this example the EXCEPTION section with the WHEN OTHERS clause catches all exceptions. Two variables are displayed. SQLCODE is a number that identifies the specific exception that occurred. SQLERRM is a text message explaining the error. See Section 4.5.7 for more information on exception handling.
Note: The INSERT command can be included in a FORALL statement. A FORALL statement allows a single INSERT command to insert multiple rows from values supplied in one or more collections. See Section 4.12.3 for more information on the FORALL statement.
4.4.5 UPDATE
The UPDATE command available in the SQL language can also be used in SPL programs.
An expression in the SPL language can be used wherever an expression is allowed in the SQL UPDATE command. Thus, SPL variables and parameters can be used to supply values to the update operation.
The SQL%FOUND conditional expression returns TRUE if a row is updated, FALSE otherwise. See Section 4.4.8 for a discussion of SQL%FOUND and other similar expressions.
Note: The UPDATE command can be included in a FORALL statement. A FORALL statement allows a single UPDATE command to update multiple rows from values supplied in one or more collections. See Section 4.12.3 for more information on the FORALL statement.
4.4.6 DELETE
The DELETE command (available in the SQL language) can also be used in SPL programs.
An expression in the SPL language can be used wherever an expression is allowed in the SQL DELETE command. Thus, SPL variables and parameters can be used to supply values to the delete operation.
The SQL%FOUND conditional expression returns TRUE if a row is deleted, FALSE otherwise. See Section 4.4.8 for a discussion of SQL%FOUND and other similar expressions.
Note: The DELETE command can be included in a FORALL statement. A FORALL statement allows a single DELETE command to delete multiple rows from values supplied in one or more collections. See Section 4.12.3 for more information on the FORALL statement.
The INSERT, UPDATE, and DELETE commands may be appended by the optional RETURNING INTO clause. This clause allows the SPL program to capture the newly added, modified, or deleted values from the results of an INSERT, UPDATE, or DELETE command, respectively.
{ insert | update | delete }
RETURNING { * | expr_1 [, expr_2 ] ...}
INTO { record | field_1 [, field_2 ] ...};
insert is a valid INSERT command. update is a valid UPDATE command. delete is a valid DELETE command. If * is specified, then the values from the row affected by the INSERT, UPDATE, or DELETE command are made available for assignment to the record or fields to the right of the INTO keyword. (Note that the use of * is an Advanced Server extension and is not compatible with Oracle databases.) expr_1, expr_2... are expressions evaluated upon the row affected by the INSERT, UPDATE, or DELETE command. The evaluated results are assigned to the record or fields to the right of the INTO keyword. record is the identifier of a record that must contain fields that match in number and order, and are data type compatible with the values in the RETURNING clause. field_1, field_2,... are variables that must match in number and order, and are data type compatible with the set of values in the RETURNING clause.
If the INSERT, UPDATE, or DELETE command returns a result set with more than one row, then an exception is thrown with SQLCODE 01422, query returned more than one row. If no rows are in the result set, then the variables following the INTO keyword are set to null.
Note: There is a variation of RETURNING INTO using the BULK COLLECT clause that allows a result set of more than one row that is returned into a collection. See Section 4.12.4 for more information on the BULK COLLECT clause.
The following example is a modification of the emp_comp_update procedure introduced in Section 4.4.5, with the addition of the RETURNING INTO clause.
The following is the output from this procedure (assuming employee 9503 created by the emp_insert procedure still exists within the table).
The following example is a modification of the emp_delete procedure, with the addition of the RETURNING INTO clause using record types.
There are several attributes that can be used to determine the effect of a command. SQL%FOUND is a Boolean that returns true if at least one row was affected by an INSERT, UPDATE or DELETE command or a SELECT INTO command retrieved one or more rows.
SQL%ROWCOUNT provides the number of rows affected by an INSERT, UPDATE or DELETE command. The following example updates the row that was just inserted and displays SQL%ROWCOUNT.
SQL%NOTFOUND is the opposite of SQL%FOUND. SQL%NOTFOUND returns true if no rows were affected by an INSERT, UPDATE or DELETE command or a SELECT INTO command retrieved no rows.

4 Stored Procedure Language : 4.4 Basic Statements

Table of Contents Previous Next