11 Language Elements

Grammar, which knows how to control even kings. --Molière

This chapter is a quick reference guide to PL/SQL syntax and semantics. It shows you how commands, parameters, and other language elements are sequenced to form PL/SQL statements. Also, to save you time and trouble, it provides usage notes and short examples.

Reading the Syntax Diagrams

When you are unsure of the syntax to use in a PL/SQL statement, trace through its syntax diagram, reading from left to right and top to bottom. You can verify or construct any PL/SQL statement that way.

The diagrams are graphic representations of Bachus-Naur Form (BNF) productions. Within the diagrams, keywords are enclosed in boxes, delimiters in circles, and identifiers in ovals.

Each diagram defines a syntactic element. Every path through the diagram describes a possible form of that element. Follow in the direction of the arrows. If a line loops back on itself, you can repeat the element enclosed by the loop.

Assignment Statement

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. For more information, see "Assignments".

Syntax

Keyword and Parameter Description

attribute_name

This identifies 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 NOTNULL constraint on an attribute.

collection_name

This identifies a nested table, index-by table, or varray previously declared within the current scope.

cursor_variable_name

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

This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of expression, see "Expressions". 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

This identifies a field in a user-defined or %ROWTYPE record.

host_cursor_variable_name

This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. 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

This identifies a variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. Host variables must be prefixed with a colon.

index

This is an expression that must yield (or convert implicitly to) an integer. For more information, see "Datatype Conversion".

indicator_name

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

This identifies an object (instance of an object type) previously declared within the current scope.

parameter_name

This identifies a formal OUT or INOUT parameter of the subprogram in which the assignment statement appears.

record_name

This identifies a user-defined or %ROWTYPE record previously declared within the current scope.

variable_name

This identifies 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. So, never reference a variable before you assign it a value.

You cannot assign nulls to a variable defined as NOTNULL. 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. When applied to an expression, the relational operators return a Boolean value. So, the following assignment is legal:

Moreover, 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. For example, the following assignment is legal:

Related Topics

Constants and Variables, Expressions, SELECT INTO Statement

AUTONOMOUS_TRANSACTION Pragma

The AUTONOMOUS_TRANSACTION pragma instructs the PL/SQL compiler to mark a routine as autonomous (independent). An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction. For more information, see "Using Autonomous Transactions".

Syntax

Keyword and Parameter Description

PRAGMA

This keyword signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.

Usage Notes

In this context, the term routine includes

top-level (not nested) anonymous PL/SQL blocks

local, stand-alone, and packaged functions and procedures

methods of a SQL object type

database triggers

You cannot use the pragma to mark all subprograms in a package (or all methods in an object type) as autonomous. Only individual routines can be marked autonomous. You can code the pragma anywhere in the declarative section of a routine. But, 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. So, you can log events, increment retry counters, and so on, even if the main transaction rolls back.

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 READCOMMITTED (the default).

If you set the isolation level of the main transaction to SERIALIZABLE, as follows, changes made by its autonomous transactions are not visible to the main transaction when it resumes:

When in the main transaction, rolling back to a savepoint marked before you started an autonomous transaction 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. In that case, 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, the transaction is rolled back.

Related Topics

Blocks

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 "Block Structure" and "Scope and Visibility".

Syntax

Keyword and Parameter Description

base_type

This is any scalar or user-defined PL/SQL datatype specifier such as CHAR, DATE, or RECORD.

BEGIN

This keyword signals the start of the executable part of a PL/SQL block, which contains executable statements. The executable part of a block is required. That is, a PL/SQL block must contain at least one executable statement. The NULL statement meets this requirement.

collection_declaration

This declares a collection (index-by table, nested table, or varray). For the syntax of collection_declaration, see "Collections".

constant_declaration

constraint

This 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

This declares an explicit cursor. For the syntax of cursor_declaration, see "Cursors".

cursor_variable_declaration

This declares a cursor variable. For the syntax of cursor_variable_declaration, see "Cursor Variables".

DECLARE

This keyword 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.

PL/SQL does not allow forward references. So, you must declare an item before referencing it in other statements, including other declarative statements. Also, you must declare subprograms at the end of a declarative section after all other program items.

END

This keyword signals the end of a PL/SQL block. It must be the last keyword in a block. Neither the ENDIF in an IF statement nor the ENDLOOP in a LOOP statement can substitute for the keyword END. Remember, END does not signal the end of a transaction. Just as a block can span multiple transactions, a transaction can span multiple blocks.

EXCEPTION

This keyword 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.

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, see Chapter 6.

exception_declaration

This declares an exception. For the syntax of exception_declaration, see "Exceptions".

exception_handler

This associates an exception with a sequence of statements, which is executed when that exception is raised. For the syntax of exception_handler, see "Exceptions".

function_declaration

This declares a function. For the syntax of function_declaration, see "Functions".

label_name

This is an undeclared identifier that optionally labels a PL/SQL block. If used, label_name must be enclosed by double angle brackets and must appear at the beginning of the block. Optionally, label_name (not enclosed by angle brackets) can also appear at the end of the block.

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 you use a block label to qualify the reference, as the following example shows:

object_declaration

This declares an object (instance of an object type). For the syntax of object_declaration, see "Object Types".

procedure_declaration

This declares a procedure. For the syntax of procedure_declaration, see "Procedures".

record_declaration

This declares a user-defined record. For the syntax of record_declaration, see "Records".

sql_statement

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. However, you can execute data definition and data control statements using native dynamic SQL (see Chapter 10).

statement

This is an executable (not declarative) statement that you use to create algorithms. A sequence of statements can include procedural statements such as RAISE, SQL statements such as UPDATE, and PL/SQL blocks (sometimes called "block statements").

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

This identifies a user-defined subtype that was defined using any scalar or user-defined PL/SQL datatype specifier such as CHAR, DATE, or RECORD.

Related Topics

Constants and Variables, Exceptions, Functions, Procedures

CLOSE Statement

The CLOSE statement allows resources held by an open cursor or cursor variable to be reused. No more rows can be fetched from a closed cursor or cursor variable. For more information, see "Managing Cursors".

Syntax

Keyword and Parameter Description

cursor_name

This identifies an explicit cursor previously declared within the current scope and currently open.

cursor_variable_name

This identifies a PL/SQL cursor variable (or parameter) previously declared within the current scope and currently open.

host_cursor_variable_name

This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. 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. If you reopen a cursor without closing it first, PL/SQL raises the predefined exception CURSOR_ALREADY_OPEN. However, you need not close a cursor variable before reopening it.

If you try to close an already-closed or never-opened cursor or cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.

Example

In the following example, after the last row is fetched and processed, you close the cursor variable emp_cv:

Related Topics

FETCH Statement, OPEN Statement, OPEN-FOR Statement

Collection Methods

A collection method is a built-in function or procedure that operates on collections and is called using dot notation. The methods EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, NEXT, EXTEND, TRIM, and DELETE help generalize code, make collections easier to use, and make your applications easier to maintain.

EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT are functions, which appear as part of an expression. EXTEND, TRIM, and DELETE are procedures, which appear as a statement. EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE take integer parameters. ssion. EXTEND and TRIM cannot be used with index-by tables.

Syntax

Keyword and Parameter Description

collection_name

This identifies an index-by table, nested table, or varray previously declared within the current scope.

COUNT

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

DELETE

This procedure has three forms. DELETE removes all elements from a collection. DELETE(n) removes the nth element from an index-by table or nested table. If n is null, DELETE(n) does nothing. DELETE(m,n) removes all elements in the range m..n from an index-by table or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

EXISTS

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.

EXTEND

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. So, if EXTEND encounters deleted elements, it includes them in its tally. You cannot use EXTEND with index-by tables.

FIRST, LAST

FIRST and LAST return the first and last (smallest and largest) index numbers in a collection. If the collection is empty, FIRST and LAST return NULL. If the collection contains only one element, FIRST and LAST return the same index number.

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

This is an expression that must yield (or convert implicitly to) an integer.

LIMIT

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

PRIOR(n) returns the index number that precedes index n in a collection. NEXT(n) returns the index number that succeeds index n. If n has no predecessor, PRIOR(n) returns NULL. Likewise, if n has no successor, NEXT(n) returns NULL.

TRIM

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. So, 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.

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 have been deleted.

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

PL/SQL keeps placeholders for deleted elements. So, you can replace a deleted element simply 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. So, 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.

Examples

In the following example, you use NEXT to traverse a nested table from which some elements have been deleted:

i := courses.FIRST; -- get subscript of first element
WHILE i IS NOT NULL LOOP
-- do something with courses(i)
i := courses.NEXT(i); -- get subscript of next element
END LOOP;

In the following example, PL/SQL executes the assignment statement only if element i exists:

IF courses.EXISTS(i) THEN
courses(i) := new_course;
END IF;

The next example shows that you can use FIRST and LAST to specify the lower and upper bounds of a loop range provided each element in that range exists:

FOR i IN courses.FIRST..courses.LAST LOOP ...

In the following example, you delete elements 2 through 5 from a nested table:

courses.DELETE(2, 5);

In the final example, you use LIMIT to determine if you can add 20 more elements to varray projects:

Related Topics

Collections, Functions, Procedures

Collections

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: index-by tables, nested tables, and varrays (short for variable-size arrays). Nested tables extend the functionality of index-by tables (formerly called "PL/SQL tables").

Collections work like the arrays found in most third-generation programming languages. However, collections can have only one dimension and must be indexed by integers. (In some languages such as Ada and Pascal, arrays can have multiple dimensions and can be indexed by enumeration types.)

Nested tables and varrays can store instances of an object type and, conversely, can be attributes of an object type. Also, collections can be passed as parameters. So, you can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms.

Syntax

Keyword and Parameter Description

element_type

This is any PL/SQL datatype except BINARY_INTEGER, BOOLEAN, LONG, LONGRAW, NATURAL, NATURALN, NCHAR, NCLOB, NVARCHAR2, object types with TABLE or VARRAY attributes, PLS_INTEGER, POSITIVE, POSITIVEN, REFCURSOR, SIGNTYPE, STRING, TABLE, or VARRAY. Also, with varrays, element_type cannot be BLOB, CLOB, or an object type with BLOB or CLOB attributes. If element_type is a record type, every field in the record must be a scalar type or an object type.

INDEX BY BINARY_INTEGER

This optional clause lets you define Version 2 PL/SQL tables, which are called index-by tables in Version 8.

size_limit

This is a positive integer literal that specifies the maximum size of a varray, which is the maximum number of elements the varray can contain.

type_name

This identifies a user-defined collection type that was defined using the datatype specifier TABLE or VARRAY.

Usage Notes

Every element reference includes the collection name and a subscript enclosed in parentheses; the subscript determines which element is processed. Except for index-by tables, which can have negative subscripts, collection subscripts have a fixed lower bound of 1.

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.

Index-by tables and nested tables can be sparse (have non-consecutive subscripts), but varrays are always dense (have consecutive subscripts). Unlike nested tables, varrays retain their ordering and subscripts when stored in the database.

Initially, index-by tables are sparse. That enables you, for example, to store reference data in a temporary index-by table using a numeric 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" a collection from the elements passed to it.

Because nested tables and varrays can be atomically null, they can be tested for nullity. However, they cannot be compared for equality or inequality. This restriction also applies to implicit comparisons. For example, collections cannot appear in a DISTINCT, GROUPBY, or ORDERBY list.

Collections can store instances of an object type and, conversely, can be attributes of an object type. Also, collections can be passed as parameters. So, you can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms.

When calling a function that returns a collection, you use the following syntax to reference elements in the collection:

collection_name(parameter_list)(subscript)

With the Oracle Call Interface (OCI) or the Oracle Precompilers, you can bind host arrays to index-by tables declared as the formal parameters of a subprogram. That allows you to pass host arrays to stored functions and procedures.

Examples

To specify the element type of a collection, you can use %TYPE or %ROWTYPE, as the following example shows:

DECLARE
TYPE JobList IS VARRAY(10) OF emp.job%TYPE; -- based on column
CURSOR c1 IS SELECT * FROM dept;
TYPE DeptFile IS TABLE OF c1%ROWTYPE; -- based on cursor
TYPE EmpFile IS VARRAY(150) OF emp%ROWTYPE; -- based on database
table

In the next example, you use a RECORD type to specify the element type:

DECLARE
TYPE Entry IS RECORD (
term VARCHAR2(20),
meaning VARCHAR2(200));
TYPE Glossary IS VARRAY(250) OF Entry;

In the example below, you declare an index-by table of records. Each element of the table stores a row from the emp database table.

Related Topics

Collection Methods, Object Types, Records

Comments

Comments describe the purpose and use of code segments and so promote readability. PL/SQL supports two comment styles: single-line and multi-line. Single-line comments begin with a double hyphen (--) anywhere on a line and extend to the end of the line. Multi-line comments begin with a slash-asterisk (/*), end with an asterisk-slash (*/), and can span multiple lines. For more information, see "Comments".

Syntax

Usage Notes

Comments can appear within a statement at the end of a line. However, you cannot nest comments.

You cannot use single-line comments in a PL/SQL block that will be processed dynamically by an Oracle Precompiler program because end-of-line characters are ignored. As a result, single-line comments extend to the end of the block, not just to the end of a line. Instead, use multi-line comments.

While testing or debugging a program, you might want to disable a line of code. The following example shows how you can "comment-out" the line:

-- UPDATE dept SET loc = my_loc WHERE deptno = my_deptno;

You can use multi-line comment delimiters to comment-out whole sections of code.

COMMIT Statement

The COMMIT statement explicitly makes permanent any changes made to the database during the current transaction. Changes made to the database are not considered permanent until they are committed. A commit also makes the changes visible to other users. For more information, see "Processing Transactions".

Syntax

Keyword and Parameter Description

COMMENT

This keyword specifies a comment to be associated with the current transaction and is typically used with distributed transactions. The text must be a quoted literal no more than 50 characters long.

WORK

This keyword is optional and has no effect except to improve readability.

Usage Notes

The COMMIT statement releases all row and table locks. It also erases any savepoints you marked since the last commit or rollback. Until your changes are committed, the following conditions hold:

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 FORUPDATE cursor is open, a subsequent fetch on that cursor raises an exception. The cursor remains open, however, so you should close it. For more information, see "Using FOR UPDATE".

When a distributed transaction fails, the text specified by COMMENT helps you diagnose the problem. If a distributed transaction is ever in doubt, Oracle stores the text in the data dictionary along with the transaction ID. For more information about distributed transactions, see Oracle8i Concepts.

In SQL, the FORCE clause manually commits an in-doubt distributed transaction. However, PL/SQL does not support this clause. For example, the following statement is illegal:

COMMIT WORK FORCE '23.51.54'; -- illegal

In embedded SQL, the RELEASE option frees all Oracle resources (locks and cursors) held by a program and disconnects from the database. However, PL/SQL does not support this option. For example, the following statement is illegal:

COMMIT WORK RELEASE; -- illegal

Related Topics

ROLLBACK Statement, SAVEPOINT Statement

Constants and Variables

You can declare constants and variables in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its datatype, and name the storage location so that you can reference it. Declarations can also assign an initial value and impose the NOTNULL constraint. For more information, see "Declarations".

Syntax

Keyword and Parameter Description

collection_name

This identifies a collection (index-by table, nested table, or varray) previously declared within the current scope.

collection_type_name

This identifies a user-defined collection type that was defined using the datatype specifier TABLE or VARRAY.

CONSTANT

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

This identifies a program constant. For naming conventions, see "Identifiers".

cursor_name

This identifies an explicit cursor previously declared within the current scope.

cursor_variable_name

This identifies a PL/SQL cursor variable previously declared within the current scope.

db_table_name

This identifies a database table (or view) that must be accessible when the declaration is elaborated.

db_table_name.column_name

This identifies a database table and column that must be accessible when the declaration is elaborated.

expression

This is 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 constant or variable. The value and the constant or variable must have compatible datatypes.

NOT NULL

This constraint prevents the assigning of nulls to a variable or constant. At run time, trying to assign a null to a variable defined as NOTNULL raises the predefined exception VALUE_ERROR. The constraint NOTNULL must be followed by an initialization clause.

object_name

This identifies an object (instance of an object type) previously declared within the current scope.

record_name

This identifies a user-defined or %ROWTYPE record previously declared within the current scope.

record_name.field_name

This identifies a field in a user-defined or %ROWTYPE record previously declared within the current scope.

record_type_name

This identifies a user-defined record type that was defined using the datatype specifier RECORD.

ref_cursor_type_name

This identifies a user-defined cursor variable type that was defined using the datatype specifier REFCURSOR .

%ROWTYPE

This attribute provides a record type that represents a row in a database table or a row fetched from a previously declared cursor. Fields in the record and corresponding columns in the row have the same names and datatypes.

scalar_datatype_name

This identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2. For more information, see "Datatypes".

%TYPE

variable_name

This identifies 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. So, unless you expressly initialize a variable, its value is undefined.

Whether public or private, constants and variables declared in a package spec are initialized only once per session.

An initialization clause is required when declaring NOTNULL variables and when declaring constants. If you use %ROWTYPE to declare a variable, initialization is not allowed.

Related Topics

Cursor Attributes

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 Attributes".

The implicit cursor SQL has an additional attribute, %BULK_ROWCOUNT. For more information, see "SQL Cursor".

Syntax

Keyword and Parameter Description

cursor_name

This identifies an explicit cursor previously declared within the current scope.

cursor_variable_name

This identifies a PL/SQL cursor variable (or parameter) previously declared within the current scope.

%FOUND

This is 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 yields NULL. Thereafter, it yields TRUE if the last fetch returned a row, or FALSE if the last fetch failed to return a row.

host_cursor_variable_name

This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. 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

This is a cursor attribute that can be appended to the name of a cursor or cursor variable. If a cursor is open, cursor_name%ISOPEN yields TRUE; otherwise, it yields FALSE.

%NOTFOUND

This is 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 yields NULL. Thereafter, it yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.

%ROWCOUNT

This is 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 yields 0. Thereafter, it yields 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. So, 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 have been 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 SELECTINTO 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. So, if FETCH never executes successfully, the loop is never exited. That is because the EXITWHEN statement executes only if its WHEN condition is true. To be safe, you might want to 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.

Examples

The PL/SQL block below uses %FOUND to select an action. The IF statement either inserts a row or exits the loop unconditionally.

-- available online in file 'examp12'
DECLARE
CURSOR num1_cur IS SELECT num FROM num1_tab
ORDER BY sequence;
CURSOR num2_cur IS SELECT num FROM num2_tab
ORDER BY sequence;
num1 num1_tab.num%TYPE;
num2 num2_tab.num%TYPE;
pair_num NUMBER := 0;
BEGIN
OPEN num1_cur;
OPEN num2_cur;
LOOP -- loop through the two tables and get pairs of numbers
FETCH num1_cur INTO num1;
FETCH num2_cur INTO num2;
IF (num1_cur%FOUND) AND (num2_cur%FOUND) THEN
pair_num := pair_num + 1;
INSERT INTO sum_tab VALUES (pair_num, num1 + num2);
ELSE
EXIT;
END IF;
END LOOP;
CLOSE num1_cur;
CLOSE num2_cur;
END;

The next example uses the same block. However, instead of using %FOUND in an IF statement, it uses %NOTFOUND in an EXITWHEN statement.

-- available online in file 'examp13'
DECLARE
CURSOR num1_cur IS SELECT num FROM num1_tab
ORDER BY sequence;
CURSOR num2_cur IS SELECT num FROM num2_tab
ORDER BY sequence;
num1 num1_tab.num%TYPE;
num2 num2_tab.num%TYPE;
pair_num NUMBER := 0;
BEGIN
OPEN num1_cur;
OPEN num2_cur;
LOOP -- loop through the two tables and get
-- pairs of numbers
FETCH num1_cur INTO num1;
FETCH num2_cur INTO num2;
EXIT WHEN (num1_cur%NOTFOUND) OR (num2_cur%NOTFOUND);
pair_num := pair_num + 1;
INSERT INTO sum_tab VALUES (pair_num, num1 + num2);
END LOOP;
CLOSE num1_cur;
CLOSE num2_cur;
END;

In the following example, you use %ISOPEN to make a decision:

IF NOT (emp_cur%ISOPEN) THEN
OPEN emp_cur;
END IF;
FETCH emp_cur INTO emp_rec;

The following PL/SQL block uses %ROWCOUNT to fetch the names and salaries of the five highest-paid employees:

Related Topics

Cursors, Cursor Variables

Cursor Variables

To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. To access the information, you can use an explicit cursor, which names the work area. Or, you can use a cursor variable, which points to the work area. Whereas a cursor always refers to the same query work area, a cursor variable can refer to different work areas. To create cursor variables, you define a REFCURSOR type, then declare cursor variables of that type.

Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself. So, declaring a cursor variable creates a pointer, not an item.

Syntax

Keyword and Parameter Description

cursor_name

This identifies an explicit cursor previously declared within the current scope.

cursor_variable_name

This identifies a PL/SQL cursor variable previously declared within the current scope.

db_table_name

This identifies a database table (or view) that must be accessible when the declaration is elaborated.

record_name

This identifies a user-defined record previously declared within the current scope.

record_type_name

This identifies a user-defined record type that was defined using the datatype specifier RECORD.

REF CURSOR

In PL/SQL, pointers have datatype REFX, where REF is short for REFERENCE and X stands for a class of objects. Therefore, cursor variables have datatype REFCURSOR.

RETURN

This keyword introduces the RETURN clause, which 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 returned by a cursor or strongly typed cursor variable. Also, you can use the %TYPE attribute to provide the datatype of a previously declared record.

%ROWTYPE

This attribute provides 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

This attribute provides the datatype of a previously declared user-defined record.

type_name

This is a user-defined cursor variable type that was defined using the datatype specifier REFCURSOR.

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 variable to PL/SQL. Moreover, application development tools such as Oracle Forms and Oracle Reports, which have a PL/SQL engine, can use cursor variables entirely on the client side.

The Oracle database server also has a PL/SQL engine. So, you can pass cursor variables back and forth between an application and server via remote procedure calls (RPCs). And, if you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. 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.

Mainly, you use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients. Neither PL/SQL nor any of its clients owns a result set; they simply share a pointer to the query work area in which the result set is stored. For example, an OCI client, Oracle Forms application, and Oracle server can all refer to the same work area.

REFCURSOR types can be strong (restrictive) or weak (nonrestrictive). A strong REFCURSOR type definition specifies a return type, but a weak definition does not. Strong REFCURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with type-compatible queries. However, weak REFCURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query.

Once you define a REFCURSOR 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 REFCURSOR type definition, you can use %ROWTYPE to specify a record type that represents a row returned by a strongly (not weakly) typed cursor variable.

You use three statements to control a cursor variable: OPEN-FOR, FETCH, and CLOSE. First, you OPEN a cursor variable FOR a multi-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 INOUT mode. If the subprogram opens the cursor variable, you must specify the INOUT 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. For more information, see "Using Cursor Attributes".

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.

Examples

You can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program. To use the host cursor variable, you must pass it as a bind variable to PL/SQL. In the following Pro*C example, you pass a host cursor variable and a selector to a PL/SQL block, which opens the cursor variable for the chosen query:

Host cursor variables are compatible with any query return type. They behave just like weakly typed PL/SQL cursor variables.

When passing host cursor variables to PL/SQL, you can reduce network traffic by grouping OPEN-FOR statements. For example, the following PL/SQL block opens three cursor variables in a single round-trip:

/* anonymous PL/SQL block in host environment */
BEGIN
OPEN :emp_cv FOR SELECT * FROM emp;
OPEN :dept_cv FOR SELECT * FROM dept;
OPEN :grade_cv FOR SELECT * FROM salgrade;
END;

You can also pass a cursor variable to PL/SQL by calling a stored procedure that declares a cursor variable as one of its formal parameters. To centralize data retrieval, you can group type-compatible queries in a packaged procedure, as the following example shows:

CREATE PACKAGE emp_data AS
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp,
choice IN NUMBER);
END emp_data;
CREATE PACKAGE BODY emp_data AS
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp,
choice IN NUMBER) IS
BEGIN
IF choice = 1 THEN
OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL;
ELSIF choice = 2 THEN
OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500;
ELSIF choice = 3 THEN
OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20;
END IF;
END open_emp_cv;
END emp_data;

Alternatively, you can use a stand-alone procedure to open the cursor variable. Simply define the REFCURSOR type in a separate package, then reference that type in the stand-alone procedure. For instance, if you create the following (bodiless) package, you can create stand-alone procedures that reference the types it defines:

Related Topics

Cursors

To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. An explicit cursor lets you name the work area, access the information, and process the rows individually. For more information, see "Managing Cursors".

Syntax

Keyword and Parameter Description

cursor_name

This identifies an explicit cursor previously declared within the current scope.

datatype

db_table_name

This identifies a database table (or view) that must be accessible when the declaration is elaborated.

expression

This is 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.

parameter_name

This identifies a cursor parameter; that is, 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

This identifies a user-defined record previously declared within the current scope.

record_type_name

This identifies a user-defined record type that was defined using the datatype specifier RECORD.

RETURN

This keyword introduces the RETURN clause, which 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

This attribute provides 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

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

This attribute 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. And, you must declare a variable before referencing it in a cursor declaration. The word SQL is reserved by PL/SQL for use 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".

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. For example, the following parameter declarations are illegal:

Related Topics

CLOSE Statement, FETCH Statement, OPEN Statement, SELECT INTO Statement

DELETE Statement

The DELETE statement removes entire rows of data from a specified table or view. For a full description of the DELETE statement, see Oracle8i SQL Reference.

Syntax

Keyword and Parameter Description

alias

This is another (usually short) name for the referenced table or view and is typically used in the WHERE clause.

BULK COLLECT

This clause 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 RETURNINGINTO list. The corresponding columns must store scalar (not composite) values. For more information, see "Taking Advantage of Bulk Binds".

returning_clause

This clause lets you return values from the deleted rows, thereby eliminating the need to SELECT the rows beforehand. You can retrieve the column values into variables and/or host variables, or into collections and/or host arrays. However, you cannot use the RETURNING clause for remote or parallel deletes.

subquery

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

This specifies 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.

WHERE CURRENT OF cursor_name

This clause refers to the latest row processed by the FETCH statement associated with the cursor identified by cursor_name. The cursor must be FORUPDATE and must be open and positioned on a row. If the cursor is not open, the CURRENTOF clause causes an error.

If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND.

WHERE search_condition

This clause 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 DELETEWHERECURRENTOF statement after a fetch from an open cursor (this includes implicit fetches executed in a cursor FOR loop), provided the associated query is FORUPDATE. 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.

Examples

The following statement deletes from the bonus table all employees whose sales were below quota:

DELETE FROM bonus WHERE sales_amt < quota;

The following statement returns two column values from a deleted row into local variables:

You can combine the BULKCOLLECT clause with a FORALL statement, in which case, the SQL engine bulk-binds column values incrementally. In the following example, if collection depts has 3 elements, each of which causes 5 rows to be deleted, then collection enums has 15 elements when the statement completes:

The column values returned by each execution are added to the values returned previously.

Related Topics

FETCH Statement, SELECT Statement

EXCEPTION_INIT Pragma

The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. That allows you to refer to any internal exception by name and to write a specific handler for it instead of using the OTHERS handler. For more information, see "Using EXCEPTION_INIT".

Syntax

Keyword and Parameter Description

error_number

This is any valid Oracle error number. These are the same error numbers returned by the function SQLCODE.

exception_name

This identifies a user-defined exception previously declared within the current scope.

PRAGMA

This keyword signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey 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.

Example

The following pragma associates the exception deadlock_detected with Oracle error 60:

Related Topics

Exceptions

An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime 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 6, "Error Handling".

Syntax

Keyword and Parameter Description

exception_name

This identifies a predefined exception such as ZERO_DIVIDE, or a user-defined exception previously declared within the current scope.

OTHERS

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

This is an executable statement. For the syntax of statement, see "Blocks".

WHEN

This keyword 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 "Predefined 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.

An exception should be raised 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.

Syntax

Keyword and Parameter Description

bind_argument

This can be an expression whose value is passed to the dynamic SQL statement or PL/SQL block, or it can be a variable that stores a value returned by the dynamic SQL statement or PL/SQL block.

define_variable_name

This identifies a variable that stores a selected column value.

dynamic_string

This is a string literal, variable, or expression that represents a SQL statement or PL/SQL block.

INTO ...

Used only for single-row queries, this clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO clause.

record_name

This identifies a user-defined or %ROWTYPE record that stores a selected row.

RETURNING INTO ...

Used only for DML statements that have a RETURNING clause (without a BULKCOLLECT clause), this clause specifies the bind variables into which column values are returned. For each value returned by the DML statement, there must be a corresponding, type-compatible variable in the RETURNINGINTO clause.

USING ...

This clause specifies a list of input and/or output bind arguments. If you do not specify a parameter mode, it defaults to IN.

Usage Notes

Except for multi-row queries, the dynamic string can contain any SQL statement (without the terminator) or any PL/SQL block (with the terminator). The string can also contain placeholders for bind arguments. However, you cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement. For the right way, see "Passing the Names of Schema Objects".

You can place all bind arguments in the USING clause. The default parameter mode is IN. For DML statements that have a RETURNING clause, you can place OUT arguments in the RETURNINGINTO clause without specifying the parameter mode, which, by definition, is OUT. If you use both the USING clause and the RETURNINGINTO clause, the USING clause can contain only IN arguments.

At run time, bind arguments replace corresponding placeholders in the dynamic string. So, every placeholder must be associated with a bind argument in the USING clause and/or RETURNINGINTO clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals (TRUE, FALSE, and NULL). To pass nulls to the dynamic string, you must use a workaround. See "Passing Nulls".

Numeric, character, and string literals are allowed in the USING clause, but Boolean literals (TRUE, FALSE, NULL) are not. To pass nulls to the dynamic string, you must use a workaround (see "Passing Nulls").

Dynamic SQL supports all the SQL datatypes. So, for example, define variables and bind arguments can be collections, LOBs, instances of an object type, and refs. As a rule, dynamic SQL does not support PL/SQL-specific types. So, for example, define variables and bind arguments cannot be Booleans or index-by tables. The only exception is that a PL/SQL record can appear in the INTO clause.

You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. However, you incur some overhead because EXECUTEIMMEDIATE re-prepares the dynamic string before every execution.

Related Topics

OPEN-FOR-USING Statement

EXIT Statement

You use the EXIT statement to exit a loop. The EXIT statement has two forms: the unconditional EXIT and the conditional EXITWHEN. With either form, you can name the loop to be exited. For more information, see "Iterative Control: LOOP and EXIT Statements".

Syntax

Keyword and Parameter Description

boolean_expression

This is an expression that yields the Boolean value TRUE, FALSE, or NULL. It is evaluated with each iteration of the loop in which the EXITWHEN statement appears. If the expression yields TRUE, the current loop (or the loop labeled by label_name) is exited immediately. For the syntax of boolean_expression, see "Expressions".

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

This identifies the loop to be exited. You can exit not only the current loop but any enclosing labeled loop.

Usage Notes

The EXIT statement can be used only inside a loop. PL/SQL allows you to code an infinite loop. For example, the following loop will never terminate normally:

WHILE TRUE LOOP ... END LOOP;

In such cases, you must use an EXIT statement to exit the 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.

Examples

The EXIT statement in the following example is illegal because you cannot exit from a block directly; you can exit only from a loop:

The following loop normally executes ten times, but it will exit prematurely if there are less than ten rows to fetch:

FOR i IN 1..10 LOOP
FETCH c1 INTO emp_rec;
EXIT WHEN c1%NOTFOUND;
total_comm := total_comm + emp_rec.comm;
END LOOP;

The following example illustrates the use of loop labels:

<<outer>>
FOR i IN 1..10 LOOP
...
<<inner>>
FOR j IN 1..100 LOOP
...
EXIT outer WHEN ... -- exits both loops
END LOOP inner;
END LOOP outer;

Related Topics

Expressions, LOOP Statements

Expressions

An expression is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression is a single variable.

The PL/SQL compiler determines the datatype of an expression from the types of the variables, constants, literals, and operators that comprise the expression. Every time the expression is evaluated, a single value of that type results. For more information, see "Expressions and Comparisons".

Syntax

Keyword and Parameter Description

BETWEEN

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

This identifies a constant of type BOOLEAN, which must be initialized to the value TRUE, FALSE, or NULL. Arithmetic operations on Boolean constants are illegal.

boolean_expression

This is an expression that yields the Boolean value TRUE, FALSE, or NULL.

boolean_function_call

This is any function call that returns a Boolean value.

boolean_literal

This is the predefined value 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

This identifies 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 illegal.

%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

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

This is an expression that yields a character or character string.

character_function_call

This is a function call that returns a character value or a value implicitly convertible to a character value.

character_literal

This is a literal that represents a character value or a value implicitly convertible to a character value.

character_variable_name

This identifies a previously declared variable that stores a character value.

collection_name

This identifies a collection (nested table, index-by table, or varray) previously declared within the current scope.

cursor_name

This identifies an explicit cursor previously declared within the current scope.

cursor_variable_name

This identifies a PL/SQL cursor variable previously declared within the current scope.

date_constant_name

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

This is an expression that yields a date/time value.

date_function_call

This is a function call that returns a date value or a value implicitly convertible to a date value.

date_literal

This is a literal that represents a date value or a value implicitly convertible to a date value.

date_variable_name

This identifies a previously declared variable that stores a date value.

EXISTS, COUNT, FIRST, LAST, LIMIT, NEXT, PRIOR

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

This is an expression that must yield a numeric value.

%FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT

These are cursor attributes. When appended to the name of a cursor or cursor variable, these attributes return useful information about the execution of a multi-row query. You can also append them to the implicit cursor SQL. For more information, see "Using Cursor Attributes".

host_cursor_variable_name

This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. Host cursor variables must be prefixed with a colon.

host_variable_name

This identifies a variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. 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

This comparison operator 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

yield FALSE if the set contains a null.

index

This is an expression that must yield (or convert implicitly to) an integer.

indicator_name

This identifies 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.

IS NULL

This comparison operator returns the Boolean value TRUE if its operand is null, or FALSE if its operand is not null.

LIKE

This comparison operator 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

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

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

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

This is an expression that yields an integer or real value.

numeric_function_call

This is a function call that returns a numeric value or a value implicitly convertible to a numeric value.

numeric_literal

This is a literal that represents a number or a value implicitly convertible to a number.

numeric_variable_name

This identifies a previously declared variable that stores a numeric value.

pattern

This is 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.

relational_operator

This operator allows you to compare expressions. For the meaning of each operator, see "Comparison Operators".

SQL

This identifies 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.

+, -, /, *, **

These symbols are the addition, subtraction, division, multiplication, and exponentiation operators, respectively.

||

This is 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 "Datatype Conversion".

In conditional control statements, if a Boolean expression yields TRUE, its associated sequence of statements is executed. But, if the expression yields 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 yield 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:

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 their predefined order of precedence. From first to last (top to bottom), the default order of operations is

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.

Related Topics

FETCH Statement

The FETCH statement retrieves rows of data one at a time from the result set of a multi-row query. The data is stored in variables or fields that correspond to the columns selected by the query. For more information, see "Managing Cursors".

Syntax

Keyword and Parameter Description

BULK COLLECT

This clause 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. For more information, see "Taking Advantage of Bulk Binds".

collection_name

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

This identifies an explicit cursor previously declared within the current scope.

cursor_variable_name

This identifies a PL/SQL cursor variable (or parameter) previously declared within the current scope.

host_array_name

This identifies an array (declared in a PL/SQL host environment and passed to PL/SQL as a bind variable) 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

This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. 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.

LIMIT

This optional clause, allowed only in bulk (not scalar) FETCH statements, lets you limit the number of rows fetched from the database.

record_name

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

This identifies a previously declared 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 multi-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 yields 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 INOUT mode. However, if the subprogram also opens the cursor variable, you must specify the INOUT mode.

Eventually, the FETCH statement must fail to return a row, so when that happens, no exception is raised. To detect the failure, you must use the cursor attribute %FOUND or %NOTFOUND. For more information, see "Using Cursor Attributes".

PL/SQL raises the predefined exception INVALID_CURSOR if you try to fetch from a closed or never-opened cursor or cursor variable.

Examples

The following example shows that any variables in the query associated with a cursor are evaluated only when the cursor is opened:

The BULKCOLLECT clause lets you bulk-bind entire columns of Oracle data. That way, you can fetch all rows from the result set at once. In the following example, you bulk-fetch from a cursor into a collection:

Related Topics

FORALL Statement

The FORALL statement instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not a FOR loop. For more information, see "Taking Advantage of Bulk Binds".

Syntax

Keyword and Parameter Description

index_name

This is 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. So, another variable with the same name cannot be referenced inside the statement. Inside a FORALL statement, index_name cannot appear in expressions and cannot be assigned a value.

lower_bound .. upper_bound

These are expressions that must yield number, which, if necessary, PL/SQL rounds to the nearest integer. The integers must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index number in the range. The expressions are evaluated only when the FORALL statement is first entered.

sql_statement

This must be an INSERT, UPDATE, or DELETE statement that references collection elements.

Usage Notes

The SQL statement can reference more than one collection. However, the PL/SQL engine bulk-binds only subscripted collections.

All collection elements in the specified range must exist. If an element is missing or was deleted, you get an error.

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 executions are not rolled back.

Example

The following example shows that you can use the lower and upper bounds to bulk-bind arbitrary slices of a collection:

Related Topics

BULK COLLECT Clause

Functions

A function is a subprogram that can take parameters and be invoked. Generally, you use a function to compute a value. A function has two parts: the specification and the body. The specification (spec for short) begins with the keyword FUNCTION and ends with the RETURN clause, which specifies the datatype of the return value. Parameter declarations are optional. Functions that take no parameters are written without parentheses. The function body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional function name.

The function body has three parts: an optional declarative part, an executable part, and an optional exception-handling part. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These items are local and cease to exist when you exit the function. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains handlers that deal with exceptions raised during execution. For more information, see "Understanding Functions".

Syntax

Keyword and Parameter Description

AUTHID

This clause determines whether a stored function executes with the privileges of its owner (the default) or current user and whether its unqualified references to schema objects are resolved in the schema of the owner or current user. You can override the default behavior by specifying CURRENT_USER. For more information, see "Invoker Rights versus Definer Rights".

CREATE

The optional CREATE clause lets you create stand-alone functions, which are stored in the Oracle database. You can execute the CREATE statement interactively from SQL*Plus or from a program using native dynamic SQL (see Chapter 10).

datatype

DETERMINISTIC

This hint helps the optimizer avoid redundant function calls. If a stored function was called previously with the same arguments, the optimizer can elect to use the previous result. The function result should not depend on the state of session variables or schema objects. Otherwise, results might vary across calls. Only DETERMINISTIC functions can be called from a function-based index or a materialized view that has query-rewrite enabled. For more information, see the statements CREATEINDEX and CREATEMATERIALIZEDVIEW inOracle8i SQL Reference.

exception_handler

This associates an exception with a sequence of statements, which is executed when that exception is raised. For the syntax of exception_handler, see "Exceptions".

expression

This is 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.

function_name

This identifies a user-defined function.

IN, OUT, IN OUT

These parameter modes define the behavior of formal parameters. An IN parameter lets you pass values to the subprogram being called. An OUT parameter lets you return values to the caller of the subprogram. An INOUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller.

item_declaration

This declares a program object. For the syntax of item_declaration, see "Blocks".

NOCOPY

This is a compiler hint (not directive), which allows the PL/SQL compiler to pass OUT and INOUT parameters by reference instead of by value (the default). For more information, see "Using the NOCOPY Compiler Hint".

PARALLEL_ENABLE

This option 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 should not depend on the state of session (static) variables. Otherwise, results might vary across sessions.

parameter_name

This identifies a formal parameter, which is a variable declared in a function spec and referenced in the function body.

PRAGMA AUTONOMOUS_TRANSACTION

This pragma instructs the PL/SQL compiler to mark a function as autonomous (independent). An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction. For more information, see "Using Autonomous Transactions".

procedure_declaration

This declares a procedure. For the syntax of procedure_declaration, see "Procedures".

RETURN

This keyword introduces the RETURN clause, which specifies the datatype of the return value.

type_definition

This specifies a user-defined datatype. For the syntax of type_definition, see "Blocks".

:= | DEFAULT

This operator or keyword allows you to initialize IN parameters to default values.

Usage Notes

A function is called as part of an expression, as the example below shows. To be callable from SQL statements, a stored function must obey certain rules meant to control side effects. See "Controlling Sides Effects".

promotable := sal_ok(new_sal, new_title) AND (rating > 3);

In a function, there must be at least one execution path that leads to a RETURN statement. Otherwise, you get a function returned without value error at run time. Also, 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 called only from inside the package.

Inside a function, an IN parameter acts like a constant. So, you cannot assign it a value. An OUT parameter acts like a local variable. So, you can change its value and reference the value in any way. An INOUT parameter acts like an initialized variable. So, you can assign it a value, which can be assigned to another variable. For summary information about the parameter modes, see Table 7-1.

Avoid using the OUT and INOUT modes with functions. The purpose of a function is to take zero or more parameters and return a single value. Also, functions should be free from side effects, which change the values of variables not local to the subprogram.

Example

The following function returns the balance of a specified bank account:

Related Topics

Collection Methods, Packages, Procedures

GOTO Statement

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 "GOTO Statement".

Syntax

Keyword and Parameter Description

label_name

This is an undeclared identifier that labels an executable statement or a PL/SQL block. You use a GOTO statement to transfer control to the statement or block following <<label_name>>.

Usage Notes

Some possible destinations of a GOTO statement are illegal. In particular, a GOTO statement cannot branch into an IF statement, LOOP statement, or sub-block. For example, the following GOTO statement is illegal:

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.

Examples

A GOTO label cannot precede just any keyword. It must precede an executable statement or a PL/SQL block. For example, the following GOTO statement is illegal:

FOR ctr IN 1..50 LOOP
DELETE FROM emp WHERE ...
IF SQL%FOUND THEN
GOTO end_loop; -- illegal
END IF;
...
<<end_loop>>
END LOOP; -- not an executable statement

To debug the last example, simply add the NULL statement, as follows:

FOR ctr IN 1..50 LOOP
DELETE FROM emp WHERE ...
IF SQL%FOUND THEN
GOTO end_loop;
END IF;
...
<<end_loop>>
NULL; -- an executable statement that specifies inaction
END LOOP;

IF Statement

The IF statement lets you execute a sequence of statements conditionally. Whether the sequence is executed or not depends on the value of a Boolean expression. For more information, see "Conditional Control: IF Statements".

Syntax

Keyword and Parameter Description

boolean_expression

This is an expression that yields the Boolean value TRUE, FALSE, or NULL. It is associated with a sequence of statements, which is executed only if the expression yields TRUE.

ELSE

If control reaches this keyword, the sequence of statements that follows it is executed.

ELSIF

This keyword introduces a Boolean expression to be evaluated if the expression following IF and all the expressions following any preceding ELSIFs yield FALSE or NULL.

THEN

This keyword associates the Boolean expression that precedes it with the sequence of statements that follows it. If the expression yields TRUE, the associated sequence of statements is 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 ENDIF. The sequence of statements is executed only if the expression yields TRUE. If the expression yields 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 yields 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 yields 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 yields TRUE, its associated sequence of statements is executed and control passes to the next statement. If all expressions yield 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.

Examples

In the example below, if shoe_count has a value of 10, both the first and second Boolean expressions yield TRUE. Nevertheless, order_quantity is assigned the proper value of 50 because processing of an IF statement stops after an expression yields TRUE and its associated sequence of statements is executed. The expression associated with ELSIF is never evaluated and control passes to the INSERT statement.

Related Topics

INSERT Statement

The INSERT statement adds new rows of data to a specified database table. For a full description of the INSERT statement, see Oracle8i SQL Reference.

Syntax

Keyword and Parameter Description

alias

This is another (usually short) name for the referenced table or view.

column_name[, column_name]...

This identifies a list of columns in a database table or view. Column names need not appear in the order in which they were defined by the CREATETABLE or CREATEVIEW statement. However, no column name can appear more than once in the list. If the list does not include all the columns in a table, the missing columns are set to NULL or to a default value specified in the CREATETABLE statement.

returning_clause

This clause lets you return values from inserted rows, thereby eliminating the need to SELECT the rows afterward. You can retrieve the column values into variables and/or host variables, or into collections and/or host arrays. However, you cannot use the RETURNING clause for remote or parallel inserts. For the syntax of returning_clause, see "DELETE Statement".

sql_expression

subquery

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

This is a SELECT statement that returns a value or set of values. As many rows are added to the table as are returned by the subquery. It 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

This identifies 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, which must be a nested table. Operator TABLE informs Oracle that the value is a collection, not a scalar value.

VALUES (...)

This clause 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 CREATETABLE statement, the second value is inserted into the second column, and so on. There must be only one value for each column in the column list. Also, 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

SELECT Statement

Literals

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

Keyword and Parameter Description

character

This is a member of the PL/SQL character set. For more information, see "Character Set".

digit

This is one of the numerals 0 .. 9.

TRUE, FALSE, NULL

This is a predefined Boolean value.

Usage Notes

Two kinds of numeric literals can be used in arithmetic expressions: integers and reals. Numeric literals must be separated by punctuation. Spaces can be used in addition to the punctuation.

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. So, for example, PL/SQL considers the literals 'Q' and 'q' to be different.

A string literal is a sequence of zero or more characters enclosed by single quotes. The null string ('') contains zero characters. To represent an apostrophe within a string, write two single quotes. PL/SQL is case sensitive within string literals. So, for example, PL/SQL considers the literals 'white' and 'White' to be different.

Also, trailing blanks are significant within string literals, so 'abc' and 'abc ' are different. Trailing blanks in a literal are never trimmed.

The Boolean values TRUE and FALSE cannot be inserted into a database column.

Examples

Several examples of numeric literals follow:

25 6.34 7E2 25e-03 .1 1. +17 -4.4

Several examples of character literals follow:

'H' '&' ' ' '9' ']' 'g'

A few examples of string literals follow:

'$5,000'
'02-AUG-87'
'Don''t leave without saving your work.'

Related Topics

Constants and Variables, Expressions

LOCK TABLE Statement

The LOCKTABLE statement lets you lock 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".

Syntax

Keyword and Parameter Description

lock_mode

This parameter specifies the lock mode. It must be one of the following: ROWSHARE, ROWEXCLUSIVE, SHAREUPDATE, SHARE, SHAREROWEXCLUSIVE, or EXCLUSIVE.

NOWAIT

This optional keyword tells Oracle not to wait if the table has been locked by another user. Control is immediately returned to your program, so it can do other work before trying again to acquire the lock.

table_reference

This identifies a table or view that must be accessible when you execute the LOCKTABLE statement. For the syntax of table_reference, see "DELETE Statement".

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.

Example

The following statement locks the accts table in shared mode:

LOCK TABLE accts IN SHARE MODE;

Related Topics

COMMIT Statement, ROLLBACK Statement

LOOP Statements

LOOP statements execute a sequence of statements multiple times. The loop encloses the sequence of statements that is to be repeated. PL/SQL provides four kinds of loop statements: basic loop, WHILE loop, FOR loop, and cursor FOR loop. For more information, see "Iterative Control: LOOP and EXIT Statements".

Syntax

Keyword and Parameter Description

basic_loop_statement

The simplest form of LOOP statement is the basic (or infinite) loop, which encloses a sequence of statements between the keywords LOOP and ENDLOOP. With each iteration of the loop, the sequence of statements is executed, then control resumes at the top of the loop. If further processing is undesirable or impossible, you can use the EXIT, GOTO, or RAISE statement to complete the loop. A raised exception will also complete the loop.

boolean_expression

This is an expression that yields the Boolean value TRUE, FALSE, or NULL. It is associated with a sequence of statements, which is executed only if the expression yields TRUE. For the syntax of boolean_expression, see "Expressions".

cursor_for_loop_statement

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 have been processed.

cursor_name

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

This identifies a cursor parameter; that is, a variable declared as the formal parameter of a cursor. (For the syntax of cursor_parameter_declaration, see "Cursors".) 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

Whereas the number of iterations through a WHILE loop is unknown until the loop completes, the number of iterations through a FOR loop is known before the loop is entered. Numeric FOR 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 sequence of statements in the loop 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

This is an undeclared identifier that names the loop index (sometimes called a loop counter). Its scope is the loop itself. Therefore, you cannot reference the index outside the loop.

The implicit declaration of index_name overrides any other declaration outside the loop. So, another variable with the same name cannot be referenced inside the loop unless a label is used, as follows:

<<main>>
DECLARE
num NUMBER;
BEGIN
...
FOR num IN 1..10 LOOP
IF main.num > 5 THEN -- refers to the variable num,
... -- not to the loop index
END IF;
END LOOP;
END main;

Inside a loop, its index is treated like a constant. The index can appear in expressions, but cannot be assigned a value.

label_name

This is an undeclared identifier that optionally labels a loop. If used, 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, as follows:

label_name.index_name

In the following example, you compare two loop indexes that have the same name, one used by an enclosing loop, the other by a nested loop:

<<outer>>
FOR ctr IN 1..20 LOOP
...
<<inner>>
FOR ctr IN 1..10 LOOP
IF outer.ctr > ctr THEN ...
END LOOP inner;
END LOOP outer;

lower_bound .. upper_bound

These are expressions that must yield 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, as the example below shows. However, the loop counter increment (or decrement) must be 1.

FOR i IN -5..10 LOOP
...
END LOOP;

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 +/-2**31. So, if a bound evaluates to a number outside that range, you get a numeric overflow error when PL/SQL attempts the assignment.

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

This identifies 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. So, another record with the same name cannot be referenced inside the loop unless a label is used.

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 sal+NVL(comm,0):

CURSOR c1 IS SELECT empno, sal+comm wages, job ...

REVERSE

By default, iteration proceeds upward from the lower bound to the upper bound. However, if you use the keyword REVERSE, iteration proceeds downward from the upper bound to the lower bound.

An example follows:

FOR i IN REVERSE 1..10 LOOP -- i starts at 10, ends at 1
-- statements here execute 10 times
END LOOP;

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

This is 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 ENDLOOP. Before each iteration of the loop, the expression is evaluated. If the expression yields TRUE, the sequence of statements is executed, then control resumes at the top of the loop. If the expression yields FALSE or NULL, the loop is bypassed and control passes to the next statement.

Usage Notes

You can use the EXITWHEN statement to exit any loop prematurely. If the Boolean expression in the WHEN clause yields 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.

Example

The following cursor FOR loop calculates a bonus, then inserts the result into a database table:

Related Topics

NULL Statement

The NULL statement explicitly specifies inaction; it does nothing other than pass control to the next statement. In a construct allowing alternative actions, the NULL statement serves as a placeholder. For more information, see "NULL Statement".

Syntax

Usage Notes

The NULL statement improves readability by making the meaning and action of conditional statements clear. It tells readers that the associated alternative has not been overlooked, but that indeed no action is necessary.

Each clause in an IF statement must contain at least one executable statement. The NULL statement meets this requirement. So, you can use the NULL statement in clauses that correspond to circumstances in which no action is taken. The NULL statement and Boolean value NULL are unrelated.

Examples

In the following example, the NULL statement emphasizes that only salespeople receive commissions:

IF job_title = 'SALESPERSON' THEN
compute_commission(emp_id);
ELSE
NULL;
END IF;

In the next example, the NULL statement shows that no action is taken for unnamed exceptions:

EXCEPTION
...
WHEN OTHERS THEN
NULL;

Object Types

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 behavior of the object type are called methods.

element Currently, you cannot define object types within PL/SQL. They must be CREATEd and stored in an Oracle database, where they can be shared by many programs. When you define an object type (in SQL*Plus for example) using the CREATETYPE statement, you create an abstract template for some real-world object. The template specifies only those attributes and behaviors the object will need in the application environment.

The data structure formed by the set of attributes is public (visible to client programs). However, well-behaved programs do not manipulate it directly. Instead, they use the set of methods provided. That way, the data is kept in a proper state. At run time, when the data structure is filled with values, you have created an instance of an object type. You can create as many instances (usually called objects) as you need. For more information, see Chapter 9, "Object Types".

attribute_name

This identifies an object attribute. 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 NOTNULL constraint on an attribute.

AUTHID Clause

This determines whether all member methods execute with the privileges of their definer (the default) or invoker, and whether their unqualified references to schema objects are resolved in the schema of the definer or invoker. For more information, see "Invoker Rights versus Definer Rights".

function_body

This defines the underlying implementation of a MEMBER function. For the syntax of function_body, see "Functions".

MAP

This keyword indicates that a method orders objects by mapping them to values of a scalar datatype such as CHAR or REAL, which have a predefined order. PL/SQL uses the ordering to evaluate Boolean expressions such as x > y, and to do comparisons implied by the DISTINCT, GROUPBY, and ORDERBY clauses. A map method returns the relative position of an object in the ordering of all such objects.

An object type can contain only one map method, which must be a parameterless function having the return type DATE, NUMBER, VARCHAR2, or an ANSI SQL type such as CHARACTER, INTEGER, or REAL.

MEMBER | STATIC

This keyword allows you to declare a subprogram or call spec as a method in an object type spec. The method cannot have the same name as the object type or any of its attributes. MEMBER methods are invoked on instances, as in

instance_expression.method()

However, STATIC methods are invoked on the object type, not its instances, as in

object_type_name.method()

For each subprogram spec in an object type spec, there must be a corresponding subprogram body in the object type body. To match specs and bodies, the compiler does a token-by-token comparison of their headers. So, the headers must match word for word.

MEMBER methods accept a built-in parameter named SELF, which is an instance of the object type. Whether declared implicitly or explicitly, it is always the first parameter passed to a MEMBER method. However, STATIC methods cannot accept or reference SELF.

In the method body, SELF denotes the object whose method was invoked. For example, method transform declares SELF as an INOUT parameter:

You cannot specify a different datatype for SELF. In MEMBER functions, if SELF is not declared, its parameter mode defaults to IN. However, in MEMBER procedures, if SELF is not declared, its parameter mode defaults to INOUT. You cannot specify the OUT parameter mode for SELF.

ORDER

This keyword indicates that a method compares two objects. An object type can contain only one order method, which must be a function that returns a numeric result.

Every order method takes just two parameters: the built-in parameter SELF and another object of the same type. If c1 and c2 are Customer objects, a comparison such as c1 > c2 calls method match automatically. The method returns a negative number, zero, or a positive number signifying that SELF is respectively less than, equal to, or greater than the other parameter. If either parameter passed to an order method is null, the method returns a null.

pragma_restrict_refs

This is pragma RESTRICT_REFERENCES, which lets you check for violations of "purity" rules. To be callable from SQL statements, a member function must obey those rules, which are meant to control side effects. If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed). For the syntax of the pragma, see "RESTRICT_REFERENCES Pragma" (in this context, omit the pragma terminator).

schema_name

This qualifier identifies the schema containing the object type. If you omit schema_name, Oracle assumes the object type is in your schema.

subprogram_body

This defines the underlying implementation of a MEMBER or STATIC function or procedure. Its syntax is like that of function_body or procedure_body without the terminator. See "Functions" and/or "Procedures".

subprogram_spec

This declares the interface to a MEMBER or STATIC function or procedure. Its syntax is like that of function_spec or procedure_spec without the terminator. See "Functions" and/or "Procedures".

type_name

This identifies a user-defined object type that was defined using the datatype specifier OBJECT.

Usage Notes

Once an object type is defined and installed in the 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 attribute, column, variable, bind variable, record field, table element, formal 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. So, 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. So, if an object type spec declares only attributes and/or call specs, 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 allows you to 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 invoked 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 invoked.

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 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. PL/SQL never calls a constructor implicitly, so you must call it explicitly. Constructor calls are allowed wherever function calls are allowed.

Examples

In the SQL*Plus script below, an object type for a stack is defined. The last item added to a stack is the first item removed. The operations push and pop update the stack while preserving last in, first out (LIFO) behavior. The simplest implementation of a stack uses an integer array. Integers are stored in array elements, with one end of the array representing the top of the stack.

In methods push and pop, the built-in procedure raise_application_error issues user-defined error messages. That way, you can report errors to the client program and avoid returning unhandled exceptions to the host environment. In an object type, methods can reference attributes and other methods without a qualifier, as the following example shows:

CREATE TYPE Stack AS OBJECT (
top INTEGER,
MEMBER FUNCTION full RETURN BOOLEAN,
MEMBER PROCEDURE push (n IN INTEGER),
...
);
CREATE TYPE BODY Stack AS
...
MEMBER PROCEDURE push (n IN INTEGER) IS
BEGIN
IF NOT full THEN
top := top + 1;
...
END push;
END;

Related Topics

Functions, Packages, Procedures

OPEN Statement

The OPEN statement executes the multi-row query associated with an explicit cursor. It also allocates resources used by Oracle to process the query and identifies the result set, which consists of all rows that meet the query search criteria. The cursor is positioned before the first row in the result set. For more information, see "Managing Cursors".

Syntax

Keyword and Parameter Description

cursor_name

This identifies an explicit cursor previously declared within the current scope and not currently open.

cursor_parameter_name

This identifies a cursor parameter; that is, a variable declared as the formal parameter of a cursor. (For the syntax of cursor_parameter_declaration, see "Cursors".) 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 (thereby creating an implicit cursor) only the first time the statement is executed. All the parsed SQL statements are cached. A SQL statement must be reparsed only if it is aged out of the cache by a new SQL statement.

So, 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. Rather, the FETCH statement retrieves the rows. With a FORUPDATE 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. Therefore, 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 need not have a corresponding actual parameter. They can simply 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. For more information, see "Positional versus Named Notation".

If a cursor is currently open, you cannot use its name in a cursor FOR loop.

OPEN emp_cur('LEE');
OPEN emp_cur('BLAKE', 300);
OPEN emp_cur(employee_name, 150);

Related Topics

CLOSE Statement, Cursors, FETCH Statement, LOOP Statements

OPEN-FOR Statement

The OPEN-FOR statement executes the multi-row query associated with a cursor variable. It also allocates resources used by Oracle to process the query and identifies the result set, which consists of all rows that meet the query search criteria. The cursor variable is positioned before the first row in the result set. For more information, see "Using Cursor Variables".

Syntax

Keyword and Parameter Description

cursor_variable_name

This identifies a cursor variable (or parameter) previously declared within the current scope.

host_cursor_variable_name

This identifies a cursor variable previously declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. 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.

select_statement

This is a query associated with cursor_variable, which returns a set of values. The query can reference bind variables and PL/SQL variables, parameters, and functions. The syntax of select_statement is similar to the syntax for select_into_statement defined in "SELECT INTO Statement", except that select_statement cannot have an INTO clause.

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 variable 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 emp;
OPEN :dept_cv FOR SELECT * FROM dept;
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 need not 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. No flexibility is lost, however, because you can pass whole queries (not just parameters) to a cursor variable.

You can pass a cursor variable to PL/SQL by calling a stored procedure that declares a cursor variable as one of its formal parameters. However, remote subprograms on another server cannot accept the values of cursor variables. Therefore, you cannot use a remote procedure call (RPC) to open a cursor variable.

When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the INOUT mode. That way, the subprogram can pass an open cursor back to the caller.

Examples

To centralize data retrieval, you can group type-compatible queries in a stored procedure. When called, the following packaged procedure opens the cursor variable emp_cv for the chosen query:

CREATE PACKAGE emp_data AS
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice IN INT);
END emp_data;
CREATE PACKAGE BODY emp_data AS
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp, choice IN INT) IS
BEGIN
IF choice = 1 THEN
OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL;
ELSIF choice = 2 THEN
OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500;
ELSIF choice = 3 THEN
OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20;
END IF;
END;
END emp_data;

For more flexibility, you can pass a cursor variable and a selector to a stored procedure that executes queries with different return types. Here is an example:

CREATE PACKAGE admin_data AS
TYPE GenCurTyp IS REF CURSOR;
PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT);
END admin_data;
CREATE PACKAGE BODY admin_data AS
PROCEDURE open_cv (generic_cv IN OUT GenCurTyp, choice INT) IS
BEGIN
IF choice = 1 THEN
OPEN generic_cv FOR SELECT * FROM emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM dept;
ELSIF choice = 3 THEN
OPEN generic_cv FOR SELECT * FROM salgrade;
END IF;
END;
END admin_data;

Related Topics

CLOSE Statement, Cursor Variables, FETCH Statement, LOOP Statements

OPEN-FOR-USING Statement

The OPEN-FOR-USING statement associates a cursor variable with a multi-row query, executes the query, identifies the result set, positions the cursor before the first row in the result set, then zeroes the rows-processed count kept by %ROWCOUNT. For more information, see Chapter 10, "Native Dynamic SQL".

Syntax

Keyword and Parameter Description

bind_argument

This is an expression whose value is passed to the dynamic SELECT statement.

cursor_variable_name

This identifies a weakly typed cursor variable (one without a return type) previously declared within the current scope.

dynamic_string

This is a string literal, variable, or expression that represents a multi-row SELECT statement.

host_cursor_variable_name

This identifies a cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. 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.

USING ...

This optional clause specifies a list of bind arguments. At run time, bind arguments in the USING clause replace corresponding placeholders in the dynamic SELECT statement.

Usage Notes

You use three statements to process a dynamic multi-row query: OPEN-FOR-USING, FETCH, and CLOSE. First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from the result set. When all the rows are processed, you CLOSE the cursor variable. (For more information about cursor variables, see "Using Cursor Variables".)

The dynamic string can contain any multi-row SELECT statement (without the terminator). The string can also contain placeholders for bind arguments. However, you cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement. For the right way, see "Passing the Names of Schema Objects".

Every placeholder in the dynamic string must be associated with a bind argument in the USING clause. Numeric, character, and string literals are allowed in the USING clause, but Boolean literals (TRUE, FALSE, NULL) are not. To pass nulls to the dynamic string, you must use a workaround. See "Passing Nulls".

Any bind arguments in the query are evaluated only when the cursor variable is opened. So, to fetch from the cursor using different bind values, you must reopen the cursor variable with the bind arguments set to their new values.

Dynamic SQL supports all the SQL datatypes. For example, bind arguments can be collections, LOBs, instances of an object type, and refs. As a rule, dynamic SQL does not support PL/SQL-specific types. For instance, bind arguments cannot be Booleans or index-by tables.

Example

In the following example, we declare a cursor variable, then associate it with a dynamic SELECT statement that returns rows from the emp table:

Related Topics

EXECUTE IMMEDIATE Statement

Packages

A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Packages have two parts: a specification (spec for short) and a body. For more information, see Chapter 8, "Packages".

Syntax

Keyword and Parameter Description

AUTHID

This determines whether all the packaged subprograms execute with the privileges of their definer (the default) or invoker, and whether their unqualified references to schema objects are resolved in the schema of the definer or invoker. For more information, see "Invoker Rights versus Definer Rights".

constant_declaration

cursor_body

This defines the underlying implementation of an explicit cursor. For the syntax of cursor_body, see "Cursors".

cursor_spec

This declares the interface to an explicit cursor. For the syntax of cursor_spec, see "Cursors".

exception_declaration

This declares an exception. For the syntax of exception_declaration, see "Exceptions".

function_body

This defines the underlying implementation of a function. For the syntax of function_body, see "Functions".

function_spec

This declares the interface to a function. For the syntax of function_spec, see "Functions".

object_declaration

This declares an object (instance of an object type). For the syntax of object_declaration, see "Object Types".

package_name

This identifies a package stored in the database. For naming conventions, see "Identifiers".

pragma_restrict_refs

This is pragma RESTRICT_REFERENCES, which lets you check for violations of "purity" rules. To be callable from SQL statements, a function must obey those rules, which are meant to control side effects. If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed). For the syntax of the pragma, see "RESTRICT_REFERENCES Pragma".

PRAGMA SERIALLY_REUSABLE

This pragma lets you mark a package as serially reusable. You can so mark a package if its state is needed only for the duration of one call to the server (for example, an OCI call to the server or a server-to-server RPC). For more information, see Oracle8i Application Developer's Guide - Fundamentals.

procedure_body

This defines the underlying implementation of a procedure. For the syntax of procedure_body, see "Procedures".

procedure_spec

This declares the interface to a procedure. For the syntax of procedure_spec, see "Procedures".

record_declaration

This declares a user-defined record. For the syntax of record_declaration, see "Records".

record_type_definition

This defines a record type using the datatype specifier RECORD or the attribute %ROWTYPE.

schema_name

This qualifier identifies the schema containing the package. If you omit schema_name, Oracle assumes the package is in your schema.

variable_declaration

Usage Notes

You cannot define packages in a PL/SQL block or subprogram. However, you can use any Oracle tool that supports PL/SQL to create and store packages in an Oracle database. You can issue the CREATEPACKAGE and CREATEPACKAGEBODY statements from an Oracle Precompiler or OCI host program, or interactively from SQL*Plus.

Most packages have a spec and a body. The spec 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 (definition). So, if a spec declares only types, constants, variables, exceptions, and call specs, the package body is unnecessary. However, the body can still be used to initialize items declared in the spec, as the following example shows:

You can code and compile a spec without its body. Once the spec has been compiled, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application. Furthermore, you can debug, enhance, or replace a package body without changing the interface (package spec) to the package body. So, you need not recompile calling programs.

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. So, except for white space, the headers must match word for word. Otherwise, PL/SQL raises an exception.

Related Topics

Collections, Cursors, Exceptions, Functions, Procedures, Records

Procedures

A procedure is a subprogram that can take parameters and be invoked. Generally, you use a procedure to perform an action. A procedure has two parts: the specification and the body. The specification (spec for short) begins with the keyword PROCEDURE and ends with the procedure name or a parameter list. Parameter declarations are optional. Procedures that take no parameters are written without parentheses. The procedure body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional procedure name.

The procedure body has three parts: an optional declarative part, an executable part, and an optional exception-handling part. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These items are local and cease to exist when you exit the procedure. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains handlers that deal with exceptions raised during execution. For more information, see "Understanding Procedures".

Syntax

Keyword and Parameter Description

AUTHID

This clause determines whether a stored procedure executes with the privileges of its owner (the default) or current user and whether its unqualified references to schema objects are resolved in the schema of the owner or current user. You can override the default behavior by specifying CURRENT_USER. For more information, see "Invoker Rights versus Definer Rights".

CREATE

The optional CREATE clause lets you create stand-alone procedures, which are stored in the Oracle database. You can execute the CREATE statement interactively from SQL*Plus or from a program using native dynamic SQL (see Chapter 10).

datatype

exception_handler

This associates an exception with a sequence of statements, which is executed when that exception is raised. For the syntax of exception_handler, see "Exceptions".

expression

This is 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.

function_declaration

This declares a function. For the syntax of function_declaration, see "Functions".

IN, OUT, IN OUT

These parameter modes define the behavior of formal parameters. An IN parameter lets you pass values to the subprogram being called. An OUT parameter lets you return values to the caller of the subprogram. An INOUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller.

item_declaration

This declares a program object. For the syntax of item_declaration, see "Blocks".

NOCOPY

This is a compiler hint (not directive), which allows the PL/SQL compiler to pass OUT and INOUT parameters by reference instead of by value (the default). For more information, see "Using the NOCOPY Compiler Hint".

parameter_name

This identifies a formal parameter, which is a variable declared in a procedure spec and referenced in the procedure body.

PRAGMA AUTONOMOUS_TRANSACTION

This pragma instructs the PL/SQL compiler to mark a function as autonomous (independent). An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction. For more information, see "Using Autonomous Transactions".

procedure_name

This identifies a user-defined procedure.

type_definition

This specifies a user-defined datatype. For the syntax of type_definition, see "Blocks".

:= | DEFAULT

This operator or keyword allows you to initialize IN parameters to default values.

Usage Notes

A procedure is called as a PL/SQL statement. For example, the procedure raise_salary might be called as follows:

raise_salary(emp_num, amount);

Inside a procedure, an IN parameter acts like a constant. So, you cannot assign it a value. An OUT parameter acts like a local variable. So, you can change its value and reference the value in any way. An INOUT parameter acts like an initialized variable. So, you can assign it a value, which can be assigned to another variable. For summary information about the parameter modes, see Table 7-1.

Unlike OUT and INOUT parameters, IN parameters can be initialized to default values. For more information, see "Using Parameter Defaults".

Before exiting a procedure, explicitly assign values to all OUT formal parameters. An OUT actual parameter can have a value before the subprogram is called. However, when you call 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 called only from inside the package.

At least one statement must appear in the executable part of a procedure. The NULL statement meets this requirement.

In the following example, you call the procedure using named notation:

debit_account(amount => 500, acct_id => 10261);

Related Topics

Collection Methods, Functions, Packages

RAISE Statement

The RAISE statement stops normal execution of a PL/SQL block or subprogram and transfers control to the appropriate exception handler. Normally, predefined exceptions are raised implicitly by the runtime system. However, RAISE statements can also raise predefined exceptions. User-defined exceptions must be raised explicitly by RAISE statements. For more information, see "User-Defined Exceptions".

Syntax

Keyword and Parameter Description

exception_name

This identifies a predefined or user-defined exception. For a list of the predefined exceptions, see "Predefined Exceptions".

Usage Notes

PL/SQL blocks and subprograms should RAISE an exception only when an error makes it undesirable or impossible 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. That is, the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search. In the latter case, PL/SQL returns an unhandled exception error to the host environment.

Omitting the exception name in a RAISE statement, which is allowed only in an exception handler, reraises the current exception. When a parameterless RAISE statement executes in an exception handler, the first block searched is the enclosing block, not the current block.

Example

In the following example, you raise an exception when an inventoried part is out of stock:

IF quantity_on_hand = 0 THEN
RAISE out_of_stock;
END IF;

Related Topics

Exceptions

Records

Records are items of type RECORD. Records have uniquely named fields that can store data values of different types. Thus, a record lets you treat related but dissimilar data as a logical unit. For more information, see "What Is a Record?".

Syntax

Keyword and Parameter Description

datatype

expression

This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of expression, see "Expressions". 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

This identifies a field in a user-defined record.

NOT NULL

This constraint prevents the assigning of nulls to a field. At run time, trying to assign a null to a field defined as NOTNULL raises the predefined exception VALUE_ERROR. The constraint NOTNULL must be followed by an initialization clause.

record_name

This identifies a user-defined record.

type_name

This identifies a user-defined record type that was defined using the datatype specifier RECORD.

:= | DEFAULT

This operator or keyword allows you to initialize 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. Also, a record can be initialized in its declaration, as the following example shows:

The next example shows that you can use the %TYPE attribute to specify the datatype of a field. It also shows that you can add the NOTNULL constraint to any field declaration and thereby prevent the assigning of nulls to that field. Fields declared as NOTNULL must be initialized.

To reference individual fields in a record, you use dot notation. For example, you might assign a value to the field dname in the record dept_rec as follows:

dept_rec.dname := 'PURCHASING';

Instead of assigning values separately to each field in a record, you can assign values to all fields at once. This can be done in two ways. First, 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.

Second, 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.

You can declare and reference nested records. That is, a record can be the component of another record, as the following example shows:

The next example shows that you can assign one nested record to another if they have the same datatype:

seminar.time_of := meeting.time_of;

Such assignments are allowed even if the containing records have different datatypes.

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

Example

In the following example, you define a RECORD type named DeptRecTyp, declare a record named dept_rec, then select a row of values into the record:

Related Topics

Collections, Functions, Procedures, Packages

RESTRICT_REFERENCES Pragma

To be callable from SQL statements, a stored function must obey certain "purity" rules, which are meant to control side effects. (See "Controlling Sides Effects".) If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed). To check for violations of the rules, you can use the pragma (compiler directive) RESTRICT_REFERENCES. The pragma asserts that a function does not read and/or write database tables and/or package variables. For more information, see Oracle8i Application Developer's Guide - Fundamentals.

Syntax

Keyword and Parameter Description

DEFAULT

This specifies that the pragma applies to all functions in the package spec or object type spec. You can still declare the pragma for individual functions. Such pragmas override the default pragma.

function_name

This identifies a user-defined function.

PRAGMA

This keyword signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.

RNDS

This asserts that the function reads no database state (does not query database tables).

RNPS

This asserts that the function reads no package state (does not reference the values of packaged variables)

TRUST

This asserts that the function can be trusted not to violate one or more rules.

WNDS

This asserts that the function writes no database state (does not modify database tables).

WNPS

This asserts that the function 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 spec or object type spec. You can specify up to four constraints (RNDS, RNPS, WNDS, WNPS) in any order. To call the function from parallel queries, you must specify all four constraints. No constraint implies another. For example, WNPS does not imply RNPS.

When you specify TRUST, the function body is not checked for violations of the constraints listed in the pragma. The function is trusted not to violate them.

If you specify DEFAULT instead of a function name, the pragma applies to all functions in the package spec or object type spec (including, in the latter case, the system-defined constructor). You can still declare the pragma for individual functions. Such pragmas override the default pragma.

A RESTRICT_REFERENCES pragma can apply to only one function declaration. So, a pragma that references the name of overloaded functions always applies to the nearest foregoing function declaration.

Examples

The following pragma asserts that packaged function balance writes no database state (WNDS) and reads no package state (RNPS):

A pragma that references the name of overloaded functions always applies to the nearest foregoing function declaration. So, in the following example, the pragma applies to the second declaration of credit_ok:

Related Topics

RETURN Statement

The RETURN statement immediately completes the execution of a subprogram and returns control to the caller. Execution then resumes with the statement following the subprogram call. In a function, the RETURN statement also sets the function identifier to the return value. For more information, see "Using the RETURN Statement".

Syntax

Keyword and Parameter Description

expression

This is an arbitrarily complex 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

Do not confuse the RETURN statement with the RETURN clause in a function spec, which specifies the datatype of the return value.

A subprogram can contain several RETURN statements, none of which need be the last lexical statement. Executing any of them completes the subprogram immediately. However, to have multiple exit points in a subprogram is a poor programming practice.

In procedures, a RETURN statement cannot contain an expression. The statement simply returns control to the caller before the normal end of the procedure is reached.

However, 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. Therefore, 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.

The RETURN statement can also be used in an anonymous block to exit the block (and all enclosing blocks) immediately, but the RETURN statement cannot contain an expression.

Example

In the following example, the function balance returns the balance of a specified bank account:

Related Topics

ROLLBACK Statement

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 "Processing Transactions".

Syntax

Keyword and Parameter Description

ROLLBACK

When a parameterless ROLLBACK statement is executed, all database changes made during the current transaction are undone.

ROLLBACK TO

This statement undoes all database changes (and releases all locks acquired) since the savepoint identified by savepoint_name was marked.

SAVEPOINT

This keyword is optional and has no effect except to improve readability.

savepoint_name

This is an undeclared identifier, which marks the current point in the processing of a transaction. For naming conventions, see "Identifiers".

WORK

This keyword is optional and has no effect except to improve readability.

Usage Notes

All savepoints marked after the savepoint to which you roll back are erased. However, 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 the implicit savepoint is done. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back.

In SQL, the FORCE clause manually rolls back an in-doubt distributed transaction. However, PL/SQL does not support this clause. For example, the following statement is illegal:

ROLLBACK WORK FORCE '24.37.85'; -- illegal

In embedded SQL, the RELEASE option frees all Oracle resources (locks and cursors) held by a program and disconnects from the database. However, PL/SQL does not support this option. For example, the following statement is illegal:

ROLLBACK WORK RELEASE; -- illegal

Related Topics

COMMIT Statement, SAVEPOINT Statement

%ROWTYPE Attribute

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. Fields in a record and corresponding columns in a row have the same names and datatypes.

You can use the %ROWTYPE attribute in variable declarations as a datatype specifier. Variables declared using %ROWTYPE are treated like those declared using a datatype name. For more information, see "Using %ROWTYPE".

Syntax

Keyword and Parameter Description

cursor_name

This identifies an explicit cursor previously declared within the current scope.

cursor_variable_name

This identifies a PL/SQL strongly (not weakly) typed cursor variable previously declared within the current scope.

table_name

This identifies a database table (or view) that must be accessible when the declaration is elaborated.

Usage Notes

The %ROWTYPE attribute lets you declare records structured like a row of data in a database table. To reference a field in the record, you use dot notation. For example, you might reference the deptno field as follows:

IF emp_rec.deptno = 20 THEN ...

You can assign the value of an expression to a specific field, as follows:

emp_rec.sal := average * 1.15;

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. Second, 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.

Examples

In the example below, you use %ROWTYPE to declare two records. The first record stores a row selected from the emp table. The second record stores a row fetched from the c1 cursor.

Related Topics

Constants and Variables, Cursors, Cursor Variables, FETCH Statement

SAVEPOINT Statement

The SAVEPOINT statement names and marks the current point in the processing of a transaction. With the ROLLBACKTO statement, savepoints let you undo parts of a transaction instead of the whole transaction. For more information, see "Processing Transactions".

Syntax

Keyword and Parameter Description

savepoint_name

This is an undeclared identifier, which marks the current point in the processing of a transaction.

Usage Notes

When you roll back to a savepoint, any savepoints marked after that savepoint are erased. However, the savepoint to which you roll back is not erased. A simple rollback or commit erases all savepoints. Savepoint names can be reused within a transaction. This moves the savepoint 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. However, 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. However, if the statement raises an unhandled exception, the host environment determines what is rolled back.

Related Topics

COMMIT Statement, ROLLBACK Statement

SELECT INTO Statement

The SELECTINTO statement retrieves data from one or more database tables, then assigns the selected values to variables or fields. For a full description of the SELECT statement, see Oracle8i SQL Reference.

Syntax

Keyword and Parameter Description

alias

This is another (usually short) name for the referenced column, table, or view.

BULK COLLECT

This clause 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. For more information, see "Taking Advantage of Bulk Binds".

collection_name

This identifies a declared collection into which select_item values are bulk fetched. For each select_item, there must be a corresponding, type-compatible collection in the list.

function_name

This identifies a user-defined function.

host_array_name

This identifies an array (declared in a PL/SQL host environment and passed to PL/SQL as a bind variable) into which select_item values are bulk 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

This is a literal that represents a number or a value implicitly convertible to a number.

parameter_name

This identifies a formal parameter of a user-defined function.

record_name

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

This is anything that can legally follow the FROM clause in a SELECT statement except the SAMPLE clause.

schema_name

This qualifier identifies the schema containing the table or view. If you omit schema_name, Oracle assumes the table or view is in your schema.

subquery

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

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

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

The BULKCOLLECT clause tells the SQL engine to bulk-bind output collections before returning them. It bulk-binds all collections referenced in the INTO list. The corresponding columns can store scalar or composite values including objects.

When you use a SELECTINTO statement without the BULKCOLLECT clause, it should return only one row. If it returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS.

However, if no rows are returned, PL/SQL raises NO_DATA_FOUND unless the SELECT statement called a SQL aggregate function such as AVG or SUM. (SQL aggregate functions always return a value or a null. So, a SELECTINTO statement that calls an aggregate function never raises NO_DATA_FOUND.)

The implicit cursor SQL and the cursor attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN let you access useful information about the execution of a SELECTINTO statement.

Examples

The following SELECT statement returns an employee's name, job title, and salary from the emp database table:

Related Topics

Assignment Statement, FETCH Statement, %ROWTYPE Attribute

SERIALLY_REUSABLE Pragma

The pragma SERIALLY_REUSABLE lets you mark a package as serially reusable. You can so mark a package if its state is needed only for the duration of one call to the server (for example, an OCI call to the server or a server-to-server RPC). For more information, see Oracle8i Application Developer's Guide - Fundamentals.

Syntax

Keyword and Parameter Description

PRAGMA

This keyword signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they simply convey information to the compiler.

Usage Notes

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. If you try, Oracle generates an error.

Related Topics

SET TRANSACTION Statement

The SETTRANSACTION 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 "Using SET TRANSACTION".

Syntax

Keyword and Parameter Description

READ ONLY

This clause establishes the current transaction as read-only. If a transaction is set to READONLY, subsequent queries see only changes committed before the transaction began. The use of READONLY does not affect other users or transactions.

READ WRITE

This clause establishes the current transaction as read-write. The use of READWRITE does not affect other users or transactions. If the transaction executes a data manipulation statement, Oracle assigns the transaction to a rollback segment.

ISOLATION LEVEL

This clause specifies how transactions that modify the database are handled. When you specify 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.

To enable SERIALIZABLE mode, your DBA must set the Oracle initialization parameter COMPATIBLE to 7.3.0 or higher.

When you specify READCOMMITTED, 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.

USE ROLLBACK SEGMENT

This clause assigns the current transaction to the specified rollback segment and establishes the transaction as read-write. You cannot use this parameter with the READONLY parameter in the same transaction because read-only transactions do not generate rollback information.

Usage Notes

The SETTRANSACTION statement must be the first SQL statement in your transaction and can appear only once in the transaction.

Related Topics

COMMIT Statement, ROLLBACK Statement, SAVEPOINT Statement

SQL Cursor

Oracle implicitly opens a cursor to process each SQL statement not associated with an explicit cursor. PL/SQL lets you refer to the most recent implicit cursor as the SQL cursor, which has four attributes: %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. They give you useful information about the execution of data manipulation statements. The SQL cursor has an additional attribute, %BULK_ROWCOUNT, designed for use with the FORALL statement.

Syntax

Keyword and Parameter Description

%BULK_ROWCOUNT

This is a composite attribute designed for use with the FORALL statement. This attribute has the semantics of 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.

%FOUND

This attribute yields TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECTINTO statement returned one or more rows. Otherwise, it yields FALSE.

index

This is an expression that must yield (or convert implicitly to) an integer.

%ISOPEN

%NOTFOUND

This attribute is the logical opposite of %FOUND. It yields TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECTINTO statement returned no rows. Otherwise, it yields FALSE.

%ROWCOUNT

This attribute yields the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECTINTO statement.

SQL

This is 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 yield 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. So, if you want to save an attribute value for later use, assign it to a Boolean variable immediately.

If a SELECTINTO 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. However, a SELECTINTO statement that calls a SQL aggregate function never raises NO_DATA_FOUND because those functions always return a value or a null. In such cases, SQL%NOTFOUND yields FALSE.

%BULK_ROWCOUNT is not maintained for bulk inserts because that would be redundant. For example, the FORALL statement below inserts one row per execution. So, after each execution, %BULK_ROWCOUNT would return 1:

FORALL i IN 1..15
INSERT INTO emp (sal) VALUES (sals(i));

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.

%FOUND and %NOTFOUND refer only to the last execution of the SQL statement. However, 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.

Examples

In the following example, %NOTFOUND is used to insert a row if an update affects no rows:

Related Topics

SQLCODE Function

The function SQLCODE returns the number code associated with the most recently raised exception. SQLCODE is meaningful only in an exception handler. Outside a handler, SQLCODE always returns 0.

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 unless you used the pragma EXCEPTION_INIT to associate the exception with an Oracle error number, in which case SQLCODE returns that error number. For more information, see "Using SQLCODE and SQLERRM".

Syntax

Usage Notes

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. First, you must assign the value of SQLCODE to a local variable, as follows:

Related Topics

Exceptions, SQLERRM Function

SQLERRM Function

The function SQLERRM returns the error message associated with its error-number argument or, if the argument is omitted, with the current value of SQLCODE. SQLERRM with no argument is meaningful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the message normal, successful completion.

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 "Using SQLCODE and SQLERRM".

Syntax

Keyword and Parameter Description

error_number

Usage Notes

SQLERRM is especially useful in the OTHERS exception handler because it lets you identify which internal exception was raised.

You can pass an error number to SQLERRM, in which case SQLERRM returns the message associated with that error number. The error number passed to SQLERRM should 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 message

User-Defined Exception

unless you pass +100, in which case SQLERRM returns the following message:

ORA-01403: no data found

You cannot use SQLERRM directly in a SQL statement. First, you must assign the value of SQLERRM to a local variable, as follows:

Related Topics

Exceptions, SQLCODE Function

%TYPE Attribute

The %TYPE attribute provides the datatype of a field, record, nested table, database column, or variable. You can use the %TYPE attribute as a datatype specifier when declaring constants, variables, fields, and parameters. For more information, see "Using %TYPE".

Syntax

Keyword and Parameter Description

collection_name

This identifies a nested table, index-by table, or varray previously declared within the current scope.

cursor_variable_name

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

This refers to a table and column that must be accessible when the declaration is elaborated.

object_name

This identifies an object (instance of an object type) previously declared within the current scope.

record_name

This identifies a user-defined or %ROWTYPE record previously declared within the current scope.

record_name.field_name

This identifies a field in a user-defined or %ROWTYPE record previously declared within the current scope.

variable_name

This identifies 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. However, the NOTNULL column constraint is not inherited by items declared using %TYPE.

Related Topics

Constants and Variables, %ROWTYPE Attribute

UPDATE Statement

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 UPDATE statement, see Oracle8i SQL Reference.

Syntax

Keyword and Parameter Description

alias

This is another (usually short) name for the referenced table or view and is typically used in the WHERE clause.

column_name

This is the name of 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.

returning_clause

This clause lets you return values from updated rows, thereby eliminating the need to SELECT the rows afterward. You can retrieve the column values into variables and/or host variables, or into collections and/or host arrays. However, you cannot use the RETURNING clause for remote or parallel updates. For the syntax of returning_clause, see "DELETE Statement".

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.

In the following example, you increase every employee's salary by 10%. The original value of the sal column is multiplied by 1.10, then the result is assigned to the sal column overwriting the original value.

UPDATE emp SET sal = sal * 1.10;

SET column_name = (subquery3)

This clause 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)

This clause 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.

In the following correlated query, the column item_id is assigned the value stored in item_num, and the column price is assigned the value stored in item_price:

sql_expression

subquery

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

This identifies 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.

WHERE CURRENT OF cursor_name

This clause refers to the latest row processed by the FETCH statement associated with the cursor identified by cursor_name. The cursor must be FORUPDATE and must be open and positioned on a row.

If the cursor is not open, the CURRENTOF clause causes an error. If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND.

WHERE search_condition

This clause chooses which rows to update in the database table. Only rows that meet the search condition are updated. If you omit the search condition, all rows in the table are updated.

Usage Notes

You can use the UPDATEWHERECURRENTOF statement after a fetch from an open cursor (this includes implicit fetches executed in a cursor FOR loop), provided the associated query is FORUPDATE. This statement updates the current row, that is, the one 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.

Examples

In the following example, a 10% raise is given to analysts in department 20: