| Oracle® Database PL/SQL Language Reference 11g Release 1 (11.1) Part Number B28370-01 |
|
|
View PDF |
| Oracle® Database PL/SQL Language Reference 11g Release 1 (11.1) Part Number B28370-01 |
|
|
View PDF |
This chapter summarizes the syntax and semantics of PL/SQL language elements and provides links to examples and related topics.
For instructions on how to read syntax diagrams, see Oracle Database SQL Language Reference.
Topics:
RESTRICT_REFERENCES Pragma (deprecated)
An assignment statement sets the current value of a variable, field, parameter, or element. The statement consists of an assignment target followed by the assignment operator and an expression. When the statement is executed, the expression is evaluated and the resulting value is stored in the target.
Syntax
assignment_statement ::=
Keyword and Parameter Description
attribute_name
An attribute of an object type. The name must be unique within the object type (but can be reused in other object types). You cannot initialize an attribute in its declaration using the assignment operator or DEFAULT clause. Also, you cannot impose the NOT NULL constraint on an attribute.
collection_name
A nested table, index-by table, or varray previously declared within the current scope.
cursor_variable_name
A PL/SQL cursor variable previously declared within the current scope. Only the value of another cursor variable can be assigned to a cursor variable.
expression
A combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of expression, see "Expression Definition". When the assignment statement is executed, the expression is evaluated and the resulting value is stored in the assignment target. The value and target must have compatible datatypes.
field_name
A field in a user-defined or %ROWTYPE record.
host_cursor_variable_name
A cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.
host_variable_name
A variable declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. Host variables must be prefixed with a colon.
index
A numeric expression that must return a value of type PLS_INTEGER or a value implicitly convertible to that datatype.
indicator_name
An indicator variable declared in a PL/SQL host environment and passed to PL/SQL. Indicator variables must be prefixed with a colon. An indicator variable indicates the value or condition of its associated host variable. For example, in the Oracle Precompiler environment, indicator variables let you detect nulls or truncated values in output host variables.
object_name
An instance of an object type previously declared within the current scope.
parameter_name
A formal OUT or IN OUT parameter of the subprogram in which the assignment statement appears.
record_name
A user-defined or %ROWTYPE record previously declared within the current scope.
variable_name
A PL/SQL variable previously declared within the current scope.
Usage Notes
By default, unless a variable is initialized in its declaration, it is initialized to NULL every time a block or subprogram is entered. Always assign a value to a variable before using that variable in an expression.
You cannot assign nulls to a variable defined as NOT NULL. If you try, PL/SQL raises the predefined exception VALUE_ERROR. Only the values TRUE, FALSE, and NULL can be assigned to a Boolean variable. You can assign the result of a comparison or other test to a Boolean variable.
You can assign the value of an expression to a specific field in a record. You can assign values to all fields in a record at once. PL/SQL allows aggregate assignment between entire records if their declarations refer to the same cursor or table. Example 1-3, "Assigning Values to Variables with the Assignment Operator" shows how to copy values from all the fields of one record to another:
You can assign the value of an expression to a specific element in a collection, by subscripting the collection name.
Related Topics
The AUTONOMOUS_TRANSACTION pragma marks a routine as autonomous. When an autonomous routine is invoked, the main transaction is suspended. Without affecting the main transaction, the autonomous routine can commit or roll back its own operations.
In this context, a routine is either a top-level (not nested) anonymous PL/SQL block or a PL/SQL subprogram. For more information, see "Doing Independent Units of Work with Autonomous Transactions".
Syntax
autonomous_transaction_pragma ::=
Keyword and Parameter Description
Signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They pass information to the compiler.
Usage Notes
You can apply this pragma to:
Top-level (not nested) anonymous PL/SQL blocks
Local, standalone, and packaged functions and procedures
Methods of a SQL object type
Database triggers
You cannot apply this pragma to an entire package or an entire an object type. Instead, you can apply the pragma to each packaged subprogram or object method.
You can code the pragma anywhere in the declarative section. For readability, code the pragma at the top of the section.
Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. You can log events, increment retry counters, and so on, even if the main transaction rolls back.
Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK, and can issue DDL statements (such as CREATE and DROP) through the EXECUTE IMMEDIATE statement.
Changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits. The changes also become visible to the main transaction when it resumes, but only if its isolation level is set to READ COMMITTED (the default). If you set the isolation level of the main transaction to SERIALIZABLE, changes made by its autonomous transactions are not visible to the main transaction when it resumes.
In the main transaction, rolling back to a savepoint located before the call to the autonomous subprogram does not roll back the autonomous transaction. Remember, autonomous transactions are fully independent of the main transaction.
If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur. Oracle raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.
If you try to exit an active autonomous transaction without committing or rolling back, Oracle raises an exception. If the exception goes unhandled, or if the transaction ends because of some other unhandled exception, the transaction is rolled back.
Related Topics
The basic program unit in PL/SQL is the block. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords partition the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required. You can nest a block within another block wherever you can place an executable statement. For more information, see "Understanding PL/SQL Block Structure" and "Scope and Visibility of PL/SQL Identifiers".
Syntax
plsql_block ::=
declare_section ::=
item_list_1 ::=
item_list_2 ::=
type_definition ::=
subtype_definition ::=
item_declaration ::=
body ::=
statement ::=
sql_statement ::=
Keyword and Parameter Description
base_type
Any scalar or user-defined PL/SQL datatype specifier such as CHAR, DATE, or RECORD.
Signals the start of the executable part of a PL/SQL block, which contains executable statements. A PL/SQL block must contain at least one executable statement (even just the NULL statement). See "Understanding PL/SQL Block Structure".
collection_declaration
Declares a collection (index-by table, nested table, or varray). For the syntax of collection_declaration, see "Collection Definition".
constant_declaration
Declares a constant. For the syntax of constant_declaration, see "Constant and Variable Declaration".
constraint
Applies only to datatypes that can be constrained such as CHAR and NUMBER. For character datatypes, this specifies a maximum size in bytes. For numeric datatypes, this specifies a maximum precision and scale.
cursor_declaration
Declares an explicit cursor. For the syntax of cursor_declaration, see "Cursor Declaration".
cursor_variable_declaration
Declares a cursor variable. For the syntax of cursor_variable_declaration, see "Cursor Variables".
Signals the start of the declarative part of a PL/SQL block, which contains local declarations. Items declared locally exist only within the current block and all its sub-blocks and are not visible to enclosing blocks. The declarative part of a PL/SQL block is optional. It is terminated implicitly by the keyword BEGIN, which introduces the executable part of the block. For more information, see "Declarations".
PL/SQL does not allow forward references. You must declare an item before referencing it in any other statements. Also, you must declare subprograms at the end of a declarative section after all other program items.
Signals the end of a PL/SQL block. It must be the last keyword in a block. Remember, END does not signal the end of a transaction. Just as a block can span multiple transactions, a transaction can span multiple blocks. See "Understanding PL/SQL Block Structure".
Signals the start of the exception-handling part of a PL/SQL block. When an exception is raised, normal execution of the block stops and control transfers to the appropriate exception handler. After the exception handler completes, execution proceeds with the statement following the block. See "Understanding PL/SQL Block Structure".
If there is no exception handler for the raised exception in the current block, control passes to the enclosing block. This process repeats until an exception handler is found or there are no more enclosing blocks. If PL/SQL can find no exception handler for the exception, execution stops and an unhandled exception error is returned to the host environment. For more information on exceptions, see Chapter 11, "Handling PL/SQL Errors".
exception_declaration
Declares an exception. For the syntax of exception_declaration, see "Exception Definition".
exception_handler
Associates an exception with a sequence of statements, which is executed when that exception is raised. For the syntax of exception_handler, see "Exception Definition".
function_declaration
Declares a function. See "Function Declaration and Definition".
An undeclared identifier that optionally labels a PL/SQL block or statement. If used, label_name must be enclosed by double angle brackets and must appear at the beginning of the block or statement which it labels. Optionally, when used to label a block, the label_name can also appear at the end of the block without the angle brackets. Multiple labels are allowed for a block or statement, but they must be unique for each block or statement.
A global identifier declared in an enclosing block can be redeclared in a sub-block, in which case the local declaration prevails and the sub-block cannot reference the global identifier unless you use a block label to qualify the reference. See Example 2-19, "PL/SQL Block Using Multiple and Duplicate Labels".
name
Is the label name (without the delimiters << and >>).
object_declaration
Declares an instance of an object type. For the syntax of object_declaration, see "Object Type Declaration".
pragma
One of the following:
procedure_declaration
Declare a procedure. See "Procedure Declaration and Definition".
record_declaration
Declares a user-defined record. For the syntax of record_declaration, see "Record Definition".
statement
An executable (not declarative) statement that. A sequence of statements can include procedural statements such as RAISE, SQL statements such as UPDATE, and PL/SQL blocks. PL/SQL statements are free format. That is, they can continue from line to line if you do not split keywords, delimiters, or literals across lines. A semicolon (;) serves as the statement terminator.
subtype_name
A user-defined subtype that was defined using any scalar or user-defined PL/SQL datatype specifier such as CHAR, DATE, or RECORD.
variable_declaration
Declares a variable. For the syntax of variable_declaration, see "Constant and Variable Declaration".
PL/SQL supports a subset of SQL statements that includes data manipulation, cursor control, and transaction control statements but excludes data definition and data control statements such as ALTER, CREATE, GRANT, and REVOKE.
Related Topics
The CASE statement chooses from a sequence of conditions, and executes a corresponding statement. The CASE statement evaluates a single expression and compares it against several potential values, or evaluates multiple Boolean expressions and chooses the first one that is TRUE.
Syntax
searched_case_statement ::=
simple_case_statement ::=
Keyword and Parameter Description
The value of the CASE operand and WHEN operands in a simple CASE statement can be any PL/SQL type other than BLOB, BFILE, an object type, a PL/SQL record, an index-by table, a varray, or a nested table.
If the ELSE clause is omitted, the system substitutes a default action. For a CASE statement, the default when none of the conditions matches is to raise a CASE_NOT_FOUND exception. For a CASE expression, the default is to return NULL.
Usage Notes
The WHEN clauses are executed in order. Each WHEN clause is executed only once. After a matching WHEN clause is found, subsequent WHEN clauses are not executed. You can use multiple statements after a WHEN clause, and that the expression in the WHEN clause can be a literal, variable, function call, or any other kind of expression. The WHEN clauses can use different conditions rather than all testing the same variable or using the same operator.
The statements in a WHEN clause can modify the database and invoke nondeterministic functions. There is no fall-through mechanism as in the C switch statement. Once a WHEN clause is matched and its statements are executed, the CASE statement ends.
The CASE statement is appropriate when there is some different action to be taken for each alternative. If you just need to choose among several values to assign to a variable, you can code an assignment statement using a CASE expression instead.
You can include CASE expressions inside SQL queries, for example instead of a call to the DECODE function or some other function that translates from one value to another.
Related Topics
See Also:
Oracle Database SQL Language Reference for information about the NULLIF function
Oracle Database SQL Language Reference for information about the COALESCE function
The CLOSE statement indicates that you are finished fetching from a cursor or cursor variable, and that the resources held by the cursor can be reused.
Syntax
close_statement ::=
Keyword and Parameter Description
cursor_name, cursor_variable_name, host_cursor_variable_name
When you close the cursor, you can specify an explicit cursor or a PL/SQL cursor variable, previously declared within the current scope and currently open.
You can also specify a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.
Usage Notes
Once a cursor or cursor variable is closed, you can reopen it using the OPEN or OPEN-FOR statement, respectively. You must close a cursor before opening it again, otherwise PL/SQL raises the predefined exception CURSOR_ALREADY_OPEN. You do not need to close a cursor variable before opening it again.
If you try to close an already-closed or never-opened cursor or cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.
Related Topics
A collection is an ordered group of elements, all of the same type. For example, the grades for a class of students. Each element has a unique subscript that determines its position in the collection. PL/SQL offers three kinds of collections: associative arrays, nested tables, and varrays (short for variable-size arrays). Nested tables extend the functionality of associative arrays (formerly called PL/SQL tables or index-by tables).
Collections work like the arrays found in most third-generation programming languages. Collections can have only one dimension. Most collections are indexed by integers, although associative arrays can also be indexed by strings. To model multi-dimensional arrays, you can declare collections whose items are other collections.
Nested tables and varrays can store instances of an object type and, conversely, can be attributes of an object type. Collections can also be passed as parameters. You can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms.
For more information, see "Defining Collection Types".
Note:
Schema level collection types created with the
CREATE TYPE statement have a different syntax than PL/SQL collection types. For information on the CREATE TYPE SQL statement, see Oracle Database SQL Language Reference. For information on the CREATE TYPE BODY SQL statement, see Oracle Database SQL Language Reference.Syntax
table_type_definition ::=
varray_type_definition ::=
collection_type_definition ::=
element_type_definition ::=
Keyword and Parameter Description
element_type
The type of PL/SQL collection element. The type can be any PL/SQL datatype except REF CURSOR.
Optional. Defines an associative array, where you specify the subscript values to use rather than the system defining them in sequence.
type_name can be BINARY_INTEGER, PLS_INTEGER, or VARCHAR2, or one of VARCHAR2 subtypes VARCHAR, STRING, or LONG. v_size specifies the length of the VARCHAR2 key.
size_limit
A positive integer literal that specifies the maximum size of a varray, which is the maximum number of elements the varray can contain. Note that a maximum limit is imposed. See "Referencing Collection Elements".
type_name
A user-defined collection type that was defined using the datatype specifier TABLE or VARRAY.
Usage Notes
Nested tables extend the functionality of associative arrays (formerly known as index-by tables), so they differ in several ways. See "Choosing Between Nested Tables and Associative Arrays".
Every element reference includes the collection name and one or more subscripts enclosed in parentheses; the subscripts determine which element is processed. Except for associative arrays, which can have negative subscripts, collection subscripts have a fixed lower bound of 1. Subscripts for multilevel collections are evaluated in any order; if a subscript includes an expression that modifies the value of a different subscript, the result is undefined. See "Referencing Collection Elements".
You can define all three collection types in the declarative part of any PL/SQL block, subprogram, or package. But, only nested table and varray types can be created and stored in an Oracle database.
Associative arrays and nested tables can be sparse (have nonconsecutive subscripts), but varrays are always dense (have consecutive subscripts). Unlike nested tables, varrays retain their ordering and subscripts when stored in the database. Initially, associative arrays are sparse. That enables you, for example, to store reference data in a temporary variable using a primary key (account numbers or employee numbers for example) as the index.
Collections follow the usual scoping and instantiation rules. In a package, collections are instantiated when you first reference the package and cease to exist when you end the database session. In a block or subprogram, local collections are instantiated when you enter the block or subprogram and cease to exist when you exit.
Until you initialize it, a nested table or varray is atomically null (that is, the collection itself is null, not its elements). To initialize a nested table or varray, you use a constructor, which is a system-defined function with the same name as the collection type. This function constructs (creates) a collection from the elements passed to it.
For information on collection comparisons that are allowed, see "Comparing Collections".
Collections can store instances of an object type and, conversely, can be attributes of an object type. Collections can also be passed as parameters. You can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms.
When invoking a function that returns a collection, you use the following syntax to reference elements in the collection:
function_name(parameter_list)(subscript)
See Example 5-16, "Referencing an Element of an Associative Array" and Example B-2, "Using the Dot Notation to Qualify Names".
With the Oracle Call Interface (OCI) or the Oracle Precompilers, you can bind host arrays to associative arrays (index-by tables) declared as the formal parameters of a subprogram. That lets you pass host arrays to stored functions and procedures.
Related Topics
A collection method is a built-in subprogram that operates on collections and is called using dot notation. You can use the methods EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, NEXT, EXTEND, TRIM, and DELETE to manage collections whose size is unknown or varies.
EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT are functions that check the properties of a collection or individual collection elements. EXTEND, TRIM, and DELETE are procedures that modify a collection.
EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE take integer parameters. EXISTS, PRIOR, NEXT, and DELETE can also take VARCHAR2 parameters for associative arrays with string keys. EXTEND and TRIM cannot be used with index-by tables.
For more information, see "Using Collection Methods".
Syntax
collection_call_method ::=
Keyword and Parameter Description
collection_name
An associative array, nested table, or varray previously declared within the current scope.
Returns the number of elements that a collection currently contains, which is useful because the current size of a collection is not always known. You can use COUNT wherever an integer expression is allowed. For varrays, COUNT always equals LAST. For nested tables, normally, COUNT equals LAST. But, if you delete elements from the middle of a nested table, COUNT is smaller than LAST.
This procedure has three forms. DELETE removes all elements from a collection. DELETE(n) removes the nth element from an associative array or nested table. If n is null, DELETE(n) does nothing. DELETE(m,n) removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.
EXISTS(n) returns TRUE if the nth element in a collection exists. Otherwise, EXISTS(n) returns FALSE. Mainly, you use EXISTS with DELETE to maintain sparse nested tables. You can also use EXISTS to avoid raising an exception when you reference a nonexistent element. When passed an out-of-range subscript, EXISTS returns FALSE instead of raising SUBSCRIPT_OUTSIDE_LIMIT.
This procedure has three forms. EXTEND appends one null element to a collection. EXTEND(n) appends n null elements to a collection. EXTEND(n,i) appends n copies of the ith element to a collection. EXTEND operates on the internal size of a collection. If EXTEND encounters deleted elements, it includes them in its tally. You cannot use EXTEND with associative arrays.
FIRST and LAST return the first and last (smallest and largest) subscript values in a collection. The subscript values are usually integers, but can also be strings for associative arrays. If the collection is empty, FIRST and LAST return NULL. If the collection contains only one element, FIRST and LAST return the same subscript value. For varrays, FIRST always returns 1 and LAST always equals COUNT. For nested tables, normally, LAST equals COUNT. But, if you delete elements from the middle of a nested table, LAST is larger than COUNT.
index
An expression that must return (or convert implicitly to) an integer in most cases, or a string for an associative array declared with string keys.
For nested tables, which have no maximum size, LIMIT returns NULL. For varrays, LIMIT returns the maximum number of elements that a varray can contain (which you must specify in its type definition).
NEXT(n) returns the subscript that succeeds index n. If n has no successor, NEXT(n) returns NULL.
PRIOR(n) returns the subscript that precedes index n in a collection. If n has no predecessor, PRIOR(n) returns NULL.
This procedure has two forms. TRIM removes one element from the end of a collection. TRIM(n) removes n elements from the end of a collection. If n is greater than COUNT, TRIM(n) raises SUBSCRIPT_BEYOND_COUNT. You cannot use TRIM with index-by tables.TRIM operates on the internal size of a collection. If TRIM encounters deleted elements, it includes them in its tally.
Usage Notes
You cannot use collection methods in a SQL statement. If you try, you get a compilation error.
Only EXISTS can be applied to atomically null collections. If you apply another method to such collections, PL/SQL raises COLLECTION_IS_NULL.
If the collection elements have sequential subscripts, you can use collection.FIRST .. collection.LAST in a FOR loop to iterate through all the elements. You can use PRIOR or NEXT to traverse collections indexed by any series of subscripts. For example, you can use PRIOR or NEXT to traverse a nested table from which some elements were deleted, or an associative array where the subscripts are string values.
EXTEND operates on the internal size of a collection, which includes deleted elements. You cannot use EXTEND to initialize an atomically null collection. Also, if you impose the NOT NULL constraint on a TABLE or VARRAY type, you cannot apply the first two forms of EXTEND to collections of that type.
If an element to be deleted does not exist, DELETE simply skips it; no exception is raised. Varrays are dense, so you cannot delete their individual elements. Because PL/SQL keeps placeholders for deleted elements, you can replace a deleted element by assigning it a new value. However, PL/SQL does not keep placeholders for trimmed elements.
The amount of memory allocated to a nested table can increase or decrease dynamically. As you delete elements, memory is freed page by page. If you delete the entire table, all the memory is freed.
In general, do not depend on the interaction between TRIM and DELETE. It is better to treat nested tables like fixed-size arrays and use only DELETE, or to treat them like stacks and use only TRIM and EXTEND.
Within a subprogram, a collection parameter assumes the properties of the argument bound to it. You can apply methods FIRST, LAST, COUNT, and so on to such parameters. For varray parameters, the value of LIMIT is always derived from the parameter type definition, regardless of the parameter mode.
Related Topics
Comments let you include arbitrary text within your code to explain what the code does. You can also disable obsolete or unfinished pieces of code by turning them into comments.
PL/SQL supports two comment styles: single-line and multi-line. A double hyphen (- -) anywhere on a line (except within a character literal) turns the rest of the line into a comment. Multi-line comments begin with a slash-asterisk (/*) and end with an asterisk-slash (*/). For more information, see "Comments".
Syntax
comment ::=
Usage Notes
While testing or debugging a program, you might want to disable lines of code. Single-line comments can appear within a statement at the end of a line. You can include single-line comments inside multi-line comments, but you cannot nest multi-line comments.
You cannot use single-line comments in a PL/SQL block that will be processed dynamically by an Oracle Precompiler program. End-of-line characters are ignored, making the single-line comments extend to the end of the block. Instead, use multi-line comments. You can use multi-line comment delimiters to comment-out whole sections of code.
The COMMIT statement makes permanent any changes made to the database during the current transaction. A commit also makes the changes visible to other users. For more information on PL/SQL transaction processing, see "Overview of Transaction Processing in PL/SQL".
The SQL COMMIT statement can be embedded as static SQL in PL/SQL. For syntax details on the SQL COMMIT statement, see the Oracle Database SQL Language Reference.
Usage Notes
The COMMIT statement releases all row and table locks, and erases any savepoints you marked since the last commit or rollback. Until your changes are committed:
You can see the changes when you query the tables you modified, but other users cannot see the changes.
If you change your mind or need to correct a mistake, you can use the ROLLBACK statement to roll back (undo) the changes.
If you commit while a FOR UPDATE cursor is open, a subsequent fetch on that cursor raises an exception. The cursor remains open, so you must still close it. For more information, see "Using FOR UPDATE".
Related Topics
See Also:
Oracle Database Advanced Application Developer's Guide for information about committing transactions
Oracle Database Reference for information about the COMMIT_WRITE initialization parameter
You can declare constants and variables in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage for a value, specify its datatype, and specify a name that you can reference. Declarations can also assign an initial value and impose the NOT NULL constraint. For more information, see "Declarations".
Syntax
variable_declaration ::=
datatype ::=
constant_declaration ::=
Keyword and Parameter Description
collection_name
A collection (associative array, nested table, or varray) previously declared within the current scope.
collection_type_name
A user-defined collection type defined using the datatype specifier TABLE or VARRAY.
Denotes the declaration of a constant. You must initialize a constant in its declaration. Once initialized, the value of a constant cannot be changed.
constant_name
A program constant. For naming conventions, see "Identifiers".
cursor_name
An explicit cursor previously declared within the current scope.
cursor_variable_name
A PL/SQL cursor variable previously declared within the current scope.
db_table_name
A database table or view that must be accessible when the declaration is elaborated.
db_table_name.column_name
A database table and column that must be accessible when the declaration is elaborated.
expression
A combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the declaration is elaborated, the value of expression is assigned to the constant or variable. The value and the constant or variable must have compatible datatypes.
A constraint that prevents the program from assigning a null value to a variable or constant. Assigning a null to a variable defined as NOT NULL raises the predefined exception VALUE_ERROR. The constraint NOT NULL must be followed by an initialization clause.
object_name
An instance of an object type previously declared within the current scope.
record_name
A user-defined or %ROWTYPE record previously declared within the current scope.
record_name.field_name
A field in a user-defined or %ROWTYPE record previously declared within the current scope.
record_type_name
A user-defined record type that is defined using the datatype specifier RECORD.
ref_cursor_type_name
A user-defined cursor variable type, defined using the datatype specifier REF CURSOR.
%ROWTYPE
Represents a record that can hold a row from a database table or a cursor. Fields in the record have the same names and datatypes as columns in the row.
scalar_datatype_name
A predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2. Includes any qualifiers for size, precision, and character or byte semantics.
%TYPE
Represents the datatype of a previously declared collection, cursor variable, field, object, record, database column, or variable.
variable_name
A program variable.
Usage Notes
Constants and variables are initialized every time a block or subprogram is entered. By default, variables are initialized to NULL. Whether public or private, constants and variables declared in a package spec are initialized only once for each session.
An initialization clause is required when declaring NOT NULL variables and when declaring constants. If you use %ROWTYPE to declare a variable, initialization is not allowed.
You can define constants of complex types that have no literal values or predefined constructors, by invoking a function that returns a filled-in value. For example, you can make a constant associative array this way.
Related Topics
The CONTINUE statement exits the current iteration of a loop and transfers control to the next iteration. The CONTINUE statement has two forms: the unconditional CONTINUE and the conditional CONTINUE WHEN. With either form, you can name the loop to be exited. For more information, see "Controlling Loop Iterations (LOOP, EXIT, and CONTINUE Statements)".
Syntax
continue_statement ::=
Keyword and Parameter Description
boolean_expression
An expression that returns the Boolean value TRUE, FALSE, or NULL. It is evaluated with each iteration of the loop. If the expression returns TRUE, the current iteration of the loop (or the iteration of the loop identified by label_name) is exited immediately. For the syntax of boolean_expression, see "Expression Definition".
CONTINUE
An unconditional CONTINUE statement (that is, one without a WHEN clause) exits the current iteration of the loop immediately. Execution resumes with the next iteration of the loop.
label_name
Identifies the loop exit from either the current loop, or any enclosing labeled loop.
Usage Notes
The CONTINUE statement can be used only inside a loop; you cannot exit from a block directly.
If you use a CONTINUE statement to exit a cursor FOR loop prematurely (for example, to exit an inner loop and transfer control to the next iteration of an outer loop), the cursor is closed automatically (in this context, CONTINUE works like GOTO). The cursor is also closed automatically if an exception is raised inside the loop.
Related Topics
Every explicit cursor and cursor variable has four attributes: %FOUND, %ISOPEN %NOTFOUND, and %ROWCOUNT. When appended to the cursor or cursor variable, these attributes return useful information about the execution of a data manipulation statement. For more information, see "Using Cursor Expressions".
The implicit cursor SQL has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS. For more information, see "SQL Cursor".
Syntax
cursor_attribute ::=
Keyword and Parameter Description
cursor_name
An explicit cursor previously declared within the current scope.
cursor_variable_name
A PL/SQL cursor variable (or parameter) previously declared within the current scope.
%FOUND Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. Before the first fetch from an open cursor, cursor_name%FOUND returns NULL. Afterward, it returns TRUE if the last fetch returned a row, or FALSE if the last fetch failed to return a row.
host_cursor_variable_name
A cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.
%ISOPEN Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. If a cursor is open, cursor_name%ISOPEN returns TRUE; otherwise, it returns FALSE.
%NOTFOUND Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. Before the first fetch from an open cursor, cursor_name%NOTFOUND returns NULL. Thereafter, it returns FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.
%ROWCOUNT Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. When a cursor is opened, %ROWCOUNT is zeroed. Before the first fetch, cursor_name%ROWCOUNT returns 0. Thereafter, it returns the number of rows fetched so far. The number is incremented if the latest fetch returned a row.
Usage Notes
The cursor attributes apply to every cursor or cursor variable. For example, you can open multiple cursors, then use %FOUND or %NOTFOUND to tell which cursors have rows left to fetch. Likewise, you can use %ROWCOUNT to tell how many rows were fetched so far.
If a cursor or cursor variable is not open, referencing it with %FOUND, %NOTFOUND, or %ROWCOUNT raises the predefined exception INVALID_CURSOR.
When a cursor or cursor variable is opened, the rows that satisfy the associated query are identified and form the result set. Rows are fetched from the result set one at a time.
If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS and sets %ROWCOUNT to 1, not the actual number of rows that satisfy the query.
Before the first fetch, %NOTFOUND evaluates to NULL. If FETCH never executes successfully, the EXIT WHEN condition is never TRUE and the loop is never exited. To be safe, use the following EXIT statement instead:
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
You can use the cursor attributes in procedural statements, but not in SQL statements.
Related Topics
To execute a multiple-row query, Oracle opens an unnamed work area that stores processing information. You can access this area through an explicit cursor, which names the work area, or through a cursor variable, which points to the work area. To create cursor variables, you define a REF CURSOR type, then declare cursor variables of that type.
Cursor variables are like C or Pascal pointers, which hold the address of some item instead of the item itself. Declaring a cursor variable creates a pointer, not an item.
For more information, see "Using Cursor Variables (REF CURSORs)".
Syntax
ref_cursor_type_definition ::=
ref_cursor_variable_declaration ::=
Keyword and Parameter Description
cursor_name
An explicit cursor previously declared within the current scope.
cursor_variable_name
A PL/SQL cursor variable previously declared within the current scope.
db_table_name
A database table or view, which must be accessible when the declaration is elaborated.
record_name
A user-defined record previously declared within the current scope.
record_type_name
A user-defined record type that was defined using the datatype specifier RECORD.
REF CURSOR
Cursor variables all have the datatype REF CURSOR.
Specifies the datatype of a cursor variable return value. You can use the %ROWTYPE attribute in the RETURN clause to provide a record type that represents a row in a database table, or a row from a cursor or strongly typed cursor variable. You can use the %TYPE attribute to provide the datatype of a previously declared record.
%ROWTYPE
A record type that represents a row in a database table or a row fetched from a cursor or strongly typed cursor variable. Fields in the record and corresponding columns in the row have the same names and datatypes.
%TYPE
Provides the datatype of a previously declared user-defined record.
type_name
A user-defined cursor variable type that was defined as a REF CURSOR.
Usage Notes
Cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as a bind argument to PL/SQL. Application development tools that have a PL/SQL engine can use cursor variables entirely on the client side.
You can pass cursor variables back and forth between an application and the database server through remote procedure invokes using a database link. If you have a PL/SQL engine on the client side, you can use the cursor variable in either location. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side.
You use cursor variables to pass query result sets between PL/SQL stored subprograms and client programs. Neither PL/SQL nor any client program owns a result set; they share a pointer to the work area where the result set is stored. For example, an OCI program, Oracle Forms application, and the database can all refer to the same work area.
REF CURSOR types can be strong or weak. A strong REF CURSOR type definition specifies a return type, but a weak definition does not. Strong REF CURSOR types are less error-prone because PL/SQL lets you associate a strongly typed cursor variable only with type-compatible queries. Weak REF CURSOR types are more flexible because you can associate a weakly typed cursor variable with any query.
Once you define a REF CURSOR type, you can declare cursor variables of that type. You can use %TYPE to provide the datatype of a record variable. Also, in the RETURN clause of a REF CURSOR type definition, you can use %ROWTYPE to specify a record type that represents a row returned by a strongly (not weakly) typed cursor variable.
Currently, cursor variables are subject to several restrictions. See "Restrictions on Cursor Variables".
You use three statements to control a cursor variable: OPEN-FOR, FETCH, and CLOSE. First, you OPEN a cursor variable FOR a multiple-row query. Then, you FETCH rows from the result set. When all the rows are processed, you CLOSE the cursor variable.
Other OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.
PL/SQL makes sure the return type of the cursor variable is compatible with the INTO clause of the FETCH statement. For each column value returned by the query associated with the cursor variable, there must be a corresponding, type-compatible field or variable in the INTO clause. Also, the number of fields or variables must equal the number of column values. Otherwise, you get an error.
If both cursor variables involved in an assignment are strongly typed, they must have the same datatype. However, if one or both cursor variables are weakly typed, they need not have the same datatype.
When declaring a cursor variable as the formal parameter of a subprogram that fetches from or closes the cursor variable, you must specify the IN or IN OUT mode. If the subprogram opens the cursor variable, you must specify the IN OUT mode.
Be careful when passing cursor variables as parameters. At run time, PL/SQL raises ROWTYPE_MISMATCH if the return types of the actual and formal parameters are incompatible.
You can apply the cursor attributes %FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT to a cursor variable.
If you try to fetch from, close, or apply cursor attributes to a cursor variable that does not point to a query work area, PL/SQL raises the predefined exception INVALID_CURSOR. You can make a cursor variable (or parameter) point to a query work area in two ways:
OPEN the cursor variable FOR the query.
Assign to the cursor variable the value of an already OPENed host cursor variable or PL/SQL cursor variable.
A query work area remains accessible as long as any cursor variable points to it. Therefore, you can pass the value of a cursor variable freely from one scope to another. For example, if you pass a host cursor variable to a PL/SQL block embedded in a Pro*C program, the work area to which the cursor variable points remains accessible after the block completes.
Related Topics
To execute a multiple-row query, Oracle opens an unnamed work area that stores processing information. A cursor lets you name the work area, access the information, and process the rows individually. For more information, see "Querying Data with PL/SQL".
Syntax
cursor_declaration ::=
cursor_spec ::=
cursor_body ::=
cursor_parameter_declaration ::=
rowtype ::=
Keyword and Parameter Description
cursor_name
An explicit cursor previously declared within the current scope.
datatype
A type specifier. For the syntax of datatype, see "Constant and Variable Declaration".
db_table_name
A database table or view that must be accessible when the declaration is elaborated.
expression
A combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the declaration is elaborated, the value of expression is assigned to the parameter. The value and the parameter must have compatible datatypes.
Note:
If you supply an actual parameter forparameter_name when you open the cursor, then expression is not evaluated.parameter_name
A variable declared as the formal parameter of a cursor. A cursor parameter can appear in a query wherever a constant can appear. The formal parameters of a cursor must be IN parameters. The query can also reference other PL/SQL variables within its scope.
record_name
A user-defined record previously declared within the current scope.
record_type_name
A user-defined record type that was defined using the datatype specifier RECORD.
RETURN
Specifies the datatype of a cursor return value. You can use the %ROWTYPE attribute in the RETURN clause to provide a record type that represents a row in a database table or a row returned by a previously declared cursor. Also, you can use the %TYPE attribute to provide the datatype of a previously declared record.
A cursor body must have a SELECT statement and the same RETURN clause as its corresponding cursor spec. Also, the number, order, and datatypes of select items in the SELECT clause must match the RETURN clause.
%ROWTYPE
A record type that represents a row in a database table or a row fetched from a previously declared cursor or cursor variable. Fields in the record and corresponding columns in the row have the same names and datatypes.
select_statement
A query that returns a result set of rows. Its syntax is like that of select_into_statement without the INTO clause. See "SELECT INTO Statement". If the cursor declaration declares parameters, each parameter must be used in the query.
%TYPE
Provides the datatype of a previously declared user-defined record.
Usage Notes
You must declare a cursor before referencing it in an OPEN, FETCH, or CLOSE statement. You must declare a variable before referencing it in a cursor declaration. The word SQL is reserved by PL/SQL as the default name for implicit cursors, and cannot be used in a cursor declaration.
You cannot assign values to a cursor name or use it in an expression. However, cursors and variables follow the same scoping rules. For more information, see "Scope and Visibility of PL/SQL Identifiers".
You retrieve data from a cursor by opening it, then fetching from it. Because the FETCH statement specifies the target variables, using an INTO clause in the SELECT statement of a cursor_declaration is redundant and invalid.
The scope of cursor parameters is local to the cursor, meaning that they can be referenced only within the query used in the cursor declaration. The values of cursor parameters are used by the associated query when the cursor is opened. The query can also reference other PL/SQL variables within its scope.
The datatype of a cursor parameter must be specified without constraints, that is, without precision and scale for numbers, and without length for strings.
Related Topics
The DELETE statement removes rows of data from a specified table or view. For a full description of the DELETE statement, see Oracle Database SQL Language Reference.
Syntax
delete_statement ::=
table_reference ::=
Keyword and Parameter Description
alias
Another (usually short) name for the referenced table or view. Typically referred to later in the WHERE clause.
static_returning_clause
Returns the column values of the deleted rows, in either individual variables or collections (eliminating the need to SELECT the rows first). For details, see "RETURNING INTO Clause".
subquery
A SELECT statement that provides a set of rows for processing. Its syntax is like the select_into_statement without the INTO clause. See "SELECT INTO Statement".
table_reference
A table or view, which must be accessible when you execute the DELETE statement, and for which you must have DELETE privileges.
TABLE (subquery2)
The operand of TABLE is a SELECT statement that returns a single column value, which must be a nested table. Operator TABLE informs Oracle that the value is a collection, not a scalar value.
Refers to the latest row processed by the FETCH statement associated with the cursor identified by cursor_name. The cursor must be FOR UPDATE and must be open and positioned on a row. If the cursor is not open, the CURRENT OF clause causes an error.
If the cursor is open, but no rows were fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND.
WHERE search_condition
Conditionally chooses rows to be deleted from the referenced table or view. Only rows that meet the search condition are deleted. If you omit the WHERE clause, all rows in the table or view are deleted.
Usage Notes
You can use the DELETE WHERE CURRENT OF statement after a fetch from an open cursor (this includes implicit fetches executed in a cursor FOR loop), provided the associated query is FOR UPDATE. This statement deletes the current row; that is, the one just fetched.
The implicit cursor SQL and the cursor attributes %NOTFOUND, %FOUND, and %ROWCOUNT let you access useful information about the execution of a DELETE statement.
Related Topics
The EXCEPTION_INIT pragma associates a user-defined exception name with an Oracle error number. You can intercept any ORA- error and write a specific handler for it instead of using the OTHERS handler. For more information, see "Associating a PL/SQL Exception with a Number (Pragma EXCEPTION_INIT)".
Syntax
exception_init_pragma ::=
Keyword and Parameter Description
error_number
Any valid Oracle error number. These are the same error numbers (always negative) returned by the function SQLCODE.
exception_name
A user-defined exception declared within the current scope.
Signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They pass information to the compiler.
Usage Notes
You can use EXCEPTION_INIT in the declarative part of any PL/SQL block, subprogram, or package. The pragma must appear in the same declarative part as its associated exception, somewhere after the exception declaration.
Be sure to assign only one exception name to an error number.
Related Topics
An exception is a run-time error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers. For more information, see Chapter 11, "Handling PL/SQL Errors".
Syntax
exception_definition ::=
exception_handler ::=
Keyword and Parameter Description
exception_name
A predefined exception such as ZERO_DIVIDE, or a user-defined exception previously declared within the current scope.
Stands for all the exceptions not explicitly named in the exception-handling part of the block. The use of OTHERS is optional and is allowed only as the last exception handler. You cannot include OTHERS in a list of exceptions following the keyword WHEN.
statement
An executable statement. For the syntax of statement, see "Block Declaration".
Introduces an exception handler. You can have multiple exceptions execute the same sequence of statements by following the keyword WHEN with a list of the exceptions, separating them by the keyword OR. If any exception in the list is raised, the associated statements are executed.
Usage Notes
An exception declaration can appear only in the declarative part of a block, subprogram, or package. The scope rules for exceptions and variables are the same. But, unlike variables, exceptions cannot be passed as parameters to subprograms.
Some exceptions are predefined by PL/SQL. For a list of these exceptions, see "Summary of Predefined PL/SQL Exceptions". PL/SQL declares predefined exceptions globally in package STANDARD, so you need not declare them yourself.
Redeclaring predefined exceptions is error prone because your local declaration overrides the global declaration. In such cases, you must use dot notation to specify the predefined exception, as follows:
EXCEPTION
WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN ...
The exception-handling part of a PL/SQL block is optional. Exception handlers must come at the end of the block. They are introduced by the keyword EXCEPTION. The exception-handling part of the block is terminated by the same keyword END that terminates the entire block. An exception handler can reference only those variables that the current block can reference.
Raise an exception only when an error occurs that makes it undesirable or impossible to continue processing. If there is no exception handler in the current block for a raised exception, the exception propagates according to the following rules:
If there is an enclosing block for the current block, the exception is passed on to that block. The enclosing block then becomes the current block. If a handler for the raised exception is not found, the process repeats.
If there is no enclosing block for the current block, an unhandled exception error is passed back to the host environment.
Only one exception at a time can be active in the exception-handling part of a block. Therefore, if an exception is raised inside a handler, the block that encloses the current block is the first block searched to find a handler for the newly raised exception. From there on, the exception propagates normally.
Related Topics
The EXECUTE IMMEDIATE statement builds and executes a dynamic SQL statement in a single operation. For more information about this statement, see "Using the EXECUTE IMMEDIATE Statement".
Syntax
execute_immediate_statement ::=
into_clause ::=
bulk_collect_into_clause ::=
using_clause ::=
Keyword and Parameter Description
bind_argument
Either an expression whose value is passed to the dynamic SQL statement (an in bind), or a variable in which a value returned by the dynamic SQL statement is stored (an out bind).
Used if and only if dynamic_sql_stmt can return multiple rows, this clause specifies one or more collections in which to store the returned rows. This clause must have a corresponding, type-compatible collection_item or :host_array_name for each select_item in dynamic_sql_stmt.
collection_name
The name of a declared collection, in which to store rows returned by dynamic_sql_stmt.
dynamic_returning_clause
Used if and only if dynamic_sql_stmt has a RETURNING INTO clause, this clause returns the column values of the rows affected by dynamic_sql_stmt, in either individual variables or records (eliminating the need to select the rows first). This clause can include OUT bind arguments. For details, see "RETURNING INTO Clause".
dynamic_sql_stmt
A string literal, string variable, or string expression that represents any SQL statement. It must be of type CHAR, VARCHAR2, or CLOB.
host_array_name
An array into which returned rows are stored. The array must be declared in a PL/SQL host environment and passed to PL/SQL as a bind argument (hence the colon (:) prefix).
IN, OUT, IN OUT
Parameter modes of bind arguments. An IN bind argument passes its value to the dynamic SQL statement. An OUT bind argument stores a value that the dynamic SQL statement returns. An IN OUT bind argument passes its initial value to the dynamic SQL statement and stores a value that the dynamic SQL statement returns. The default parameter mode for bind_argument is IN.
INTO
Used if and only if dynamic_sql_stmt is a SELECT statement that can return at most one row, this clause specifies the variables or record into which the column values of the returned row are stored. For each select_item in dynamic_sql_stmt, this clause must have either a corresponding, type-compatible define_variable or a type-compatible record.
record_name
A user-defined or %ROWTYPE record into which a returned row is stored.
Used only if dynamic_sql_stmt includes placeholders, this clause specifies a list of bind arguments.
variable_name
The name of a define variable in which to store a column value of the row returned by dynamic_sql_stmt.
Usage Notes
For DML statements that have a RETURNING clause, you can place OUT bind arguments in the RETURNING INTO clause without specifying the parameter mode, which, by definition, is OUT. If you use both the USING clause and the RETURNING INTO clause, the USING clause can contain only IN arguments.
At run time, bind arguments or define variables replace corresponding placeholders in the dynamic SQL statement. Every placeholder must be associated with a bind argument in the USING clause or RETURNING INTO clause (or both) or with a define variable in the INTO clause.
The value a of bind argument cannot be a Boolean literal (TRUE, FALSE, or NULL). To pass the value NULL to the dynamic SQL statement, use an uninitialized variable where you want to use NULL, as in "Uninitialized Variable for NULL in USING Clause".
You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. You incur some overhead, because EXECUTE IMMEDIATE prepares the dynamic string before every execution.
Note:
When using dynamic SQL, be aware of SQL injection, a security risk. For more information on SQL injection, see "Avoiding SQL Injection in PL/SQL".Related Topics
The EXIT statement exits a loop and transfers control to the end of the loop. The EXIT statement has two forms: the unconditional EXIT and the conditional EXIT WHEN. With either form, you can name the loop to be exited. For more information, see "Controlling Loop Iterations (LOOP, EXIT, and CONTINUE Statements)".
Syntax
exit_statement ::=
Keyword and Parameter Description
boolean_expression
An expression that returns the Boolean value TRUE, FALSE, or NULL. It is evaluated with each iteration of the loop. If the expression returns TRUE, the current loop (or the loop labeled by label_name) is exited immediately. For the syntax of boolean_expression, see "Expression Definition".
EXIT
An unconditional EXIT statement (that is, one without a WHEN clause) exits the current loop immediately. Execution resumes with the statement following the loop.
label_name
Identifies the loop exit from: either the current loop, or any enclosing labeled loop.
Usage Notes
The EXIT statement can be used only inside a loop; you cannot exit from a block directly. PL/SQL lets you code an infinite loop. For example, the following loop will never terminate normally so you must use an EXIT statement to exit the loop.
WHILE TRUE LOOP ... END LOOP;
If you use an EXIT statement to exit a cursor FOR loop prematurely, the cursor is closed automatically. The cursor is also closed automatically if an exception is raised inside the loop.
Related Topics
An expression is an arbitrarily complex combination of operands (variables, constants, literals, operators, function calls, and placeholders) and operators. The simplest expression is a single variable.
The PL/SQL compiler determines the datatype of an expression from the types of the operands and operators that comprise the expression. Every time the expression is evaluated, a single value of that type results.
Syntax
expression ::=
boolean_expression ::=
other_boolean_form ::=
character_expression ::=
numeric_subexpression ::=
date_expression ::=
numeric_expression ::=
Keyword and Parameter Description
This comparison operator tests whether a value lies in a specified range. It means: greater than or equal to low value and less than or equal to high value.
boolean_constant_name
A constant of type BOOLEAN, which must be initialized to the value TRUE, FALSE, or NULL. Arithmetic operations on Boolean constants are not allowed.
boolean_expression
An expression that returns the Boolean value TRUE, FALSE, or NULL.
boolean_function_call
Any function call that returns a Boolean value.
boolean_literal
The predefined values TRUE, FALSE, or NULL (which stands for a missing, unknown, or inapplicable value). You cannot insert the value TRUE or FALSE into a database column.
boolean_variable_name
A variable of type BOOLEAN. Only the values TRUE, FALSE, and NULL can be assigned to a BOOLEAN variable. You cannot select or fetch column values into a BOOLEAN variable. Also, arithmetic operations on BOOLEAN variables are not allowed.
%BULK_ROWCOUNT
Designed for use with the FORALL statement, this is a composite attribute of the implicit cursor SQL. For more information, see "SQL Cursor".
character_constant_name
A previously declared constant that stores a character value. It must be initialized to a character value or a value implicitly convertible to a character value.
character_expression
An expression that returns a character or character string.
character_function_call
A function call that returns a character value or a value implicitly convertible to a character value.
character_literal
A literal that represents a character value or a value implicitly convertible to a character value.
character_variable_name
A previously declared variable that stores a character value.
collection_name
A collection (nested table, index-by table, or varray) previously declared within the current scope.
cursor_name
An explicit cursor previously declared within the current scope.
cursor_variable_name
A PL/SQL cursor variable previously declared within the current scope.
date_constant_name
A previously declared constant that stores a date value. It must be initialized to a date value or a value implicitly convertible to a date value.
date_expression
An expression that returns a date/time value.
date_function_call
A function call that returns a date value or a value implicitly convertible to a date value.
date_literal
A literal representing a date value or a value implicitly convertible to a date value.
date_variable_name
A previously declared variable that stores a date value.
EXISTS, COUNT, FIRST, LAST, LIMIT, NEXT, PRIOR
Collection methods. When appended to the name of a collection, these methods return useful information. For example, EXISTS(n) returns TRUE if the nth element of a collection exists. Otherwise, EXISTS(n) returns FALSE. For more information, see "Collection Methods".
exponent
An expression that must return a numeric value.
%FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT
Cursor attributes. When appended to the name of a cursor or cursor variable, these attributes return useful information about the execution of a multiple-row query. You can also append them to the implicit cursor SQL.
host_cursor_variable_name
A cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. Host cursor variables must be prefixed with a colon.
host_variable_name
A variable declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. The datatype of the host variable must be implicitly convertible to the appropriate PL/SQL datatype. Also, host variables must be prefixed with a colon.
IN
Comparison operator that tests set membership. It means: equal to any member of. The set can contain nulls, but they are ignored. Also, expressions of the form
value NOT IN set
return FALSE if the set contains a null.
index
A numeric expression that must return a value of type BINARY_INTEGER, PLS_INTEGER, or a value implicitly convertible to that datatype.
indicator_name
An indicator variable declared in a PL/SQL host environment and passed to PL/SQL. Indicator variables must be prefixed with a colon. An indicator variable indicates the value or condition of its associated host variable. For example, in the Oracle Precompiler environment, indicator variables can detect nulls or truncated values in output host variables.
Comparison operator that returns the Boolean value TRUE if its operand is null, or FALSE if its operand is not null.
Comparison operator that compares a character value to a pattern. Case is significant. LIKE returns the Boolean value TRUE if the character patterns match, or FALSE if they do not match.
NOT, AND, OR
Logical operators, which follow the tri-state logic of Table 2-3. AND returns the value TRUE only if both its operands are true. OR returns the value TRUE if either of its operands is true. NOT returns the opposite value (logical negation) of its operand. For more information, see "Logical Operators".
NULL
Keyword that represents a null. It stands for a missing, unknown, or inapplicable value. When NULL is used in a numeric or date expression, the result is a null.
numeric_constant_name
A previously declared constant that stores a numeric value. It must be initialized to a numeric value or a value implicitly convertible to a numeric value.
numeric_expression
An expression that returns an integer or real value.
numeric_function_call
A function call that returns a numeric value or a value implicitly convertible to a numeric value.
numeric_literal
A literal that represents a number or a value implicitly convertible to a number.
numeric_variable_name
A previously declared variable that stores a numeric value.
pattern
A character string compared by the LIKE operator to a specified string value. It can include two special-purpose characters called wildcards. An underscore (_) matches exactly one character; a percent sign (%) matches zero or more characters. The pattern can be followed by ESCAPE 'character_literal', which turns off wildcard expansion wherever the escape character appears in the string followed by a percent sign or underscore.
relational_operator
Operator that compares expressions. For the meaning of each operator, see "Comparison Operators".
SQL
A cursor opened implicitly by Oracle to process a SQL data manipulation statement. The implicit cursor SQL always refers to the most recently executed SQL statement.
+, -, /, *, **
Symbols for the addition, subtraction, division, multiplication, and exponentiation operators.
||
The concatenation operator. As the following example shows, the result of concatenating string1 with string2 is a character string that contains string1 followed by string2:
'Good' || ' morning!' = 'Good morning!'
The next example shows that nulls have no effect on the result of a concatenation:
'suit' || NULL || 'case' = 'suitcase'
A null string (''), which is zero characters in length, is treated like a null.
Usage Notes
In a Boolean expression, you can only compare values that have compatible datatypes. For more information, see "PL/SQL Datatype Conversion".
In conditional control statements, if a Boolean expression returns TRUE, its associated sequence of statements is executed. But, if the expression returns FALSE or NULL, its associated sequence of statements is not executed.
The relational operators can be applied to operands of type BOOLEAN. By definition, TRUE is greater than FALSE. Comparisons involving nulls always return a null. The value of a Boolean expression can be assigned only to Boolean variables, not to host variables or database columns. Also, datatype conversion to or from type BOOLEAN is not supported.
You can use the addition and subtraction operators to increment or decrement a date value, as the following examples show:
hire_date := '10-MAY-95'; hire_date := hire_date + 1; -- makes hire_date '11-MAY-95' hire_date := hire_date - 5; -- makes hire_date '06-MAY-95'
When PL/SQL evaluates a boolean expression, NOT has the highest precedence, AND has the next-highest precedence, and OR has the lowest precedence. However, you can use parentheses to override the default operator precedence.
Within an expression, operations occur in the following order (first to last):
Parentheses
Exponents
Unary operators
Multiplication and division
Addition, subtraction, and concatenation
PL/SQL evaluates operators of equal precedence in no particular order. When parentheses enclose an expression that is part of a larger expression, PL/SQL evaluates the parenthesized expression first, then uses the result in the larger expression. When parenthesized expressions are nested, PL/SQL evaluates the innermost expression first and the outermost expression last.
(a + b) > c -- Boolean expression NOT finished -- Boolean expression TO_CHAR(acct_no) -- character expression 'Fat ' || 'cats' -- character expression '15-NOV-05' -- date expression MONTHS_BETWEEN(d1, d2) -- date expression pi * r**2 -- numeric expression emp_cv%ROWCOUNT -- numeric expression
Related Topics
The FETCH statement retrieves rows of data from the result set of a multiple-row query. You can fetch rows one at a time, several at a time, or all at once. The data is stored in variables or fields that correspond to the columns selected by the query. For more information, see "Querying Data with PL/SQL".
Syntax
fetch_statement ::=
into_clause ::=
bulk_collect_into_clause ::=
Keyword and Parameter Description
Instructs the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. The SQL engine bulk-binds all collections referenced in the INTO list.
collection_name
The name of a declared collection into which column values are bulk fetched. For each query select_item, there must be a corresponding, type-compatible collection in the list.
cursor_name
An explicit cursor declared within the current scope.
cursor_variable_name
A PL/SQL cursor variable (or parameter) declared within the current scope.
host_array_name
An array (declared in a PL/SQL host environment and passed to PL/SQL as a bind argument) into which column values are bulk fetched. For each query select_item, there must be a corresponding, type-compatible array in the list. Host arrays must be prefixed with a colon.
host_cursor_variable_name
A cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.
This optional clause, allowed only in bulk (not scalar) FETCH statements, lets you bulk fetch several rows at a time, rather than the entire result set.
record_name
A user-defined or %ROWTYPE record into which rows of values are fetched. For each column value returned by the query associated with the cursor or cursor variable, there must be a corresponding, type-compatible field in the record.
variable_name
A variable into which a column value is fetched. For each column value returned by the query associated with the cursor or cursor variable, there must be a corresponding, type-compatible variable in the list.
Usage Notes
You must use either a cursor FOR loop or the FETCH statement to process a multiple-row query.
Any variables in the WHERE clause of the query are evaluated only when the cursor or cursor variable is opened. To change the result set or the values of variables in the query, you must reopen the cursor or cursor variable with the variables set to their new values.
To reopen a cursor, you must close it first. However, you need not close a cursor variable before reopening it.
You can use different INTO lists on separate fetches with the same cursor or cursor variable. Each fetch retrieves another row and assigns values to the target variables.
If you FETCH past the last row in the result set, the values of the target fields or variables are indeterminate and the %NOTFOUND attribute returns TRUE.
PL/SQL makes sure the return type of a cursor variable is compatible with the INTO clause of the FETCH statement. For each column value returned by the query associated with the cursor variable, there must be a corresponding, type-compatible field or variable in the INTO clause. Also, the number of fields or variables must equal the number of column values.
When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the IN or IN OUT mode. However, if the subprogram also opens the cursor variable, you must specify the IN OUT mode.
Because a sequence of FETCH statements always runs out of data to retrieve, no exception is raised when a FETCH returns no data. To detect this condition, you must use the cursor attribute %FOUND or %NOTFOUND.
PL/SQL raises the predefined exception INVALID_CURSOR if you try to fetch from a closed or never-opened cursor or cursor variable.
Restrictions on BULK COLLECT INTO
The following restrictions apply to the BULK COLLECT INTO clause:
You cannot bulk collect into an associative array that has a string type for the key.
You can use the BULK COLLECT INTO clause only in server-side programs (not in client-side programs). Otherwise, you get the following error:
this feature is not supported in client-side programs
All target variables listed in a BULK COLLECT INTO clause must be collections.
Composite targets (such as objects) cannot be used in the RETURNING INTO clause. Otherwise, you get the following error:
error unsupported feature with RETURNING clause
When implicit datatype conversions are needed, multiple composite targets cannot be used in the BULK COLLECT INTO clause.
When an implicit datatype conversion is needed, a collection of a composite target (such as a collection of objects) cannot be used in the BULK COLLECT INTO clause.
Related Topics
The FORALL statement issues a series of static or dynamic DML statements, usually much faster than an equivalent FOR loop. It requires some setup code, because each iteration of the loop must use values from one or more collections in its VALUES or WHERE clauses. For more information, see "Reducing Loop Overhead for DML Statements and Queries with Bulk SQL".
Syntax
forall_statement ::=
bounds_clause ::=
Keyword and Parameter Description
A clause specifying that the values of the index variable correspond to the subscripts of the elements of the specified collection. With this clause, you can use FORALL with nested tables where some elements were deleted, or with associative arrays that have numeric subscripts.
BETWEEN lower_bound AND upper_bound
Limits the range of subscripts in the INDICES OF clause. If a subscript in the range does not exist in the collection, that subscript is skipped.
VALUES OF index_collection_name
A clause specifying that the subscripts for the FORALL index variable are taken from the values of the elements in another collection, specified by index_collection_name. This other collection acts as a set of pointers; FORALL can iterate through subscripts in arbitrary order, even using the same subscript more than once, depending on what elements you include in index_collection_name.
The index collection must be a nested table, or an associative array indexed by PLS_INTEGER or BINARY_INTEGER, whose elements are also PLS_INTEGER or BINARY_INTEGER. If the index collection is empty, an exception is raised and the FORALL statement is not executed.
index_name
An undeclared identifier that can be referenced only within the FORALL statement and only as a collection subscript.
The implicit declaration of index_name overrides any other declaration outside the loop. You cannot refer to another variable with the same name inside the statement. Inside a FORALL statement, index_name cannot appear in expressions and cannot be assigned a value.
lower_bound .. upper_bound
Numeric expressions that specify a valid range of consecutive index numbers. PL/SQL rounds them to the nearest integer, if necessary. The SQL engine executes the SQL statement once for each index number in the range. The expressions are evaluated once, when the FORALL statement is entered.
Optional keywords that cause the FORALL loop to continue even if some DML operations fail. Instead of raising an exception immediately, the program raises a single exception after the FORALL statement finishes. The details of the errors are available after the loop in SQL%BULK_EXCEPTIONS. The program can report or clean up all the errors after the FORALL loop, rather than handling each exception as it happens. See "Handling FORALL Exceptions (%BULK_EXCEPTIONS Attribute)".
sql_statement
A static, such as UPDATE or DELETE, or dynamic (EXECUTE IMMEDIATE) DML statement that references collection elements in the VALUES or WHERE clauses.
Usage Notes
Although the SQL statement can reference more than one collection, the performance benefits apply only to subscripted collections.
If sql-statement is an INSERT statement that fires a compound trigger, each iteration of the FORALL loop fires the compound trigger anew, destroying its performance benefit. For information about compound triggers, see "Compound Triggers".
If a FORALL statement fails, database changes are rolled back to an implicit savepoint marked before each execution of the SQL statement. Changes made during previous iterations of the FORALL loop are not rolled back.
Restrictions
The following restrictions apply to the FORALL statement:
You cannot loop through the elements of an associative array that has a string type for the key.
Within a FORALL loop, you cannot refer to the same collection in both the SET clause and the WHERE clause of an UPDATE statement. You might need to make a second copy of the collection and refer to the new name in the WHERE clause.
You can use the FORALL statement only in server-side programs, not in client-side programs.
The INSERT, UPDATE, or DELETE statement must reference at least one collection. For example, a FORALL statement that inserts a set of constant values in a loop raises an exception.
When you specify an explicit range, all collection elements in that range must exist. If an element is missing or was deleted, you get an error.
When you use the INDICES OF or VALUES OF clauses, all the collections referenced in the DML statement must have subscripts matching the values of the index variable. Make sure that any DELETE, EXTEND, and so on operations are applied to all the collections so that they have the same set of subscripts. If any of the collections is missing a referenced element, you get an error. If you use the SAVE EXCEPTIONS clause, this error is treated like any other error and does not stop the FORALL statement.
You cannot refer to individual record fields within DML statements invoked by a FORALL statement. Instead, you can specify the entire record with the SET ROW clause in an UPDATE statement, or the VALUES clause in an INSERT statement.
Collection subscripts must be just the index variable rather than an expression, such as i rather than i+1.
The cursor attribute %BULK_ROWCOUNT cannot be assigned to other collections, or be passed as a parameter to subprograms.
If the FORALL uses a dynamic SQL statement, then values (binds for the dynamic SQL statement) in the USING clause must be simple references to the collection, not expressions. For example, collection_name(i) is valid, but UPPER(collection_name(i) is not valid.
Related Topics
A function is a subprogram that returns a single value. A PL/SQL block or parent subprogram must declare and define a function before invoking it. The declaration always includes the specification ("spec"). The declaration can also include the definition. If the declaration does not include the definition, the definition must appear later in the same block or subprogram as the declaration.
For more information about functions, see "What Are PL/SQL Subprograms?".
Note:
Declaring and defining a function in a PL/SQL block or package is different from creating a function with the SQL statementCREATE FUNCTION. For information about CREATE FUNCTION, see Oracle Database SQL Language Reference.Syntax
function_declaration ::=
function_heading ::=
parameter_declaration ::=
function_definition ::=
result_cache_clause ::=
Keyword and Parameter Description
body
For syntax, see "Block Declaration".
datatype
A type specifier. For syntax, see "Constant and Variable Declaration".
You cannot constrain (with NOT NULL for example) the datatype of a function parameter or a function return value.
data_source
The name of either a database table or a database view.
declare_section
Declares function elements. For syntax, see "Block Declaration".
Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is invoked with the same values for its parameters. This helps the optimizer avoid redundant function calls: If a stored function was invoked previously with the same arguments, the optimizer can elect to use the previous result.
Do not specify DETERMINISTIC for a function whose result depends on the state of session variables or schema objects, because results might vary across calls. Instead, consider making the function result-cached (see "Making Result-Cached Functions Handle Session-Specific Settings" and "Making Result-Cached Functions Handle Session-Specific Application Contexts").
Only DETERMINISTIC functions can be invoked from a function-based index or a materialized view that has query-rewrite enabled. For more information and possible limitations of the DETERMINISTIC option, see the CREATE FUNCTION statement in the Oracle Database SQL Language Reference.
expression
An arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the declaration is elaborated, the value of expression is assigned to the parameter. The value and the parameter must have compatible datatypes. For syntax, see "Expression Definition".
Note:
If a function call includes an actual parameter forparameter_name, then expression is not evaluated for that function call (see Example 8-8).function_name
The name you choose for the function.
IN, OUT, IN OUT
Parameter modes that define the action of formal parameters. An IN parameter passes values to the subprogram being invoked. An OUT parameter returns values to the invoker of the subprogram. An IN OUT parameter passes initial values to the subprogram being invoked, and returns updated values to the invoker.
A compiler hint (not directive) that allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference instead of by value (the default). The function can run faster, because it does not have to make temporary copies of these parameters, but the results can be different if the function ends with an unhandled exception. For more information, see "Using Default Values for Subprogram Parameters".
Declares that a stored function can be used safely in the slave sessions of parallel DML evaluations. The state of a main (logon) session is never shared with slave sessions. Each slave session has its own state, which is initialized when the session begins. The function result must not depend on the state of session (static) variables. Otherwise, results might vary across sessions. For information on the PARALLEL_ENABLE option, see the CREATE FUNCTION statement in the Oracle Database SQL Language Reference.
parameter_name
A formal parameter, a variable declared in a function spec and referenced in the function body.
PIPELINED
PIPELINED specifies to return the results of a table function iteratively. A table function returns a collection type (a nested table or varray) with elements that are SQL datatypes. You can query table functions using the TABLE keyword before the function name in the FROM clause of a SQL query. For more information, see "Performing Multiple Transformations with Pipelined Table Functions".
procedure_declaration
Declares, and might also define, a procedure. If the declaration does not define the procedure, the definition must appear later in the same block or subprogram as the declaration. See "Procedure Declaration and Definition".
Specifies the data sources on which the results of a function depend. For more information, see "Using the Cross-Session PL/SQL Function Result Cache".
Causes the results of the function to be cached. For more information, see "Using the Cross-Session PL/SQL Function Result Cache".
Introduces the RETURN clause, which specifies the datatype of the return value.
statement
A statement. For syntax, see "Block Declaration".
type_definition
Specifies a user-defined datatype. For syntax, see "Block Declaration".
view_name
The name of a database view.
Initializes IN parameters to default values.
Usage Notes
A function is invoked as part of an expression. For example:
promotable := sal_ok(new_sal, new_title) AND (rating > 3);
To be callable from SQL statements, a stored function must obey certain rules that control side effects. See "Controlling Side Effects of PL/SQL Subprograms".
In a function, at least one execution path must lead to a RETURN statement. Otherwise, you get the following run-time error:
function returned without value
The RETURN statement must contain an expression, which is evaluated when the RETURN statement is executed. The resulting value is assigned to the function identifier, which acts like a variable.
You can write the function spec and body as a unit. Or, you can separate the function spec from its body. That way, you can hide implementation details by placing the function in a package. You can define functions in a package body without declaring their specs in the package spec. However, such functions can be invoked only from inside the package.
Inside a function, an IN parameter acts like a constant; you cannot assign it a value. An OUT parameter acts like a local variable; you can change its value and reference the value in any way. An IN OUT parameter acts like an initialized variable; you can assign it a value, which can be assigned to another variable. For information about the parameter modes, see Table 8-3.
Avoid using the OUT and IN OUT modes with functions. The purpose of a function is to take zero or more parameters and return a single value. Functions must be free from side effects, which change the values of variables not local to the subprogram.
Related Topics
The GOTO statement branches unconditionally to a statement label or block label. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. The GOTO statement transfers control to the labelled statement or block. For more information, see "Using the GOTO Statement".
Syntax
label_declaration ::=
goto_statement ::=
Keyword and Parameter Description
label_name
A label that you assigned to an executable statement or a PL/SQL block. A GOTO statement transfers control to the statement or block following <<label_name>>.
Usage Notes
A GOTO label must precede an executable statement or a PL/SQL block. A GOTO statement cannot branch into an IF statement, LOOP statement, or sub-block. To branch to a place that does not have an executable statement, add the NULL statement.
From the current block, a GOTO statement can branch to another place in the block or into an enclosing block, but not into an exception handler. From an exception handler, a GOTO statement can branch into an enclosing block, but not into the current block.
If you use the GOTO statement to exit a cursor FOR loop prematurely, the cursor is closed automatically. The cursor is also closed automatically if an exception is raised inside the loop.
A given label can appear only once in a block. However, the label can appear in other blocks including enclosing blocks and sub-blocks. If a GOTO statement cannot find its target label in the current block, it branches to the first enclosing block in which the label appears.
The IF statement executes or skips a sequence of statements, depending on the value of a Boolean expression. For more information, see "Testing Conditions (IF and CASE Statements)".
Syntax
if_statement ::=
Keyword and Parameter Description
boolean_expression
An expression that returns the Boolean value TRUE, FALSE, or NULL. Examples are comparisons for equality, greater-than, or less-than. The sequence following the THEN keyword is executed only if the expression returns TRUE.
ELSE
If control reaches this keyword, the sequence of statements that follows it is executed. This occurs when none of the previous conditional tests returned TRUE.
ELSIF
Introduces a Boolean expression that is evaluated if none of the preceding conditions returned TRUE.
THEN
If the expression returns TRUE, the statements after the THEN keyword are executed.
Usage Notes
There are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF. The simplest form of IF statement associates a Boolean expression with a sequence of statements enclosed by the keywords THEN and END IF. The sequence of statements is executed only if the expression returns TRUE. If the expression returns FALSE or NULL, the IF statement does nothing. In either case, control passes to the next statement.
The second form of IF statement adds the keyword ELSE followed by an alternative sequence of statements. The sequence of statements in the ELSE clause is executed only if the Boolean expression returns FALSE or NULL. Thus, the ELSE clause ensures that a sequence of statements is executed.
The third form of IF statement uses the keyword ELSIF to introduce additional Boolean expressions. If the first expression returns FALSE or NULL, the ELSIF clause evaluates another expression. An IF statement can have any number of ELSIF clauses; the final ELSE clause is optional. Boolean expressions are evaluated one by one from top to bottom. If any expression returns TRUE, its associated sequence of statements is executed and control passes to the next statement. If all expressions return FALSE or NULL, the sequence in the ELSE clause is executed.
An IF statement never executes more than one sequence of statements because processing is complete after any sequence of statements is executed. However, the THEN and ELSE clauses can include more IF statements. That is, IF statements can be nested.
Related Topics
The INLINE pragma specifies that a subprogram call is, or is not, to be inlined. Inlining replaces a subprogram call (to a subprogram in the same program unit) with a copy of the called subprogram. For more information, see "How PL/SQL Optimizes Your Programs".
Syntax
inline_pragma ::=
Keyword and Parameter Descriptions
identifier
The name of a subprogram.
YES
If PLSQL_OPTIMIZE_LEVEL=2, YES specifies that the subprogram call is to be inlined.
If PLSQL_OPTIMIZE_LEVEL=3, YES specifies that the subprogram call has a high priority for inlining.
NO
Specifies that the subprogram call is not to be inlined.
Usage Notes
The INLINE pragma affects only the immediately following declaration or statement, and only some kinds of statements.
When the INLINE pragma immediately precedes one of the following statements, the pragma affects every call to the specified subprogram in that statement (see Example 13-1):
Assignment
Call
Conditional
CASE
CONTINUE-WHEN
EXECUTE IMMEDIATE
EXIT-WHEN
LOOP
RETURN
The INLINE pragma does not affect statements that are not in the preceding list.
When the INLINE pragma immediately precedes a declaration, it affects the following:
Every call to the specified subprogram in that declaration
Every initialization value in that declaration except the default initialization values of records
If the name of the subprogram (identifier) is overloaded (that is, if it belongs to more than one subprogram), the INLINE pragma applies to every subprogram with that name (see Example 13-2). For information about overloaded subprogram names, see "Overloading PL/SQL Subprogram Names".
The PRAGMA INLINE (identifier, 'YES') very strongly encourages the compiler to inline a particular call, but the compiler might not to do so if other considerations or limits make the inlining undesirable. If you specify PRAGMA INLINE ( identifier,'NO'), the compiler does not inline calls to subprograms named identifier (see Example 13-3).
Multiple pragmas can affect the same declaration or statement. Each pragma applies its own effect to the statement. If PRAGMA INLINE(identifier,'YES') and PRAGMA INLINE (identifier,'NO') have the same identifier, 'NO' overrides 'YES' (see Example 13-4). One PRAGMA INLINE (identifier,'NO') overrides any number of occurrences of PRAGMA INLINE (identifier,'YES'), and the order of these pragmas is not important.
In Example 13-1 and Example 13-2, assume that PLSQL_OPTIMIZE_LEVEL=2.
In Example 13-1, the INLINE pragma affects the procedure calls p1(1) and p1(2), but not the procedure calls p1(3) and p1(4).
Example 13-1 Specifying that a Subprogram Is To Be Inlined
PROCEDURE p1 (x PLS_INTEGER) IS ...
...
PRAGMA INLINE (p1, 'YES');
x:= p1(1) + p1(2) + 17; -- These 2 calls to p1 will be inlined
...
x:= p1(3) + p1(4) + 17; -- These 2 calls to p1 will not be inlined
...
In Example 13-2 the INLINE pragma affects both functions named p2.
Example 13-2 Specifying that an Overloaded Subprogram Is To Be Inlined
FUNCTION p2 (p boolean) return PLS_INTEGER IS ...
FUNCTION p2 (x PLS_INTEGER) return PLS_INTEGER IS ...
...
PRAGMA INLINE(p2, 'YES');
x := p2(true) + p2(3);
...
In Example 13-3, assume that PLSQL_OPTIMIZE_LEVEL=3. The INLINE pragma affects the procedure calls p1(1) and p1(2), but not the procedure calls p1(3) and p1(4).
Example 13-3 Specifying that a Subprogram Is Not To Be Inlined
PROCEDURE p1 (x PLS_INTEGER) IS ...
...
PRAGMA INLINE (p1, 'NO');
x:= p1(1) + p1(2) + 17; -- These 2 calls to p1 will not be inlined
...
x:= p1(3) + p1(4) + 17; -- These 2 calls to p1 might be inlined
...
In Example 13-4, the first INLINE pragma affects the two calls to procedure p1, but then the second INLINE pragma overrides it.
Example 13-4 Applying Two INLINE Pragmas to the Same Subprogram
PROCEDURE p1 (x PLS_INTEGER) IS ... ... PRAGMA INLINE (p1, PRAGMA INLINE (p1, 'YES'); PRAGMA INLINE (p1, PRAGMA INLINE (p1, 'NO'); x:= p1(1) + p1(2) + 17; -- These 2 calls to p1 will not be inlined ...
Related Topics
The INSERT statement inserts one or more rows of data into a database table. For a full description of the INSERT statement, see Oracle Database SQL Language Reference.
Syntax
insert_statement ::=
Keyword and Parameter Description
alias
Another (usually short) name for the referenced table or view.
column_name [, column_name]...
A list of columns in a database table or view. The columns can be listed in any order, as long as the expressions in the VALUES clause are listed in the same order. Each column name can only be listed once. If the list does not include all the columns in a table, each missing columns is set to NULL or to a default value specified in the CREATE TABLE statement.
sql_expression
Any expression valid in SQL—for example, a literal, a PL/SQL variable, or a SQL query that returns a single value. For more information, see Oracle Database SQL Language Reference. PL/SQL also lets you use a record variable here.
static_returning_clause
Returns the column values of the inserted rows, in either individual variables or collections (eliminating the need to SELECT the rows first). For details, see "RETURNING INTO Clause".
subquery
A SELECT statement that provides a set of rows for processing. Its syntax is like that of select_into_statement without the INTO clause. See "SELECT INTO Statement".
subquery3
A SELECT statement that returns a set of rows. Each row returned by the select statement is inserted into the table. The subquery must return a value for every column in the column list, or for every column in the table if there is no column list.
table_reference
A table or view that must be accessible when you execute the INSERT statement, and for which you must have INSERT privileges. For the syntax of table_reference, see "DELETE Statement".
TABLE (subquery2)
The operand of TABLE is a SELECT statement that returns a single column value representing a nested table. This operator specifies that the value is a collection, not a scalar value.
Assigns the values of expressions to corresponding columns in the column list. If there is no column list, the first value is inserted into the first column defined by the CREATE TABLE statement, the second value is inserted into the second column, and so on. There must be one value for each column in the column list. The datatypes of the values being inserted must be compatible with the datatypes of corresponding columns in the column list.
Usage Notes
Character and date literals in the VALUES list must be enclosed by single quotes ('). Numeric literals are not enclosed by quotes.
The implicit cursor SQL and the cursor attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN let you access useful information about the execution of an INSERT statement.
Related Topics
A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. The numeric literal 135 and the string literal 'hello world' are examples. For more information, see "Literals".
Syntax
numeric_literal ::=
integer_literal ::=
real_number_literal ::=
character_literal ::=
string_literal ::=
boolean_literal ::=
Keyword and Parameter Description
character
A member of the PL/SQL character set. For more information, see "Character Sets and Lexical Units".
digit
One of the numerals 0 .. 9.
TRUE, FALSE, NULL
A predefined Boolean value.
Usage Notes
Integer and real numeric literals can be used in arithmetic expressions. Numeric literals must be separated by punctuation. Spaces can be used in addition to the punctuation. For additional information, see "Numeric Literals".
A character literal is an individual character enclosed by single quotes (apostrophes). Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols. PL/SQL is case sensitive within character literals. For example, PL/SQL considers the literals 'Q' and 'q' to be different. For additional information, see "Character Literals".
A string literal is a sequence of zero or more characters enclosed by single quotes. The null string ('') contains zero characters. A string literal can hold up to 32,767 characters. PL/SQL is case sensitive within string literals. For example, PL/SQL considers the literals 'white' and 'White' to be different.
To represent an apostrophe within a string, enter two single quotes instead of one. For literals where doubling the quotes is inconvenient or hard to read, you can designate an escape character using the notation q'esc_char ... esc_char'. This escape character must not occur anywhere else inside the string.
Trailing blanks are significant within string literals, so 'abc' and 'abc ' are different. Trailing blanks in a string literal are not trimmed during PL/SQL processing, although they are trimmed if you insert that value into a table column of type CHAR. For additional information, including NCHAR string literals, see "String Literals".
The BOOLEAN values TRUE and FALSE cannot be inserted into a database column. For additional information, see "BOOLEAN Literals".
Numeric literals:
25 6.34 7E2 25e-03 .1 1. +17 -4.4 -4.5D -4.6F
Character literals:
'H' '&' ' ' '9' ']' 'g'
String literals:
'$5,000' '02-AUG-87' 'Don''t leave until you''re ready and I''m ready.' q'#Don't leave until you're ready and I'm ready.#'
More examples:
Related Topics
The LOCK TABLE statement locks entire database tables in a specified lock mode. That enables you to share or deny access to tables while maintaining their integrity. For more information, see "Using LOCK TABLE".
Oracle has extensive automatic features that allow multiple programs to read and write data simultaneously, while each program sees a consistent view of the data; you rarely, if ever, need to lock tables yourself. For additional information on the LOCK TABLE SQL statement, see Oracle Database SQL Language Reference.
Syntax
lock_table_statement ::=
table_reference ::=
lock_mode_clause ::=
Keyword and Parameter Description
See Oracle Database SQL Language Reference.
Usage Notes
If you omit the keyword NOWAIT, Oracle waits until the table is available; the wait has no set limit. Table locks are released when your transaction issues a commit or rollback. A table lock never keeps other users from querying a table, and a query never acquires a table lock. If your program includes SQL locking statements, make sure the Oracle users requesting locks have the privileges needed to obtain the locks. Your DBA can lock any table. Other users can lock tables they own or tables for which they have a privilege, such as SELECT, INSERT, UPDATE, or DELETE.
This statement locks the employees table in row shared mode with the NOWAIT option:
LOCK TABLE employees IN ROW SHARE MODE NOWAIT;
Related Topics
ALOOP statement executes a sequence of statements multiple times. The LOOP and END LOOP keywords enclose the sequence of statements. PL/SQL provides four kinds of loop statements:
Basic loop
WHILE loop
FOR loop
Cursor FOR loop
For usage information, see "Controlling Loop Iterations (LOOP, EXIT, and CONTINUE Statements)".
Syntax
basic_loop_statement ::=
while_loop_statement ::=
for_loop_statement ::=
cursor_for_loop_statement ::=
Keyword and Parameter Description
basic_loop_statement
A loop that executes an unlimited number of times. It encloses a sequence of statements between the keywords LOOP and END LOOP. With each iteration, the sequence of statements is executed, then control resumes at the top of the loop. An EXIT, GOTO, or RAISE statement branches out of the loop. A raised exception also ends the loop.
boolean_expression
An expression that returns the Boolean value TRUE, FALSE, or NULL. It is associated with a sequence of statements, which is executed only if the expression returns TRUE. For the syntax of boolean_expression, see "Expression Definition".
cursor_for_loop_statement
Issues a SQL query and loops through the rows in the result set. This is a convenient technique that makes processing a query as simple as reading lines of text in other programming languages.
A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows were processed.
cursor_name
An explicit cursor previously declared within the current scope. When the cursor FOR loop is entered, cursor_name cannot refer to a cursor already opened by an OPEN statement or an enclosing cursor FOR loop.
cursor_parameter_name
A variable declared as the formal parameter of a cursor. For the syntax of cursor_parameter_declaration, see "Cursor Declaration". A cursor parameter can appear in a query wherever a constant can appear. The formal parameters of a cursor must be IN parameters.
for_loop_statement
Numeric FOR_LOOP loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP.
The range is evaluated when the FOR loop is first entered and is never re-evaluated. The loop body is executed once for each integer in the range defined by lower_bound..upper_bound. After each iteration, the loop index is incremented.
index_name
An undeclared identifier that names the loop index (sometimes called a loop counter). Its scope is the loop itself; you cannot reference the index outside the loop.
The implicit declaration of index_name overrides any other declaration outside the loop. To refer to another variable with the same name, use a label. See Example 4-18, "Using a Label for Referencing Variables Outside a Loop".
Inside a loop, the index is treated like a constant: it can appear in expressions, but cannot be assigned a value.
label_name
An optional undeclared identifier that labels a loop. label_name must be enclosed by double angle brackets and must appear at the beginning of the loop. Optionally, label_name (not enclosed in angle brackets) can also appear at the end of the loop.
You can use label_name in an EXIT statement to exit the loop labelled by label_name. You can exit not only the current loop, but any enclosing loop.
You cannot reference the index of a FOR loop from a nested FOR loop if both indexes have the same name, unless the outer loop is labeled by label_name and you use dot notation. See Example 4-19, "Using Labels on Loops for Referencing".
lower_bound .. upper_bound
Expressions that return numbers. Otherwise, PL/SQL raises the predefined exception VALUE_ERROR. The expressions are evaluated only when the loop is first entered. The lower bound need not be 1, it can be a negative integer as in the following example:
FOR i IN -5..10
The loop counter increment (or decrement) must be 1.
Internally, PL/SQL assigns the values of the bounds to temporary PLS_INTEGER variables, and, if necessary, rounds the values to the nearest integer. The magnitude range of a PLS_INTEGER is -2147483648 to 2147483647, represented in 32 bits. If a bound evaluates to a number outside that range, you get a numeric overflow error when PL/SQL attempts the assignment. See "PLS_INTEGER and BINARY_INTEGER Datatypes".
By default, the loop index is assigned the value of lower_bound. If that value is not greater than the value of upper_bound, the sequence of statements in the loop is executed, then the index is incremented. If the value of the index is still not greater than the value of upper_bound, the sequence of statements is executed again. This process repeats until the value of the index is greater than the value of upper_bound. At that point, the loop completes.
record_name
An implicitly declared record. The record has the same structure as a row retrieved by cursor_name or select_statement.
The record is defined only inside the loop. You cannot refer to its fields outside the loop. The implicit declaration of record_name overrides any other declaration outside the loop. You cannot refer to another record with the same name inside the loop unless you qualify the reference using a block label.
Fields in the record store column values from the implicitly fetched row. The fields have the same names and datatypes as their corresponding columns. To access field values, you use dot notation, as follows:
record_name.field_name
Select-items fetched from the FOR loop cursor must have simple names or, if they are expressions, must have aliases. In the following example, wages is an alias for the select item salary+NVL(commission_pct,0)*1000:
CURSOR c1 IS SELECT employee_id, salary + NVL(commission_pct,0) * 1000 wages FROM employees ...
By default, iteration proceeds upward from the lower bound to the upper bound. If you use the keyword REVERSE, iteration proceeds downward from the upper bound to the lower bound. An example follows:
BEGIN FOR i IN REVERSE 1..10 LOOP -- i starts at 10, ends at 1 DBMS_OUTPUT.PUT_LINE(i); -- statements here execute 10 times END LOOP; END; /
The loop index is assigned the value of upper_bound. If that value is not less than the value of lower_bound, the sequence of statements in the loop is executed, then the index is decremented. If the value of the index is still not less than the value of lower_bound, the sequence of statements is executed again. This process repeats until the value of the index is less than the value of lower_bound. At that point, the loop completes.
select_statement
A query associated with an internal cursor unavailable to you. Its syntax is like that of select_into_statement without the INTO clause. See "SELECT INTO Statement". PL/SQL automatically declares, opens, fetches from, and closes the internal cursor. Because select_statement is not an independent statement, the implicit cursor SQL does not apply to it.
while_loop_statement
The WHILE-LOOP statement associates a Boolean expression with a sequence of statements enclosed by the keywords LOOP and END LOOP. Before each iteration of the loop, the expression is evaluated. If the expression returns TRUE, the sequence of statements is executed, then control resumes at the top of the loop. If the expression returns FALSE or NULL, the loop is bypassed and control passes to the next statement.
Usage Notes
You can use the EXIT WHEN statement to exit any loop prematurely. If the Boolean expression in the WHEN clause returns TRUE, the loop is exited immediately.
When you exit a cursor FOR loop, the cursor is closed automatically even if you use an EXIT or GOTO statement to exit the loop prematurely. The cursor is also closed automatically if an exception is raised inside the loop.
Related Topics
The MERGE statement inserts some rows and updates others in a single operation. The decision about whether to update or insert into the target table is based upon a join condition: rows already in the target table that match the join condition are updated; otherwise, a row is inserted using values from a separate subquery.
For a full description and examples of the MERGE statement, see Oracle Database SQL Language Reference.
Usage Notes
This statement is primarily useful in data warehousing situations where large amounts of data are commonly inserted and updated. If you only need to insert or update a single row, it is more efficient to do that with the regular PL/SQL techniques: try to update the row, and do an insert instead if the update affects zero rows; or try to insert the row, and do an update instead if the insert raises an exception because the table already contains that primary key.
The NULL statement is a no-op (no operation); it passes control to the next statement without doing anything. In the body of an IF-THEN clause, a loop, or a procedure, the NULL statement serves as a placeholder. For more information, see "Using the NULL Statement".
Syntax
null_statement ::=
Usage Notes
The NULL statement improves readability by making the meaning and action of conditional statements clear. It tells readers that the associated alternative was not overlooked, that you decided that no action is necessary.
Certain clauses in PL/SQL, such as in an IF statement or an exception handler, must contain at least one executable statement. You can use the NULL statement to make these constructs compile, while not taking any action.
You might not be able to branch to certain places with the GOTO statement because the next statement is END, END IF, and so on, which are not executable statements. In these cases, you can put a NULL statement where you want to branch.
The NULL statement and Boolean value NULL are not related.
Related Topics
An object type is a user-defined composite datatype that encapsulates a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are called attributes. The functions and procedures that characterize the action of the object type are called methods. A special kind of method called the constructor creates a new instance of the object type and fills in its attributes.
Object types must be created through SQL and stored in an Oracle database, where they can be shared by many programs. When you define an object type using the CREATE TYPE statement, you create an abstract template for some real-world object. The template specifies the attributes and actions the object needs in the application environment. For information on the CREATE TYPE SQL statement, see Oracle Database SQL Language Reference. For information on the CREATE TYPE BODY SQL statement, see Oracle Database SQL Language Reference.
The data structure formed by the set of attributes is public (visible to client programs). However, well designed programs do not manipulate it directly. Instead, they use the set of methods provided, so that the data is kept in a proper state.
For more information on object types, see Oracle Database Object-Relational Developer's Guide.
Usage Notes
Once an object type is created in your schema, you can use it to declare objects in any PL/SQL block, subprogram, or package. For example, you can use the object type to specify the datatype of an object attribute, table column, PL/SQL variable, bind argument, record field, collection element, formal procedure parameter, or function result.
Like a package, an object type has two parts: a specification and a body. The specification (spec for short) is the interface to your applications; it declares a data structure (set of attributes) along with the operations (methods) needed to manipulate the data. The body fully defines the methods, and so implements the spec.
All the information a client program needs to use the methods is in the spec. Think of the spec as an operational interface and of the body as a black box. You can debug, enhance, or replace the body without changing the spec.
An object type encapsulates data and operations. You can declare attributes and methods in an object type spec, but not constants, exceptions, cursors, or types. At least one attribute is required (the maximum is 1000); methods are optional.
In an object type spec, all attributes must be declared before any methods. Only subprograms have an underlying implementation. If an object type specification declares only attributes or call specifications, the object type body is unnecessary. You cannot declare attributes in the body. All declarations in the object type spec are public (visible outside the object type).
You can refer to an attribute only by name (not by its position in the object type). To access or change the value of an attribute, you use dot notation. Attribute names can be chained, which lets you access the attributes of a nested object type.
In an object type, methods can reference attributes and other methods without a qualifier. In SQL statements, calls to a parameterless method require an empty parameter list. In procedural statements, an empty parameter list is optional unless you chain calls, in which case it is required for all but the last call.
From a SQL statement, if you call a MEMBER method on a null instance (that is, SELF is null), the method is not called and a null is returned. From a procedural statement, if you call a MEMBER method on a null instance, PL/SQL raises the predefined exception SELF_IS_NULL before the method is called.
You can declare a map method or an order method but not both. If you declare either method, you can compare objects in SQL and procedural statements. However, if you declare neither method, you can compare objects only in SQL statements and only for equality or inequality. Two objects of the same type are equal only if the values of their corresponding attributes are equal.
Like packaged subprograms, methods of the same kind (functions or procedures) can be overloaded. That is, you can use the same name for different methods if their formal parameters differ in number, order, or datatype family.
Every object type has a default constructor method (constructor for short), which is a system-defined function with the same name as the object type. You use the constructor to initialize and return an instance of that object type. You can also define your own constructor methods that accept different sets of parameters. PL/SQL never calls a constructor implicitly, so you must call it explicitly. Constructor calls are allowed wherever function calls are allowed.
Related Topics
The OPEN statement executes the query associated with a cursor. It allocates database resources to process the query and identifies the result set—the rows that match the query conditions. The cursor is positioned before the first row in the result set. For more information, see "Querying Data with PL/SQL".
Syntax
open_statement ::=
Keyword and Parameter Description
cursor_name
An explicit cursor previously declared within the current scope and not currently open.
cursor_parameter_name
A variable declared as the formal parameter of a cursor. (For the syntax of cursor_parameter_declaration, see "Cursor Declaration".) A cursor parameter can appear in a query wherever a constant can appear.
Usage Notes
Generally, PL/SQL parses an explicit cursor only the first time it is opened and parses a SQL statement (creating an implicit cursor) only the first time the statement is executed. All the parsed SQL statements are cached. A SQL statement is reparsed only if it is aged out of the cache by a new SQL statement. Although you must close a cursor before you can reopen it, PL/SQL need not reparse the associated SELECT statement. If you close, then immediately reopen the cursor, a reparse is definitely not needed.
Rows in the result set are not retrieved when the OPEN statement is executed. The FETCH statement retrieves the rows. With a FOR UPDATE cursor, the rows are locked when the cursor is opened.
If formal parameters are declared, actual parameters must be passed to the cursor. The formal parameters of a cursor must be IN parameters; they cannot return values to actual parameters. The values of actual parameters are used when the cursor is opened. The datatypes of the formal and actual parameters must be compatible. The query can also reference PL/SQL variables declared within its scope.
Unless you want to accept default values, each formal parameter in the cursor declaration must have a corresponding actual parameter in the OPEN statement. Formal parameters declared with a default value do not need a corresponding actual parameter. They assume their default values when the OPEN statement is executed.
You can associate the actual parameters in an OPEN statement with the formal parameters in a cursor declaration using positional or named notation.
If a cursor is currently open, you cannot use its name in a cursor FOR loop.
Related Topics
The OPEN-FOR statement executes the SELECT statement associated with a cursor variable. It allocates database resources to process the statement, identifies the result set (the rows that meet the conditions), and positions the cursor variable before the first row in the result set. For more information about cursor variables, see "Using Cursor Variables (REF CURSORs)".
With the optional USING clause, the OPEN-FOR statement processes a dynamic SELECT statement that returns multiple rows: it associates a cursor variable with the SELECT statement, executes the statement, identifies the result set, positions the cursor before the first row in the result set, and zeroes the rows-processed count kept by %ROWCOUNT. For more information, see "Using the OPEN-FOR, FETCH, and CLOSE Statements".
Syntax
open_for_statement ::=
using_clause ::=
Keyword and Parameter Description
bind_argument
Either an expression whose value is passed to the dynamic SQL statement (an in bind), or a variable in which a value returned by the dynamic SQL statement is stored (an out bind). The default parameter mode for bind_argument is IN.
cursor_variable_name
A cursor variable or parameter (without a return type), previously declared within the current scope.
host_cursor_variable_name
A cursor variable, which must be declared in a PL/SQL host environment and passed to PL/SQL as a bind argument (hence the colon (:) prefix). The datatype of the cursor variable is compatible with the return type of any PL/SQL cursor variable.
select_statement
A string literal, string variable, or string expression that represents a multiple-row SELECT statement (without the final semicolon) associated with cursor_variable_name. It must be of type CHAR, VARCHAR2, or CLOB (not NCHAR or NVARCHAR2).
Used only if select_statment includes placeholders, this clause specifies a list of bind arguments.
Usage Notes
You can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program. To open the host cursor variable, you can pass it as a bind argument to an anonymous PL/SQL block. You can reduce network traffic by grouping OPEN-FOR statements. For example, the following PL/SQL block opens five cursor variables in a single round-trip:
/* anonymous PL/SQL block in host environment */ BEGIN OPEN :emp_cv FOR SELECT * FROM employees; OPEN :dept_cv FOR SELECT * FROM departments; OPEN :grade_cv FOR SELECT * FROM salgrade; OPEN :pay_cv FOR SELECT * FROM payroll; OPEN :ins_cv FOR SELECT * FROM insurance END;
Other OPEN-FOR statements can open the same cursor variable for different queries. You do not need to close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.
Unlike cursors, cursor variables do not take parameters. Instead, you can pass whole queries (not just parameters) to a cursor variable. Although a PL/SQL stored subprogram can open a cursor variable and pass it back to a calling subprogram, the calling and called subprograms must be in the same instance. You cannot pass or return cursor variables to procedures and functions called through database links. When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the IN OUT mode. That way, the subprogram can pass an open cursor back to the caller.
Related Topics
A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Use packages when writing a set of related subprograms that form an application programming interface (API) that you or others might reuse. Packages have two parts: a specification (spec for short) and a body. For more information, see Chapter 10, "Using PL/SQL Packages". For an example of a package declaration, see Example 10-3.
This section shows the package specification and body options for PL/SQL. For information on the CREATE PACKAGE SQL statement, see Oracle Database SQL Language Reference. For information on the CREATE PACKAGE BODY SQL statement, see Oracle Database SQL Language Reference.
Syntax
package_specification ::=
package_body ::=
Keyword and Parameter Description
body
For syntax, see "Block Declaration".
declare_section
Declares package elements. For syntax, see "Block Declaration".
item_list_1
Declares a list of items. For syntax, see "Block Declaration".
If an item in item_list_1 is a pragma, it must one of the following:
package_name
A package stored in the database. For naming conventions, see "Identifiers".
Usage Notes
You can use any Oracle tool that supports PL/SQL to create and store packages in an Oracle database. You can issue the CREATE PACKAGE and CREATE PACKAGE BODY statements interactively from SQL*Plus, or from an Oracle Precompiler or OCI host program. However, you cannot define packages in a PL/SQL block or subprogram.
Most packages have a specification and a body. The specification is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the spec.
Only subprograms and cursors have an underlying implementation. If a specification declares only types, constants, variables, exceptions, and call specifications, the package body is unnecessary. The body can still be used to initialize items declared in the specification:
CREATE OR REPLACE PACKAGE emp_actions AS -- additional code here ... number_hired INTEGER; END emp_actions; / CREATE OR REPLACE PACKAGE BODY emp_actions AS BEGIN number_hired := 0; END emp_actions; /
You can code and compile a spec without its body. Once the spec is compiled, stored subprograms that reference the package can be compiled as well. You do not need to define the package bodies fully until you are ready to complete the application. You can debug, enhance, or replace a package body without changing the package spec, which saves you from recompiling subprograms that invoke the package.
Cursors and subprograms declared in a package spec must be defined in the package body. Other program items declared in the package spec cannot be redeclared in the package body.
To match subprogram specs and bodies, PL/SQL does a token-by-token comparison of their headers. Except for white space, the headers must match word for word. Otherwise, PL/SQL raises an exception.
Variables declared in a package keep their values throughout a session, so you can set the value of a package variable in one procedure, and retrieve the same value in a different procedure.
Related Topics
A procedure is a subprogram that performs a specific action. A PL/SQL block or parent subprogram must declare and define a procedure before invoking it. The declaration always includes the specification ("spec"). The declaration can also include the definition. If the declaration does not include the definition, the definition must appear later in the same block or subprogram as the declaration.
For more information about procedures, see "What Are PL/SQL Subprograms?".
Note:
Declaring and defining a procedure in a PL/SQL block or package is different from creating a function with the SQL statementCREATE PROCEDURE. For information about CREATE PROCEDURE, see Oracle Database SQL Language Reference.Syntax
procedure_declaration ::=
procedure_heading ::=
parameter_declaration ::=
procedure_definition ::=
Keyword and Parameter Description
body
For syntax, see "Block Declaration".
datatype
A type specifier. For syntax, see "Constant and Variable Declaration".
declare_section
Declares procedure elements. For syntax, see "Block Declaration".
exception_handler
Associates an exception with a sequence of statements, which is executed when that exception is raised. For syntax, see "Exception Definition".
expression
A combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the declaration is elaborated, the value of expression is assigned to the parameter. The value and the parameter must have compatible datatypes.
Note:
If a procedure call includes an actual parameter forparameter_name, then expression is not evaluated for that procedure call (see Example 8-8).procedure_declaration
Declares, and might also define, a procedure. If the declaration does not define the procedure, the definition must appear later in the same block or subprogram as the declaration. See "Function Declaration and Definition".
IN, OUT, IN OUT
Parameter modes that define the action of formal parameters. An IN parameter passes values to the subprogram being invoked. An OUT parameter returns values to the invoker of the subprogram. An IN OUT parameter passes initial values to the subprogram being invoked and returns updated values to the invoker.
item_declaration
Declares and defines a program object. For syntax, see "Block Declaration".
name
Is the procedure name.
NOCOPY
A compiler hint (not directive) that allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference instead of by value (the default). For more information, see "Specifying Subprogram Parameter Modes".
parameter_name
A formal parameter, which is a variable declared in a procedure spec and referenced in the procedure body.
procedure_name
The name you choose for the procedure.
statement
A statement. For syntax, see "Block Declaration".
type_definition
Specifies a user-defined datatype. For syntax, see "Block Declaration".
:= | DEFAULT
Initializes IN parameters to default values, if they are not specified when the procedure is invoked.
Usage Notes
A procedure is invoked as a PL/SQL statement. For example, the procedure raise_salary might be invoked as follows:
raise_salary(emp_num, amount);
Inside a procedure, an IN parameter acts like a constant; you cannot assign it a value. An OUT parameter acts like a local variable; you can change its value and reference the value in any way. An IN OUT parameter acts like an initialized variable; you can assign it a value, which can be assigned to another variable. For summary information about the parameter modes, see Table 8-3.
Unlike OUT and IN OUT parameters, IN parameters can be initialized to default values. For more information, see "Using Default Values for Subprogram Parameters".
Before exiting a procedure, explicitly assign values to all OUT formal parameters. An OUT actual parameter can have a value before the subprogram is invoked. However, when you invoke the subprogram, the value is lost unless you specify the compiler hint NOCOPY or the subprogram exits with an unhandled exception.
You can write the procedure spec and body as a unit. Or, you can separate the procedure spec from its body. That way, you can hide implementation details by placing the procedure in a package. You can define procedures in a package body without declaring their specs in the package spec. However, such procedures can be invoked only from inside the package. At least one statement must appear in the executable part of a procedure. The NULL statement meets this requirement.
Related Topics
The RAISE statement stops normal execution of a PL/SQL block or subprogram and transfers control to an exception handler.
RAISE statements can raise predefined exceptions, such as ZERO_DIVIDE or NO_DATA_FOUND, or user-defined exceptions whose names you decide. For more information, see "Defining Your Own PL/SQL Exceptions".
Syntax
raise_statement ::=
Keyword and Parameter Description
exception_name
A predefined or user-defined exception. For a list of the predefined exceptions, see "Summary of Predefined PL/SQL Exceptions".
Usage Notes
Raise an exception in a PL/SQL block or subprogram only when an error makes it impractical to continue processing. You can code a RAISE statement for a given exception anywhere within the scope of that exception.
When an exception is raised, if PL/SQL cannot find a handler for it in the current block, the exception propagates to successive enclosing blocks, until a handler is found or there are no more blocks to search. If no handler is found, PL/SQL returns an unhandled exception error to the host environment.
In an exception handler, you can omit the exception name in a RAISE statement, which raises the current exception again. This technique enables you to take some initial corrective action (perhaps just logging the problem), then pass control to another handler that does more extensive correction. When an exception is reraised, the first block searched is the enclosing block, not the current block.
Related Topics
Records are composite variables that can store data values of different types, similar to a struct type in C, C++, or Java. For more information, see "Understanding PL/SQL Records".
In PL/SQL records are useful for holding data from table rows, or certain columns from table rows. For ease of maintenance, you can declare variables as table%ROWTYPE or cursor%ROWTYPE instead of creating new record types.
Syntax
record_type_definition ::=
record_field_declaration ::=
record_type_declaration ::=
Keyword and Parameter Description
datatype
A datatype specifier. For the syntax of datatype, see "Constant and Variable Declaration".
expression
A combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of expression, see "Expression Definition". When the declaration is elaborated, the value of expression is assigned to the field. The value and the field must have compatible datatypes.
field_name
A field in a user-defined record.
At run time, trying to assign a null to a field defined as NOT NULL raises the predefined exception VALUE_ERROR. The constraint NOT NULL must be followed by an initialization clause.
record_name
A user-defined record.
type_name
A user-defined record type that was defined using the datatype specifier RECORD.
:= | DEFAULT
Initializes fields to default values.
Usage Notes
You can define RECORD types and declare user-defined records in the declarative part of any block, subprogram, or package.
A record can be initialized in its declaration. You can use the %TYPE attribute to specify the datatype of a field. You can add the NOT NULL constraint to any field declaration to prevent the assigning of nulls to that field. Fields declared as NOT NULL must be initialized. To reference individual fields in a record, you use dot notation. For example, to reference the dname field in the dept_rec record, use dept_rec.dname.
Instead of assigning values separately to each field in a record, you can assign values to all fields at once:
You can assign one user-defined record to another if they have the same datatype. (Having fields that match exactly is not enough.) You can assign a %ROWTYPE record to a user-defined record if their fields match in number and order, and corresponding fields have compatible datatypes.
You can use the SELECT or FETCH statement to fetch column values into a record. The columns in the select-list must appear in the same order as the fields in your record.
User-defined records follow the usual scoping and instantiation rules. In a package, they are instantiated when you first reference the package and cease to exist when you end the database session. In a block or subprogram, they are instantiated when you enter the block or subprogram and cease to exist when you exit the block or subprogram.
Like scalar variables, user-defined records can be declared as the formal parameters of procedures and functions. The restrictions that apply to scalar parameters also apply to user-defined records.
You can specify a RECORD type in the RETURN clause of a function spec. That allows the function to return a user-defined record of the same type. When invoking a function that returns a user-defined record, use the following syntax to reference fields in the record:
function_name(parameter_list).field_name
To reference nested fields, use this syntax:
function_name(parameter_list).field_name.nested_field_name
If the function takes no parameters, code an empty parameter list. The syntax follows:
function_name().field_name
Related Topics
Note:
TheRESTRICT REFERENCES pragma is deprecated. Oracle recommends using DETERMINISTIC and PARALLEL_ENABLE (described in "Function Declaration and Definition") instead of RESTRICT REFERENCES.The RESTRICT REFERENCES pragma asserts that a subprogram (usually a function) in a package specification or object type specification does not read or write database tables or package variables. Subprograms that do read or write database tables or package variables are difficult to optimize, because any call to the subprogram might produce different results or encounter errors.
Syntax
restrict_references_pragma ::=
Keyword and Parameter Description
Specifies that the pragma applies to all subprograms in the package specification or object type specification.
function_name
The name of a user-defined subprogram, usually a function.
Signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They pass information to the compiler.
Asserts that the subprogram reads no database state (does not query database tables).
Asserts that the subprogram reads no package state (does not reference the values of packaged variables)
Asserts that the subprogram can be trusted not to violate one or more rules.
Asserts that the subprogram writes no database state (does not modify tables).
Asserts that the subprogram writes no package state (does not change the values of packaged variables).
Usage Notes
You can declare the pragma RESTRICT_REFERENCES only in a package specification or object type specification. You can specify up to four constraints (RNDS, RNPS, WNDS, WNPS) in any order. To invoke a subprogram from parallel queries, you must specify all four constraints. No constraint implies another. Typically, this pragma is specified for functions. If a function calls procedures, specify the pragma for those procedures as well.
When you specify TRUST, the subprogram body is not checked for violations of the constraints listed in the pragma. The subprogram is trusted not to violate them. Skipping these checks can improve performance. TRUST is needed for functions written in C or Java that are invoked from PL/SQL, since PL/SQL cannot verify them at run time.
If you specify DEFAULT instead of a subprogram name, the pragma applies to all subprograms in the package specification or object type specification (including the system-defined constructor for object types). You can still declare the pragma for individual subprograms, overriding the DEFAULT pragma.
A RESTRICT_REFERENCES pragma can apply to only one subprogram declaration. A pragma that references the name of overloaded subprograms always applies to the most recent subprogram declaration.
Related Topics
The RETURN statement immediately completes the execution of a subprogram and returns control to the invoker. Execution resumes with the statement following the subprogram call. In a function, the RETURN statement also sets the function identifier to the return value. See "Using the RETURN Statement".
Syntax
return_statement ::=
Keyword and Parameter Description
expression
A combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the RETURN statement is executed, the value of expression is assigned to the function identifier.
Usage Notes
The RETURN statement is different than the RETURN clause in a function spec, which specifies the datatype of the return value.
A subprogram can contain several RETURN statements. Executing any of them completes the subprogram immediately. The RETURN statement might not be positioned as the last statement in the subprogram. The RETURN statement can be used in an anonymous block to exit the block and all enclosing blocks, but the RETURN statement cannot contain an expression.
In procedures, a RETURN statement cannot contain an expression. The statement just returns control to the invoker before the normal end of the procedure is reached. In functions, a RETURN statement must contain an expression, which is evaluated when the RETURN statement is executed. The resulting value is assigned to the function identifier. In functions, there must be at least one execution path that leads to a RETURN statement. Otherwise, PL/SQL raises an exception at run time.
Related Topics
The RETURNING INTO clause specifies the variables in which to store the values returned by the statement to which the clause belongs. The variables can be either individual variables or collections. If the statement does not affect any rows, the values of the variables are undefined.
The static RETURNING INTO clause belongs to a DELETE, INSERT, or UPDATE statement. The dynamic RETURNING INTO clause belongs to an EXECUTE IMMEDIATE statement.
You cannot use the RETURNING INTO clause for remote or parallel deletes.
Syntax
static_returning_clause ::=
dynamic_returning_clause ::=
into_clause ::=
bulk_collect_into_clause ::=
Keyword and Parameter Description
Used only for a statement that returns multiple rows, this clause specifies one or more collections in which to store the returned rows. This clause must have a corresponding, type-compatible collection_item or :host_array_name for each select_item in the statement to which the RETURNING INTO clause belongs.
For the reason to use this clause, see "Reducing Loop Overhead for DML Statements and Queries with Bulk SQL".
collection_name
The name of a declared collection, into which returned rows are stored.
host_array_name
An array into which returned rows are stored. The array must be declared in a PL/SQL host environment and passed to PL/SQL as a bind argument (hence the colon (:) prefix).
INTO
Used only for a statement that returns a single row, this clause specifies the variables or record into which the column values of the returned row are stored. This clause must have a corresponding, type-compatible variable or record field for each select_item in the statement to which the RETURNING INTO clause belongs.
multiple_row_expression
An expression that returns multiple rows of a table.
record_name
A record into which a returned row is stored.
single_row_expression
An expression that returns a single row of a table.
variable_name
Either the name of a variable into which a column value of the returned row is stored, or the name of a cursor variable that is declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. The datatype of the cursor variable is compatible with the return type of any PL/SQL cursor variable.
Usage
For DML statements that have a RETURNING clause, you can place OUT bind arguments in the RETURNING INTO clause without specifying the parameter mode, which, by definition, is OUT. If you use both the USING clause and the RETURNING INTO clause, the USING clause can contain only IN arguments.
At run time, bind arguments or define variables replace corresponding placeholders in the dynamic SQL statement. Every placeholder must be associated with a bind argument in the USING clause or RETURNING INTO clause (or both) or with a define variable in the INTO clause.
The value a of bind argument cannot be a Boolean literal (TRUE, FALSE, or NULL). To pass the value NULL to the dynamic SQL statement, see "Uninitialized Variable for NULL in USING Clause".
Related Topics
The ROLLBACK statement is the inverse of the COMMIT statement. It undoes some or all database changes made during the current transaction. For more information, see "Overview of Transaction Processing in PL/SQL".
The SQL ROLLBACK statement can be embedded as static SQL in PL/SQL. For syntax details on the SQL ROLLBACK statement, see Oracle Database SQL Language Reference.
Usage Notes
All savepoints marked after the savepoint to which you roll back are erased. The savepoint to which you roll back is not erased. For example, if you mark savepoints A, B, C, and D in that order, then roll back to savepoint B, only savepoints C and D are erased.
An implicit savepoint is marked before executing an INSERT, UPDATE, or DELETE statement. If the statement fails, a rollback to this implicit savepoint is done. Normally, just the failed SQL statement is rolled back, not the whole transaction. If the statement raises an unhandled exception, the host environment determines what is rolled back.
Related Topics
The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Variables declared using %ROWTYPE are treated like those declared using a datatype name. You can use the %ROWTYPE attribute in variable declarations as a datatype specifier.
Fields in a record and corresponding columns in a row have the same names and datatypes. However, fields in a %ROWTYPE record do not inherit constraints, such as the NOT NULL column or check constraint, or default values. For more information, see "Using the %ROWTYPE Attribute".
Syntax
%rowtype_attribute ::=
Keyword and Parameter Description
cursor_name
An explicit cursor previously declared within the current scope.
cursor_variable_name
A PL/SQL strongly typed cursor variable, previously declared within the current scope.
table_name
A database table or view that must be accessible when the declaration is elaborated.
Usage Notes
Declaring variables as the type table_name%ROWTYPE is a convenient way to transfer data between database tables and PL/SQL. You create a single variable rather than a separate variable for each column. You do not need to know the name of every column. You refer to the columns using their real names instead of made-up variable names. If columns are later added to or dropped from the table, your code can keep working without changes.
To reference a field in the record, use dot notation (record_name.field_name). You can read or write one field at a time this way.
There are two ways to assign values to all fields in a record at once:
First, PL/SQL allows aggregate assignment between entire records if their declarations refer to the same table or cursor.
You can assign a list of column values to a record by using the SELECT or FETCH statement. The column names must appear in the order in which they were declared. Select-items fetched from a cursor associated with %ROWTYPE must have simple names or, if they are expressions, must have aliases.
Related Topics
The SAVEPOINT statement names and marks the current point in the processing of a transaction. With the ROLLBACK TO statement, savepoints undo parts of a transaction instead of the whole transaction. For more information, see "Overview of Transaction Processing in PL/SQL".
The SQL SAVEPOINT statement can be embedded as static SQL in PL/SQL. For syntax details on the SQL SAVEPOINT statement, see Oracle Database SQL Language Reference.
Syntax
savepoint_statement ::=
Usage Notes
A simple rollback or commit erases all savepoints. When you roll back to a savepoint, any savepoints marked after that savepoint are erased. The savepoint to which you roll back remains.
You can reuse savepoint names within a transaction. The savepoint moves from its old position to the current point in the transaction.
If you mark a savepoint within a recursive subprogram, new instances of the SAVEPOINT statement are executed at each level in the recursive descent. You can only roll back to the most recently marked savepoint.
An implicit savepoint is marked before executing an INSERT, UPDATE, or DELETE statement. If the statement fails, a rollback to the implicit savepoint is done. Normally, just the failed SQL statement is rolled back, not the whole transaction; if the statement raises an unhandled exception, the host environment (such as SQL*Plus) determines what is rolled back.
Related Topics
The SELECT INTO statement retrieves values from one or more database tables (as the SQL SELECT statement does) and stores them in either variables or a record (which the SQL SELECT statement does not do). For information about the SQL SELECT statement, see Oracle Database SQL Language Reference.
By default, the SELECT INTO statement retrieves one or more columns from a single row. With the BULK COLLECT INTO clause, this statement retrieves an entire result set at once.
Syntax
select_into_statement ::=
select_item ::=
Keyword and Parameter Description
alias
Another (usually short) name for the referenced column, table, or view.
Stores result values in one or more collections, for faster queries than loops with FETCH statements. For more information, see "Reducing Loop Overhead for DML Statements and Queries with Bulk SQL".
collection_name
A declared collection into which select_item values are fetched. For each select_item, there must be a corresponding, type-compatible collection in the list.
function_name
A user-defined function.
host_array_name
An array (declared in a PL/SQL host environment and passed to PL/SQL as a bind argument) into which select_item values are fetched. For each select_item, there must be a corresponding, type-compatible array in the list. Host arrays must be prefixed with a colon.
numeric_literal
A literal that represents a number or a value implicitly convertible to a number.
parameter_name
A formal parameter of a user-defined function.
record_name
A user-defined or %ROWTYPE record into which rows of values are fetched. For each select_item value returned by the query, there must be a corresponding, type-compatible field in the record.
rest_of_statement
Anything that can follow the FROM clause in a SQL SELECT statement (except the SAMPLE clause).
schema_name
The schema containing the table or view. If you omit schema_name, Oracle assumes the table or view is in your schema.
subquery
A SELECT statement that provides a set of rows for processing. Its syntax is similar to that of select_into_statement without the INTO clause.
table_reference
A table or view that must be accessible when you execute the SELECT statement, and for which you must have SELECT privileges. For the syntax of table_reference, see "DELETE Statement".
TABLE (subquery2)
The operand of TABLE is a SELECT statement that returns a single column value, which must be a nested table or a varray. Operator TABLE informs Oracle that the value is a collection, not a scalar value.
variable_name
A previously declared variable into which a select_item value is fetched. For each select_item value returned by the query, there must be a corresponding, type-compatible variable in the list.
Usage Notes
By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure your WHERE clause is specific enough to only match one row
If no rows are returned, PL/SQL raises NO_DATA_FOUND. You can guard against this exception by selecting the result of the aggregate function COUNT(*), which returns a single value, even if no rows match the condition.
A SELECT BULK COLLECT INTO statement can return multiple rows. You must set up collection variables to hold the results. You can declare associative arrays or nested tables that grow as needed to hold the entire result set.
The implicit cursor SQL and its attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN provide information about the execution of a SELECT INTO statement.
Related Topics
The SERIALLY_REUSABLE pragma indicates that the package state is needed only for the duration of one call to the server (for example, an OCI call to the database or a stored procedure call through a database link). After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions. For more information, see Oracle Database Advanced Application Developer's Guide.
Syntax
serially_resuable_pragma ::=
Keyword and Parameter Description
Signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They pass information to the compiler.
Usage Notes
This pragma is appropriate for packages that declare large temporary work areas that are used once and not needed during subsequent database calls in the same session.
You can mark a bodiless package as serially reusable. If a package has a spec and body, you must mark both. You cannot mark only the body.
The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to NULL.
Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, Oracle generates an error.
Example 13-5 creates a serially reusable package.
Example 13-5 Creating a Serially Reusable Package
CREATE PACKAGE pkg1 IS
PRAGMA SERIALLY_REUSABLE;
num NUMBER := 0;
PROCEDURE init_pkg_state(n NUMBER);
PROCEDURE print_pkg_state;
END pkg1;
/
CREATE PACKAGE BODY pkg1 IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE init_pkg_state (n NUMBER) IS
BEGIN
pkg1.num := n;
END;
PROCEDURE print_pkg_state IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Num: ' || pkg1.num);
END;
END pkg1;
/
Related Topics
The SET TRANSACTION statement begins a read-only or read/write transaction, establishes an isolation level, or assigns the current transaction to a specified rollback segment. Read-only transactions are useful for running multiple queries against one or more tables while other users update the same tables. For more information, see "Setting Transaction Properties (SET TRANSACTION Statement)".
For additional information on the SET TRANSACTION SQL statement, see Oracle Database SQL Language Reference.
Syntax
set_transaction_statement ::=
Keyword and Parameter Description
Establishes the current transaction as read-only, so that subsequent queries see only changes committed before the transaction began. The use of READ ONLY does not affect other users or transactions.
Establishes the current transaction as read/write. The use of READ WRITE does not affect other users or transactions. If the transaction executes a data manipulation statement, Oracle assigns the transaction to a rollback segment.
Specifies how to handle transactions that modify the database.
SERIALIZABLE: If a serializable transaction tries to execute a SQL data manipulation statement that modifies any table already modified by an uncommitted transaction, the statement fails.
SERIALIZABLE
To enable SERIALIZABLE mode, your DBA must set the Oracle initialization parameter COMPATIBLE to 7.3.0 or higher.
READ COMMITTED: If a transaction includes SQL data manipulation statements that require row locks held by another transaction, the statement waits until the row locks are released.
Assigns the current transaction to the specified rollback segment and establishes the transaction as read/write. You cannot use this parameter with the READ ONLY parameter in the same transaction because read-only transactions do not generate rollback information.
Specifies a name or comment text for the transaction. This is better than using the COMMIT COMMENT feature because the name is available while the transaction is running, making it easier to monitor long-running and in-doubt transactions.
Usage Notes
The SET TRANSACTION statement must be the first SQL statement in the transaction and can appear only once in the transaction.
Related Topics
Oracle implicitly opens a cursor to process each SQL statement not associated with an explicit cursor. In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. They provide information about the execution of data manipulation statements. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. For more information, see "Querying Data with PL/SQL".
Syntax
sql_cursor ::=
Keyword and Parameter Description
%BULK_ROWCOUNT
A composite attribute designed for use with the FORALL statement. This attribute acts like an index-by table. Its ith element stores the number of rows processed by the ith execution of an UPDATE or DELETE statement. If the ith execution affects no rows, %BULK_ROWCOUNT(i) returns zero.
%BULK_EXCEPTIONS
An associative array that stores information about any exceptions encountered by a FORALL statement that uses the SAVE EXCEPTIONS clause. You must loop through its elements to determine where the exceptions occurred and what they were. For each index value i between 1 and SQL%BULK_EXCEPTIONS.COUNT, SQL%BULK_EXCEPTIONS(i).ERROR_INDEX specifies which iteration of the FORALL loop caused an exception. SQL%BULK_EXCEPTIONS(i).ERROR_CODE specifies the Oracle error code that corresponds to the exception.
%FOUND
Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
%ISOPEN
Always returns FALSE, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
%NOTFOUND
The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
%ROWCOUNT
Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.
SQL
The name of the Oracle implicit cursor.
Usage Notes
You can use cursor attributes in procedural statements but not in SQL statements. Before Oracle opens the SQL cursor automatically, the implicit cursor attributes return NULL. The values of cursor attributes always refer to the most recently executed SQL statement, wherever that statement appears. It might be in a different scope. If you want to save an attribute value for later use, assign it to a variable immediately.
If a SELECT INTO statement fails to return a row, PL/SQL raises the predefined exception NO_DATA_FOUND, whether you check SQL%NOTFOUND on the next line or not. A SELECT INTO statement that invokes a SQL aggregate function never raises NO_DATA_FOUND, because those functions always return a value or a NULL. In such cases, SQL%NOTFOUND returns FALSE. %BULK_ROWCOUNT is not maintained for bulk inserts because a typical insert affects only one row. See "Counting Rows Affected by FORALL (%BULK_ROWCOUNT Attribute)".
You can use the scalar attributes %FOUND, %NOTFOUND, and %ROWCOUNT with bulk binds. For example, %ROWCOUNT returns the total number of rows processed by all executions of the SQL statement. Although %FOUND and %NOTFOUND refer only to the last execution of the SQL statement, you can use %BULK_ROWCOUNT to infer their values for individual executions. For example, when %BULK_ROWCOUNT(i) is zero, %FOUND and %NOTFOUND are FALSE and TRUE, respectively.
Related Topics
The function SQLCODE returns the number code of the most recent exception.
For internal exceptions, SQLCODE returns the number of the associated Oracle error. The number that SQLCODE returns is negative unless the Oracle error is no data found, in which case SQLCODE returns +100. For user-defined exceptions, SQLCODE returns +1, or a value you assign if the exception is associated with an Oracle error number through pragma EXCEPTION_INIT.
Syntax
sqlcode_function ::=
Usage Notes
SQLCODE is only useful in an exception handler. Outside a handler, SQLCODE always returns 0. SQLCODE is especially useful in the OTHERS exception handler, because it lets you identify which internal exception was raised. You cannot use SQLCODE directly in a SQL statement. Assign the value of SQLCODE to a local variable first.
When using pragma RESTRICT_REFERENCES to assert the purity of a stored function, you cannot specify the constraints WNPS and RNPS if the function invokes SQLCODE.
Related Topics
The function SQLERRM returns the error message associated with its error-number argument. If the argument is omitted, it returns the error message associated with the current value of SQLCODE. SQLERRM with no argument is useful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the normal, successful completion message. For internal exceptions, SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code.
For user-defined exceptions, SQLERRM returns the message user-defined exception, unless you used the pragma EXCEPTION_INIT to associate the exception with an Oracle error number, in which case SQLERRM returns the corresponding error message. For more information, see "Retrieving the Error Code and Error Message (SQLCODE and SQLERRM Functions)".
Syntax
sqlerrm_function ::=
Keyword and Parameter Description
error_number
A valid Oracle error number. For a list of Oracle errors (which are prefixed by ORA-), see Oracle Database Error Messages.
Usage Notes
SQLERRM is especially useful in the OTHERS exception handler, where it lets you identify which internal exception was raised. The error number passed to SQLERRM must be negative. Passing a zero to SQLERRM always returns the following message:
ORA-0000: normal, successful completion
Passing a positive number to SQLERRM always returns the User-Defined Exception message unless you pass +100, in which case SQLERRM returns the ORA-01403: no data found message.
You cannot use SQLERRM directly in a SQL statement. Assign the value of SQLERRM to a local variable first.
When using pragma RESTRICT_REFERENCES to assert the purity of a stored function, you cannot specify the constraints WNPS and RNPS if the function invokes SQLERRM.
Related Topics
The %TYPE attribute lets use the datatype of a field, record, nested table, database column, or variable in your own declarations, rather than hardcoding the type names. You can use the %TYPE attribute as a datatype specifier when declaring constants, variables, fields, and parameters. If the types that you reference change, your declarations are automatically updated. This technique saves you from making code changes when, for example, the length of a VARCHAR2 column is increased. Note that column constraints, such as the NOT NULL and check constraint, or default values are not inherited by items declared using %TYPE. For more information, see "Using the %TYPE Attribute".
Syntax
%type_attribute ::=
Keyword and Parameter Description
collection_name
A nested table, index-by table, or varray previously declared within the current scope.
cursor_variable_name
A PL/SQL cursor variable previously declared within the current scope. Only the value of another cursor variable can be assigned to a cursor variable.
db_table_name.column_name
A table and column that must be accessible when the declaration is elaborated.
object_name
An instance of an object type, previously declared within the current scope.
record_name
A user-defined or %ROWTYPE record, previously declared within the current scope.
record_name.field_name
A field in a user-defined or %ROWTYPE record, previously declared within the current scope.
variable_name
A variable, previously declared in the same scope.
Usage Notes
The %TYPE attribute is particularly useful when declaring variables, fields, and parameters that refer to database columns. Your code can keep working even when the lengths or types of the columns change.
Related Topics
The UPDATE statement changes the values of specified columns in one or more rows in a table or view. For a full description of the SQL statement UPDATE, see Oracle Database SQL Language Reference.
Syntax
update_statement ::=
set_clause ::=
column_list ::=
Keyword and Parameter Description
alias
Another (usually short) name for the referenced table or view, typically used in the WHERE clause.
column_name
The column (or one of the columns) to be updated. It must be the name of a column in the referenced table or view. A column name cannot be repeated in the column_name list. Column names need not appear in the UPDATE statement in the same order that they appear in the table or view.
SET column_name = sql_expression
This clause assigns the value of sql_expression to the column identified by column_name. If sql_expression contains references to columns in the table being updated, the references are resolved in the context of the current row. The old column values are used on the right side of the equal sign.
SET column_name = (subquery3)
Assigns the value retrieved from the database by subquery3 to the column identified by column_name. The subquery must return exactly one row and one column.
SET (column_name, column_name, ...) = (subquery4)
Assigns the values retrieved from the database by subquery4 to the columns in the column_name list. The subquery must return exactly one row that includes all the columns listed. The column values returned by the subquery are assigned to the columns in the column list in order. The first value is assigned to the first column in the list, the second value is assigned to the second column in the list, and so on.
sql_expression
Any valid SQL expression. For more information, see Oracle Database SQL Language Reference.
static_returning_clause
Returns the column values of the updated rows, in either individual variables or collections (eliminating the need to SELECT the rows first). For details, see "RETURNING INTO Clause".
subquery
A SELECT statement that provides a set of rows for processing. Its syntax is like that of select_into_statement without the INTO clause. See "SELECT INTO Statement".
table_reference
A table or view that must be accessible when you execute the UPDATE statement, and for which you must have UPDATE privileges. For the syntax of table_reference, see "DELETE Statement".
TABLE (subquery2)
The operand of TABLE is a SELECT statement that returns a single column value, which must be a nested table or a varray. Operator TABLE informs Oracle that the value is a collection, not a scalar value.
Refers to the latest row processed by the FETCH statement associated with the specified cursor. The cursor must be FOR UPDATE and must be open and positioned on a row. If the cursor is not open, the CURRENT OF clause causes an error. If the cursor is open, but no rows were fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND.
WHERE search_condition
Chooses which rows to update in the database table. Only rows that meet the search condition are updated. If you omit this clause, all rows in the table are updated.
Usage Notes
You can use the UPDATE WHERE CURRENT OF statement after a fetch from an open cursor (including fetches done by a cursor FOR loop), provided the associated query is FOR UPDATE. This statement updates the row that was just fetched.
The implicit cursor SQL and the cursor attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN let you access useful information about the execution of an UPDATE statement.
Related Topics
