Release 8.0
A54654_01
 Library |
 Product |
 Contents |
 Index |
| PL/SQL User's Guide and Reference Release 8.0 A54654_01 |
|
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.
The following sections are arranged alphabetically for easy reference:
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.
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" on page 2-38.
This identifies a nested table, index-by table, or varray previously declared within the current scope.
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.
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.
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.
This identifies an object (instance of an object type) previously declared within the current scope.
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.
This identifies a formal OUT or IN OUT parameter of the subprogram in which the assignment statement appears.
This is a numeric expression that must yield a value of type BINARY_INTEGER or a value implicitly convertible to that datatype.
This identifies a field in a user-defined or %ROWTYPE record previously declared within the current scope.
This identifies a PL/SQL variable previously declared within the current scope.
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" on page 11-59. 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.
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 NOT NULL. If you try, PL/SQL raises the predefined exception VALUE_ERROR.
Only the values TRUE and FALSE and the non-value 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:
DECLARE
out_of_range BOOLEAN;
...
BEGIN
...
out_of_range := (salary < minimum) OR (salary > maximum);
As the next example shows, you can assign the value of an expression to a specific field in a record:
DECLARE
emp_rec emp%ROWTYPE;
BEGIN
...
emp_rec.sal := current_salary + increase;
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:
DECLARE
emp_rec1 emp%ROWTYPE;
emp_rec2 emp%ROWTYPE;
dept_rec dept%ROWTYPE;
BEGIN
...
emp_rec1 := emp_rec2;
The next assignment is illegal because you cannot use the assignment operator to assign a list of values to a record:
dept_rec := (60, 'PUBLICITY', 'LOS ANGELES');
Using the following syntax, you can assign the value of an expression to a specific element in a collection:
collection_name(index) := expression;
In the following example, you assign the uppercase value of last_name to the third row in nested table ename_tab:
ename_tab(3) := UPPER(last_name);
Several examples of assignment statements follow:
wages := hours_worked * hourly_salary;
country := 'France';
costs := labor + supplies;
done := (count > 100);
dept_rec.loc := 'BOSTON';
comm_tab(5) := sales * 0.15;
Constants and Variables, Expressions, SELECT INTO Statement
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 PL/SQL 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" on page 1-2 and "Scope and Visibility" on page 2-36.
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 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. To reference the global identifier, you must use a block label to qualify the reference, as the following example shows:
<<outer>>
DECLARE
x INTEGER;
BEGIN
...
DECLARE
x INTEGER;
BEGIN
...
IF x = outer.x THEN -- refers to global x
...
END IF;
END;
END outer;
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.
This identifies a nested table, index-by table, or varray previously declared within the current scope. For the syntax of collection_declaration, see "Collections" on page 11-21
This construct declares a constant. For the syntax of constant_declaration, see "Constants and Variables" on page 11-29.
This construct declares an explicit cursor. For the syntax of cursor_declaration, see "Cursors" on page 11-45.
This construct declares a cursor variable. For the syntax of cursor_variable_declaration, see "Cursor Variables" on page 11-38.
This construct declares an exception. For the syntax of exception_declaration, see "Exceptions" on page 11-54.
This identifies an object (instance of an object type) previously declared within the current scope. For the syntax of object_declaration, see "Object Types" on page 11-102.
This construct declares a user-defined record. For the syntax of record_declaration, see "Records" on page 11-126.
This construct declares a variable. For the syntax of variable_declaration, see "Constants and Variables" on page 11-29.
This construct declares a function. For the syntax of function_declaration, see "Functions" on page 11-78.
This construct declares a procedure. For the syntax of procedure_declaration, see "Procedures" on page 11-119.
This keyword signals the start of the executable part of a PL/SQL block, which contains executable statements. The executable part of a PL/SQL block is required. That is, a block must contain at least one executable statement. The NULL statement meets this requirement.
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 (;) terminates every PL/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.
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.
This construct associates an exception with a sequence of statements, which is executed when that exception is raised. For the syntax of exception_handler, see "Exceptions" on page 11-54.
This keyword signals the end of a PL/SQL block. It must be the last keyword in a block. Neither the END IF in an IF statement nor the END LOOP in a LOOP statement can substitute for the keyword END.
END does not signal the end of a transaction. Just as a block can span multiple transactions, a transaction can span multiple blocks.
The following PL/SQL block declares several variables and constants, then calculates a ratio using values selected from a database table:
-- available online in file EXAMP11
DECLARE
numerator NUMBER;
denominator NUMBER;
the_ratio NUMBER;
lower_limit CONSTANT NUMBER := 0.72;
samp_num CONSTANT NUMBER := 132;
BEGIN
SELECT x, y INTO numerator, denominator FROM result_table
WHERE sample_id = samp_num;
the_ratio := numerator/denominator;
IF the_ratio > lower_limit THEN
INSERT INTO ratio VALUES (samp_num, the_ratio);
ELSE
INSERT INTO ratio VALUES (samp_num, -1);
END IF;
COMMIT;
EXCEPTION
WHEN ZERO_DIVIDE THEN
INSERT INTO ratio VALUES (samp_num, 0);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;
END;
Constants and Variables, Exceptions, Functions, Procedures
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" on page 5-9.
This identifies an explicit cursor previously declared within the current scope and currently open.
This identifies a PL/SQL cursor variable (or parameter) previously declared within the current scope and currently open.
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.
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.
In the following example, after the last row is fetched and processed, you close the cursor variable emp_cv:
LOOP
FETCH emp_cv INTO emp_rec;
EXIT WHEN emp_cv%NOTFOUND;
... -- process data record
END LOOP;
/* Close cursor variable. */
CLOSE emp_cv;
FETCH Statement, OPEN Statement, OPEN-FOR Statement
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.
For more information, see "Using Collection Methods" on page 4-18.
This identifies a nested table, index-by table, or varray previously declared within the current scope.
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.
This procedure has three forms. DELETE removes all elements from a collection. DELETE(n) removes the nth element from a nested table. If n is
null, DELETE(n) does nothing. DELETE(m, n) removes all elements in the range m .. n from a nested table. If m is larger than n or if m or n is null, DELETE(m, n) does nothing.
This is an expression that must yield (or convert implicitly to) an integer. For more information, see "Datatype Conversion" on page 2-25.
EXISTS(n) returns TRUE if the nth element in a collection exists. Otherwise, EXISTS(n) returns FALSE. Mainly, you use EXISTS with DELETE to maintain sparse nested tables. You can also use EXISTS to avoid raising an exception when you reference a nonexistent element.
When passed an out-of-range subscript, EXISTS returns FALSE instead of raising SUBSCRIPT_OUTSIDE_LIMIT.
This procedure has three forms. EXTEND appends one null element to a collection. EXTEND(n) appends n null elements to a collection. EXTEND(n, i) appends n copies of the ith element to a collection.
EXTEND operates on the internal size of a collection. So, if EXTEND encounters deleted elements, it includes them in its tally.
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.
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 specify in its type definition).
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.
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.
TRIM operates on the internal size of a collection. So, if TRIM encounters deleted elements, it includes them in its tally.
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 NOT NULL constraint on a TABLE or VARRAY type, you cannot apply the first two forms of EXTEND to collections of that type.
If an element to be deleted does not exist, DELETE simply skips it; no exception is raised. Varrays are dense, so you cannot delete their individual elements.
PL/SQL keeps placeholders for deleted elements. So, you can replace a deleted element simply by assigning it a new value. Howwever, 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.
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 next example, you use LIMIT to determine if you can add 20 more elements to varray projects:
IF (projects.COUNT + 20) < projects.LIMIT THEN
-- add 20 more elements
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 two kinds of collections: nested tables and varrays (short for variable-size arrays).
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.)
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.
For more information, see "Defining and Declaring Collections" on page 4-5.
This identifies a user-defined type specifier, which is used in subsequent declarations of collections.
This is any PL/SQL datatype except BOOLEAN, NCHAR, NCLOB, NVARCHAR2, REF CURSOR, TABLE, and VARRAY, or is any object type except those with TABLE or VARRAY attributes. If element_type is a record type, every field in the record must be a scalar type or an object type.
This optional clause lets you define Version 2 PL/SQL tables, which are called index-by tables in Version 8.
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.
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, collection subscripts have a fixed lower bound of 1.
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.
You can define collection types in the declarative part of any PL/SQL block, subprogram, or package. In SQL, collection types can be CREATEd and stored in the database.
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 collection is atomically null (that is, the collection itself is null, not its elements). To initialize a collection, 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 collections can be atomically null, they can be tested for nullity. However, collections cannot be compared for equality or inequality. This restriction also applies to implicit comparisons. For example, collections cannot appear in a DISTINCT, GROUP BY, or ORDER BY list.
Collections can store instances of an object type and, conversely, can be attributes of an object type. Also, nested tables and varrays can be passed as parameters. So, you can use them to move collections 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 collections declared as the formal parameters of a subprogram. That allows you to pass host arrays to stored functions and procedures.
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;
When defining a VARRAY type, you must specify its maximum size. In the following example, you define a type that stores up to 366 dates:
DECLARE
TYPE Calendar IS VARRAY(366) OF DATE;
Once you define a collection type, you can declare collections of that type, as the following SQL*Plus script shows:
CREATE TYPE Project AS OBJECT(
project_no NUMBER(2),
title VARCHAR2(35),
cost NUMBER(7,2))
/
CREATE TYPE ProjectList AS VARRAY(50) OF Project -- define VARRAY type
/
CREATE TABLE department (
idnum NUMBER(2),
name VARCHAR2(15),
budget NUMBER(11,2),
projects ProjectList) -- declare varray
/
The identifier projects represents an entire varray. Each element of projects will store a Project object.
In the following example, you declare a nested table as the formal parameter of a packaged procedure:
CREATE PACKAGE personnel AS
TYPE Staff IS TABLE OF Employee;
...
PROCEDURE award_bonuses (members IN Staff);
You can specify a collection type in the RETURN clause of a function specification, as the following example shows:
DECLARE
TYPE SalesForce IS VARRAY(20) OF Salesperson;
FUNCTION top_performers (n INTEGER) RETURN SalesForce IS ...
In the following example, you update the list of projects assigned to the Security Department:
DECLARE
new_projects ProjectList :=
ProjectList(Project(1, 'Issue New Employee Badges', 13500),
Project(2, 'Inspect Emergency Exits', 1900),
Project(3, 'Upgrade Alarm System', 3350),
Project(4, 'Analyze Local Crime Statistics', 825));
BEGIN
UPDATE department
SET projects = new_projects WHERE name = 'Security';
In the next example, you retrieve all the projects for the Accounting Department into a local varray:
DECLARE
my_projects ProjectList;
BEGIN
SELECT projects INTO my_projects FROM department
WHERE name = 'Accounting';
Collection Methods, Object Types, Records
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" on page 2-8.
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.
The following examples show various comment styles:
-- compute the area of a circle
area := pi * radius**2; -- pi equals 3.14159
/* Compute the area of a circle. */
area := pi * radius**2; /* pi equals 3.14159 */
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" on page 5-42.
This keyword is optional and has no effect except to improve readability.
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.
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:
If you commit while a cursor that was declared using FOR UPDATE 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" on page 5-48.
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 Oracle8 Server Concepts.
PL/SQL does not support the FORCE clause, which, in SQL, manually commits an in-doubt distributed transaction. For example, the following COMMIT statement is illegal:
COMMIT FORCE '23.51.54'; -- illegal
With embedded SQL, the optional RELEASE parameter is allowed after COMMIT WORK. The keyword RELEASE acts like a "disconnect" statement, which logs you off the database once your transaction is committed. PL/SQL does not support data control statements such as CONNECT, GRANT, or REVOKE. Therefore, it does not support the RELEASE parameter.
ROLLBACK Statement, SAVEPOINT Statement
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 NOT NULL constraint. For more information, see "Declarations" on page 2-28.
This identifies a program constant. For naming conventions, see "Identifiers" on page 2-4.
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.
This identifies a field in a user-defined or %ROWTYPE record previously declared within the current scope.
This identifies a predefined scalar datatype such as BOOLEAN, NUMBER, or VARCHAR2. For more information, see "Datatypes" on page 2-10.
This identifies a database table and column that must be accessible when the declaration is elaborated.
This identifies a program variable.
This identifies a nested table, index-by table, or varray previously declared within the current scope.
This identifies an explicit cursor previously declared within the current scope.
This identifies a PL/SQL cursor variable previously declared within the current scope.
This identifies an object (instance of an object type) previously declared within the current scope.
This identifies a user-defined record previously declared within the current scope.
This identifies a database table (or view) that must be accessible when the declaration is elaborated.
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.
This attribute provides the datatype of a previously declared collection, cursor variable, field, object, record, database column, or variable.
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 NOT NULL raises the predefined exception VALUE_ERROR. The constraint NOT NULL must be followed by an initialization clause.
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.
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 specification are initialized only once per session.
An initialization clause is required when declaring NOT NULL variables and when declaring constants.
You cannot use the attribute %ROWTYPE to declare a constant. If you use %ROWTYPE to declare a variable, initialization is not allowed.
Several examples of variable and constant declarations follow:
credit_limit CONSTANT NUMBER := 5000;
invalid BOOLEAN := FALSE;
acct_id INTEGER(4) NOT NULL DEFAULT 9999;
pi CONSTANT REAL := 3.14159;
last_name VARCHAR2(20);
my_ename emp.ename%TYPE;
Assignment Statement, Expressions, %ROWTYPE Attribute, %TYPE Attribute
Cursors and cursor variables have four attributes that give you useful information about the execution of a data manipulation statement. For more information, see "Using Cursor Attributes" on page 5-36.
There are two kinds of cursors: implicit and explicit. PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including single-row queries. For multi-row queries, you can explicitly declare a cursor or cursor variable to process the rows.
This identifies an explicit cursor previously declared within the current scope.
This identifies a PL/SQL cursor variable (or parameter) previously declared within the current scope.
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.
This is the name of the implicit SQL cursor. For more information, see "SQL Cursor" on page 11-142.
This is a cursor attribute, which can be appended to the name of a cursor or cursor variable. After a cursor is opened but before the first fetch, 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.
Until a SQL statement is executed, SQL%FOUND yields NULL. Thereafter, it yields TRUE if the statement affected any rows, or FALSE if it affected no rows.
This is a cursor attribute, which 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.
Oracle automatically closes the implicit SQL cursor after executing its associated SQL statement, so SQL%ISOPEN always yields FALSE.
This is a cursor attribute, which can be appended to the name of a cursor or cursor variable. After a cursor is opened but before the first fetch, 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.
Until a SQL statement is executed, SQL%NOTFOUND yields NULL. Thereafter, it yields FALSE if the statement affected any rows, or TRUE if it affected no rows.
This is a cursor attribute, which 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.
Until a SQL statement is executed, SQL%ROWCOUNT yields NULL. Thereafter, it yields the number of rows affected by the statement. SQL%ROWCOUNT yields 0 if the statement affected no rows.
You can use the cursor attributes in procedural statements but not in SQL statements.
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 SELECT INTO statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS and sets %ROWCOUNT to 1, not the actual number of rows that satisfy the query.
Before the first fetch, %NOTFOUND evaluates to NULL. So, if FETCH never executes successfully, the loop is never exited. That is because the EXIT WHEN 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 ci%NOTFOUND IS NULL;
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 EXIT WHEN 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:
-- available online in file EXAMP14
DECLARE
CURSOR c1 is
SELECT ename, empno, sal FROM emp
ORDER BY sal DESC; -- start with highest-paid employee
my_ename CHAR(10);
my_empno NUMBER(4);
my_sal NUMBER(7,2);
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_empno, my_sal;
EXIT WHEN (c1%ROWCOUNT > 5) OR (c1%NOTFOUND);
INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
COMMIT;
END LOOP;
CLOSE c1;
END;
In the final example, you use %ROWCOUNT to raise an exception if an unexpectedly high number of rows is deleted:
DELETE FROM accts WHERE status = 'BAD DEBT';
IF SQL%ROWCOUNT > 10 THEN
RAISE out_of_bounds;
END IF;
Cursors, 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. 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. For more information, see "Using Cursor Variables" on page 5-18.
To create cursor variables, you define a REF CURSOR type, then declare cursor variables of that type.
This is a user-defined type specifier, which is used in subsequent declarations of PL/SQL cursor variables.
In PL/SQL, pointers have datatype REF X, where REF is short for REFERENCE and X stands for a class of objects. Therefore, cursor variables have datatype REF CURSOR.
This keyword introduces the RETURN clause, which specifies the datatype of a cursor variable result 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.
This identifies an explicit cursor previously declared within the current scope.
This identifies a PL/SQL cursor variable previously declared within the current scope.
This identifies a user-defined record previously declared within the current scope.
This identifies a RECORD type previously defined within the current scope.
This identifies a database table (or view) that must be accessible when the declaration is elaborated.
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.
This attribute provides the datatype of a previously declared user-defined record.
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 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.
REF CURSOR types can be strong (restrictive) or weak (nonrestrictive). A strong REF CURSOR type definition specifies a return type, but a weak definition does not. Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with type-compatible queries. However, weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query.
Once you define a REF CURSOR type, you can declare cursor variables of that type. Yu can use %TYPE to provide the datatype of a record variable. Also, in the RETURN clause of a REF CURSOR type definition, you can use %ROWTYPE to specify a record type that represents a row returned by a strongly (not weakly) typed cursor variable.
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 one at a time. When all the rows are processed, you CLOSE the cursor variable.
Other OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.
PL/SQL makes sure the return type of the cursor variable is compatible with the INTO clause of the FETCH statement. For each column value returned by the query associated with the cursor variable, there must be a corresponding, type-compatible field or variable in the INTO clause. Also, the number of fields or variables must equal the number of column values. Otherwise, you get an error.
If both cursor variables involved in an assignment are strongly typed, they must have the same datatype. However, if one or both cursor variables are weakly typed, they need not have the same datatype.
When declaring a cursor variable as the formal parameter of a subprogram that fetches from or closes the cursor variable, you must specify the IN (or IN OUT) mode. If the subprogram opens the cursor variable, you must specify the IN OUT mode.
Be careful when passing cursor variables as parameters. At run time, PL/SQL raises ROWTYPE_MISMATCH if the return types of the actual and formal parameters are incompatible.
You can apply the cursor attributes %FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT to a cursor variable. For more information, see "Using Cursor Attributes" on page 5-36.
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:
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.
Currently, cursor variables are subject to the following restrictions, some of which future releases of PL/SQL will remove:
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 selector to a PL/SQL block, which opens the cursor variable for the chosen query:
EXEC SQL BEGIN DECLARE SECTION;
...
/* Declare host cursor variable. */
SQL_CURSOR generic_cv;
int choice;
EXEC SQL END DECLARE SECTION;
...
/* Initialize host cursor variable. */
EXEC SQL ALLOCATE :generic_cv;
... /* Pass host cursor variable and selector to PL/SQL block. */
EXEC SQL EXECUTE
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-EXEC;
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 standalone procedure to open the cursor variable. Simply define the REF CURSOR type in a separate package, then reference that type in the standalone procedure. For instance, if you create the following (bodiless) package, you can create standalone procedures that reference the types it defines:
CREATE PACKAGE cv_types AS
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
TYPE BonusCurTyp IS REF CURSOR RETURN bonus%ROWTYPE;
...
END cv_types;
CLOSE Statement, Cursor Attributes, Cursors, FETCH Statement, OPEN-FOR Statement
To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. A cursor lets you name the work area, access the information, and process the rows individually. For more information, see "Managing Cursors" on page 5-9.
This is a query that returns a result set of rows. If the cursor declaration declares parameters, each parameter must be used in the query. The syntax of select_statement is like that of select_into_statement, which is defined in "SELECT INTO Statement" on page 11-137, except that select_statement cannot have an INTO clause.
This keyword introduces the RETURN clause, which specifies the datatype of a cursor result 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 specification. Also, the number, order, and datatypes of select items in the SELECT clause must match the RETURN clause.
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.
This identifies a database table (or view) that must be accessible when the declaration is elaborated.
This identifies an explicit cursor previously declared within the current scope.
This identifies a user-defined record previously declared within the current scope.
This identifies a RECORD type previously defined within the current scope.
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.
This attribute provides the datatype of a previously declared collection, cursor variable, field, object, record, database column, or variable.
This is a type specifier. For the syntax of datatype, see "Constants and Variables" on page 11-29.
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.
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" on page 2-36.
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:
CURSOR c1 (emp_id NUMBER NOT NULL, dept_no NUMBER(2)) -- illegal
Some examples of cursor declarations follow:
CURSOR c1 IS SELECT empno, ename, job, sal FROM emp
WHERE sal > 2000;
CURSOR c2 RETURN dept%ROWTYPE IS
SELECT * FROM dept WHERE deptno = 10;
CURSOR c3 (start_date DATE) IS
SELECT empno, sal FROM emp WHERE hiredate > start_date;
CLOSE Statement, FETCH Statement, OPEN Statement, SELECT INTO Statement
The DELETE statement removes entire rows of data from a specified table or view. For a full description of the DELETE statement, see Oracle8 Server SQL 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.
The operand of THE is a subquery that returns a single column value to the DELETE statment. The column value must be a nested table. Operator THE informs Oracle that the value is a nested table, not a scalar value.
This is a select statement that provides a value or set of values to the DELETE statement. The syntax of subquery is like the syntax of select_into_statement defined in "SELECT INTO Statement" on page 11-137, except that subquery cannot have an INTO clause.
This is another (usually short) name for the referenced table or view and is typically used in the WHERE clause.
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.
This clause refers to the latest row processed by the FETCH statement associated with the cursor identified by cursor_name. The cursor must be FOR UPDATE and must be open and positioned on a row. If the cursor is not open, the CURRENT OF clause causes an error.
If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND.
This clause lets you return values from deleted rows, thereby eliminating the need to SELECT the rows beforehand. If you are deleting a single row, you can retrieve the column values into variables and/or host variables.
If you are deleting multiple rows you can retrieve the values into host arrays. The datatypes of the column values and array elements must be compatible, and the number of rows returned cannot exceed the size of the array.
You can use the DELETE WHERE CURRENT OF statement after a fetch from an open cursor (this includes implicit fetches executed in a cursor FOR loop), provided the associated query is FOR UPDATE. This statement deletes the current row; that is, the one just fetched.
The implicit SQL cursor and the cursor attributes %NOTFOUND, %FOUND, and %ROWCOUNT let you access useful information about the execution of a DELETE statement.
A DELETE statement might delete one or more rows or no rows. If one or more rows are deleted, you get the following results:
If no rows are deleted, you get these results:
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 column sal from deleted rows and stores the column values in the elements of a host array:
DELETE FROM emp WHERE job = `CLERK' AND sal > 3000
RETURNING sal INTO :clerk_sals;
FETCH Statement, SELECT Statement
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" on page 6-8.
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.
This identifies a user-defined exception previously declared within the current scope.
This is any valid Oracle error number. These are the same error numbers returned by the function SQLCODE.
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.
The following pragma associates the exception deadlock_detected with Oracle error 60:
DECLARE
deadlock_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
...
EXCEPTION
WHEN deadlock_detected THEN
-- handle the error
...
END;
Exceptions, SQLCODE Function
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.
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.
This identifies a predefined exception such as ZERO_DIVIDE, or a user-defined exception previously declared within the current scope.
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.
