Release 8.0
A54654_01
 Library |
 Product |
 Contents |
 Index |
| PL/SQL User's Guide and Reference Release 8.0 A54654_01 |
|
This keyword indicates that a method compares two objects. Every order method takes just two parameters: the built-in parameter SELF and another object of the same type. If o1 and o2 are objects, a comparison such as o1 > o2 automatically calls the order method with parameters o1 and o2. The method returns a negative number, zero, or a positive number signifying that SELF is less than, equal to, or greater than the other parameter, respectively.
An object type can contain only one order method, which must be a function that returns a numeric result.
You use this pragma (compiler directive) to enforce rules that control side effects. The pragma tells the PL/SQL compiler to deny the member function read/write access to database tables, packaged variables, or both.
In the object type specification, you code the pragma somewhere after the method to which it applies using the following syntax:
PRAGMA_RESTRICT_REFERENCES ({DEFAULT | method_name},
{RNDS | WNDS | RNPS | WNPS}[, {RNDS | WNDS | RNPS | WNPS}])
You can specify up to four constraints in any order, but you must specify WNDS. To call the method from parallel queries, you must also specify WNPS, RNDS, and RNPS. No constraint implies another.
If you specify the keyword DEFAULT instead of a method name, the pragma applies to all member functions including the system-defined constructor. You can declare the pragma for any member function. Such pragmas override the default pragma. However, a non-default pragma can apply to only one method. So, among overloaded methods, the pragma always applies to the nearest preceding method.
Once an object type is defined and installed in the schema, you can use it to declare objects in any PL/SQL block, subprogram, or package. For example, you can use the object type to specify the datatype of an attribute, column, variable, bind variable, record field, table element, formal parameter, or function result.
Like a package, an object type has two parts: a specification and a body. The specification is the interface to your applications; it declares a data structure (set of attributes) along with the operations (methods) needed to manipulate the data. The body fully defines the methods, and so implements the specification.
All the information a client program needs to use the methods is in the specification. Think of the specification as an operational interface and of the body as a black box. You can debug, enhance, or replace the body without changing the specification.
An object type encapsulates data and operations. So, you can declare attributes and methods in an object type specification, but not constants, exceptions, cursors, or types. At least one attribute is required; methods are optional.
In an object type specification, all attributes must be declared before any methods. Only subprograms have an underlying implementation. So, if an object type specification declares only attributes, the object type body is unnecessary. You cannot declare attributes in the body.
All declarations in the object type specification are public (visible outside the object type). However, the object type body can contain private declarations, which define methods necessary for the internal workings of the object type. The scope of private declarations is local to the object type body.
You can refer to an attribute only by name (not by its position in the object type). To access or change the value of an attribute, you use dot notation. Attribute names can be chained, which allows you to access the attributes of a nested object type.
In an object type, methods can reference attributes and other methods without a qualifier. In SQL statements, calls to a parameterless method require an empty parameter list. In procedural statements, an empty parameter list is optional unless you chain calls, in which case it is required for all but the last call.
You can declare a map method or an order method but not both. If you declare either method, you can compare objects in SQL and procedural statements. However, if you declare neither method, you can compare objects only in SQL statements and only for equality or inequality. Two objects of the same type are equal only if the values of their corresponding attributes are equal.
Like packaged subprograms, methods of the same kind (functions or procedures) can be overloaded. That is, you can use the same name for different methods if their formal parameters differ in number, order, or datatype family.
Every object type has a constructor method (constructor for short), which is a system-defined function with the same name as the object type. You use the constructor to initialize and return an instance of that object type. PL/SQL never calls a constructor implicitly, so you must call it explicitly. Constructor calls are allowed wherever function calls are allowed.
In the SQL*Plus script below, an object type for a stack is defined. The last item added to a stack is the first item removed. The operations push and pop update the stack while preserving last in, first out (LIFO) behavior. The simplest implementation of a stack uses an integer array. Integers are stored in array elements, with one end of the array representing the top of the stack.
CREATE TYPE IntArray AS VARRAY(25) OF INTEGER
/
CREATE TYPE Stack AS OBJECT (
max_size INTEGER,
top INTEGER,
position IntArray,
MEMBER PROCEDURE initialize,
MEMBER FUNCTION full RETURN BOOLEAN,
MEMBER FUNCTION empty RETURN BOOLEAN,
MEMBER PROCEDURE push (n IN INTEGER),
MEMBER PROCEDURE pop (n OUT INTEGER)
)
/
CREATE TYPE BODY Stack AS
MEMBER PROCEDURE initialize IS
-- fill stack with nulls
BEGIN
top := 0;
-- call constructor for varray and set element 1 to NULL
position := IntArray(NULL);
max_size := position.LIMIT; -- use varray size constraint (25)
position.EXTEND(max_size - 1, 1); -- copy element 1 into 2..25
END initialize;
MEMBER FUNCTION full RETURN BOOLEAN IS
-- return TRUE if stack is full
BEGIN
RETURN (top = max_size);
END full;
MEMBER FUNCTION empty RETURN BOOLEAN IS
-- return TRUE if stack is empty
BEGIN
RETURN (top = 0);
END empty;
MEMBER PROCEDURE push (n IN INTEGER) IS
-- push integer onto stack
BEGIN
IF NOT full THEN
top := top + 1;
position(top) := n;
ELSE -- stack is full
RAISE_APPLICATION_ERROR(-20101, `stack overflow');
END IF;
END push;
MEMBER PROCEDURE pop (n OUT INTEGER) IS
-- pop integer off stack and return its value
BEGIN
IF NOT empty THEN
n := position(top);
top := top - 1;
ELSE -- stack is empty
RAISE_APPLICATION_ERROR(-20102, `stack underflow');
END IF;
END pop;
END;
Notice that in member procedures push and pop, we use the built-in procedure RAISE_APPLICATION_ERROR to issue user-defined error messages. That way, we can report errors to the client program and avoid returning unhandled exceptions to the host environment.
The following example shows that you can nest object types:
CREATE TYPE Address AS OBJECT (
street_address VARCHAR2(35),
city VARCHAR2(15),
state CHAR(2),
zip_code INTEGER
); CREATE TYPE Person AS OBJECT (
first_name VARCHAR2(15),
last_name VARCHAR2(15),
birthday DATE,
home_address Address, -- nested object type
phone_number VARCHAR2(15),
ss_number INTEGER,
);
Functions, Packages, Procedures
The OPEN statement executes the multi-row query associated with an explicit cursor. It also allocates resources used by Oracle to process the query and identifies the result set, which consists of all rows that meet the query search criteria. The cursor is positioned before the first row in the result set. For more information, see "Managing Cursors" on page 5-9.
This identifies an explicit cursor previously declared within the current scope and not currently open.
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. For the syntax of cursor_parameter_ declaration, see "Cursors" on page 11-45.
Generally, PL/SQL parses an explicit cursor only the first time it is opened and parses a SQL statement (thereby creating an implicit cursor) only the first time the statement is executed. All the parsed SQL statements are cached. A SQL statement must be reparsed only if it is bumped out of the cache by a new SQL statement.
So, although you must close a cursor before you can reopen it, PL/SQL need not reparse the associated SELECT statement. If you close, then immediately reopen the cursor, a reparse is definitely not needed.
Rows in the result set are not retrieved when the OPEN statement is executed. 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" on page 7-12.
Given the cursor declaration
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);
CLOSE Statement, Cursors, FETCH Statement, LOOP Statements
The OPEN-FOR statement executes the multi-row query associated with a cursor variable. It also allocates resources used by Oracle to process the query and identifies the result set, which consists of all rows that meet the query search criteria. The cursor variable is positioned before the first row in the result set. For more information, see "Using Cursor Variables" on page 5-18.
This identifies a cursor variable (or parameter) previously declared within the current scope.
This identifies a cursor variable previously declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.
This is a query associated with cursor_variable, which returns a set of values. The query can reference bind variables and PL/SQL variables, parameters, and functions but cannot be FOR UPDATE. The syntax of select_statement is similar to the syntax for select_into_statement defined in "SELECT INTO Statement" on page -137, except that select_statement cannot have an INTO clause.
You can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program. To open the host cursor variable, you can pass it as a bind variable to an anonymous PL/SQL block. You can reduce network traffic by grouping OPEN-FOR statements. For example, the following PL/SQL block opens five cursor variables in a single round-trip:
/* anonymous PL/SQL block in host environment */
BEGIN
OPEN :emp_cv FOR SELECT * FROM emp;
OPEN :dept_cv FOR SELECT * FROM dept;
OPEN :grade_cv FOR SELECT * FROM salgrade;
OPEN :pay_cv FOR SELECT * FROM payroll;
OPEN :ins_cv FOR SELECT * FROM insurance;
END;
Other OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.
Unlike cursors, cursor variables do not take parameters. No flexibility is lost, however, because you can pass whole queries (not just parameters) to a cursor variable.
You can pass a cursor variable to PL/SQL by calling a stored procedure that declares a cursor variable as one of its formal parameters. However, remote subprograms on another server cannot accept the values of cursor variables. Therefore, you cannot use a remote procedure call (RPC) to open a cursor variable.
When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the IN OUT mode. That way, the subprogram can pass an open cursor back to the caller.
In the following Pro*C example, you pass a host cursor variable and selector to a PL/SQL block, which opens the cursor variable for the chosen query:
EXEC SQL BEGIN DECLARE SECTION;
...
/* Declare host cursor variable. */
SQL_CURSOR generic_cv;
int choice;
EXEC SQL END DECLARE SECTION;
...
/* Initialize host cursor variable. */
EXEC SQL ALLOCATE :generic_cv;
...
/* Pass host cursor variable and selector to PL/SQL block. */
EXEC SQL EXECUTE
BEGIN
IF :choice = 1 THEN
OPEN :generic_cv FOR SELECT * FROM emp;
ELSIF :choice = 2 THEN
OPEN :generic_cv FOR SELECT * FROM dept;
ELSIF :choice = 3 THEN
OPEN :generic_cv FOR SELECT * FROM salgrade;
END IF;
END;
END-EXEC;
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;
CLOSE Statement, Cursor Variables, FETCH Statement, LOOP Statements
A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Packages have two parts: a specification and a body. For more information, see Chapter 8.
This identifies a package. For naming conventions, see "Identifiers" on page 2-4.
This identifies a nested table, index-by table, or varray previously declared within the current scope. For the syntax of collection_declaration, see "Collections" on page 11-21
This construct declares a constant. For the syntax of constant_declaration, see "Constants and Variables" on page 11-29.
This construct declares an exception. For the syntax of exception_declaration, see "Exceptions" on page 11-54.
This identifies an object (instance of an object type) previously declared within the current scope. For the syntax of object_declaration, see "Object Types" on page 11-102.
This construct declares a user-defined record. For the syntax of record_declaration, see "Records" on page 11-126.
This construct declares a variable. For the syntax of variable_declaration, see "Constants and Variables" on page 11-29.
This construct declares the interface to an explicit cursor. For the syntax of cursor_specification, see "Cursors" on page 11-45.
This construct declares the interface to a function. For the syntax of function_specification, see "Functions" on page 11-78.
This construct declares the interface to a procedure. For the syntax of procedure_specification, see "Procedures" on page 11-119.
This construct defines the underlying implementation of an explicit cursor. For the syntax of cursor_body, see "Cursors" on page 11-45.
This construct defines the underlying implementation of a procedure. For the syntax of procedure_body, see "Procedures" on page 11-119.
This construct defines the underlying implementation of a function. For the syntax of function_body, see "Functions" on page 11-78.
You cannot define packages in a PL/SQL block or subprogram. However, you can use any Oracle tool that supports PL/SQL to create and store packages in an Oracle database. You can issue the CREATE PACKAGE and CREATE PACKAGE BODY statements interactively from SQL*Plus or Enterprise Manager and from an Oracle Precompiler or OCI host program. For the full syntax of the CREATE PACKAGE statement, see Oracle8 Server SQL Reference.
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 items 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 items 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.
Collections, Cursors, Exceptions, Functions, Procedures, Records
A procedure is a named PL/SQL block, which can take parameters and be invoked. Generally, you use a procedure to perform an action. For more information, see "Procedures" on page 7-3.
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 items are local and cease to exist when you exit the procedure. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains exception handlers, which deal with exceptions raised during execution.
This identifies a user-defined procedure.
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 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.
At least one statement must appear in the executable part of a procedure. The NULL statement meets this requirement.
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 on page 7-15.
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" on page 7-15.
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 Enterprise Manager. For the full syntax of the CREATE PROCEDURE statement, see Oracle8 Server SQL Reference.
The following procedure debits a bank account:
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);
Collections, Functions, Packages, Records
The RAISE statement stops normal execution of a PL/SQL block or subprogram and transfers control to the appropriate exception handler. For more information, see "User-Defined Exceptions" on page 6-6.
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.
This identifies a predefined or user-defined exception. For a list of the predefined exceptions, see "Predefined Exceptions" on page 6-4.
PL/SQL blocks and subprograms should RAISE an exception only when an error makes it impractical or impossible to continue processing. You can code a RAISE statement for a given exception anywhere within the scope of that exception.
When an exception is raised, if PL/SQL cannot find a handler for it in the current block, the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search. In the latter case, PL/SQL returns an unhandled exception error to the host environment.
Omitting the exception name in a RAISE statement, which is allowed only in an exception handler, reraises the current exception. When a parameterless RAISE statement executes in an exception handler, the first block searched is the enclosing block, not the current block.
In the following example, you raise an exception when an inventoried part is out of stock:
IF quantity_on_hand = 0 THEN
RAISE out_of_stock;
END IF;
Exceptions
Records are items of type RECORD. Records have uniquely named fields that can store data values of different types. For more information, see "What Is a Record?" on page 4-25.
This identifies a user-defined type specifier, which is used in subsequent declarations of records.
This constraint prevents the assigning of nulls to a field. At run time, trying to assign a null to a field defined as NOT NULL raises the predefined exception VALUE_ERROR. The constraint NOT NULL must be followed by an initialization clause.
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 fields 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. For the syntax of expression, see "Expressions" on page 11-59. When the declaration is elaborated, the value of expression is assigned to the field. The value and the field must have compatible datatypes.
You can define RECORD types and declare user-defined records in the declarative part of any block, subprogram, or package. Also, a record can be initialized in its declaration, as the following example shows:
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.
In the following example, you define a RECORD type named DeptRecTyp, declare a record named dept_rec, then select a row of values into the record:
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;
Assignment Statement, Collections, Functions, Procedures
The RETURN statement immediately completes the execution of a subprogram and returns control to the caller. Execution then resumes with the statement following the subprogram call. In a function, the RETURN statement also sets the function identifier to the result value. For more information, see "RETURN Statement" on page 7-7.
This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the RETURN statement is executed, the value of expression is assigned to the function identifier.
Do not confuse the RETURN statement with the RETURN clause, which specifies the datatype of the result value in a function specification.
A subprogram can contain several RETURN statements, none of which need be the last lexical statement. Executing any of them completes the subprogram immediately. However, to have multiple exit points in a subprogram is a poor programming practice.
In procedures, a RETURN statement cannot contain an expression. The statement simply returns control to the caller before the normal end of the procedure is reached.
However, in functions, a RETURN statement must contain an expression, which is evaluated when the RETURN statement is executed. The resulting value is assigned to the function identifier. Therefore, 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.
In the following example, the function balance 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;
Functions, Procedures
The ROLLBACK statement is the inverse of the COMMIT statement. It undoes some or all database changes made during the current transaction. For more information, see "Processing Transactions" on page 5-42.
When a parameterless ROLLBACK statement is executed, all database changes made during the current transaction are undone.
This keyword is optional and has no effect except to improve readability.
This statement undoes all database changes (and releases all locks acquired) since the savepoint identified by savepoint_name was marked.
This keyword is optional and has no effect except to improve readability.
This is an undeclared identifier, which marks the current point in the processing of a transaction. For naming conventions, see "Identifiers" on page 2-4.
All savepoints marked after the savepoint to which you roll back are erased. However, the savepoint to which you roll back is not erased. For example, if you mark savepoints A, B, C, and D in that order, then roll back to savepoint B, only savepoints C and D are erased.
An implicit savepoint is marked before executing an INSERT, UPDATE, or DELETE statement. If the statement fails, a rollback to the implicit savepoint is done. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back.
COMMIT Statement, SAVEPOINT Statement
The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Fields in a record and corresponding columns in a row have the same names and datatypes.
You can use the %ROWTYPE attribute in variable declarations as a datatype specifier. Variables declared using %ROWTYPE are treated like those declared using a datatype name. For more information, see "Using %ROWTYPE" on page 2-31.
This identifies an explicit cursor previously declared within the current scope.
This identifies a PL/SQL strongly (not weakly) typed cursor variable previously declared within the current scope.
This identifies a database table (or view) that must be accessible when the declaration is elaborated.
The %ROWTYPE attribute lets you declare records structured like a row of data in a database table.
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.
In the example below, you use %ROWTYPE to declare two records. The first record stores a row selected from the emp table. The second record stores a row fetched from the c1 cursor.
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;
Constants and Variables, Cursors, Cursor Variables, FETCH Statement
The SAVEPOINT statement names and marks the current point in the processing of a transaction. With the ROLLBACK TO statement, savepoints let you undo parts of a transaction instead of the whole transaction. For more information, see "Processing Transactions" on page 5-42.
This is an undeclared identifier, which marks the current point in the processing of a transaction.
When you roll back to a savepoint, any savepoints marked after that savepoint are erased. However, the savepoint to which you roll back is not erased. A simple rollback or commit erases all savepoints. Savepoint names can be reused within a transaction. This moves the savepoint from its old position to the current point in the transaction.
If you mark a savepoint within a recursive subprogram, new instances of the SAVEPOINT statement are executed at each level in the recursive descent. However, you can only roll back to the most recently marked savepoint.
An implicit savepoint is marked before executing an INSERT, UPDATE, or DELETE statement. If the statement fails, a rollback to the implicit savepoint is done. Normally, just the failed SQL statement is rolled back, not the whole transaction. However, if the statement raises an unhandled exception, the host environment determines what is rolled back.
COMMIT Statement, ROLLBACK Statement
The SELECT INTO statement retrieves data from one or more database tables, then assigns the selected values to variables or fields. For a full description of the SELECT statement, see Oracle8 Server SQL Reference.
This is a value returned by the SELECT statement, then assigned to the corresponding variable or field in the INTO clause.
This identifies a list of previously declared scalar variables into which select_item values are fetched. For each select_item value returned by the query, 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 select_item value returned by the query, there must be a corresponding, type-compatible field in the record.
This identifies a table or view that must be accessible when you execute the SELECT statement, and for which you must have SELECT privileges. For the syntax of table_reference, see "DELETE Statement" on page 11-49.
The operand of THE is a subquery that returns a single column value to the SELECT 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 query that provides a value or set of values to the SELECT statement. The syntax of subquery is like the syntax of select_into_ statement, except that subquery cannot have an INTO clause.
This is another (usually short) name for the referenced column, table, or view.
This is anything that can legally follow the FROM clause in a SELECT statement.
The implicit SQL cursor and the cursor attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN let you access useful information about the execution of a SELECT INTO statement.
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:
If no rows are returned, you get these results:
The following SELECT statement returns an employee's name, job title, and salary from the emp database table:
SELECT ename, job, sal INTO my_ename, my_job, my_sal FROM emp
WHERE empno = my_empno;
Assignment Statement, FETCH Statement, %ROWTYPE Attribute
The SET TRANSACTION statement begins a read-only or read-write transaction, establishes an isolation level, or assigns the current transaction to a specified rollback segment. Read-only transactions are useful for running multiple queries against one or more tables while other users update the same tables. For more information, see "Using SET TRANSACTION" on page 5-47.
This clause establishes the current transaction as read-only. If a transaction is set to READ ONLY, subsequent queries see only changes committed before the transaction began. The use of READ ONLY does not affect other users or transactions.
This clause establishes the current transaction as read-write. The use of READ WRITE does not affect other users or transactions. If the transaction executes a data manipulation statement, Oracle assigns the transaction to a rollback segment.
This clause specifies how transactions that modify the database are handled. When you specify SERIALIZABLE, if a serializable transaction trys to execute a SQL data manipulation statement that modifies any table already modified by an uncommitted transaction, the statement fails. To enable SERIALIZABLE mode, your DBA must set the Oracle initialization parameter COMPATIBLE to 7.3.0 or higher.
When you specify 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.
This clause assigns the current transaction to the specified rollback segment and establishes the transaction as read-write. You cannot use this parameter with the READ ONLY parameter in the same transaction because read-only transactions do not generate rollback information.
The SET TRANSACTION statement must be the first SQL statement in your transaction and can appear only once in the transaction.
In the following example, you establish a read-only transaction:
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
COMMIT Statement, ROLLBACK Statement, SAVEPOINT Statement
Oracle implicitly opens a cursor to process each SQL statement not associated with an explicit cursor. PL/SQL lets you refer to the most recent implicit cursor as the "SQL" cursor. The SQL cursor has four attributes: %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. They give you useful information about the execution of INSERT, UPDATE, DELETE, and SELECT INTO statements. For more information, see "Managing Cursors" on page 5-9.
This is the name of the implicit SQL cursor.
This attribute yields TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it yields FALSE.
This attribute always yields FALSE because Oracle closes the SQL cursor automatically after executing its associated SQL statement.
This attribute is the logical opposite of %FOUND. It yields TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it yields FALSE.
This attribute yields the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.
You can use cursor attributes in procedural statements but not in SQL statements. Before Oracle opens the SQL cursor automatically, the implicit cursor attributes yield NULL.
The values of cursor attributes always refer to the most recently executed SQL statement, wherever that statement appears. It might be in a different scope. So, if you want to save an attribute value for later use, assign it to a Boolean variable immediately.
If a 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.
In the following example, %NOTFOUND is used to insert a row if an update affects no rows:
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;
Cursors, Cursor Attributes
The function SQLCODE returns the number code associated with the most recently raised exception. SQLCODE is meaningful only in an exception handler. Outside a handler, SQLCODE always returns zero.
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-17.
You cannot use SQLCODE directly in a SQL statement. 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.
Exceptions, SQLERRM Function
The function SQLERRM returns the error message associated with its error-number argument or, if the argument is omitted, with the current value of SQLCODE. SQLERRM with no argument is meaningful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the message normal, successful completion.
For internal exceptions, SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code.
For user-defined exceptions, SQLERRM returns the message user-defined exception unless you used the pragma EXCEPTION_INIT to associate the exception with an Oracle error number, in which case SQLERRM returns the corresponding error message. For more information, see "Using SQLCODE and SQLERRM" on page 6-17.
This must be a valid Oracle error number. For a list of Oracle errors, see Oracle8 Server Messages.
You can pass an error number to SQLERRM, in which case SQLERRM returns the message associated with that error number. The error number passed to SQLERRM should be negative. Passing a zero to SQLERRM always returns the following message:
ORA-0000: normal, successful completion
Passing a positive number to SQLERRM always returns the message
User-Defined Exception
unless you pass +100, in which case SQLERRM returns the following message:
ORA-01403: no data found
You cannot use SQLERRM directly in a SQL statement. 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.
Exceptions, SQLCODE Function
The %TYPE attribute provides the datatype of a field, record, nested table, database column, or variable. You can use the %TYPE attribute as a datatype specifier when declaring constants, variables, fields, and parameters. For more information, see "Using %TYPE" on page 2-30.
This identifies a nested table, index-by table, or varray previously declared within the current scope.
This identifies a PL/SQL cursor variable previously declared within the current scope. Only the value of another cursor variable can be assigned to a cursor variable.
This identifies an object (instance of an object type) previously declared within the current scope.
This identifies a user-defined or %ROWTYPE record previously declared within the current scope.
This identifies a field in a user-defined or %ROWTYPE record previously declared within the current scope.
This refers to a table and column that must be accessible when the declaration is elaborated.
This is the name of a variable previously declared in the same scope.
The %TYPE attribute is particularly useful when declaring variables, fields, and parameters that refer to database columns. However, the NOT NULL column constraint does not apply to items declared using %TYPE.
Constants and Variables, %ROWTYPE Attribute
The UPDATE statement changes the values of specified columns in one or more rows in a table or view. For a full description of the UPDATE statement, see Oracle8 Server SQL Reference.
This identifies a table or view that must be accessible when you execute the UPDATE statement, and for which you must have UPDATE privileges. For the syntax of table_reference, see "DELETE Statement" on page 11-49.
The operand of THE is a subquery that returns a single column value to the UPDATE 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 UPDATE statement. The syntax of subquery1 is like the syntax of select_into_statement defined in "SELECT INTO Statement" on page -137, except that subquery1 cannot have an INTO clause.
This is another (usually short) name for the referenced table or view and is typically used in the WHERE clause.
This is the name of the column (or one of the columns) to be updated. It must be the name of a column in the referenced table or view. A column name cannot be repeated in the column_name list. Column names need not appear in the UPDATE statement in the same order that they appear in the table or view.
This is any expression valid in SQL. For more information, see Oracle8 Server SQL Reference.
This clause assigns the value of sql_expression to the column identified by column_name. If sql_expression contains references to columns in the table being updated, the references are resolved in the context of the current row. The old column values are used on the right side of the equal sign.
In the following example, you increase every employee's salary by 10%. The original value of the sal column is multiplied by 1.1, then the result is assigned to the sal column.
UPDATE emp SET sal = sal * 1.1;
This clause assigns the value retrieved from the database by subquery2 to the column identified by column_name. The subquery must return exactly one row and one column.
This clause assigns the values retrieved from the database by subquery3 to the columns identified by column_name[, column_nam]...
This clause assigns the values retrieved from the database by subquery to the columns in the column_name list. The subquery must return exactly one row that includes all the columns listed.
The column values returned by 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);
This clause chooses which rows to update in the database table. Only rows that meet the search condition are updated. If you omit the search condition, all rows in the table are updated.
This clause refers to the latest row processed by the FETCH statement associated with the cursor identified by cursor_name. The cursor must be FOR UPDATE and must be open and positioned on a row.
If the cursor is not open, the CURRENT OF clause causes an error. If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND.
This clause lets you return values from updated rows, thereby eliminating the need to SELECT the rows afterward. If you are updatting a single row, you can retrieve the column values into variables and/or host variables.
If you are updating multiple rows you can retrieve the values into host arrays. The datatypes of the column values and array elements must be compatible, and the number of rows returned cannot exceed the size of the array.
You can use the UPDATE WHERE CURRENT OF statement after a fetch from an open cursor (this includes implicit fetches executed in a cursor FOR loop), provided the associated query is FOR UPDATE. This statement updates the current row; that is, the one just fetched.
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:
If no rows are updated, you get these results:
In the following example, a 10% raise is given to all analysts and clerks in department 20:
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';
In the final example, you return values from an updated row and store them in variables:
UPDATE emp SET sal = sal + 500
WHERE ename = 'MILLER' RETURNING sal, ename NTO my_sal, my_ename;
DELETE Statement, FETCH Statement
