Release 8.1.5
A67842-01
 Library |
 Product |
 Contents |
 Index |
| PL/SQL User's Guide and Reference Release 8.1.5 A67842-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.
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".
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". 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, 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:
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 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".
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. 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 an index-by table, nested table, or varray previously declared within the current scope. For the syntax of collection_declaration, see "Collections".
This construct declares a constant. For the syntax of constant_declaration, see "Constants and Variables".
This construct declares an explicit cursor. For the syntax of cursor_declaration, see "Cursors".
This construct declares a cursor variable. For the syntax of cursor_variable_declaration, see "Cursor Variables".
This construct declares an exception. For the syntax of exception_declaration, see "Exceptions".
This identifies an object (instance of an object type) previously declared within the current scope. For the syntax of object_declaration, see "Object Types".
This construct declares a user-defined record. For the syntax of record_declaration, see "Records".
This construct declares a variable. For the syntax of variable_declaration, see "Constants and Variables".
This construct declares a function. For the syntax of function_declaration, see "Functions".
This construct declares a procedure. For the syntax of procedure_declaration, see "Procedures".
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.
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.
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".
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".
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".
This identifies an index-by table, nested 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".
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 must 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. 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.
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:
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 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. For more information, see "Defining and Declaring Collections".
This identifies a user-defined type specifier, which is used in subsequent declarations of collections.
This is any PL/SQL datatype except BINARY_INTEGER, BOOLEAN, LONG, LONG RAW, NATURAL, NATURALN, NCHAR, NCLOB, NVARCHAR2, object types with TABLE or VARRAY attributes, PLS_INTEGER, POSITIVE, POSITIVEN, REF CURSOR, 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.
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.
Nested tables extend the functionality of index-by tables, so they differ in several ways. See "Nested Tables versus Index-by Tables".
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, GROUP BY, or ORDER BY 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 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;
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.
DECLARE TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER; emp_tab EmpTabTyp; BEGIN /* Retrieve employee record. */ SELECT * INTO emp_tab(7468) FROM emp WHERE empno = 7788;
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 -- 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 spec, 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 Stats', 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".
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".
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:
ROLLBACK statement to roll back (undo) the changes.
If you commit while a FOR UPDATE cursor is open, a subsequent fetch on that cursor raises an exception. The cursor remains open, 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
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".
This identifies a program constant. For naming conventions, see "Identifiers".
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".
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 spec 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".
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".
This is a cursor attribute, which 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.
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. 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.
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.
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;
You can use the cursor attributes in procedural statements but not in SQL statements.
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. To create cursor variables, you define a REF CURSOR type, then declare cursor variables of that type.
Cursor variables are like C or Pascal pointers, which hold the 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".
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 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.
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".
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.
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.
Currently, cursor variables are subject to several restrictions. See "Restrictions on Cursor Variables".
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:
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 stand-alone procedure to open the cursor variable. Simply define the REF CURSOR 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:
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".
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.
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 spec. 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".
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".
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 Oracle8i 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.
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".
The operand of TABLE is a SELECT statement that returns a single column value, which must be a nested table or a varray cast as a nested table. Operator TABLE informs Oracle that the value is a collection, not a scalar value.
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 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.
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 RETURNING INTO list. The corresponding columns must store scalar (not composite) values. For more information, see "Taking Advantage of Bulk Binds".
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;
You can combine the BULK COLLECT 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:
FORALL j IN depts.FIRST..depts.LAST DELETE FROM emp WHERE empno = depts(j) RETURNING empno BULK COLLECT INTO enums;
The column values returned by each execution are added to the values returned previously.
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".
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, "Error Handling".
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.
This is an executable statement. For the syntax of statement, see "Blocks".
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 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:
However, exceptions cannot propagate across remote procedure calls (RPCs). Therefore, a PL/SQL block cannot catch an exception raised by a remote subprogram. For a workaround, see "Using raise_application_error".
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.
An exception handler can reference only those variables that the current block can reference.
The following PL/SQL block has two exception handlers:
DELARE bad_emp_id EXCEPTION; bad_acct_no EXCEPTION; ... BEGIN ... EXCEPTION WHEN bad_emp_id OR bad_acct_no THEN -- user-defined ROLLBACK; WHEN ZERO_DIVIDE THEN -- predefined INSERT INTO inventory VALUES (part_number, quantity); COMMIT; END;
Blocks, EXCEPTION_INIT Pragma, RAISE Statement
The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block. For more information, see Chapter 10, "Native Dynamic SQL".
This is a string literal, variable, or expression that represents a SQL statement or PL/SQL block.
This identifies a variable that stores a SELECTed column value.
This identifies a user-defined or %ROWTYPE record that stores a SELECTed row.
This identifies an expression whose value is passed to the dynamic SQL statement or PL/SQL block.
This clause, useful only for single-row queries, specifies the variables or record into which column values are fetched.
This clause specifies a list of bind arguments. If you do not specify a parameter mode, it defaults to IN. At run time, any bind arguments in the USING clause replace corresponding placeholders in the SQL statement or PL/SQL block.
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".
For each column value returned by the query, there must be a corresponding, type-compatible variable or field in the INTO clause Also, 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").
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 EXECUTE IMMEDIATE re-prepares the dynamic string before every execution.
The following PL/SQL block contains several examples of dynamic SQL:
DECLARE sql_stmt VARCHAR2(100); plsql_block VARCHAR2(200); my_deptno NUMBER(2) := 50; my_dname VARCHAR2(15) := 'PERSONNEL'; my_loc VARCHAR2(15) := 'DALLAS'; emp_rec emp%ROWTYPE; BEGIN sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)'; EXECUTE IMMEDIATE sql_stmt USING my_deptno, my_dname, my_loc; sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING 7788; EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :n' USING my_deptno; plsql_block := 'BEGIN emp_stuff.raise_salary(:id, :amt); END;'; EXECUTE IMMEDIATE plsql_block USING 7788, 500; EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)'; sql_stmt := 'ALTER SESSION SET SQL_TRACE TRUE'; EXECUTE IMMEDIATE sql_stmt; END;
OPEN-FOR-USING Statement
You use the EXIT statement to exit a loop. The EXIT statement has two forms: the unconditional EXIT and the conditional EXIT WHEN. With either form, you can name the loop to be exited. For more information, see "Iterative Control: LOOP and EXIT Statements".
An unconditional EXIT statement (that is, one without a WHEN clause) exits the current loop immediately. Execution resumes with