Release 8.0
A54654_01
 Library |
 Product |
 Contents |
 Index |
| PL/SQL User's Guide and Reference Release 8.0 A54654_01 |
|
This is an executable statement. For the syntax of statement, see "Blocks" on page 11-7.
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" on page 6-4. 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:
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" on page 6-8.
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
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" on page 3-6.
An unconditional EXIT statement (that is, one without a WHEN clause) exits the current loop immediately. Execution resumes with the statement following the loop.
This identifies the loop to be exited. You can exit not only the current loop but any enclosing labeled loop.
This is an expression that yields the Boolean value TRUE, FALSE, or NULL. It is evaluated with each iteration of the loop in which the EXIT WHEN statement appears. If the expression yields TRUE, the current loop (or the loop labeled by label_name) is exited immediately. For the syntax of boolean_expression, see "Expressions" on page 11-59.
The EXIT statement can be used only inside a loop.
PL/SQL allows you to code an infinite loop. For example, the following loop will never terminate 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.
The EXIT statement in the following example is illegal because you cannot exit from a block directly; you can exit only from a 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;
Expressions, LOOP Statements
An expression is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable.
The PL/SQL compiler determines the datatype of an expression from the types of the variables, constants, literals, and operators that comprise the expression. Every time the expression is evaluated, a single value of that type results. For more information, see "Expressions and Comparisons" on page 2-40.
This is an expression that yields the Boolean value TRUE, FALSE, or NULL.
This is an expression that yields a character or character string.
This is an expression that yields a date/time value.
This is an expression that yields an integer or real value.
These are logical operators, which follow the tri-state logic of Table 2-3 on page 2-41. 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" on page 2-41.
This identifies a constant of type BOOLEAN, which must be initialized to the value TRUE or FALSE or the non-value NULL. Arithmetic operations on Boolean constants are illegal.
This is any function call that returns a Boolean value.
This is the predefined value TRUE or FALSE or the non-value NULL, which stands for a missing, unknown, or inapplicable value. You cannot insert the value TRUE or FALSE into a database column.
This identifies a variable of type BOOLEAN. Only the values TRUE and FALSE and the non-value NULL can be assigned to a BOOLEAN variable. You cannot select or fetch column values into a BOOLEAN variable. Also, arithmetic operations on Boolean variables are illegal.
This operator allows you to compare expressions. For the meaning of each operator, see "Comparison Operators" on page 2-43.
This comparison operator returns the Boolean value TRUE if its operand is null, or FALSE if its operand is not null.
This comparison operator compares a character value to a pattern. Case is significant. LIKE returns the Boolean value TRUE if the character patterns match, or FALSE if they do not match.
This is a character string compared by the LIKE operator to a specified string value. It can include two special-purpose characters called wildcards. An underscore (_) matches exactly one character; a percent sign (%) matches zero or more characters.
This comparison operator tests whether a value lies in a specified range. It means "greater than or equal to low value and less than or equal to high value."
This comparison operator tests set membership. It means "equal to any member of." The set can contain nulls, but they are ignored. Also, expressions of the form
value NOT IN set
yield FALSE if the set contains a null.
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 cursor variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. Host cursor variables must be prefixed with a colon.
This identifies a cursor opened implicitly by Oracle to process a SQL data manipulation statement. The implicit SQL cursor always refers to the most recently executed SQL statement.
These are cursor attributes. When appended to the name of a cursor or cursor variable, these attributes return useful information about the execution of a multi-row query. You can also append them to the implicit SQL cursor. For more information, see "Using Cursor Attributes" on page 5-36.
These are collection methods. When appended to the name of a collection, these methods return useful information. For example, EXISTS(n) returns TRUE if the nth element of a collection exists. Otherwise, EXISTS(n) returns FALSE. For more information, see "Collection Methods" on page 11-16.
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 variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host variable must be implicitly convertible to the appropriate PL/SQL datatype. Also, host variables must be prefixed with a colon.
This identifies an indicator variable declared in a PL/SQL host environment and passed to PL/SQL. Indicator variables must be prefixed with a colon. An indicator variable "indicates" the value or condition of its associated host variable. For example, in the Oracle Precompiler environment, indicator variables can detect nulls or truncated values in output host variables.
This identifies a previously declared constant that stores a numeric value. It must be initialized to a numeric value or a value implicitly convertible to a numeric value.
This is a function call that returns a numeric value or a value implicitly convertible to a numeric value.
This is a literal that represents a numeric value or a value implicitly convertible to a numeric value.
This identifies a nested table, index-by table, or varray previously declared within the current scope.
This identifies a previously declared variable that stores a numeric value.
This keyword represents a null; it stands for a missing, unknown, or inapplicable value. When NULL is used in a numeric or date expression, the result is always a null.
This is an expression that must yield a numeric value.
These symbols are the addition, subtraction, division, multiplication, and exponentiation operators, respectively.
This identifies a previously declared constant that stores a character value. It must be initialized to a character value or a value implicitly convertible to a character value.
This is a function call that returns a character value or a value implicitly convertible to a character value.
This is a literal that represents a character value or a value implicitly convertible to a character value.
This identifies a previously declared variable that stores a character value.
This is the concatenation operator. As the following example shows, the result of concatenating string1 with string2 is a character string that contains string1 followed by string2:
'Good' || ' morning!' 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.
This identifies a previously declared constant that stores a date value. It must be initialized to a date value or a value implicitly convertible to a date value.
This is a function call that returns a date value or a value implicitly convertible to a date value.
This is a literal that represents a date value or a value implicitly convertible to a date value.
This identifies a previously declared variable that stores a date value.
In a Boolean expression, you can only compare values that have compatible datatypes. For more information, see "Datatype Conversion" on page 2-25.
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.
Several examples of expressions follow:
(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
Assignment Statement, Constants and Variables, EXIT Statement, IF Statement, LOOP Statements
An external procedure is a third-generation-language routine stored in a dynamic link library (or shared library that loads dynamically), registered with PL/SQL, and called by you to do special-purpose processing. At run time, PL/SQL loads the library dynamically, then calls the routine as if it were a PL/SQL subprogram. To safeguard your database, the routine runs in a separate address space.
A dynamic link libray (DLL) is an operating-system file that stores external procedures. For safety, your DBA controls access to the DLL. Using the CREATE LIBRARY statement, the DBA creates a schema object called an alias library, which represents the DLL. Then, if you are an authorized user, the DBA grants you EXECUTE privileges on the alias library. After registering an external procedure, you can call it from any PL/SQL program. It executes with the privileges granted to your userid.
For more information, see Chapter 10: External Procedures.
This clause specifies the name of the alias library. The name is a PL/SQL identifier. So, if you enclose the name in double quotes, it becomes case sensitive. You must have EXECUTE privileges on the alias library.
This clause specifies the external procedure to be called. If you enclose the procedure name in double quotes, it becomes case sensitive. If you omit this clause, the procedure name defaults to the upper case name of the PL/SQL subprogram.
This clause specifies the third-generation language in which the external procedure was written. Currently, the only language name allowed is C. If you omit this clause, the language name defaults to C.
This clause specifies the Windows NT calling standard (C or Pascal) under which the external procedure was compiled. (Under the Pascal Calling Standard, arguments are reversed on the stack and the called function must pop the stack.) If you omit this clause, the calling standard defaults to C.
This clause specifies that a context pointer will be passed to the external procedure. The context data structure is opaque to the external procedure but is available to service routines called by the external procedure.
By including the WITH CONTEXT clause, you can give an external procedure access to information about parameters, exceptions, memory allocation, and the user environment.
This clause specifies the positions and datatypes of parameters passed to the external procedure. It can also specify parameter properties such as current length and maximum length, and the preferred parameter passing method (by value or by reference).
You do not pass parameters to an external procedure directly. Instead, you pass them to the PL/SQL subprogram that registered the external procedure. So, you must specify PL/SQL datatypes for the parameters. For guidance, see Table 10-1 on page 10-8. Each PL/SQL datatype maps to a default external datatype. (In turn, each external datatype maps to a C datatype.)
To avoid errors when declaring C prototype parameters, refer to Table 10-2 on page 10-9, which shows the C datatype to specify for a given external datatype and PL/SQL parameter mode.
You can also use the PARAMETERS clause to pass additional information about PL/SQL formal parameters and function results to an external procedure. You do that by specifying the following properties INDICATOR, LENGTH, MAXLEN, CHARSETID, CHARSETFORM.
This parameter cannot be repeated. If you include the WITH CONTEXT and PARAMETERS clauses, you must specify this parameter, which shows the position of the context pointer in the parameter list. If you omit the PARAMETERS clause, the context pointer is the first parameter passed to the external procedure.
This property lets you associate a null/not null indicator with a formal parameter. If the PL/SQL subprogram is a function, you can also associate an indicator with the return value.
These properties let you specify parameters that store the current length and maximum length of a formal parameter. With parameters of type CHAR, RAW, LONG RAW, or VARCHAR2, you must use the property LENGTH.
With CHAR, CLOB, and VARCHAR2 parameters, these properties let you pass a national character set ID and form to an external procedure. The OCI attribute names for these properties are OCI_ATTR_CHARSET_ID and OCI_ATTR_CHARSET_FORM.
In C, you can pass IN parameters by value (the value of the parameter is passed) or by reference (a pointer to the value is passed). When an external procedure expects a pointer, BY REF lets you pass the parameter by reference.
When calling external procedures, never write to IN parameters or overflow the capacity of OUT parameters. (PL/SQL does no runtime checks for these error conditions.) Likewise, never read an OUT parameter or a function result. Also, always assign a value to IN OUT and OUT parameters and to function results. Otherwise, your external procedure will not return successfully.
If you include the WITH CONTEXT and PARAMETERS clauses, you must specify the parameter CONTEXT, which shows the position of the context pointer in the parameter list. If you omit the PARAMETERS clause, the context pointer is the first parameter passed to the external procedure.
If you include the PARAMETERS clause and the external routine is a function, you must specify the parameter RETURN (not RETURN property) in the last position.
For every formal parameter, there must be a corresponding parameter in the PARAMETERS clause. Also, make sure that the datatypes of parameters in
the PARAMETERS clause are compatible with those in the C prototype because no implicit conversions are done.
A parameter for which you specify INDICATOR or LENGTH has the same parameter mode as the corresponding formal parameter. But, a parameter for which you specify MAXLEN, CHARSETID, or CHARSETFORM is always treated like an IN parameter, even if you also specify BY REF.
To check the value of an indicator, you can use the constants OCI_IND_NULL and OCI_IND_NOTNULL. If the indicator equals OCI_IND_NULL, the associated parameter or function result is null. If the indicator equals OCI_IND_NOTNULL, the parameter or function result is not null.
With a parameter of type CHAR, LONG RAW, RAW, or VARCHAR2, you must use the property LENGTH. Also, if that parameter is IN OUT or OUT and null, you must set the length of the corresponding C parameter to zero.
Currently, the following restrictions apply to external procedures:
Assume that C routine c_gcd, which finds the greatest common divisor of two numbers, is stored in DLL utils.dll and that you have EXECUTE privileges on alias library c_utils. The C prototype for c_gcd follows:
int c_gcd(int x_val, int y_val);
In the following example, you write a PL/SQL standalone function named gcd that registers the C routine c_gcd as an external function:
CREATE FUNCTION gcd (
-- find greatest common divisor of x and y
x BINARY_INTEGER,
y BINARY_INTEGER)
RETURN BINARY_INTEGER AS EXTERNAL
LIBRARY c_utils
NAME "c_gcd" -- quotes preserve lower case
LANGUAGE C;
In the example below, you call PL/SQL function gcd from an anonymous block. PL/SQL passes the two integer parameters to external function c_gcd, which returns their greatest common divisor.
DECLARE
g INTEGER;
a INTEGER;
b INTEGER;
...
BEGIN
...
g := gcd(a, b); -- call function
IF g IN (2,4,8) THEN ...
The following example uses the PARAMETERS clause to specify properties for the PL/SQL formal parameters and function result:
CREATE FUNCTION parse (
x IN BINARY_INTEGER,
Y IN OUT CHAR)
RETURN CHAR AS EXTERNAL
LIBRARY c_utils
NAME "c_parse"
LANGUAGE C
CALLING STANDARD PASCAL
PARAMETERS (
x, -- stores value of x
x INDICATOR, -- stores null status of x
y, -- stores value of y
y LENGTH, -- stores current length of y
y MAXLEN, -- stores maximum length of y
RETURN INDICATOR,
RETURN);
The corresponding C prototype follows:
char * c_parse(int x, short x_ind, char *y, int *y_len,
int *y_maxlen, short *retind);
Functions, Procedures
The FETCH statement retrieves rows of data one at a time from the result set of a multi-row query. The data is stored in variables or fields that correspond to the columns selected by the query. For more information, see "Managing Cursors" on page 5-9.
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 identifies a list of previously declared scalar variables into which column values are fetched. For each column value returned by the query associated with the cursor or cursor variable, there must be a corresponding, type-compatible variable in the list.
This identifies a user-defined or %ROWTYPE record into which rows of values are fetched. For each column value returned by the query associated with the cursor or cursor variable, there must be a corresponding, type-compatible field in the record.
You must use either a cursor FOR loop or the FETCH statement to process a multi-row query.
Any variables in the WHERE clause of the query are evaluated only when the cursor or cursor variable is opened. To change the result set or the values of variables in the query, you must reopen the cursor or cursor variable with the variables set to their new values.
To reopen a cursor, you must close it first. However, you need not close a cursor variable before reopening it.
You can use different INTO lists on separate fetches with the same cursor or cursor variable. Each fetch retrieves another row and assigns values to the target variables.
If you FETCH past the last row in the result set, the values of the target fields or variables are indeterminate and the %NOTFOUND attribute yields TRUE.
PL/SQL makes sure the return type of a cursor variable is compatible with the INTO clause of the FETCH statement. For each column value returned by the query associated with the cursor variable, there must be a corresponding, type-compatible field or variable in the INTO clause. Also, the number of fields or variables must equal the number of column values.
When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the IN (or 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" on page 5-36.
PL/SQL raises the predefined exception INVALID_CURSOR if you try to fetch from a closed or never-opened cursor or cursor variable.
The following example shows that any variables in the query associated with a cursor are evaluated only when the cursor is opened:
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 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. */
}
CLOSE Statement, Cursors, Cursor Variables, LOOP Statements, OPEN Statement, OPEN-FOR Statement
A function is a named program unit that takes parameters and returns a computed value. For more information, see "Functions" on page 7-5.
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 items are local and cease to exist when you exit the function. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains exception handlers, which deal with exceptions raised during execution.
This identifies a user-defined function.
This identifies a formal parameter, which is a variable declared in a function specification and referenced in the function body.
These parameter modes define the behavior of formal parameters. An IN parameter lets you pass values to the subprogram being called. An OUT parameter lets you return values to the caller of the subprogram. An IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller.
This is a type specifier. For the syntax of datatype, see "Constants and Variables" on page 11-29.
This operator or keyword allows you to initialize IN parameters to default values.
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.
This keyword introduces the RETURN clause, which specifies the datatype of the result value.
This specifies a user-defined datatype. For the syntax of type_definition, see "Blocks" on page 11-7.
This declares a program object. For the syntax of item_declaratoion, see "Blocks" on page 11-7.
This construct declares a function. For the syntax of function_declaration, see "Functions" on page 11-78.
This construct declares a procedure. For the syntax of procedure_declaration, see "Procedures" on page 11-119.
This construct associates an exception with a sequence of statements, which is executed when that exception is raised. For the syntax of exception_handler, see "Exceptions" on page 11-54.
Every function must contain at least one RETURN statement. Otherwise, PL/SQL raises the predefined exception PROGRAM_ERROR at run time.
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 packaged functions, you must use the pragma RESTRICT_REFERENCES to enforce the rules. For more information, see Oracle8 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 on page 7-15.
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. Also, functions should be free from side effects, which change the values of variables not local to the subprogram.
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 Enterprise Manager. For the full syntax of the CREATE FUNCTION statement, see Oracle8 Server SQL Reference.
The following function returns the balance of a specified bank account:
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;
Collections, Packages, Procedures, Records
The GOTO statement branches unconditionally to a statement label or block label. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. The GOTO statement transfers control to the labelled statement or block. For more information, see "GOTO Statement" on page 3-14.
This is an undeclared identifier that labels an executable statement or a
PL/SQL block. You use a GOTO statement to transfer control to the statement or block following <<label_name>>.
Some possible destinations of a GOTO statement are illegal. In particular, a GOTO statement cannot branch into an IF statement, LOOP statement, or sub-block. For example, the following GOTO statement is illegal:
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.
A GOTO label cannot precede just any keyword. It must precede an executable statement or a PL/SQL block. For example, the following GOTO statement is illegal:
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" on page 3-14.
The IF statement lets you execute a sequence of statements conditionally. Whether the sequence is executed or not depends on the value of a Boolean expression. For more information, see "Conditional Control: IF Statements" on page 3-2.
This is an expression that yields the Boolean value TRUE, FALSE, or NULL. It is associated with a sequence of statements, which is executed only if the expression yields TRUE.
This keyword associates the Boolean expression that precedes it with the sequence of statements that follows it. If the expression yields TRUE, the associated sequence of statements is executed.
This keyword introduces a Boolean expression to be evaluated if the expression following IF and all the expressions following any preceding ELSIFs yield FALSE or NULL.
If control reaches this keyword, the sequence of statements that follows it is executed.
There are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF. The simplest form of IF statement associates a Boolean expression with a sequence of statements enclosed by the keywords THEN and END IF. The sequence of statements is executed only if the expression yields TRUE. If the expression yields FALSE or NULL, the IF statement does nothing. In either case, control passes to the next statement.
The second form of IF statement adds the keyword ELSE followed by an alternative sequence of statements. The sequence of statements in the ELSE clause is executed only if the Boolean expression yields FALSE or NULL. Thus, the ELSE clause ensures that a sequence of statements is executed.
The third form of IF statement uses the keyword ELSIF to introduce additional Boolean expressions. If the first expression yields FALSE or NULL, the ELSIF clause evaluates another expression. An IF statement can have any number of ELSIF clauses; the final ELSE clause is optional. Boolean expressions are evaluated one by one from top to bottom. If any expression yields TRUE, its associated sequence of statements is executed and control passes to the next statement. If all expressions yield FALSE or NULL, the sequence in the ELSE clause is executed.
An IF statement never executes more than one sequence of statements because processing is complete after any sequence of statements is executed. However, the THEN and ELSE clauses can include more IF statements. That is, IF statements can be nested.
In the example below, if shoe_count has a value of 10, both the first and second Boolean expressions yield TRUE. Nevertheless, order_quantity is assigned the proper value of 50 because processing of an IF statement stops after an expression yields TRUE and its associated sequence of statements is executed. The expression associated with ELSIF is never evaluated and control passes to the INSERT statement.
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;
Expressions
The INSERT statement adds new rows of data to a specified database table or view. For a full description of the INSERT statement, see Oracle8 Server SQL Reference.
This identifies a table or view that must be accessible when you execute the INSERT statement, and for which you must have INSERT privileges. For the syntax of table_reference, see "DELETE Statement" on page 11-49.
This identifies a list of columns in a database table or view. Column names need not appear in the order in which they were defined by the CREATE TABLE or CREATE VIEW statement. However, no column name can appear more than once in the list. If the list does not include all the columns in a table, the missing columns are set to NULL or to a default value specified in the CREATE TABLE statement.
This is any expression valid in SQL. For more information, see Oracle8 Server SQL Reference.
This clause assigns the values of expressions to corresponding columns in the column list. If there is no column list, the first value is inserted into the first column defined by the CREATE TABLE statement, the second value is inserted into the second column, and so on.
There must be only one value for each column in the column list. 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.
The operand of THE is a subquery that returns a single column value to the INSERT statement. The column value must be a nested table. Operator THE informs Oracle that the value is a nested table, not a scalar value.
This is a select statement that provides a value or set of values to the INSERT statement. The syntax of subquery is like the syntax of select_into_statement defined in "SELECT INTO Statement" on page -137, except that subquery cannot have an INTO clause.
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.
This is a select statement that provides a value or set of values to the VALUES clause. The subquery must return only one row containing a value for every column in the column list or for every column in the table if there is no column list.
This clause lets you return values from the inserted row, thereby eliminating the need to SELECT the row afterward. You can retrieve the column values into variables and/or host variables.
All character and date literals in the VALUES list must be enclosed by single quotes ('). Numeric literals are not enclosed by quotes.
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:
If no rows are inserted, you get these results:
The following examples show various forms of INSERT statement:
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');
SELECT Statement
A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. The numeric literal 135 and the string literal 'hello world' are examples. For more information, see "Literals" on page 2-7.
This is an optionally signed whole number without a decimal point.
This is an optionally signed whole or fractional number with a decimal point.
This is one of the numerals 0 .. 9.
This is a member of the PL/SQL character set. For more information, see "Character Set" on page 2-2.
This is a predefined Boolean value.
This is a predefined non-value, which stands for a missing, unknown, or inapplicable value.
Two kinds of numeric literals can be used in arithmetic expressions: integers and reals. Numeric literals must be separated by punctuation. Space characters can be used in addition to the punctuation.
A character literal is an individual character enclosed by single quotes (apostrophes). Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols. PL/SQL is case sensitive within character literals. So, for example, PL/SQL considers the literals 'Q' and 'q' to be different.
A string literal is a sequence of zero or more characters enclosed by single quotes. The null string ('') contains zero characters. To represent an apostrophe within a string, write two single quotes. PL/SQL is case sensitive within string literals. So, for example, PL/SQL considers the literals 'white' and 'White' to be different.
Also, trailing blanks are significant within string literals, so '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.
Several examples of numeric literals follow:
25 6.34 7E2 25e-03 .1 1. +17 -4.4
Several examples of character literals follow:
'H' '&' ' ' '9' ']' 'g'
A few examples of string literals follow:
'$5,000'
'02-AUG-87'
'Don''t leave without saving your work.'
Constants and Variables, Expressions
The LOCK TABLE statement lets you lock entire database tables in a specified lock mode so that you can share or deny access to tables while maintaining their integrity. For more information, see "Using LOCK TABLE" on page 5-49.
This identifies a table or view that must be accessible when you execute the LOCK TABLE statement. For the syntax of table_reference, see "DELETE Statement" on page 11-49.
This parameter specifies the lock mode. It must be one of the following: ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE, SHARE, SHARE ROW EXCLUSIVE, or EXCLUSIVE.
This optional keyword tells Oracle not to wait if the table has been locked by another user. Control is immediately returned to your program, so it can do other work before trying again to acquire the lock.
If you omit the keyword NOWAIT, Oracle waits until the table is available; the wait has no set limit. Table locks are released when your transaction issues a commit or rollback.
A table lock never keeps other users from querying a table, and a query never acquires a table lock.
If your program includes SQL locking statements, make sure the Oracle users requesting locks have the privileges needed to obtain the locks. Your DBA can lock any table. Other users can lock tables they own or tables for which they have a privilege, such as SELECT, INSERT, UPDATE, or DELETE.
The following statement locks the accts table in shared mode:
LOCK TABLE accts IN SHARE MODE;
COMMIT Statement, ROLLBACK Statement, UPDATE Statement
LOOP statements execute a sequence of statements multiple times. The loop encloses the sequence of statements that is to be repeated. PL/SQL provides the following types of loop statements:
For more information, see "Iterative Control: LOOP and EXIT Statements" on page 3-6.
This is an undeclared identifier that optionally labels a loop. If used, label_name must be enclosed by double angle brackets and must appear at the beginning of the loop. Optionally, label_name can also appear at the end of the loop.
You can use label_name in an EXIT statement to exit the loop labelled by label_name.
You 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 simplest form of LOOP statement is the basic (or infinite) loop, which encloses a sequence of statements between the keywords LOOP and END LOOP. With each iteration of the loop, the sequence of statements is executed, then control resumes at the top of the loop. If further processing is undesirable or impossible, you can use the EXIT, GOTO, or RAISE statement to complete the loop. A raised exception will also complete the loop.
The WHILE-LOOP statement associates a Boolean expression with a sequence of statements enclosed by the keywords LOOP and END LOOP. Before each iteration of the loop, the expression is evaluated. If the expression yields TRUE, the sequence of statements is executed, then control resumes at the top of the loop. If the expression yields FALSE or NULL, the loop is bypassed and control passes to the next statement.
This is an expression that yields the Boolean value TRUE, FALSE, or NULL. It is associated with a sequence of statements, which is executed only if the expression yields TRUE. For the syntax of boolean_expression, see "Expressions" on page -59.
Whereas the number of iterations through a WHILE loop is unknown until the loop completes, the number of iterations through a FOR loop is known before the loop is entered. Numeric FOR loops iterate over a specified range of integers. (Cursor FOR loops, which iterate over the result set of a cursor, are discussed later.) The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP.
The range is evaluated when the FOR loop is first entered and is never re-evaluated. The sequence of statements in the loop is executed once for each integer in the range defined by lower_bound..upper_bound. After each iteration, the loop index is incremented.
This is an undeclared identifier that names the loop index (sometimes called a loop counter). Its scope is the loop itself. Therefore, you cannot reference the index outside the loop.
The implicit declaration of index_name overrides any other declaration outside the loop. So, another variable with the same name cannot be referenced inside the loop unless a label is used, as follows:
<<main>>
DECLARE
num NUMBER;
BEGIN
...
FOR num IN 1..10 LOOP
...
IF main.num > 5 THEN -- refers to the variable num,
... -- not to the loop index
END IF;
END LOOP;
END main;
Inside a loop, its index is treated like a constant. The index can appear in expressions, but cannot be assigned a value.
These are expressions that must yield integer values. The expressions are evaluated only when the loop is first entered.
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.
By default, iteration proceeds upward from the lower bound to the upper bound. However, if you use the keyword REVERSE, iteration proceeds downward from the upper bound to the lower bound. After each iteration, the loop index is decremented.
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;
A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed. Thus, the sequence of statements in the loop is executed once for each row that satisfies the query associated with cursor_name.
This identifies an explicit cursor previously declared within the current scope. When the cursor FOR loop is entered, cursor_name cannot refer to a cursor already opened by an OPEN statement or an enclosing cursor FOR loop.
This identifies an implicitly declared record. The record has the same structure as a row retrieved by cursor_name and is equivalent to a record declared as follows:
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 ...
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. For the syntax of cursor_parameter_declaration, see "Cursors" on page 11-45.
This is a query associated with an internal cursor unavailable to you. PL/SQL automatically declares, opens, fetches from, and closes the internal cursor. Because select_statement is not an independent statement, the implicit SQL cursor does not apply to it.
The syntax of select_statement is like the syntax of select_into_ statement defined in "SELECT INTO Statement" on page -137, except that select_statement cannot have an INTO clause.
You can use the EXIT WHEN statement to exit any loop prematurely. If the Boolean expression in the WHEN clause yields TRUE, the loop is exited immediately.
When you exit a cursor FOR loop, the cursor is closed automatically even if you use an EXIT or GOTO statement to exit the loop prematurely. The cursor is also closed automatically if an exception is raised inside the loop.
The following cursor FOR loop calculates a bonus, then inserts the result into a database table:
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;
Cursors, EXIT Statement, FETCH Statement, OPEN Statement, %ROWTYPE Attribute
The NULL statement explicitly specifies inaction; it does nothing other than pass control to the next statement. In a construct allowing alternative actions, the NULL statement serves as a placeholder. For more information, see "NULL Statement" on page 3-17.
The NULL statement improves readability by making the meaning and action of conditional statements clear. It tells readers that the associated alternative has not been overlooked, but that indeed no action is necessary.
Each clause in an IF statement must contain at least one executable statement. The NULL statement meets this requirement. So, you can use the NULL statement in clauses that correspond to circumstances in which no action is taken. The NULL statement and Boolean non-value NULL are unrelated.
In the following example, the NULL statement emphasizes that only salespeople receive commissions:
IF job_title = 'SALESPERSON' THEN
compute_commission(emp_id);
ELSE
NULL;
END IF;
In the next example, the NULL statement shows that no action is taken for unnamed exceptions:
EXCEPTION
WHEN ZERO_DIVIDE THEN
ROLLBACK;
WHEN OTHERS THEN
NULL;
An object type is a user-defined composite datatype that encapsulates a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are called attributes. The functions and procedures that characterize the behavior of the object type are called methods.
Currently, you cannot define object types within PL/SQL. They must be CREATEd and stored in an Oracle database, where they can be shared by many programs. When you define an object type (in SQL*Plus for example) using the CREATE TYPE statement, you create an abstract template for some real-world object. The template specifies only those attributes and behaviors the object will need in the application environment.
The data structure formed by the set of attributes is public (visible to client programs). However, well-behaved programs do not manipulate it directly. Instead, they use the set of methods provided. That way, the data is kept in a proper state. At run time, when the data structure is filled with values, you have created an instance of an object type. You can create as many instances (usually called objects) as you need. For more information, see Chapter 9: Object Types.
This identifies a user-defined type specifier, which is used in subsequent declarations of objects.
This identifies an object attribute. The name must be unique within the object type (but can be reused in other object types). You cannot initialize an attribute in its declaration using the assignment operator or DEFAULT clause. Also, you cannot impose the NOT NULL constraint on an attribute.
This is any Oracle datatype except LONG, LONG RAW, MLSLABEL, NCHAR, NCLOB, NVARCHAR2, ROWID, the PL/SQL-specific types BINARY_INTEGER (and its subtypes), BOOLEAN, PLS_INTEGER, RECORD, REF CURSOR, %TYPE, and %ROWTYPE, and types defined inside a PL/SQL package.
This keyword allows you to declare a function or procedure in an object type specification. The method cannot have the same name as the object type or any of its attributes.
Like packaged subprograms, methods have two parts: a specification and a body. The specification consists of a method name, an optional parameter list, and, for functions, a return type. The body is the code that executes to perform a specific operation.
For each method specification in an object type specification, there must be a corresponding method body in the object type body. To match method specifications and bodies, the compiler does a token-by-token comparison of their headers. So, the headers must match word for word.
All methods in an object type accept an instance of that type as their first parameter. The name of this built-in parameter is SELF. Whether declared implicitly or explicitly, SELF is always the first parameter passed to a method. In a method body, SELF denotes the object whose method was called.
In member functions, if SELF is not declared, its parameter mode defaults to IN. However, in member procedures, if SELF is not declared, its parameter mode defaults to IN OUT. You cannot specify a different datatype for SELF.
This keyword indicates that a method orders objects by mapping them to values of a scalar datatype such as CHAR or REAL, which have a predefined order. PL/SQL uses the ordering to evaluate Boolean expressions such
as x > y, and to do comparisons implied by the DISTINCT, GROUP BY, and ORDER BY clauses. A map method returns the relative position of an object in the ordering of all such objects.
An object type can contain only one map method, which must be a parameterless function having the return type DATE, NUMBER, VARCHAR2, or an ANSI SQL type such as CHARACTER, INTEGER, or REAL.
