| PL/SQL User's Guide and Reference |  Library |
 Product |
 Contents |
 Index |
| PL/SQL User's Guide and Reference | Library |
Product |
Contents |
Index |
Molière
This chapter is a quick reference guide to PL/SQL syntax; 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:
| Assignment Statement | LOOP Statement |
| Blocks | NULL Statement |
| CLOSE Statement | OPEN Statement |
| Comments | OPEN-FOR Statement |
| COMMIT Statement | Packages |
| Constants and Variables | PL/SQL Table Attributes |
| Cursor Attributes | PL/SQL Tables |
| Cursors | Procedures |
| Cursor Variables | RAISE Statement |
| DELETE Statement | Records |
| EXCEPTION_INIT Pragma | RETURN Statement |
| Exceptions | ROLLBACK Statement |
| EXIT Statement | %ROWTYPE Attribute |
| Expressions | SAVEPOINT Statement |
| FETCH Statement | SELECT INTO Statement |
| Functions | SET TRANSACTION Statement |
| GOTO Statement | SQL Cursor |
| IF Statement | SQLCODE Function |
| INSERT Statement | SQLERRM Function |
| Literals | %TYPE Attribute |
| LOCK TABLE Statement | UPDATE Statement |
| Description |
| Syntax |
| Keyword and Parameter Description |
| Usage Notes |
| Examples |
| Related Topics |
When you are unsure of the syntax to use in a PL/SQL statement, trace through its syntax definition, reading from left to right and top to bottom. You can verify or construct any PL/SQL statement that way.
Syntax definitions use the following symbols and lexical conventions:
::=
This symbol means "is defined as."
[ ]
Brackets enclose optional items.
{ }
Braces enclose items only one of which is required.
|
A vertical bar separates alternatives within brackets or braces.
...
An ellipsis shows that the preceding syntactic element can be repeated.
lower case
Lower case denotes a syntactic element for which you must substitute a literal, identifier, or construct, whichever is appropriate.
UPPER CASE
Upper case denotes PL/SQL keywords, which must be spelled as shown but can be entered in lower or mixed case.
punctuation
Punctuation other than brackets, braces, vertical bars, and ellipses must be entered as shown.
![[*]](jump.gif)
.
{ cursor_variable_name
| :host_cursor_variable_name
| :host_variable_name[:indicator_name]
| parameter_name
| plsql_table_name(index)
| record_name.field_name
| variable_name} := expression;
.
.
You cannot assign nulls to a variable defined as NOT NULL. If you try, PL/SQL raises the predefined exception VALUE_ERROR.
Only the values TRUE 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 PL/SQL table:
plsql_table_name(index) := expression;
In the following example, you assign the uppercase value of last_name to the third row in PL/SQL table ename_tab:
ename_tab(3) := UPPER(last_name);
wages := hours_worked * hourly_salary; country := 'France'; costs := labor + supplies; done := (count > 100); dept_rec.loc := 'BOSTON'; comm_tab(5) := sales * 0.15;
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" .
[<<label_name>>] [DECLARE object_declaration [object_declaration] ... [subprogram_declaration [subprogram_declaration] ...]] BEGIN seq_of_statements [EXCEPTION exception_handler [exception_handler] ...] END [label_name];
object_declaration ::=
{ constant_declaration
| cursor_declaration
| cursor_variable_declaration
| exception_declaration
| plsql_table_declaration
| record_declaration
| variable_declaration}subprogram_declaration ::=
{function_declaration | procedure_declaration}
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;
PL/SQL does not allow forward references. So, you must declare an object 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 objects.
.
.
.
.
.
.
.
.
.
seq_of_statements ::=
statement [statement] ...
Statements are used to create algorithms. Besides SQL statements, PL/SQL has flow-of-control and error-handling statements. PL/SQL statements are free format. That is, they can continue from line to line, providing you do not split keywords, delimiters, or literals across lines. A semicolon (;) must terminate every PL/SQL statement. The syntax of statement follows:
statement ::=
[<<label_name>>]
{ assignment_statement
| exit_statement
| goto_statement
| if_statement
| loop_statement
| null_statement
| plsql_block
| raise_statement
| return_statement
| 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. The syntax of sql_statement follows:
sql_statement ::=
{ close_statement
| commit_statement
| delete_statement
| fetch_statement
| insert_statement
| lock_table_statement
| open_statement
| open-for_statement
| rollback_statement
| savepoint_statement
| select_statement
| set_transaction_statement
| update_statement}
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.
.
END does not signal the end of a transaction. Just as a block can span multiple transactions, a transaction can span multiple blocks.
-- 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;
.
CLOSE { cursor_name
| cursor_variable_name
| :host_cursor_variable_name};
If you try to close an already-closed or never-opened cursor or cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.
LOOP FETCH emp_cv INTO emp_rec; EXIT WHEN emp_cv%NOTFOUND; ... -- process data record END LOOP; /* Close cursor variable. */ CLOSE emp_cv;
.
{-- text | /* text */}
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.
-- 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 following line computes the area of a circle using pi, which is the ratio between the circumference and diameter. Pi is an irrational number, meaning that it cannot be expressed as the ratio between two integers. */ area := pi * radius**2;
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" .
COMMIT [WORK] [COMMENT 'text'];
.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 Oracle7 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.
.
constant_name CONSTANT
{ record_name.field_name%TYPE
| scalar_type_name
| table_name.column_name%TYPE
| variable_name%TYPE} [NOT NULL] {:= | DEFAULT} expression;variable_declaration ::=
variable_name
{ cursor_name%ROWTYPE
| cursor_variable_name%TYPE
| plsql_table_name%TYPE
| record_name%TYPE
| record_name.field_name%TYPE
| scalar_type_name
| table_name%ROWTYPE
| table_name.column_name%TYPE
| variable_name%TYPE} [[NOT NULL] {:= | DEFAULT} expression];
.
.
.
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.
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;
. 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.
{ cursor_name
| cursor_variable_name
| :host_cursor_variable_name
| SQL}{%FOUND | %ISOPEN | %NOTFOUND | %ROWCOUNT}
.
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.
Oracle automatically closes the implicit SQL cursor after executing its associated SQL statement, so SQL%ISOPEN always yields FALSE.
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.
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.
-- 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;
.
CURSOR cursor_name [(cursor_parameter_declaration[, cursor_parameter_declaration]...)] IS select_statement;
cursor_specification ::=
CURSOR cursor_name [(cursor_parameter_declaration[,
cursor_parameter_declaration]...)]
RETURN { cursor_name%ROWTYPE
| record_name%TYPE
| record_type_name
| table_name%ROWTYPE};cursor_body ::=
CURSOR cursor_name [(cursor_parameter_declaration[,
cursor_parameter_declaration]...)]
RETURN { cursor_name%ROWTYPE
| record_name%TYPE
| record_type_name
| table_name%ROWTYPE} IS select_statement;cursor_parameter_declaration ::=
cursor_parameter_name [IN]
{ cursor_name%ROWTYPE
| cursor_variable_name%TYPE
| plsql_table_name%TYPE
| record_name%TYPE
| scalar_type_name
| table_name%ROWTYPE
| table_name.column_name%TYPE
| variable_name%TYPE} [{:= | DEFAULT} expression]
, except that select_statement cannot have an INTO clause.
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.
.
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
CURSOR c1 IS SELECT ename, job, sal FROM emp WHERE deptno = 20; CURSOR c2 (start_date DATE) IS SELECT empno, sal FROM emp WHERE hiredate > start_date;
. To create cursor variables, you take two steps. First, you define a REF CURSOR type, then declare cursor variables of that type.
TYPE ref_type_name IS REF CURSOR
RETURN { cursor_name%ROWTYPE
| cursor_variable_name%ROWTYPE
| record_name%TYPE
| record_type_name
| table_name%ROWTYPE};cursor_variable_declaration ::=
cursor_variable_name ref_type_name;
.
.
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. They follow the usual scoping and instantiation rules. Local PL/SQL cursor variables are instantiated when you enter a block or subprogram and cease to exist when you exit.
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:
Currently, cursor variables are subject to the following restrictions, some of which future releases of PL/SQL will remove:
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;
DELETE [FROM] {table_reference | (subquery)} [alias]
[WHERE {search_condition | CURRENT OF cursor_name}]; table_reference ::=
[schema_name.]{table_name | view_name}[@dblink_name]
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.
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:
DELETE FROM bonus WHERE sales_amt < quota;
.
PRAGMA EXCEPTION_INIT (exception_name, error_number);
Be sure to assign only one exception name to an error number.
DECLARE
insufficient_privileges EXCEPTION;
PRAGMA EXCEPTION_INIT(insufficient_privileges, -1031);
BEGIN
...
EXCEPTION
WHEN insufficient_privileges THEN
-- handle the error
END;
exception_name EXCEPTION;
exception_handler ::=
WHEN {exception_name [OR exception_name] ... | OTHERS}
THEN seq_of_statements
Each WHEN clause can associate a different sequence of statements with a list of exceptions. However, an exception name can appear only once in the exception-handling part of a PL/SQL block or subprogram.
.
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 impossible or undesirable 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:
. 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.
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;
.
EXIT [label_name] [WHEN boolean_expression];
.
PL/SQL allows you to code an infinite loop. For example, the following loop will never terminate in the normal way:
WHILE TRUE LOOP ... END LOOP;
In such cases, you must use an EXIT statement to exit the loop.
If you use an EXIT statement to exit a cursor FOR loop prematurely, the cursor is closed automatically. The cursor is also closed automatically if an exception is raised inside the loop.
DECLARE
amount NUMBER;
maximum NUMBER;
BEGIN
...
BEGIN
...
IF amount >= maximum THEN
EXIT; -- illegal
END IF;
END;
...
END;The following loop normally executes ten times, but it will exit prematurely if there are less than ten rows to fetch:
FOR i IN 1..10 FETCH c1 INTO emp_rec; EXIT WHEN c1%NOTFOUND; total_comm := total_comm + emp_rec.comm; END LOOP;
The following example illustrates the use of loop labels:
<<outer>>
FOR i IN 1..10 LOOP
...
<<inner>>
FOR j IN 1..100 LOOP
...
EXIT outer WHEN ... -- exits both loops
END LOOP inner;
END LOOP outer;
The PL/SQL compiler determines the datatype of an expression from the types of the variables, constants, literals, and operators that comprise the expression. Every time the expression is evaluated, a single value of that type results. For more information, see "Expressions and Comparisons" .
[(]{ boolean_expression
| character_expression
| date_expression
| numeric_expression}[)]boolean_expression ::=
[NOT] { boolean_constant_name
| boolean_function_call
| boolean_literal
| boolean_variable_name
| other_boolean_form}
[{AND | OR} [NOT] { boolean_constant_name
| boolean_function_call
| boolean_literal
| boolean_variable_name
| other_boolean_form}] ...other_boolean_form ::=
expression
{ relational_operator expression
| IS [NOT] NULL
| [NOT] LIKE pattern
| [NOT] BETWEEN expression AND expression
| [NOT] IN (expression[, expression]...)
| { cursor_name
| cursor_variable_name
| :host_cursor_variable_name
| SQL}{%FOUND | %ISOPEN | %NOTFOUND}
| plsql_table_name.EXISTS(index)}numeric_expression ::=
{ { cursor_name
| cursor_variable_name
| :host_cursor_variable_name
| SQL}%ROWCOUNT
| :host_variable_name[:indicator_name]
| numeric_constant_name
| numeric_function_call
| numeric_literal
| numeric_variable_name
| plsql_table_name{ .COUNT
| .FIRST
| .LAST
| .NEXT(index)
| .PRIOR(index)}}[**exponent]
[ {+ | - | * | /}
{ { cursor_name
| cursor_variable_name
| :host_cursor_variable_name
| SQL}%ROWCOUNT
| :host_variable_name[:indicator_name]
| numeric_constant_name
| numeric_function_call
| numeric_literal
| numeric_variable_name
| plsql_table_name{ .COUNT
| .FIRST
| .LAST
| .NEXT(index)
| .PRIOR(index)}}[**exponent]]...character_expression ::=
{ character_constant_name
| character_function_call
| character_literal
| character_variable_name
| :host_variable_name[:indicator_name]}
[ || { character_constant_name
| character_function_call
| character_literal
| character_variable_name
| :host_variable_name[:indicator_name]}]...date_expression ::=
{ date_constant_name
| date_function_call
| date_literal
| date_variable_name
| :host_variable_name[:indicator_name]}
[{+ | -} numeric_expression]...
. AND returns the value TRUE only if both its operands are true. OR returns the value TRUE if either of its operands is true. NOT returns the opposite value (logical negation) of its operand. NOT NULL returns NULL because nulls are indeterminate. For more information, see "Logical Operators" .
.
value NOT IN set
yield FALSE if the set contains a null.
.
.
.
.
'Good' || ' morning!' yields 'Good morning!'
The next example shows that nulls have no effect on the result of a concatenation:
'suit' || NULL || 'case' yields 'suitcase'
A string zero characters in length ('') is called a null string and is treated like a null.
.In conditional control statements, if a Boolean expression yields TRUE, its associated sequence of statements is executed. But, if the expression yields FALSE or NULL, its associated sequence of statements is not executed.
When PL/SQL evaluates a boolean expression, NOT has the highest precedence, AND has the next-highest precedence, and OR has the lowest precedence. However, you can use parentheses to override the default operator precedence.
The relational operators can be applied to operands of type BOOLEAN. By definition, TRUE is greater than FALSE. Comparisons involving nulls always yield a null.
The value of a Boolean expression can be assigned only to Boolean variables, not to host variables or database columns. Also, datatype conversion to or from type BOOLEAN is not supported.
You can use the addition and subtraction operators to increment or decrement a date value, as the following examples show:
hire_date := '10-MAY-95'; hire_date := hire_date + 1; -- makes hire_date '11-MAY-95' hire_date := hire_date - 5; -- makes hire_date '06-MAY-95'
Within an expression, operations occur in their predefined order of precedence. From first to last (top to bottom), the default order of operations is
parentheses exponents unary operators multiplication and division addition, subtraction, and concatenation
PL/SQL evaluates operators of equal precedence in no particular order. When parentheses enclose an expression that is part of a larger expression, PL/SQL evaluates the parenthesized expression first, then uses the result value in the larger expression. When parenthesized expressions are nested, PL/SQL evaluates the innermost expression first and the outermost expression last.
(a + b) > c -- Boolean expression NOT finished -- Boolean expression TO_CHAR(acct_no) -- character expression 'Fat ' || 'cats' -- character expression '15-NOV-95' -- date expression MONTHS_BETWEEN(d1, d2) -- date expression pi * r**2 -- numeric expression emp_cv%ROWCOUNT -- numeric expression
.
FETCH { cursor_name
| cursor_variable_name
| :host_cursor_variable_name}
INTO {variable_name[, variable_name]... | record_name};
Any variables in the WHERE clause of the query are evaluated only when the cursor or cursor variable is opened. To change the result set or the values of variables in the query, you must reopen the cursor or cursor variable with the variables set to their new values.
To reopen a cursor, you must close it first. However, you need not close a cursor variable before reopening it.
You can use different INTO lists on separate fetches with the same cursor or cursor variable. Each fetch retrieves another row and assigns values to the target variables.
If you FETCH past the last row in the result set, the values of the target fields or variables are indeterminate and the %NOTFOUND attribute yields TRUE.
PL/SQL makes sure the return type of a cursor variable is compatible with the INTO clause of the FETCH statement. For each column value returned by the query associated with the cursor variable, there must be a corresponding, type-compatible field or variable in the INTO clause. Also, the number of fields or variables must equal the number of column values.
When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the IN (or IN OUT) mode. However, if the subprogram also opens the cursor variable, you must specify the IN OUT mode.
Eventually, the FETCH statement must fail to return a row; so when that happens, no exception is raised. To detect the failure, you must use the cursor attribute %FOUND or %NOTFOUND. For more information, see "Using Cursor Attributes" .
PL/SQL raises the predefined exception INVALID_CURSOR if you try to fetch from a closed or never-opened cursor or cursor variable.
DECLARE
my_sal NUMBER(7,2);
num INTEGER(2) := 2;
CURSOR emp_cur IS SELECT num*sal FROM emp;
BEGIN
OPEN emp_cur; -- num equals 2 here
LOOP
FETCH emp_cur INTO my_sal;
EXIT WHEN emp_cur%NOTFOUND;
-- process the data
num := num + 1; -- does not affect next FETCH; sal will
-- be multiplied by 2
END LOOP;
CLOSE emp_cur;
END;In this example, each retrieved value equals 2 * sal, even though num is incremented after each fetch. To change the result set or the values of variables in the query, you must close and reopen the cursor with the variables set to their new values.
In the following Pro*C example, you fetch rows from a host cursor variable into a host record (struct) named emp_rec:
/* Exit loop when done fetching. */
EXEC SQL WHENEVER NOTFOUND DO break;
for (;;)
{
/* Fetch row into record. */
EXEC SQL FETCH :emp_cur INTO :emp_rec;
/* process the data. */
} The next example shows that you can use a different INTO clause on separate fetches with the same cursor variable. Each fetch retrieves another row from the same result set.
/* Exit loop when done fetching. */
EXEC SQL WHENEVER NOTFOUND DO break;
for (;;)
{
/* Fetch row from result set. */
EXEC SQL FETCH :emp_cur INTO :emp_rec1;
/* Fetch next row from same result set. */
EXEC SQL FETCH :emp_cur INTO :emp_rec2;
/* process the data. */
}
. A function has two parts: the specification and the body. The function specification begins with the keyword FUNCTION and ends with the RETURN clause, which specifies the datatype of the result value. Parameter declarations are optional. Functions that take no parameters are written without parentheses.
The function body begins with the keyword IS and ends with the keyword END followed by an optional function name. The function body has three parts: an optional declarative part, an executable part, and an optional exception-handling part.
The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These objects are local and cease to exist when you exit the function. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains exception handlers, which deal with exceptions raised during execution.
FUNCTION function_name [(parameter_declaration[, parameter_declaration]...)] RETURN return_type;
function_body ::=
FUNCTION function_name [(parameter_declaration[,
parameter_declaration]...)]
RETURN return_type IS
[[object_declaration [object_declaration] ...]
[subprogram_declaration [subprogram_declaration] ...]]
BEGIN
seq_of_statements
[EXCEPTION
exception_handler [exception_handler] ...]
END [function_name];parameter_declaration ::=
parameter_name [IN | OUT | IN OUT]
{ cursor_name%ROWTYPE
| cursor_variable_name%TYPE
| plsql_table_name%TYPE
| record_name%TYPE
| scalar_type_name
| table_name%ROWTYPE
| table_name.column_name%TYPE
| variable_name%TYPE} [{:= | DEFAULT} expression]return_type ::=
{ cursor_name%ROWTYPE
| cursor_variable_name%ROWTYPE
| plsql_table_name%TYPE
| record_name%TYPE
| scalar_type_name
| table_name%ROWTYPE
| table_name.column_name%TYPE
| variable_name%TYPE}object_declaration ::=
{ constant_declaration
| cursor_declaration
| cursor_variable_declaration
| exception_declaration
| plsql_table_declaration
| record_declaration
| variable_declaration}subprogram_declaration ::=
{function_declaration | procedure_declaration}
.
.
.
.
.
.
.
.
.
.
.
A function is called as part of an expression. For example, the function sal_ok might be called as follows:
promotable := sal_ok(new_sal, new_title) AND (rating > 3);
To be callable from SQL expressions, a stored function must obey certain rules meant to control side effects. For standalone functions, Oracle can enforce these rules by checking the function body. However, the body of a packaged function is hidden. So, for packaged functions, you must use the pragma RESTRICT_REFERENCES to enforce the rules. For more information, see "Calling Stored Functions from SQL Expressions" in Oracle7 Server Application Developer's Guide.
You can write the function specification and body as a unit. Or, you can separate the function specification from its body. That way, you can hide implementation details by placing the function in a package. You can define functions in a package body without declaring their specifications in the package specification. However, such functions can be called only from inside the package.
Inside a function, an IN parameter acts like a constant. Therefore, it cannot be assigned a value. An OUT parameter acts like an uninitialized variable. So, its value cannot be assigned to another variable or reassigned to itself. An IN OUT parameter acts like an initialized variable. Therefore, it can be assigned a value, and its value can be assigned to another variable. For summary information about the parameter modes, see Table 7 - 1 .
Avoid using the OUT and IN OUT modes with functions. The purpose of a function is to take zero or more parameters and return a single value. It is poor programming practice to have a function return multiple values. Also, functions should be free from side effects, which change the values of variables not local to the subprogram. Thus, a function should not change the values of its actual parameters.
Functions can be defined using any Oracle tool that supports PL/SQL. However, to become available for general use, functions must be CREATEd and stored in an Oracle database. You can issue the CREATE FUNCTION statement interactively from SQL*Plus or Server Manager. For the full syntax of the CREATE FUNCTION statement, see Oracle7 Server SQL Reference.
FUNCTION balance (acct_id INTEGER) RETURN REAL IS acct_bal REAL; BEGIN SELECT bal INTO acct_bal FROM accts WHERE acctno = acct_id; RETURN acct_bal; END balance;
.
<<label_name>>
goto_statement ::=
GOTO label_name;
BEGIN
...
GOTO update_row; -- illegal branch into IF statement
...
IF valid THEN
...
<<update_row>>
UPDATE emp SET ...
END IF;
END;From the current block, a GOTO statement can branch to another place in the block or into an enclosing block, but not into an exception handler. From an exception handler, a GOTO statement can branch into an enclosing block, but not into the current block.
If you use the GOTO statement to exit a cursor FOR loop prematurely, the cursor is closed automatically. The cursor is also closed automatically if an exception is raised inside the loop.
A given label can appear only once in a block. However, the label can appear in other blocks including enclosing blocks and sub-blocks. If a GOTO statement cannot find its target label in the current block, it branches to the first enclosing block in which the label appears.
BEGIN
...
FOR ctr IN 1..50 LOOP
DELETE FROM emp WHERE ...
IF SQL%FOUND THEN
GOTO end_loop; -- illegal
END IF;
...
<<end_loop>>
END LOOP; -- not an executable statement
END;To debug the last example, simply add the NULL statement, as follows:
BEGIN
...
FOR ctr IN 1..50 LOOP
DELETE FROM emp WHERE ...
IF SQL%FOUND THEN
GOTO end_loop;
END IF;
...
<<end_loop>>
NULL; -- an executable statement that specifies inaction
END LOOP;
END;
For more examples of legal and illegal GOTO statements, see "GOTO Statement" .
.
IF boolean_expression THEN seq_of_statements [ELSIF boolean_expression THEN seq_of_statements [ELSIF boolean_expression THEN seq_of_statements] ...] [ELSE seq_of_statements] END IF;
.
The second form of IF statement adds the keyword ELSE followed by an alternative sequence of statements. The sequence of statements in the ELSE clause is executed only if the Boolean expression yields FALSE or NULL. Thus, the ELSE clause ensures that a sequence of statements is executed.
The third form of IF statement uses the keyword ELSIF to introduce additional Boolean expressions. If the first expression yields FALSE or NULL, the ELSIF clause evaluates another expression. An IF statement can have any number of ELSIF clauses; the final ELSE clause is optional. Boolean expressions are evaluated one by one from top to bottom. If any expression yields TRUE, its associated sequence of statements is executed and control passes to the next statement. If all expressions yield FALSE or NULL, the sequence in the ELSE clause is executed.
An IF statement never executes more than one sequence of statements because processing is complete after any sequence of statements is executed. However, the THEN and ELSE clauses can include more IF statements. That is, IF statements can be nested.
IF shoe_count < 20 THEN order_quantity := 50; ELSIF shoe_count < 30 THEN order_quantity := 20; ELSE order_quantity := 10; END IF; INSERT INTO purchase_order VALUES (shoe_type, order_quantity);
In the following example, depending on the value of score, one of two status messages is inserted into the grades table:
IF score < 70 THEN fail := fail + 1; INSERT INTO grades VALUES (student_id, 'Failed'); ELSE pass := pass + 1; INSERT INTO grades VALUES (student_id, 'Passed'); END IF;
INSERT INTO {table_reference | (subquery)}
[(column_name[, column_name]...)]
{VALUES (sql_expression[, sql_expression]...) | subquery};table_reference ::=
[schema_name.]{table_name | view_name}[@dblink_name]
There must be only one value for each column in the column list. The first value is associated with the first column, the second value is associated with the second column, and so on. If there is no column list, you must supply a value for each column in the table.
The datatypes of the values being inserted must be compatible with the datatypes of corresponding columns in the column list. For more information, see "Datatypes" .
As many rows are added to the table as are returned by the subquery in the VALUES clause. The subquery must return a value for every column in the column list or for every column in the table if there is no column list.
The implicit SQL cursor and cursor attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN let you access useful information about the execution of an INSERT statement.
An INSERT statement might insert one or more rows or no rows. If one or more rows are inserted, you get the following results:
INSERT INTO bonus SELECT ename, job, sal, comm FROM emp WHERE comm > sal * 0.25; ... INSERT INTO emp (empno, ename, job, sal, comm, deptno) VALUES (4160, 'STURDEVIN', 'SECURITY GUARD', 2045, NULL, 30); ... INSERT INTO dept VALUES (my_deptno, UPPER(my_dname), 'CHICAGO');
.
[+ | -]{integer | real_number}integer ::=
digit[digit]...
real_number ::=
{ integer[.integer]
| integer.
| .integer}[{E | e}[+ | -]integer]character_literal ::=
{'character' | ''''}string_literal ::=
'{character[character]... | ''['']...}'boolean_literal ::=
{TRUE | FALSE | NULL}
.
A character literal is an individual character enclosed by single quotes (apostrophes). Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols. PL/SQL is case sensitive within character literals. So, for example, PL/SQL considers the literals 'Q' and 'q' to be different.
A string literal is a sequence of zero or more characters enclosed by single quotes. The null string ('') contains zero characters. To represent an apostrophe within a string, write two single quotes. PL/SQL is case sensitive within string literals. So, for example, PL/SQL considers the literals 'white' and 'White' to be different.
Also, trailing blanks are significant within string literals, so 'White' and 'White ' are different. How a string literal compares to a variable does not depend on the variable; trailing blanks in a literal are never trimmed.
Unlike the non-value NULL, the Boolean values TRUE and FALSE cannot be inserted into a database column.
25 6.34 7E2 25e-03 .1 1. +17 -4.4
Several examples of character literals follow:
'H' '&' ' ' '9' ']' 'g'
A few examples of string literals follow:
'$5,000' '02-AUG-87' 'Don''t leave without saving your work.'
.
LOCK TABLE table_reference[, table_reference]... IN lock_mode MODE [NOWAIT];
table_reference ::=
[schema_name.]{table_name | view_name}[@dblink_name]
A table lock never keeps other users from querying a table, and a query never acquires a table lock.
If your program includes SQL locking statements, make sure the Oracle users requesting locks have the privileges needed to obtain the locks. Your DBA can lock any table. Other users can lock tables they own or tables for which they have a privilege, such as SELECT, INSERT, UPDATE, or DELETE.
LOCK TABLE accts IN SHARE MODE;
.
[<<label_name>>] LOOP seq_of_statements END LOOP [label_name];
while_loop_statement ::=
[<<label_name>>] WHILE boolean_expression LOOP seq_of_statements END LOOP [label_name];
for_loop_statement ::=
[<<label_name>>] FOR index_name IN [REVERSE] lower_bound..upper_bound LOOP seq_of_statements END LOOP [label_name];
cursor_for_loop_statement ::=
[<<label_name>>]
FOR record_name IN
{ cursor_name [(cursor_parameter_name[,
cursor_parameter_name]...)]
| (select_statement)}
LOOP
seq_of_statements
END LOOP [label_name];
You can use label_name in an EXIT statement to exit the loop labelled by label_name.
You cannot reference the index of a FOR loop from a nested FOR loop if both indexes have the same name unless the outer loop is labeled by label_name and you use dot notation, as follows:
label_name.index_name
In the following example, you compare two loop indexes that have the same name, one used by an enclosing loop, the other by a nested loop:
<<outer>>
FOR ctr IN 1..20 LOOP
...
<<inner>>
FOR ctr IN 1..10 LOOP
IF outer.ctr > ctr THEN ...
...
END LOOP inner;
END LOOP outer;
.
The range is evaluated when the FOR loop is first entered and is never re-evaluated. The sequence of statements in the loop is executed once for each integer in the range defined by lower_bound..upper_bound. After each iteration, the loop index is incremented.
The implicit declaration of index_name overrides any other declaration outside the loop. So, another variable with the same name cannot be referenced inside the loop unless a label is used, as follows:
<<main>>
DECLARE
num NUMBER;
BEGIN
...
FOR num IN 1..10 LOOP
...
IF main.num > 5 THEN -- refers to the variable num,
... -- not to the loop index
END IF;
END LOOP;
END main;Inside a loop, its index is treated like a constant. The index can appear in expressions, but cannot be assigned a value.
By default, the loop index is assigned the value of lower_bound. If that value is not greater than the value of upper_bound, the sequence of statements in the loop is executed, then the index is incremented. If the value of the index is still not greater than the value of upper_bound, the sequence of statements is executed again. This process repeats until the value of the index is greater than the value of upper_bound. At that point, the loop completes.
In this case, the loop index is assigned the value of upper_bound. If that value is not less than the value of lower_bound, the sequence of statements in the loop is executed, then the index is decremented. If the value of the index is still not less than the value of lower_bound, the sequence of statements is executed again. This process repeats until the value of the index is less than the value of lower_bound. At that point, the loop completes. An example follows:
FOR i IN REVERSE 1..10 LOOP -- i starts at 10, ends at 1 -- statements here execute 10 times END LOOP;
record_name cursor_name%ROWTYPE;
The record is defined only inside the loop. You cannot refer to its fields outside the loop. The implicit declaration of record_name overrides any other declaration outside the loop. So, another record with the same name cannot be referenced inside the loop unless a label is used.
Fields in the record store column values from the implicitly fetched row. The fields have the same names and datatypes as their corresponding columns. To access field values, you use dot notation, as follows:
record_name.field_name
Select-items fetched from the FOR loop cursor must have simple names or, if they are expressions, must have aliases. In the following example, wages is an alias for the select item sal+NVL(comm,0):
CURSOR c1 IS SELECT empno, sal+NVL(comm,0) wages, job ...
.
The syntax of select_statement is like the syntax of select_into_ statement defined in "SELECT INTO Statement" , except that select_statement cannot have an INTO clause.
When you exit a cursor FOR loop, the cursor is closed automatically even if you use an EXIT or GOTO statement to exit the loop prematurely. The cursor is also closed automatically if an exception is raised inside the loop.
DECLARE
bonus REAL;
CURSOR c1 IS SELECT empno, sal, comm FROM emp;
BEGIN
FOR c1rec IN c1 LOOP
bonus := (c1rec.sal * 0.05) + (c1rec.comm * 0.25);
INSERT INTO bonuses VALUES (c1rec.empno, bonus);
END LOOP;
COMMIT;
END;
.
NULL;
Each clause in an IF statement must contain at least one executable statement. The NULL statement meets this requirement. So, you can use the NULL statement in clauses that correspond to circumstances in which no action is taken.
Do not confuse the NULL statement with the Boolean non-value NULL; they are unrelated.
IF job_title = 'SALESPERSON' THEN compute_commission(emp_id); ELSE NULL; END IF;
In the next example, the NULL statement shows that no action is taken for unnamed exceptions:
EXCEPTION
WHEN ZERO_DIVIDE THEN
ROLLBACK;
...
WHEN OTHERS THEN
NULL;
END;
OPEN cursor_name [(cursor_parameter_name[, cursor_parameter_name]...)];
.
So, although you must close a cursor before you can reopen it, PL/SQL need not reparse the associated SELECT statement. If you close, then immediately reopen the cursor, a reparse is definitely not needed.
Rows in the result set are not retrieved when the OPEN statement is executed. The FETCH statement retrieves the rows. With a FOR UPDATE cursor, the rows are locked when the cursor is opened.
If a cursor is currently open, you cannot use its name in a cursor FOR loop.
If formal parameters are declared, actual parameters must be passed to the cursor. The values of actual parameters are used when the cursor is opened. The datatypes of the formal and actual parameters must be compatible. The query can also reference PL/SQL variables declared within its scope.
Unless you want to accept default values, each formal parameter in the cursor declaration must have a corresponding actual parameter in the OPEN statement. Formal parameters declared with a default value need not have a corresponding actual parameter. They can simply assume their default values when the OPEN statement is executed.
The formal parameters of a cursor must be IN parameters. Therefore, they cannot return values to actual parameters.
You can associate the actual parameters in an OPEN statement with the formal parameters in a cursor declaration using positional or named notation. For more information, see "Positional and Named Notation"
.
CURSOR parts_cur IS SELECT part_num, part_price FROM parts;
the following statement opens the cursor:
OPEN parts_cur;
Given the cursor declaration
CURSOR emp_cur(my_ename CHAR, my_comm NUMBER DEFAULT 0) IS SELECT * FROM emp WHERE ...
any of the following statements opens the cursor:
OPEN emp_cur('LEE');
OPEN emp_cur('BLAKE', 300);
OPEN emp_cur(employee_name, 150);
OPEN emp_cur('TRUSDALE', my_comm);In the last example, an actual parameter in the OPEN statement has the same name as its corresponding formal parameter in the cursor declaration. To avoid confusion, use unique identifiers.
.
OPEN {cursor_variable_name | :host_cursor_variable_name}
FOR select_statement;
, except that select_statement cannot have an INTO clause.
/* anonymous PL/SQL block in host environment */ BEGIN OPEN :emp_cv FOR SELECT * FROM emp; OPEN :dept_cv FOR SELECT * FROM dept; OPEN :grade_cv FOR SELECT * FROM salgrade; OPEN :pay_cv FOR SELECT * FROM payroll; OPEN :ins_cv FOR SELECT * FROM insurance; END;
Other OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.
Unlike cursors, cursor variables do not take parameters. No flexibility is lost, however, because you can pass whole queries (not just parameters) to a cursor variable.
You can pass a cursor variable to PL/SQL by calling a stored procedure that declares a cursor variable as one of its formal parameters. However, remote subprograms on another server cannot accept the values of cursor variables. Therefore, you cannot use a remote procedure call (RPC) to open a cursor variable.
When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the IN OUT mode. That way, the subprogram can pass an open cursor back to the caller.
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;To centralize data retrieval, you can group type-compatible queries in a stored procedure. When called, the following packaged procedure opens the cursor variable emp_cv for the chosen query:
CREATE PACKAGE emp_data AS
TYPE GenericCurTyp IS REF CURSOR;
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;For more flexibility, you can pass a cursor variable to a stored procedure that executes queries with different return types, as follows:
CREATE PACKAGE BODY emp_data AS
PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,
choice IN NUMBER) IS
BEGIN
IF choice = 1 THEN
OPEN generic_cv FOR SELECT * FROM emp;
ELSIF choice = 2 THEN
OPEN generic_cv FOR SELECT * FROM dept;
ELSIF choice = 3 THEN
OPEN generic_cv FOR SELECT * FROM salgrade;
END IF;
END open_cv;
END emp_data;
PACKAGE package_name IS
{object_declaration | spec_declaration}
[{object_declaration | spec_declaration}]...
END [package_name];package_body ::=
PACKAGE BODY package_name IS
[[object_declaration [object_declaration] ...]
[body_declaration [body_declaration] ...]]
[BEGIN
seq_of_statements]
END [package_name];object_declaration ::=
{ constant_declaration
| cursor_declaration
| exception_declaration
| plsql_table_declaration
| record_declaration
| variable_declaration}spec_declaration ::=
{ cursor_specification
| function_specification
| procedure_specification}body_declaration ::=
{ cursor_body
| function_body
| procedure_body}
.
.
.
.
.
.
.
.
.
.
.
.
.
Most packages have a specification and a body. The specification is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the specification.
Only subprograms and cursors have an underlying implementation (definition). So, if a specification declares only types, constants, variables, and exceptions, the package body is unnecessary. However, the body can still be used to initialize objects declared in the specification, as the following example shows:
CREATE PACKAGE emp_actions AS ... number_hired INTEGER; END emp_actions; CREATE PACKAGE BODY emp_actions AS BEGIN number_hired := 0; END emp_actions;
You can code and compile a specification without its body. Once the specification has been compiled, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application.
Furthermore, you can debug, enhance, or replace a package body without changing the interface (package specification) to the package body. That means you need not recompile calling programs.
Cursors and subprograms declared in a package specification must be defined in the package body. Other program objects declared in the package specification cannot be redeclared in the package body.
To match subprogram specifications and bodies, PL/SQL does a token-by-token comparison of their headers. So, except for white space, the headers must match word for word. Otherwise, PL/SQL raises an exception.
The attributes EXISTS, PRIOR, NEXT, and DELETE take parameters. Each parameter must be an expression that yields a BINARY_INTEGER value or a value implicitly convertible to that datatype.
DELETE acts like a procedure, which is called as a statement. The other PL/SQL table attributes act like a function, which is called as part of an expression. For more information, see "Using PL/SQL Table Attributes" .
plsql_table_name{ .COUNT
| .DELETE[(index[, index])]
| .EXISTS(index)
| .FIRST
| .LAST
| .NEXT(index)
| .PRIOR(index)}
.
DELETE lets you free the resources held by a PL/SQL table. DELETE(n) and DELETE(m, n) let you prune a PL/SQL table. If an element to be deleted does not exist, DELETE simply skips it; no exception is raised.
The amount of memory allocated to a PL/SQL table can increase or decrease dynamically. As you delete elements, memory is freed page by page. If you delete the entire PL/SQL table, all the memory is freed.
You can use EXISTS to avoid the exception NO_DATA_FOUND, which is raised when you reference a nonexistent element.
You can use PRIOR or NEXT to traverse PL/SQL tables from which some elements have been deleted.
ename_tab.DELETE(20, 30);
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 emp_tab.FIRST .. emp_tab.LAST LOOP ... END LOOP;
In the following example, PL/SQL executes the assignment statement only if the element sal_tab(i) exists:
IF sal_tab.EXISTS(i) THEN sal_tab(i) := sal_tab(i) + 500; ELSE RAISE salary_missing; END IF;
You can use PRIOR or NEXT to traverse PL/SQL tables from which some elements have been deleted, as the following generic example shows:
DECLARE
...
i BINARY_INTEGER;
BEGIN
..
i := any_tab.FIRST; -- get index of first element
WHILE i IS NOT NULL LOOP
... -- process any_tab(i)
i := any_tab.NEXT(i); -- get index of next element
END LOOP;
END;
However, PL/SQL tables differ from arrays in two important ways. First, arrays have fixed lower and upper bounds, but PL/SQL tables are unbounded. So, the size of a PL/SQL table can increase dynamically. Second, arrays require consecutive index numbers, but PL/SQL tables do not. So, a PL/SQL table can be indexed by any series of integers. For more information, see "PL/SQL Tables" .
To create PL/SQL tables, you must take two steps. First, you define a TABLE type, then declare PL/SQL tables of that type.
TYPE table_type_name IS TABLE OF
{ cursor_name%ROWTYPE
| record_type_name
| record_name%TYPE
| scalar_type_name
| table_name%ROWTYPE
| table_name.column_name%TYPE
| variable_name%TYPE} [NOT NULL] INDEX BY BINARY_INTEGER;plsql_table_declaration ::=
plsql_table_name table_type_name;
.
A PL/SQL table is unbounded; its index can include any BINARY_ INTEGER value. So, you cannot initialize a PL/SQL table in its declaration. For example, the following declaration is illegal:
sal_tab SalTabTyp := (1500, 2750, 2000, 950, 1800); -- illegal
The INDEX BY clause must specify datatype BINARY_INTEGER, which has a magnitude range of -2147483647 .. 2147483647. If the element type is a record type, every field in the record must have a scalar datatype such as CHAR, DATE, or NUMBER.
You can declare PL/SQL tables as the formal parameters of functions and procedures. That way, you can pass PL/SQL tables to stored subprograms and from one subprogram to another.
PL/SQL tables follow the usual scoping and instantiation rules. In a package, PL/SQL tables are instantiated when you first reference the package and cease to exist when you end the database session. In a block or subprogram, local PL/SQL tables are instantiated when you enter the block or subprogram and cease to exist when you exit.
Every PL/SQL table has the attributes EXISTS, COUNT, FIRST, LAST, PRIOR, NEXT, and DELETE. They make PL/SQL tables easier to use and your applications easier to maintain. For more information, see "Using PL/SQL Table Attributes" .
The first reference to an element in a PL/SQL table must be an assignment. Until an element is assigned a value, it does not exist. If you reference a nonexistent element, PL/SQL raises the predefined exception NO_DATA_FOUND.
To reference elements in a PL/SQL table, you specify an index number using the following syntax:
plsql_table_name(index)
When calling a function that returns a PL/SQL table, you use the following syntax to reference elements in the table:
function_name(parameters)(index)
If the function result is a PL/SQL table of records, you use the following syntax to reference fields in a record:
function_name(parameters)(index).field_name
Currently, you cannot use the syntax above to call a parameterless function because PL/SQL does not allow empty parameter lists. That is, the following syntax is illegal:
function_name()(index) -- illegal; empty parameter list
Instead, declare a local PL/SQL table to which you can assign the function result, then reference the PL/SQL table directly.
You can retrieve Oracle data into a PL/SQL table in three ways: the SELECT INTO statement lets you select a single row of data; the FETCH statement or a cursor FOR loop lets you fetch multiple rows.
Using the SELECT INTO statement, you can select a column entry into a scalar element. Or, you can select an entire row into a record element. Using the FETCH statement or a cursor FOR loop, you can fetch an entire column of Oracle data into a PL/SQL table of scalars. Or, you can fetch an entire table of Oracle data into a PL/SQL table of records.
You cannot reference record variables in the VALUES clause. So, you cannot insert entire records from a PL/SQL table of records into rows in a database table.
With the Oracle Call Interface (OCI) or the Oracle Precompilers, you can bind host arrays to PL/SQL tables declared as the formal parameters of a subprogram. That allows you to pass host arrays to stored functions and procedures.
You can use a BINARY_INTEGER variable or compatible host variable to index the host arrays. Given the array subscript range m .. n, the corresponding PL/SQL table index range is always 1 .. n - m + 1. For example, if the array subscript range is 5 .. 10, the corresponding PL/SQL table index range is 1 .. (10 - 5 + 1) or 1 .. 6.
DECLARE
TYPE SalTabTyp IS TABLE OF emp.sal%TYPE
INDEX BY BINARY_INTEGER;Once you define type SalTabTyp, you can declare PL/SQL tables of that type, as follows:
sal_tab SalTabTyp;
The identifier sal_tab represents an entire PL/SQL table.
In the next example, you assign the sum of variables salary and increase to the tenth row in PL/SQL table sal_tab:
sal_tab(10) := salary * increase;
In the following example, you select a row from the database table dept into a record stored by the first element of the PL/SQL table dept_tab:
DECLARE
TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER;
dept_tab DeptTabTyp;
BEGIN
/* Select entire row into record stored by first element. */
SELECT * INTO dept_tab(1) FROM dept WHERE deptno = 10;
IF dept_tab(1).dname = 'ACCOUNTING' THEN
...
END IF;
...
END;In the final example, you fetch rows from a cursor into the PL/SQL table of records emp_tab:
DECLARE
TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
emp_tab EmpTabTyp;
i BINARY_INTEGER := 0;
CURSOR c1 IS SELECT * FROM emp;
BEGIN
OPEN c1;
LOOP
i := i + 1;
/* Fetch entire row into record stored by ith element. */
FETCH c1 INTO emp_tab(i);
EXIT WHEN c1%NOTFOUND;
-- process data record
END LOOP;
CLOSE c1;
END;
. A procedure has two parts: the specification and the body. The procedure specification begins with the keyword PROCEDURE and ends with the procedure name or a parameter list. Parameter declarations are optional. Procedures that take no parameters are written without parentheses.
The procedure body begins with the keyword IS and ends with the keyword END followed by an optional procedure name. The procedure body has three parts: an optional declarative part, an executable part, and an optional exception-handling part.
The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These objects are local and cease to exist when you exit the procedure. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains exception handlers, which deal with exceptions raised during execution.
PROCEDURE procedure_name (parameter_declaration[, parameter_declaration]...)];
procedure_body ::=
PROCEDURE procedure_name [(parameter_declaration[,
parameter_declaration]...)] IS
[[object_declaration [object_declaration] ...]
[subprogram_declaration [subprogram_declaration] ...]]
BEGIN
seq_of_statements
[EXCEPTION
exception_handler [exception_handler] ...]
END [procedure_name];parameter_declaration ::=
parameter_name [IN | OUT | IN OUT]
{ cursor_name%ROWTYPE
| cursor_variable_name%TYPE
| plsql_table_name%TYPE
| record_name%TYPE
| scalar_type_name
| table_name%ROWTYPE
| table_name.column_name%TYPE
| variable_name%TYPE} [{:= | DEFAULT} expression]object_declaration ::=
{ constant_declaration
| cursor_declaration
| cursor_variable_declaration
| exception_declaration
| plsql_table_declaration
| record_declaration
| variable_declaration}subprogram_declaration ::=
{function_declaration | procedure_declaration}
.
.
.
.
.
.
.
.
.
.
.
A procedure is called as a PL/SQL statement. For example, the procedure raise_salary might be called as follows:
raise_salary(emp_num, amount);
Inside a procedure, an IN parameter acts like a constant. Therefore, it cannot be assigned a value. An OUT parameter acts like an uninitialized variable. So, its value cannot be assigned to another variable or reassigned to itself. An IN OUT parameter acts like an initialized variable. Therefore, it can be assigned a value, and its value can be assigned to another variable. For summary information about the parameter modes, see Table 7 - 1 .
Before exiting a procedure, explicitly assign values to all OUT formal parameters. Otherwise, the values of corresponding actual parameters are indeterminate. If you exit successfully, PL/SQL assigns values to the actual parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.
Unlike OUT and IN OUT parameters, IN parameters can be initialized to default values. For more information, see "Parameter Default Values" .
You can write the procedure specification and body as a unit. Or, you can separate the procedure specification from its body. That way, you can hide implementation details by placing the procedure in a package. You can define procedures in a package body without declaring their specifications in the package specification. However, such procedures can be called only from inside the package.
Procedures can be defined using any Oracle tool that supports PL/SQL. To become available for general use, however, procedures must be CREATEd and stored in an Oracle database. You can issue the CREATE PROCEDURE statement interactively from SQL*Plus or Server Manager. For the full syntax of the CREATE PROCEDURE statement, see Oracle7 Server SQL Reference.
PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
old_balance REAL;
new_balance REAL;
overdrawn EXCEPTION;
BEGIN
SELECT bal INTO old_balance FROM accts WHERE acctno = acct_id;
new_balance := old_balance - amount;
IF new_balance < 0 THEN
RAISE overdrawn;
ELSE
UPDATE accts SET bal = new_balance WHERE acctno = acct_id;
END IF;
EXCEPTION
WHEN overdrawn THEN
...
END debit_account;In the following example, you call the procedure using named notation:
debit_account(amount => 500, acct_id => 10261);
. Normally, predefined exceptions are raised implicitly by the runtime system. However, RAISE statements can also raise predefined exceptions. User-defined exceptions must be raised explicitly by RAISE statements.
RAISE [exception_name];
.
When an exception is raised, if PL/SQL cannot find a handler for it in the current block, the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search. In the latter case, PL/SQL returns an unhandled exception error to the host environment.
Omitting the exception name in a RAISE statement, which is allowed only in an exception handler, reraises the current exception. When a parameterless RAISE statement executes in an exception handler, the first block searched is the enclosing block, not the current block.
IF quantity_on_hand = 0 THEN RAISE out_of_stock; END IF;
. To create records, you must take two steps. First, you define a RECORD type, then declare user-defined records of that type.
TYPE record_type_name IS RECORD (field_declaration[, field_declaration]...);
record_declaration ::=
record_name record_type_name;
field_declaration ::=
field_name
{ cursor_name%ROWTYPE
| cursor_variable_name%TYPE
| local_field_name%TYPE
| plsql_table_name%TYPE
| record_name%TYPE
| scalar_type_name
| table_name%ROWTYPE
| table_name.column_name%TYPE
| variable_name%TYPE} [[NOT NULL] {:= | DEFAULT} expression]
.
.
DECLARE
TYPE TimeTyp IS RECORD(
second SMALLINT := 0,
minute SMALLINT := 0,
hour SMALLINT := 0);The next example shows that you can use the %TYPE attribute to specify a field datatype. It also shows that you can add the NOT NULL constraint to any field declaration and so prevent the assigning of nulls to that field.
DECLARE
TYPE DeptRecTyp IS RECORD(
deptno NUMBER(2) NOT NULL,
dname dept.dname%TYPE,
loc dept.loc%TYPE);
dept_rec DeptRecTyp;To reference individual fields in a record, you use dot notation. For example, you might assign a value to the dname field in the dept_rec record as follows:
dept_rec.dname := 'PURCHASING';
Instead of assigning values separately to each field in a record, you can assign values to all fields at once. This can be done in two ways. First, PL/SQL lets you assign one record to another if they have the same datatype. Note, however, that even if their fields match exactly, a user-defined record and a %ROWTYPE record have different types. Second, you can assign a list of column values to a record by using the SELECT or FETCH statement. Just make sure the column names appear in the same order as the fields in your record.
You can declare and reference nested records. That is, a record can be the component of another record, as the following example shows:
DECLARE
TYPE TimeTyp IS RECORD(
minute SMALLINT,
hour SMALLINT);
TYPE MeetingTyp IS RECORD(
day DATE,
time TimeTyp, -- nested record
place CHAR(20),
purpose CHAR(50));
TYPE PartyTyp IS RECORD(
day DATE,
time TimeTyp, -- nested record
loc CHAR(15));
meeting MeetingTyp;
seminar MeetingTyp;
party PartyTyp;The next example shows that you can assign one nested record to another if they have the same datatype:
seminar.time := meeting.time;
Such assignments are allowed even if the containing records have different datatypes.
User-defined records follow the usual scoping and instantiation rules. In a package, they are instantiated when you first reference the package and cease to exist when you exit the application or end the database session. In a block or subprogram, they are instantiated when you enter the block or subprogram and cease to exist when you exit the block or subprogram.
Like scalar variables, user-defined records can be declared as the formal parameters of procedures and functions. The restrictions that apply to scalar parameters also apply to user-defined records.
You can specify a RECORD type in the RETURN clause of a function specification. That allows the function to return a user-defined record of the same type. When calling a function that returns a user-defined record, you use the following syntax to reference fields in the record:
function_name(parameters).field_name
To reference nested fields in a record returned by a function, you use the following syntax:
function_name(parameters).field_name.nested_field_name
Currently, you cannot use the syntax above to call a parameterless function because PL/SQL does not allow empty parameter lists. That is, the following syntax is illegal:
function_name().field_name -- illegal; empty parameter list
You cannot just drop the empty parameter list because the following syntax is also illegal:
function_name.field_name -- illegal; no parameter list
Instead, declare a local user-defined record to which you can assign the function result, then reference its fields directly.
DECLARE
TYPE DeptRecTyp IS RECORD(
deptno NUMBER(2),
dname CHAR(14),
loc CHAR(13));
dept_rec DeptRecTyp;
...
BEGIN
SELECT deptno, dname, loc INTO dept_rec FROM dept
WHERE deptno = 20;
...
END;
.
RETURN [expression];
A subprogram can contain several RETURN statements, none of which need be the last lexical statement. Executing any of them completes the subprogram immediately. However, it is poor programming practice to have multiple exit points in a subprogram.
In procedures, a RETURN statement cannot contain an expression. The statement simply returns control to the caller before the normal end of the procedure is reached.
However, in functions, a RETURN statement must contain an expression, which is evaluated when the RETURN statement is executed. The resulting value is assigned to the function identifier. Therefore, a function must contain at least one RETURN statement. Otherwise, PL/SQL raises the predefined exception PROGRAM_ERROR at run time.
The RETURN statement can also be used in an anonymous block to exit the block (and all enclosing blocks) immediately, but the RETURN statement cannot contain an expression.
FUNCTION balance (acct_id INTEGER) RETURN REAL IS
acct_bal REAL;
BEGIN
SELECT bal INTO acct_bal FROM accts WHERE acctno = acct_id;
RETURN acct_bal;
END balance;
.
ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];
.
An implicit savepoint is marked before executing an INSERT, UPDATE, or DELETE statement. If the statement fails, a rollback to the implicit savepoint is done. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back. For more information, see "Unhandled Exceptions" .
You can use the %ROWTYPE attribute in variable declarations as a datatype specifier. Variables declared using %ROWTYPE are treated like those declared using a datatype name. For more information, see "Using %ROWTYPE" .
{cursor_name | cursor_variable_name | table_name}%ROWTYPE
emp_rec emp%ROWTYPE;
The column values returned by the SELECT statement are stored in fields. To reference a field, you use dot notation. For example, you might reference the deptno field as follows:
IF emp_rec.deptno = 20 THEN ...
You can assign the value of an expression to a specific field, as the following example shows:
emp_rec.sal := average * 1.15;
There are two ways to assign values to all fields in a record at once. First, PL/SQL allows aggregate assignment between entire records if their declarations refer to the same table or cursor.
Second, you can assign a list of column values to a record by using the SELECT or FETCH statement. The column names must appear in the order in which they were defined by the CREATE TABLE or CREATE VIEW statement. Select-items fetched from a cursor associated with %ROWTYPE must have simple names or, if they are expressions, must have aliases.
DECLARE emp_rec emp%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec c1%ROWTYPE;
In the next example, you select a row from the emp table into a %ROWTYPE record:
DECLARE
emp_rec emp%ROWTYPE;
...
BEGIN
SELECT * INTO emp_rec FROM emp WHERE empno = my_empno;
IF (emp_rec.deptno = 20) AND (emp_rec.sal > 2000) THEN
...
END IF;
END;
.
SAVEPOINT savepoint_name;
.
If you mark a savepoint within a recursive subprogram, new instances of the SAVEPOINT statement are executed at each level in the recursive descent. However, you can only roll back to the most recently marked savepoint.
Savepoint names can be reused within a transaction. This moves the savepoint from its old position to the current point in the transaction.
An implicit savepoint is marked before executing an INSERT, UPDATE, or DELETE statement. If the statement fails, a rollback to the implicit savepoint is done. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back. For more information, see "Unhandled Exceptions" .
By default, the number of active savepoints per user process is limited to 5. You or your DBA can raise the limit (up to 255) by increasing the value of the Oracle initialization parameter SAVEPOINTS.
SELECT [DISTINCT | ALL] {* | select_item[, select_item]...}
INTO {variable_name[, variable_name]... | record_name}
FROM {table_reference | (subquery)} [alias]
[, {table_reference | (subquery)} [alias]]...
rest_of_select_statement;select_item ::=
{ function_name[(parameter_name[, parameter_name]...)]
| NULL
| numeric_literal
| [schema_name.]{table_name | view_name}.*
| [[schema_name.]{table_name. | view_name.}]column_name
| sequence_name.{CURRVAL | NEXTVAL}
| 'text'} [[AS] alias]table_reference ::=
[schema_name.]{table_name | view_name}[@dblink_name]
When you use a SELECT INTO statement to assign values to variables, it should return only one row. If it returns more than one row, you get the following results:
SELECT ename, job, sal INTO my_ename, my_job, my_sal FROM emp WHERE empno = my_empno;
.
SET TRANSACTION
{ READ ONLY
| READ WRITE
| ISOLATION LEVEL {SERIALIZABLE | READ COMMITTED}
| USE ROLLBACK SEGMENT rollback_segment_name};
When you specify READ COMMITTED, if a transaction includes SQL data manipulation statements that require row locks held by another transaction, the statement waits until the row locks are released.
Only the SELECT INTO, OPEN, FETCH, CLOSE, LOCK TABLE, COMMIT, and ROLLBACK statements are allowed in a read-only transaction. For example, including an INSERT statement raises an exception. Also, queries cannot be FOR UPDATE.
COMMIT; -- end previous transaction SET TRANSACTION READ ONLY; SELECT ... FROM emp WHERE ... SELECT ... FROM dept WHERE ... SELECT ... FROM emp WHERE ... COMMIT; -- end read-only transaction
.
SQL{%FOUND | %ISOPEN | %NOTFOUND | %ROWCOUNT}
The values of cursor attributes always refer to the most recently executed SQL statement, wherever that statement appears. It might be in a different scope. So, if you want to save an attribute value for later use, assign it to a Boolean variable immediately.
If a SELECT INTO statement fails to return a row, PL/SQL raises the predefined exception NO_DATA_FOUND whether you check SQL%NOTFOUND on the next line or not.
However, a SELECT INTO statement that calls a SQL group function never raises NO_DATA_FOUND. That is because group functions such as AVG and SUM always return a value or a null. In such cases, SQL%NOTFOUND yields FALSE.
UPDATE emp SET sal = sal * 1.05 WHERE empno = my_empno; IF SQL%NOTFOUND THEN INSERT INTO emp VALUES (my_empno, my_ename, ...); END IF;
In the next example, you use %ROWCOUNT to raise an exception if more than 100 rows are deleted:
DELETE FROM parts WHERE status = 'OBSOLETE'; IF SQL%ROWCOUNT > 100 THEN -- more than 100 rows were deleted RAISE large_deletion; END IF;
For internal exceptions, SQLCODE returns the number of the associated Oracle error. The number that SQLCODE returns is negative unless the Oracle error is no data found, in which case SQLCODE returns +100.
For user-defined exceptions, SQLCODE returns +1 unless you used the pragma EXCEPTION_INIT to associate the exception with an Oracle error number, in which case SQLCODE returns that error number. For more information, see "Using SQLCODE and SQLERRM" on page 6 - 18.
SQLCODE
INSERT INTO errors VALUES (SQLCODE, ...);
Instead, you must assign the value of SQLCODE to a local variable, then use the variable in the SQL statement, as follows:
DECLARE
my_sqlcode NUMBER;
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
my_sqlcode := SQLCODE;
INSERT INTO errors VALUES (my_sqlcode, ...);
END;SQLCODE is especially useful in the OTHERS exception handler because it lets you identify which internal exception was raised.
For internal exceptions, SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code.
For user-defined exceptions, SQLERRM returns the message user-defined exception unless you used the pragma EXCEPTION_INIT to associate the exception with an Oracle error number, in which case SQLERRM returns the corresponding error message. For more information, see "Using SQLCODE and SQLERRM" .
SQLERRM [(error_number)]
ORA-0000: normal, successful completion
Passing a positive number to SQLERRM always returns the message
User-Defined Exception
unless you pass +100, in which case SQLERRM returns the following message:
ORA-01403: no data found
You cannot use SQLERRM directly in a SQL statement. For example, the following statement is illegal:
INSERT INTO errors VALUES (SQLERRM, ...);
Instead, you must assign the value of SQLERRM to a local variable, then use the variable in the SQL statement, as follows:
DECLARE my_sqlerrm CHAR(150); ... BEGIN ... EXCEPTION ... WHEN OTHERS THEN my_sqlerrm := SUBSTR(SQLERRM, 1, 150); INSERT INTO errors VALUES (my_sqlerrm, ...); END;
The string function SUBSTR ensures that a VALUE_ERROR exception (for truncation) is not raised when you assign the value of SQLERRM to my_sqlerrm. SQLERRM is especially useful in the OTHERS exception handler because it lets you identify which internal exception was raised.
.
{ cursor_variable_name
| plsql_table_name
| record_name
| record_name.field_name
| table_name.column_name
| variable_name}%TYPE
.
UPDATE {table_reference | (subquery)} [alias]
SET { column_name = {sql_expression | (subquery)}
| (column_name[, column_name]...) = (subquery)}
[, { column_name = {sql_expression | (subquery)}
| (column_name[, column_name]...) = (subquery)}]...
[WHERE {search_condition | CURRENT OF cursor_name}]; table_reference ::=
[schema_name.]{table_name | view_name}[@dblink_name]
In the following example, you increase every employee's salary by 10%. The original value of the sal column is multiplied by 1.1, then the result is assigned to the sal column.
UPDATE emp SET sal = sal * 1.1;
The column values returned by subquery are assigned to the columns in the column_name list in order. Thus, the first value is assigned to the first column in the column_name list, the second value is assigned to the second column in the column_name list, and so on.
In the following correlated query, the column item_id is assigned the value stored in item_num, and the column price is assigned the value stored in item_price:
UPDATE inventory inv -- alias
SET (item_id, price) = (SELECT item_num, item_price
FROM item_table
WHERE item_name = inv.item_name);
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.
The implicit SQL cursor and the cursor attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN let you access useful information about the execution of an UPDATE statement.
An UPDATE statement might update one or more rows or no rows. If one or more rows are updated, you get the following results:
UPDATE emp SET sal = sal * 1.10 WHERE (job = 'ANALYST' OR job = 'CLERK') AND DEPTNO = 20;
In the next example, an employee named Ford is promoted to the position of Analyst and her salary is raised by 15%:
UPDATE emp SET job = 'ANALYST', sal = sal * 1.15 WHERE ename = 'FORD';
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |