User-Defined Functions Written In Pl/Sql What Is The Assignment

This section will provide a basic understanding of PL/SQL. This document will briefly cover the main concepts behind PL/SQL and provide brief examples illustrating the important facets of the language. Most of the information contained in this section is DIRECTLY extracted from ``PL/SQL User's Guide and Reference'' and all credit should be given to ORACLE. If you require more detailed information than provided in this section, consult the above stated manual.

PL/SQL is Oracle's procedural language extension to SQL, the relational database language. PL/SQL fully integrates modern software engineering features such as data encapsulation, information hiding, overloading, and exception handling, and so brings state-of-the-art programming to the ORACLE Server and a variety of ORACLE tools.

With PL/SQL, you can use SQL statements to manipulate ORACLE data and flow-of-control statements to process the data. Moreover, you can declare constants and variables, define subprograms (procedures and functions), and trap runtime errors. Thus, PL/SQL combines the data manipulating power of SQL with the data processing power of procedural languages.

PL/SQL is a block-structured language. That is, the basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can contain any number of nested sub-blocks. Typically, each logical block corresponds to a problem or subproblem to be solved.

A block (or sub-block) lets you group logically related declarations and statements. That way you can place declarations close to where they are used. The declarations are local to the block and cease to exist when the block completes.

PL/SQL is not case-sensitive, so lower-case letters are equivalent to corresponding upper-case letters except within string and character literals. A line of PL/SQL text contains groups of characters known as lexical units, which can be classified as follows:

A delimiter is a simple or compound symbol that has a special meaning to PL/SQL. For example, you use delimiters to represent arithmetic operations such as addition and subtraction.

You use identifiers to name PL/SQL program objects and units, which include constants, variables, exceptions, cursors, subprograms, and packages. Some identifiers called RESERVED WORDS, have a special syntactic meaning to PL/SQL and so cannot be redefined. For flexibility, PL/SQL lets you enclose identifiers within double quotes. Quoted identifiers are seldom needed, but occasionally they can be useful.

A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier.

The PL/SQL compiler ignores comments but you should not. Adding comments to your program promotes readability and aids understanding. PL/SQL supports two comment styles: single-line and multiline. Single-line comments begin with a double hyphen (--) anywhere on a line and extend to the end of the line. Multiline comments begin with a slash-asterisk (/*), end with an asterisk-slash (*/), and can span multiple lines. You cannot nest comments.

Every constant and variable has a datatype, which specifies a storage format, constraints, and valid range of values. PL/SQL provides a variety of predefined scalar and composite datatypes. A scalar type has no internal components. A composite type has internal components that can be manipulated individually. PL/SQL Datatypes are similar to SQL's Datatypes but some of the common datatypes are discussed again. For more information on the PL/SQL Datatypes see Chapter 2 of ``PL/SQL User's Guide and Reference.''

Sometimes it is necessary to convert a value from one datatype to another. PL/SQL supports both explicit and implicit (automatic) datatype conversions.

To specify conversions explicitly, you use built-in functions that convert values from one datatype to another. PL/SQL conversion functions are similar to those in SQL. For more information on conversion functions see Chapter 2 of ``PL/SQL User's Guide and Reference.''

When it makes sense, PL/SQL can convert the datatype of a value implicitly. This allows you to use literals, variables, and parameters of one type where another type is expected. If PL/SQL cannot determine which implicit conversion is needed, you get a compilation error. It is your responsibility to ensure that values are convertible. For instance, PL/SQL can convert the CHAR value '02-JUN-92' to a DATE value, but PL/SQL cannot convert the CHAR value 'YESTERDAY' to a DATE value.

Your program stores values in variables and constants. As the program executes, the values of variables can change, but the values of constants cannot.

You can declare variables and constants in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its datatype, and name the storage location so that the value can be referenced. They can also assign an initial value and specify the NOT NULL constraint.

The first declaration names a variable of type DATE. The second declaration names a variable of type SMALLINT and uses the assignment operator (:=) to assign an initial value of zero to the variable. The third declaration names a variable of type VARCHAR2, specifies the NOT NULL constraint, and assigns an initial value of 'AP001' to the variable.

In constant declarations, the reserved word CONSTANT must precede the type specifier.

The same naming conventions apply to all PL/SQL program objects and units including constants, variables, cursors, exceptions, procedures, functions, and packages.

Within the same scope, all declared identifiers must be unique. So, even if their datatypes differ, variables and parameters cannot share the same name.

In potentially ambiguous SQL statements, the names of local variables and formal parameters take precedence over the names of database tables. For example, the following SELECT statement fails because PL/SQL assumes that emp refers to the formal parameter:

The names of database columns take precedence over the names of local variables and formal parameters. For example, the following DELETE statement removes all employees from the emp table, not just KING, because ORACLE assumes that both enames in the WHERE clause refer to the database column:

In such cases, to avoid ambiguity, prefix the names of local variables and formal parameters with my_ as follows:

The next example shows that you can use a subprogram name to qualify references to local variables and formal parameters:

References to an identifier are resolved according to its scope and visibility. The scope of an identifier is that region of a program unit (block, subprogram, or package) from which you can reference the identifier. An identifier is visible only in the regions from which you can reference the identifier using an unqualified name.

For example, identifiers declared in a PL/SQL block are considered local to that block and global to all its sub-blocks. If a global identifier is redeclared in a sub-block, both identifiers remain in scope. Within the sub-block, however, only the local identifier is visible because you must use a qualified name to reference the global identifier.

Although you cannot declare an identifier twice in the same block, you can declare the same identifier in two different blocks. The two objects represented by the identifier are distinct, and any change in one does not affect the other. Note that a block cannot reference identifiers declared in other blocks nested at the same level because those identifiers are neither local nor global to the block.

If you redeclare a identifier in a sub-block, you cannot reference the global identifier unless you use a qualified name. The qualifier can be the label of an enclosing block (or enclosing subprogram) as follows:

Variables and constants are initialized every time a block or subprogram is entered. By default, variables are initialized to NULL. So, unless you expressly initialize a variable, its value is undefined, as the following example shows:

Therefore, never reference a variable before you assign it a value. Only the values TRUE and FALSE and the non-value NULL can be assigned to a Boolean variable. When applied to PL/SQL expressions, the relational operators return a Boolean value. So, the following assignment is legal:

Alternatively, you can use the SELECT or FETCH statement to have ORACLE assign values to a variable.

For each item in the SELECT list, there must be a corresponding variable in the INTO list. Also, each item must return a value that is implicitly convertible to the datatype of its corresponding variable.

All expressions and comparisons are the same as those explained in the SQL Reference section. Some guidelines follow to help you prevent falling into common traps.

In general, do not compare real numbers for exact equality or inequality. It is also a good idea to use parentheses when doing comparisons.

Remember that a boolean variable is itself either true or false. So comparisons with the boolean values TRUE and FALSE are redundant. For example, assuming the variable done belongs to type BOOLEAN, the IF statement

Recall that applying the logical operator NULL to a null yields NULL.

If a null argument is passed to a function, a null is returned except in the following cases:

PL/SQL provides many powerful functions to help you manipulate data. You can use them wherever expressions of the same type are allowed. Furthermore, you can nest them.

miscellaneous functions

You can use all the built-in functions in SQL statements except the error-reporting functions SQLCODE and SQLERRM. In addition, you can use all the functions in procedural statements except the miscellaneous function DECODE.

Most functions are the same as those discussed in SQL Reference section except the ones that are discussed below.

Two functions, SQLCODE and SQLERRM, give you information about PL/SQL execution errors.

  • (SQLCODE) function SQLCODE return NUMBER
    Returns the number associated with the most recently raised exception. This function 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.

  • (SQLERRM) function SQLERRM [(error_number NUMBER)] return CHAR
    Returns the error message associated with the current value of SQLCODE. SQLERRM is meaningful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the message ``ORA-0000: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.

    You can pass the argument error_number to SQLERRM, in which case SQLERRM returns the message associated with error_number.

The following miscellaneous functions may be of use to you in PL/SQL coding.

  • (UID) function UID return NUMBER
    Returns the unique identification number assigned to the current ORACLE user. UID takes no arguments.

  • (USER) function USER return VARCHAR2
    Returns the username of the current ORACLE user. USER takes no arguments.

  • (USERENV) function USERENV (str VARCHAR2) return VARCHAR2
    Returns information about the current session. You can use the information to write an application audit trail table or to determine the language and character set are in use.\\

    The string str can have any of the following values:

    • 'ENTRYID' returns an auditing entry identifier

    • 'LANGUAGE' returns the language, territory, and database character set in use

    • 'SESSIONID' returns the auditing session identifier

    • 'TERMINAL' returns the operating system identifier for the session terminal

    You cannot specify the 'ENTRYID' or 'SESSIONID' option in SQL statements that access a remote database.


PL/SQL Tables

PL/SQL provides two composite datatypes: TABLE and RECORD. Objects of type TABLE are called PL/SQL tables, which are modeled as (but not the same as) database tables. PL/SQL tables use a primary key to give you array-like access to rows.

Like the size of a database table, the size of a PL/SQL table is unconstrained. That is, the number of rows in a PL/SQL table can increase dynamically. The PL/SQL table grows as new rows are added.

PL/SQL tables can have one column and a primary key, neither of which can be named. The column can belong to any scalar type, but the primary key must belong to type BINARY_INTEGER.

PL/SQL tables must be declared in two steps. First, you define a TABLE type, then declare PL/SQL tables of that type. You can declare TABLE types in the declarative part of any block, subprogram, or package using the syntax:

TYPE type_name IS TABLE OF { column_type | variable%TYPE | table.column%TYPE } [NOT NULL] INDEX BY BINARY_INTEGER;

where type_name is a type specifier used in subsequent declarations of PL/SQL tables and column_type is any scalar (not composite) datatype such as CHAR, DATE, or NUMBER. You can use the %TYPE attribute to specify a column datatype.

In the following example, you declare a TABLE type called EnameTabTyp:

DECLARE TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; ...

Once you define type EnameTabTyp, you can declare PL/SQL tables of that type, as follows:

ename_tab EnameTabTyp

The identifier ename_tab represents an entire PL/SQL table.

PL/SQL table is unconstrained because its primary key can assume any value in the range of values defined for BINARY_INTEGER. As a result, you cannot initialize a PL/SQL table in its declaration. For example, the following declaration is illegal:

ename_tab EnameTabTyp := ('CASEY', 'STUART', 'CHU');

To reference rows in a PL/SQL table, you specify a primary key value using the array-like syntax

plsql_table_name(primary_key_value)

where primary_key_value belongs to type BINARY_INTEGER. The magnitude range of a BINARY_INTEGER value is -2**31-1 ... 2**31-1. For example, you reference the third row in PL/SQL table ename_tab as follows:

ename_tab(3) ...

You can assign the value of a PL/SQL expression to a specific row using the following syntax:

plsql_table_name(primary_key_value) := plsql_expression;

In the example below, you use a cursor FOR loop to load two PL/SQL tables. A cursor FOR loop implicitly declares its loop index as a record, opens the cursor associated with a given query, repeatedly fetches rows of values from the cursor into fields in the record, then closes the cursor.

DECLARE TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; TYPE SalTabTyp IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER; ename_tab EnameTabTyp; sal_tab SalTabTyp; i BINARY_INTEGER := 0; ... BEGIN -- load employee names and salaries into PL/SQL tables For emprec IN (SELECT ename, sal FROM emp) LOOP i := i + 1; ename_tab(i) := emprec.ename; sal_tab(i) := emprec.sal; END LOOP; --process the tables process_sals(ename_tab, sal_tab); ... END;

Until a row is assigned a value, it does not exist. If you try to reference an uninitialized row, PL/SQL raises the predefined exception NO_DATA_FOUND.

Remember that the size of PL/SQL table is unconstrained so, that if you want to maintain a row count, you must declare a variable for that purpose. A PL/SQL table can grow large, constrained only by available memory. When PL/SQL runs out of memory it raises the predefined exception STORAGE_ERROR.

You must use a loop to INSERT values from a PL/SQL table into a database column. Likewise, you must use a loop to FETCH values from a database column into a PL/SQL table. Therefore, you cannot reference PL/SQL tables in the INTO clause of a SELECT statement.

There is no straightforward way to delete rows from a PL/SQL table because the DELETE statement cannot specify PL/SQL tables. Setting a row to NULL does not work because the row remains and does not raise the exception NO_DATA_FOUND when referenced.

Although you cannot delete individual rows from a PL/SQL table, you can use a simple workaround to delete entire PL/SQL tables. First, declare another PL/SQL table of the same type and leave it empty. Later, when you want to delete the original PL/SQL tables, simply assign the empty table to them.


User-defined Records

You can use the %ROWTYPE attribute to declare a record that represents a row in a table or a row fetched by a cursor. However, you cannot specify the datatypes of fields in the record or define fields of your own. The composite datatype RECORD lifts those restrictions.

As you might expect, objects of type RECORD are called records. Unlike PL/SQL tables, records have uniquely named fields, which can belong to different datatypes. For example, suppose you have different kinds of data about an employee such as name, salary, hire date, and so on. This data is dissimilar in type but logically related. A record that contains such fields as the name, salary, and hire date of an employee would let you treat the data as a logical unit.

Like PL/SQL tables, records must be declared in two steps. First, you define a RECORD type, then declare user-defined records of that type.

You can declare RECORD types in the declarative part of any block, subprogram, or package using the syntax

TYPE type_name IS RECORD (field_name1 {field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE} [NOT NULL], (field_name2 {field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE} [NOT NULL], ... );

where type_name is a type specifier used in subsequent declarations of records and field_type is any datatype including RECORD and TABLE. You can use the %TYPE or %ROWTYPE attribute to specify a field datatype. In the following example, you declare a RECORD type named DeptRecTyp:

DECLARE TYPE DeptRecTyp is RECORD (deptno NUMBER(2) NOT NULL := 20, dname dept.dname%TYPE, loc dept.dname%TYPE); ...

Once you define type DeptRecTyp, you can declare records of that type, as follows:

dept_rec DeptRecTyp;

The identifier dept_rec represents an entire record.

To reference individual fields in a record, you use the dot notation and the following syntax:

record_name.field_name

You can assign the value of a PL/SQL expression to a specific field by using the following syntax:

record_name.field_name := plsql_expression;

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, you can assign one record to another if they belong to the same datatype. Second, you can assign a list of column values to a record by using the SELECT or FETCH statement, as the example below shows. Just make sure the column names appear in the same order as the fields in your record.

DECLARE TYPE DeptRecTyp IS RECORD (deptno NUMBER(2) NOT NULL := 20, dname dept.dname%TYPE, loc dept.dname%TYPE); dept_rec DeptRecTyp; ... BEGIN SELECT deptno, dname, loc INTO dept_rec FROM dept WHERE deptno = 30; ... END;

Even if their fields match exactly, records of different types cannot be assigned to each other. Furthermore, a user-defined record and a %ROWTYPE record always belong to different types.

You cannot assign a list of values to a record by using an assignment statement. So, the following syntax is illegal:

record_name := (value1, value2, value3, ...); -- illegal

Also, records cannot be tested for equality or inequality. For instance, the following IF condition is illegal:

IF dept_rec1 = dept_rec2 THEN -- illegal ... END IF;

PL/SQL lets you declare and reference nested records. That is, a record can be the component of another record. You can assign one nested record to another if they belong to the same datatype. Such assignments are allowed even if the containing records belong to different datatypes, as follows:

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 (date DATE, time TimeTyp, -- nested record loc CHAR(15)); meeting MeetingTyp; seminar MeetingTyp; party PartyTyp; ... BEGIN meeting.day := '26-Jun-91'; meeting.time.minute := 45; meeting.time.hour := 10; ... seminar.time := meeting.time; party.time := meeting.time; END;

CONTROL STRUCTURES

According to the structure theorem, any computer program can be written using the basic control structures which can be combined in any way necessary to deal with a given problem.

The selection structure tests a condition, then executes one sequence of statements instead of another, depending on whether the condition is true or false. A condition is any variable or expression that returns a Boolean value (TRUE, FALSE, or NULL). The iteration structure executes a sequence of statements repeatedly as long as a condition holds true. The sequence structure simply executes a sequence of statements in the order in which they occur.


Conditional Control: IF Statements

Often, it is necessary to take alternative actions depending on circumstances. The IF statement lets you execute a sequence of statements conditionally. That is, whether the sequence is executed or not depends on the value of a condition. There are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF.

The third form of IF statement uses the keyword ELSIF (NOT ELSEIF) to introduce additional conditions, as follows:

IF condition1 THEN sequence_of_statements1; ELSIF condition2 THEN sequence_of_statements2; ELSE sequence_of_statements3; END IF;

Iterative Control: LOOP and EXIT Statements

LOOP statements let you execute a sequence of statements multiple times. There are three forms of LOOP statements: LOOP, WHILE-LOOP, and FOR-LOOP.


LOOP

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, as follows:

LOOP sequence_of_statements3; ... 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 statement to complete the loop. You can place one or more EXIT statements anywhere inside a loop, but nowhere outside a loop. There are two forms of EXIT statements: EXIT and EXIT-WHEN.

The EXIT statement forces a loop to complete unconditionally. When an EXIT statement is encountered, the loop completes immediately and control passes to the next statement.

LOOP ... IF ... THEN ... EXIT; -- exit loop immediately END IF; END LOOP; -- control resumes here

The EXIT-WHEN statement allows a loop to complete conditionally. When the EXIT statement is encountered, the condition in the WHEN clause is evaluated. If the condition evaluates to TRUE, the loop completes and control passes to the next statement after the loop.

LOOP FETCH c1 INTO ... EXIT WHEN c1%NOTFOUND; -- exit loop if condition is true ... END LOOP; CLOSE c1;

Until the condition evaluates to TRUE, the loop cannot complete. So, statements within the loop must change the value of the condition.

Like PL/SQL blocks, loops can be labeled. The label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the LOOP statement, as follows:

<<label_name>> LOOP sequence_of_statements; ... END LOOP [label_name];

Optionally, the label name can also appear at the end of the LOOP statement.

With either form of EXIT statement, you can complete not only the current loop, but any enclosing loop. Simply label the enclosing loop that you want to complete, then use the label in an EXIT statement.

<<outer>> LOOP ... LOOP ... EXIT outer WHEN ... -- exit both loops END LOOP; ... END LOOP outer;
WHILE-LOOP

The WHILE-LOOP statement associates a condition with a sequence of statements enclosed by the keywords LOOP and END LOOP, as follows:

WHILE condition LOOP sequence_of_statements; ... END LOOP;

Before each iteration of the loop, the condition is evaluated. If the condition evaluates to TRUE, the sequence of statements is executed, then control resumes at the top of the loop. If the condition evaluates to FALSE or NULL, the loop is bypassed and control passes to the next statement. Since the condition is tested at the top of the loop, the sequence might execute zero times.


FOR-LOOP

Whereas the number of iteration 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. FOR loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP.

FOR counter IN [REVERSE] lower_bound..upper_bound LOOP sequence_of_statements; ... END LOOP;

The lower bound need not be 1. However, the loop counter increment (or decrement) must be 1.

PL/SQL lets you determine the loop range dynamically at run time, as the following example shows:

SELECT COUNT(empno) INTO emp_count FROM emp; FOR i IN 1..emp_count LOOP ... END LOOP;

The loop counter is defined only within the loop. You cannot reference it outside the loop. You need not explicitly declare the loop counter because it is implicitly declared as a local variable of type INTEGER.

The EXIT statement allows a FOR loop to complete prematurely. You can complete not only the current loop, but any enclosing loop.


Sequential Control: GOTO and NULL statements

Unlike the IF and LOOP statements, the GOTO and NULL statements are not crucial to PL/SQL programming. The structure of PL/SQL is such that the GOTO statement is seldom needed. Occasionally, it can simplify logic enough to warrant its use. The NULL statement can make the meaning and action of conditional statements clear and so improve readability.

BEGIN ... GOTO insert_row; ... <<insert_row>> INSERT INTO emp VALUES ... END;

A GOTO statement cannot branch into an IF statement, LOOP statement, or sub-block. A GOTO statement cannot branch from one IF statement clause to another. A GOTO statement cannot branch out of a subprogram. Finally, a GOTO statement cannot branch from an exception handler into the current block.

The NULL statement explicitly specifies inaction; it does nothing other than pass control to the next statement. It can, however, improve readability. Also, the NULL statement is a handy way to create stubs when designing applications from the top down.


Interaction With ORACLE

SQL Support

By extending SQL, PL/SQL offers a unique combination of power and ease of use. You can manipulate ORACLE data flexibly and safely because PL/SQL supports all SQL data manipulation commands (except EXPLAIN PLAN), transaction control commands, functions, pseudocolumns, and operators. However, PL/SQL does not support data definition commands such as CREATE, session control commands such as SET ROLES, or the system control command ALTER SYSTEM.


Data Manipulation

To manipulate ORACLE data, you use the INSERT, UPDATE, DELETE, SELECT, and LOCK TABLE commands.


Transaction Control

ORACLE is transaction oriented; that is, ORACLE uses transactions to ensure data integrity. A transaction is a series of SQL data manipulation statements that does a logical unit of work. For example, two UPDATE statements might credit one bank account and debit another. At the same instant, ORACLE makes permanent or undoes all database changes made by a transaction. If your program fails in the middle of a transaction, ORACLE detects the error and rolls back the transaction. Hence, the database is restored to its former state automatically.

You use the COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION commands to control transactions. COMMIT makes permanent any database changes made during the current transaction. Until you commit your changes, other users cannot see them. ROLLBACK ends the current transaction and undoes any changes made since the transaction began. SAVEPOINT marks the current point in the processing of a transaction. Used with ROLLBACK, undoes part of a transaction. SET TRANSACTION establishes a read-only transaction.


SQL Functions

PL/SQL lets you use all the SQL functions including group functions, which summarize entire columns of ORACLE data.


SQL Pseudocolumns

PL/SQL recognizes the following SQL pseudocolumns, which return specific data items: CURRVAL, LEVEL, NEXTVAL, ROWID, and ROWNUM.

For example, NEXTVAL returns the next value in a database sequence. They are called pseudocolumns because they are not actual columns in a table but behave like columns. For instance, you can reference pseudocolumns in SQL statements. Furthermore, you can select values from a pseudocolumn. However, you cannot insert values into, update values in, or delete values from a pseudocolumn. Assume that you have declared empno_seq as a database sequence, then the following statement inserts a new employee number into the emp table:

INSERT INTO emp VALUES (empno_seq.NEXTVAL, new_ename, ...);

A sequence is a database object that generates sequential numbers. When you create a sequence, you can specify its initial value and an increment. CURRVAL returns the current value in a specified sequence. Before you can reference CURRVAL in a session, you must use NEXTVAL to generate a number.

LEVEL is used with the SELECT CONNECT BY statement to organize rows from a database table into a tree structure. LEVEL returns the level number of a node in a tree structure. The root is level 1, children of the root are level 2 and so on. You specify the direction in which the query walks the tree (down from the root or up from the branches) with the PRIOR operator. In the START WITH clause, you specify a condition that identifies the root of the tree.

ROWID returns the rowid (binary address) of a row in a database table.

ROWNUM returns a number indicating the order in which a row was selected from a table. If a SELECT statement includes an ORDER BY clause, ROWNUMs are assigned to the retrieved rows before the sort is done.


Operators

PL/SQL lets you use all the SQL comparison, set, and row operators in SQL statements.


Cursor Management

PL/SQL uses two types of cursors: implicit and explicit. PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row. However, for queries that return more than one row, you must declare an explicit cursor or use a cursor FOR loop


Explicit Cursors

The set of rows returned by a query can consist of zero, one, or multiple rows, depending on how many rows meet your search criteria. When a query returns multiple rows, you can explicitly define a cursor to process the rows. You define a cursor in a declarative part of a PL/SQL block, subprogram, or package by naming it and specifying a query. Then, you use three commands to control the cursor: OPEN, FETCH, and CLOSE.

Forward references are not allowed in PL/SQL. So, you must declare a cursor before referencing it in other statements. When you declare a cursor, you name it and associate it with a specific query. The cursor name is an undeclared identifier, not a PL/SQL variable; it is used only to reference a query.

Cursors can take parameters, as the example below shows. A cursor parameter can appear in a query wherever a constant can appear. The formal parameters of a cursor must be IN parameter.

CURSOR c1 (median IN NUMBER) IS SELECT job, ename FROM emp WHERE sal > median;

To declare formal cursor parameters, you use the syntax:

CURSOR name [ (parameter [, parameter, ...]) ] IS

where parameter stands for the following syntax:

variable_name [IN] datatype [{:= | DEFAULT} value]

OPENing the cursor executes the query and identifies the active set, which consists of all rows that meet the query search criteria. For cursors declared using the FOR UPDATE clause, the OPEN statement also locks those rows. Rows in the active set are not retrieved when the OPEN statement is executed. Rather, the FETCH statement retrieves the rows.

The FETCH statement retrieves the rows in the active set one at a time. Each time FETCH is executed, the cursor advances to the next row in the active set. For each column value returned by the query associated with the cursor, there must be a corresponding variable in the INTO list. Also, their datatypes must be compatible. Any variables in the WHERE clause of the query associated with the cursor are evaluated only when the cursor is OPENed. As the following example shows, the query can reference PL/SQL variables within its scope:

DECLARE my_sal emp.sal%TYPE; my_job emp.job%TYPE; factor INTEGER := 2; cursor c1 IS SELECT factor*sal FROM emp WHERE job = my_job; BEGIN ... OPEN c1; -- here factor equals 2 LOOP FETCH c1 INTO my_sal; EXIT WHEN c1%NOTFOUND; ... factor := factor + 1; -- does not affect FETCH END LOOP; CLOSE c1; END;
Explicit Cursor Attributes

Each cursor that you explicitly define has four attributes: %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN. When appended to the cursor name, these attributes let you access useful information about the execution of a multirow query. You can use explicit cursor attributes in procedural statements but not in SQL statements.

  • Using %NOTFOUND. When a cursor is OPENed, the rows that satisfy the associated query are identified and form the active set. Rows are FETCHed from the active set one at a time. If the last fetch returned a row, %NOTFOUND evaluates to FALSE. If the last fetch failed to return a row (because the active set was empty), %NOTFOUND evaluates to TRUE. FETCH is expected to fail eventually, so when that happens, no exception is raised.

    Before the first fetch, %NOTFOUND evaluates to NULL. So, if FETCH never executes successfully, the loop is never exited unless your EXIT WHEN statement is as follows:

    EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

    You can open multiple cursors, then use %NOTFOUND to tell which cursors have rows left to fetch.

  • Using %FOUND. %FOUND is the logical opposite of %NOTFOUND. After an explicit cursor is open but before the first fetch, %FOUND evaluates to NULL. Thereafter, it evaluates to TRUE if the last fetch returned a row or to FALSE if no row was returned.

  • Using %ROWCOUNT. When you open its cursor, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT returns a zero. Thereafter, it returns the number of rows fetched so far. The number is incremented if the latest fetch returned a row.

  • Using %ISOPEN. %ISOPEN evaluates to TRUE if its cursor is open; otherwise, %ISOPEN evaluates to FALSE.

Implicit Cursors

ORACLE implicitly opens a cursor to process each SQL statement not associated with an explicitly declared cursor. PL/SQL lets you refer to the most recent implicit cursor as the ``SQL'' cursor. So, although you cannot use the OPEN, FETCH, and CLOSE statements to control an implicit cursor, you can still use cursor attributes to access information about the most recently executed SQL statement.


Implicit Cursor Attributes

The SQL cursor has four attributes: %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN. When appended to the cursor name (SQL), these attributes let you access information about the execution of INSERT, UPDATE, DELETE, and SELECT INTO statements. You can use implicit cursor attributes in procedural statements but not in SQL statements.

  • Using %NOTFOUND. The features of %NOTFOUND are similar to those of the explicit cursor attributes but you must bear in mind the following: if a SELECT INTO fails to return a row, the predefined exception NO_DATA_FOUND is raised whether you check %NOTFOUND on the next line or not. The check for %NOTFOUND on the next line would be useless because when NO_DATA_FOUND is raised, normal execution stops and control transfers to the exception-handling part of the block. In this situation %NOTFOUND is useful in the OTHERS exception handler. Instead of coding a NO_DATA_FOUND handler, you find out if that exception was raised by checking %NOTFOUND. DECLARE my_sal NUMBER(7,2); my_empno NUMBER(4); BEGIN ... SELECT sal INTO my_sal FROM emp WHERE empno = my_empno; -- might raise NO_DATA_FOUND EXCEPTION WHEN OTHERS THEN IF SQL%NOTFOUND THEN -- check for 'no data found' ... END IF; ... END;

    However, a SELECT INTO that calls a SQL group function never raises the exception NO_DATA_FOUND. That is because group functions such as AVG and SUM always return a value or a null.

  • Using %FOUND, %ROWCOUNT and %ISOPEN. These attributes are similar in use to those of explicit cursor attributes.

Packaged Cursors

You can separate a cursor specification from its body for placement in a package by using the RETURN clause:

CREATE PACKAGE emp_actions AS /* Declare cursor specification */ CURSOR c1 RETURN emp%ROWTYPE ... END emp_action; CREATE PACKAGE BODY emp_actions AS /* Define cursor body */ CURSOR c1 RETURN emp%ROWTYPE SELECT * FROM emp WHERE sal > 3000; ... END emp_actions;

This way, you can change the cursor body without changing the cursor specification. A cursor specification has no SELECT statement because the RETURN clause defines the datatype of the result value.

A cursor body must have a SELECT statement and the same RETURN clause as its corresponding cursor specification. Furthermore, the number and datatypes of select-list items in the SELECT statement must match the RETURN clause.


Cursor FOR Loops

You can use a cursor FOR loop to simplify coding. A cursor FOR loop implicitly declares its loop index as a record of type %ROWTYPE, opens a cursor, repeatedly fetches rows of values from the active set into fields in the record, then closes the cursor when all rows have been processed or when you exit the loop.

You can pass parameters to a cursor used in a cursor FOR loop. In the following example, you pass a department number. Then, you compute the total wages paid to employees in that department. Also, you determine how many employees have salaries higher than $2000 and how many have commissions larger than their salaries.

DECLARE CURSOR emp_cursor(dnum NUMBER) IS SELECT sal, comm FROM emp WHERE deptno = dnum; total_wages NUMBER(11,2) := 0; high_paid NUMBER(4) := 0; higher_comm NUMBER(4) := 0; BEGIN /* the number of iterations will equal the number of rows * * returned by emp_cursor */ FOR emp_record IN emp_cursor(20) LOOP emp_record.comm := NVL(emp_record.comm,0); total_wages := total_wages + emp_record.sal + emp_record.comm; IF emp_record.sal > 2000 THEN high_paid := high_paid + 1; END IF; END LOOP; INSERT INTO temp VALUES (high_paid, higher_comm, 'Total Wages: ' || TO_CHAR(total_wages)); COMMIT; END;

Overriding Default Locking

By default ORACLE locks data structures for you automatically. However, you can request specific data locks on rows or tables when it is to your advantage to override default locking.

  • Using FOR UPDATE. When declaring a cursor that will be referenced in the WHERE CURRENT OF clause of an UPDATE or DELETE statement, you must use the FOR UPDATE clause to acquire exclusive row locks. If present, the FOR UPDATE clause must appear at the end of the cursor declaration, as the following example shows. DECLARE CURSOR c1 IS SELECT empno, sal FROM emp WHERE job = 'SALESMAN' AND comm > sal FOR UPDATE;

    The FOR UPDATE clause indicates that rows will be updated or deleted and locks all rows in the active set. All rows in the active set are locked when you OPEN the cursor. The rows are unlocked when you COMMIT the transaction. So, you cannot FETCH from a FOR UPDATE cursor after a COMMIT. When querying multiple tables, you can use the FOR UPDATE OF clause to confine row locking to particular tables.

    DECLARE CURSOR c1 IS SELECT ename, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND job = 'MANAGER' FOR UPDATE OF sal;
  • Using a 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. Table locks are released when your transaction issues a COMMIT or ROLLBACK. LOCK TABLE emp IN ROW SHARE MODE NOWAIT;

Database Triggers

A database trigger is a stored PL/SQL program unit associated with a specific database table. ORACLE executes (fires) the database trigger automatically whenever a given SQL operation affects the table. So, unlike subprograms, which must be invoked explicitly, database triggers are invoked implicitly. Among other things, you can use database triggers to

  • audit data modification

  • log events transparently

  • enforce complex business rules

  • derive column values automatically

  • implement complex security authorizations

  • maintain replicate tables

You can associate up to 12 database triggers with a give table. A database trigger has three parts: a triggering event, an optional trigger constraint, and a trigger action. When the event occurs, the database trigger fires and an anonymous PL/SQL block performs the action. Database triggers fire with the privileges of the owner, not the current user. So, the owner must have appropriate access to all objects referenced by the trigger action.

The example below illustrates transparent event logging. The database trigger named reorder ensures that a part is reordered when its quantity on hand drops below the reorder point.

CREATE TRIGGER reorder /* triggering event */ AFTER UPDATE OF qty_on_hand ON inventory -- table FOR EACH ROW /* trigger constraint */ WHEN (new.reorderable = 'T') BEGIN /* trigger action */ IF :new.qty_on_hand < :new.reorder_point THEN INSERT INTO pending_orders VALUES (:new.part_no, :new.reorder_qty, SYSDATE); END IF; END;

The name in the ON clause identifies the database table associated with the database trigger. The triggering event specifies the SQL data manipulation statement that affects the table. In this case, the statement is UPDATE. If the trigger statement fails, it is rolled back. The keyword AFTER specifies that the database trigger fires after the update is done.

By default, a database trigger fires once per table. The FOR EACH ROW option specifies that the trigger fires once per row. For the trigger to fire, however, the Boolean expression in the WHEN clause must evaluate to TRUE.

The prefix :new is a correlation name that refers to the newly updated column value. Within a database trigger, you can reference :new and :old values of changing rows. Notice that the colon is not used in the WHEN clause. You can use the REFERENCING clause (not shown) to replace :new and :old with other correlation names.

Except for transaction control statements such as COMMIT and ROLLBACK, any SQL or procedural statement, including subprogram calls, can appear in the BEGIN...END block. A database trigger can also have DECLARE and EXCEPTION sections.

The next example shows that the trigger action can include calls to the built-in ORACLE procedure raise_application_error, which lets you issue user-defined error messages:

CREATE TRIGGER check_salary BEFORE INSERT OR UPDATE OF sal, job ON emp FOR EACH ROW WHEN (new.job != 'PRESIDENT') DECLARE minsal NUMBER; maxsal NUMBER; BEGIN /* Get salary range for a given job from table sals. */ SELECT losal, hisal INTO minsal, maxsal FROM sals WHERE job = :new.job; /* If salary is out of range, increase is negative, * * or increase exceeds 10%, raise an exception. */ IF (:new.sal < minsal OR :new.sal > maxsal) THEN raise_application_error(-20225, 'Salary out of range'); ELSIF (:new.sal < :old.sal) THEN raise_application_error(-20320, 'Negative increase'); ELSIF (:new.sal > 1.1 * :old.sal) THEN raise_application_error(-20325, 'Increase exceeds 10%'); END IF: END;

More information on built-in procedures is provided later in this chapter. For a full discussion of database triggers, see ``ORACLE7 Server Application Developer's Guide''.


Error Handling

Overview

In PL/SQL a warning or error condition is called an exception. Exceptions can be internally defined (by the runtime system) or user-defined.

Examples of internally defined exceptions include division by zero and out of memory. Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names.

You can define exceptions of your own in the declarative part of any PL/SQL block, subprogram, or package. For example, you might define an exception named insufficient_funds to flag an overdrawn bank accounts. Unlike internal exceptions, user-defined exceptions must be given names.

When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions.

To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment.


Advantages of Exceptions

Using exceptions for error handling has several advantages. Without exception handling, every time you issue a command, you must check for execution errors. Exceptions also improve reliability. You need not worry about checking for an error at every point it might occur. Just add an exception handler to your PL/SQL block. If the exception is ever raised in that block (or any sub-block), you can be sure it will be handled.


Predefined Exceptions

An internal exception is raised explicitly whenever your PL/SQL program violates an ORACLE rule or exceeds a system-dependent limit. Every ORACLE error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common ORACLE errors as exceptions. For example, the predefined exception NO_DATA_FOUND is raised if a SELECT INTO statement returns no rows.

PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names shown below:

Exception Name ORACLE Error SQLCODE Value CURSOR_ALREADY OPEN ORA-06511 -6511 DUP_VAL_ON_INDEX ORA-00001 -1 INVALID_CURSOR ORA-01001 -1001 INVALID_NUMBER ORA-01722 -1722 LOGIN_DENIED ORA-01017 -1017 NO_DATA_FOUND ORA-01403 +100 NOT_LOGGED_ON ORA-01012 -1012 PROGRAM_ERROR ORA-06501 -6501 STORAGE_ERROR ORA-06500 -6500 TIMEOUT_ON_RESOURCE ORA-00051 -51 TOO_MANY_ROWS ORA-01422 -1422 VALUE_ERROR ORA-06502 -6502 ZERO_DIVIDE ORA-01476 -1476
  • CURSOR_ALREADY_OPEN is raised if you try to OPEN an already open cursor.

  • DUP_VAL_ON_INDEX is raised if you try to store duplicate values in a database column that is constrained by a unique index.

  • INVALID_CURSOR is raised if you try an illegal cursor operation. For example, if you try to CLOSE an unopened cursor.

  • INVALID_NUMBER is raised in a SQL statement if the conversion of a character string to a number fails.

  • LOGIN_DENIED is raised if you try logging on to ORACLE with an invalid username/password.

  • NO_DATA_FOUND is raised if a SELECT INTO statement returns no rows or if you reference an uninitialized row in a PL/SQL table.

  • NOT_LOGGED_ON is raised if your PL/SQL program issues a database call without being logged on to ORACLE.

  • PROGRAM_ERROR is raised if PL/SQL has an internal problem.

  • STORAGE_ERROR is raised if PL/SQL runs out of memory or if memory is corrupted.

  • TIMEOUT_ON_RESOURCE is raised if a timeout occurs while ORACLE is waiting for a resource.

  • TOO_MANY_ROWS is raised if a SELECT INTO statement returns more than one row.

  • VALUE_ERROR is raised if an arithmetic, conversion, truncation, or constraint error occurs.

  • ZERO_DIVIDE is raised if you try to divide a number by zero.

User-defined Exceptions

PL/SQL lets you define exceptions of your own. Unlike predefined exceptions, user-defined exceptions must be declared and must be raised explicitly by RAISE statements. Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an exception by introducing its name, followed by the keyword EXCEPTION.

DECLARE past_due EXCEPTION; acct_num NUMBER(5); BEGIN

Exceptions and variable declarations are similar. But remember, an exception is an error condition, not an object. Unlike variables, exceptions cannot appear in assignment statements or SQL statements. However, the same scope rules apply to variables and exceptions.

  • Using EXCEPTION_INIT. To handle unnamed internal exceptions, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A pragma is a compiler directive, which can be thought of as a parenthetical remark to the compiler.

    In PL/SQL, the predefined pragma EXCEPTION_INIT tells the compiler to associate an exception name with an ORACLE error number. That allows you to refer to any internal exception by name and to write a specific handler for it.

    You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the syntax

    PRAGMA EXCEPTION_INIT(exception_name, ORACLE_error_number);

    where exception_name is the name of a previously declared exception.

    DECLARE insufficient_privileges EXCEPTION; PRAGMA EXCEPTION_INIT(insufficient_privileges, -1031); ----------------------------------------------------- -- ORACLE returns error number -1031 if, for example -- you try to UPDATE a table for which you have only -- SELECT privileges ----------------------------------------------------- BEGIN ... EXCEPTION WHEN insufficient_privileges THEN -- handle the error ... END;
  • Using raise_application_error. A package named DBMS_STANDARD (part of the Procedural Database Extention) provides language facilities that help your application interact with ORACLE. This package includes a procedure named raise_application_error, which lets you issue user-defined error messages from a stored subprogram or database trigger. The calling syntax is raise_application_error(error_number, error_message);

    where error_number is a negative integer in the range -20000..-20999 and error_message is a character string up to 512 bytes in length.

    An application can call raise_application_error only from an executing stored subprogram. When called, raise_application_error ends a subprogram, rolls back any database changes it made, and returns a user-defined error message to the application.

    PROCEDURE raise_salary (emp_id NUMBER, increase NUMBER) IS current_salary NUMBER; BEGIN SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; IF current_salary is NULL THEN raise_application_error(-20101, 'Salary is missing'); ELSE UPDATE emp SET sal = current_salary + increase WHERE empno = emp_id; END IF; END raise_salary;

    The calling application gets a PL/SQL exception, which it can process using the error-reporting functions SQLCODE and SQLERRM in an OTHERS handler. Furthermore, it can use EXCEPTION_INIT to map specific error numbers returned by raise_application_error to exceptions of its own.

    DECLARE ... null_salary EXCEPTION; PRAGMA EXCEPTION_INIT(null_salary, -20101); ...

How Exceptions Are Raised

Internal exceptions are raised implicitly by the runtime system, as are user-defined exceptions that you have associated with an ORACLE error number using EXCEPTION_INIT. However, other user-defined exceptions must be raised explicitly by RAISE statements.

  • Using RAISE statement. PL/SQL blocks and subprograms should RAISE an exception only when an error makes it undesirable or impossible to finish processing. You can code a RAISE statement for a given exception anywhere within the scope of that exception. DECLARE out_of_stock EXCEPTION; number_on_hand NUMBER(4); BEGIN ... IF number_on_hand < 1 THEN RAISE out_of_stock; END IF; ... EXCEPTION WHEN out_of_stock THEN -- handle the error END;

    You can also raise a predefined exception explicitly:

    RAISE INVALID_NUMBER;

    That way, you can use an exception handler written for the predefined exception to process other errors.

    Sometimes, you want to reraise an exception, that is, handle it locally, then pass it to an enclosing block. To reraise an exception, simply place a RAISE statement in the local handler, as shown below

    DECLARE out_of_balance EXCEPTION; BEGIN ... ------------- beginning of sub-block ----------------------- BEGIN ... IF ... THEN RAISE out_of_balance; -- raise the exception END IF; ... EXCEPTION WHEN out_of_balance THEN -- handle the error RAISE; -- reraise the current exception END; ------------- end of sub-block ----------------------------- EXCEPTION WHEN out_of_balance THEN - handle the error differently ... END;

    Omitting the exception name in a RAISE statement, which is allowed only in an exception handler, reraises the current exception.


Handling Raised Exceptions

When an exception is raised, normal execution of your PL/SQL block or subprogram stops and control transfers to its exception-handling part and control does NOT return to where the exception was raised. In other words, you cannot resume processing where you left off.

The optional OTHERS exception handler, which is always the last handler in a block or subprogram, acts as the handler for all exceptions not named specifically. Use of the OTHERS handler guarantees that no exception will go unhandled.

If you want two or more exceptions to execute the same sequence of statements, list the exception names in the WHEN clause, separating them by the keyword OR. The keyword OTHERS cannot appear in the list of exception names; it must appear by itself.

... EXCEPTION WHEN ... THEN - handle the error differently WHEN ... OR ... THEN - handle the error differently ... WHEN OTHERS THEN - handle the error differently END;
  • Using SQLCODE and SQLERRM. You cannot use SQLCODE and SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement. DECLARE err_num NUMBER; err_msg CHAR(100); BEGIN ... WHEN OTHERS THEN err_num := SQLCODE; err_msg := SUBSTR(SQLERRM, 1, 100); INSERT INTO errors VALUES (err_num, err_msg); END;

    The string function SUBSTR ensures that a VALUE_ERROR exception (for truncation) is not raised when you assign the value of SQLERRM to err_msg. SQLCODE and SQLERRM are especially useful in the OTHERS exception handler because they tell you which internal exception was raised.


    Subprograms

    Subprograms are named PL/SQL blocks that can take parameters and be invoked. PL/SQL has two types of subprograms called procedures and functions. Generally, you use a procedure to perform an action and a function to compute a value.

    Like unnamed or anonymous PL/SQL blocks, subprograms have a declarative part, an executable part, and an optional exception-handling part.


    Procedures

    A procedure is a subprogram that performs a specific action. You write procedures using the syntax

    PROCEDURE name [ (parameter, [, parameter, ...]) ] IS [local declarations] BEGIN executable statements [EXCEPTION] exception-handlers] END [name];

    where parameter stands for the following syntax

    var_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} value]

    Unlike the datatype specifier in a variable declaration, the datatype specifier in a parameter declaration must be unconstrained.

    PROCEDURE ... (name CHAR(20) ) IS -- illegal; should be CHAR

    The procedure specification begins with the keyword PROCEDURE and ends with the procedure name or a parameter list. The procedure body begins with the keyword IS and ends with the keyword END followed by an optional procedure name.

    PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL; salary_missing EXCEPTION; BEGIN SELECT sal INTO current_salary FROM emp WHERE empno = emp_id; IF current_salary IS NULL THEN RAISE salary_missing; ESLE UPDATE emp SET sal = sal + increase WHERE empno = emp_id; END IF; [EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO emp_audit VALUES (emp_id, 'No such number'); WHEN salary_missing THEN INSERT INTO emp_audit VALUES (emp_id, 'Salary is null'); END raise_salary;

    Functions

    A function is a subprogram that computes a value. Functions and procedure are structured alike, except that functions have a RETURN clause. You write functions using the syntax

    FUNCTION name [ (parameter, [, parameter, ...]) ] RETURN datatype IS [local declarations] BEGIN executable statements [EXCEPTION exception-handlers] END [name];

    where parameter stands for the following syntax

    var_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} value]

    The function body begins with the keyword IS and ends with the keyword RETURN clause, which specifies the datatype of the result value.

    Calls to user-defined functions can appear in procedural statements, but not in SQL statements.

    FUNCTION sal_ok (salary REAL, title REAL) RETURN BOOLEAN IS min_sal REAL; max_sal REAL; BEGIN SELECT losal, hisal INTO min_sal, max_sal FROM sals WHERE job = title; RETURN (salary >= min_sal) AND (salary <= max_sal); END sal_ok;

    RETURN Statement

    The RETURN statement immediatedly completes the execution of a subprogram and returns control to the caller. Execution then resumes with the statement following the subprogram call. A subprogram can contain several RETURN statements, none of which need be the last lexical statement.

    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 reached. A function must contain at least one RETURN statement. Otherwise, PL/SQL raises the predefined exception PROGRAM_ERROR at run time.


    Forward Declarations

    PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. PL/SQL solves the problem of subprograms used before they are declared by providing a special subprogram declaration called forward declaration.

    A forward declaration consists of a subprogram specification terminated by a semicolon.

    DECLARE PROCEDURE calc_rating (...); -- forward declaration /* Define subprogram in alphabetical order */ PROCEDURE award_bonus (..) IS BEGIN calc_rating(..); ... END; PROCEDURE calc_rating (...) IS BEGIN ... END; ...

    Although the formal parameter list appears in the forward declaration, it must also appear in the subprogram body. You can place the subprogram body anywhere after the forward declaration, but they must appear in the same block, subprogram, or package.


    Packaged Subprograms

    Forward declarations also let you group logically related subprograms in a package. The subprogram specifications go in the package specification, and the subprogram bodies go in the package body, where they are invisible to applications. Thus, packages allow you to hide implementation details.


    Actual versus Formal Parameters

    Subprograms pass information using parameters. The variables or expressions referenced in the parameter list of a subprogram call are actual parameters. The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters.

    The actual parameter and its corresponding formal parameter must belong to compatible datatypes.

    When calling a subprogram, you can write the actual parameters using either positional or named notation. For example, the call to the procedure raise_salary can be made as follows:

    raise_salary(emp, inc); raise_salary(increase => inc, emp_id => emp) raise_salary(emp, increase => inc)

    The first procedure call uses positional notation, the second uses named notation, and the third uses mixed notation.


    Parameter Modes

    You use parameter modes to define the behavior of formal parameters. The three parameter modes, IN (the default), OUT, and IN OUT, can be used with any subprogram. However, avoid using the OUT and IN OUT modes with functions.

    • an IN parameter lets you pass values to the subprogram being called. Inside the subprogram, an IN parameter acts like a constant. Therefore, it cannot be assigned a value. Unlike OUT and IN OUT parameters, an IN parameter can be initialized to default values.

    • an OUT parameter lets you return values to the caller of a subprogram. Inside the subprogram, an OUT parameter acts like an uninitialized variable. Therefore, its value cannot be assigned to another variable or reassigned to itself.

      The actual parameter that corresponds to an OUT formal parameter must be a variable; it cannot be a constant or expression.

      An OUT actual parameter can (but need not) have a value before the subprogram is called. However, the value is lost when you call the subprogram.

      Before exiting a subprogram, 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.

    • an IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller. Inside the subprogram, an IN OUT parameter acts like an initialized variable.
      The actual parameter that corresponds to an IN OUT formal parameter must be a variable; it cannot be a constant or expression.

    Overloading

    PL/SQL lets you overload subprogram names. That is, you can use the same name for several different subprograms as long as their formal parameters differ in number, order, or datatype family.

    DECLARE TYPE DateTabTyp IS TABLE OF DATE INDEX BY BINARY_INTEGER; TYPE RealTabTyp IS TABLE OF REAL INDEX BY BINARY_INTEGER; hiredate_tab DateTabTyp; sal_tab RealTabTyp; ...

    You might write the following procedures to initialize the PL/SQL tables named initialize for hiredate_tab and sal_tab.

    PROCEDURE initialize (tab OUT DateTabTyp, n INTEGER) IS BEGIN FOR i IN 1..n LOOP tab(i) := SYSDATE; END LOOP; END initialize; PROCEDURE initialize (tab OUT RealTabTyp, n INTEGER) IS BEGIN FOR i IN 1..n LOOP tab(i) := 0.0; END LOOP; END initialize;

    Because the processing in these two procedures is the same, it is logical to give them the same name. You can place the two overloaded initialize procedures in the same block, subprogram, or package. PL/SQL determines which of the two procedures is being called by checking their formal parameters.

    You cannot overload the names of stand-alone subprograms. You cannot overload two subprograms if their formal parameters differ only in name or parameter mode. You cannot overload two subprograms if their formal parameters differ only in datatype and the different datatypes are in the same family (REAL and INTEGER). Finally, you cannot overload two functions that differ only in return type even if the types are in different families.


    Stored Subprograms

    Subprograms can be compiled separately and stored permanently in an ORACLE database, ready to be executed.

    Stored subprograms offer higher productivity, better performance, memory savings, application integrity, and tighter security. Stored subprograms can help enforce data security. You can restrict users to specific database operations by granting access only through subprograms. For example you can grant users EXECUTE access to a stored procedure that updates the emp table, but not grant them access to the table itself. That way, users can call the procedure, but cannot arbitrarily manipulate table data.

    You can call stored subprograms from a database trigger, another stored subprogram, an ORACLE Precompiler application, an OCI application, or an ORACLE tool such as SQL*Plus.


    PACKAGES

    A package is a database object that groups logically related PL/SQL types, objects, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification is the interface to your application; 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.

    Unlike subprograms, packages cannot be called, passed parameters, or nested. Still, the format of a package is similar to that of a subprogram:

    PACKAGE name IS -- specification (visible part) -- public type and object declarations -- subprogram specifications END [name}; PACKAGE BODY name IS -- body (hidden part) -- private type and object declarations -- subprogram bodies [BEGIN -- initialization statements] END [name];

    Packages are created interactively with SQL*Plus using the CREATE PACKAGE and CREATE PACKAGE BODY commands. In the following example, a record type, a cursor, and two employment procedures are packaged:

    CREATE PACKAGE emp_actions AS -- specification TYPE EmpRecTyp is RECORD (emp_id INTEGER, salary REAL); CURSOR desc_salary (emp_id NUMBER) RETURN EmpRecTyp; PROCEDURE hire_employee (ename CHAR, job CHAR, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER ); PROCEDURE fire_employee (emp_id NUMBER); END emp_actions; CREATE PACKAGE BODY emp_actions AS -- body CURSOR desc_salary (emp_id NUMBER) RETURN EmpRecTyp IS SELECT empno, sal FROM emp ORDER BY sal DESC; PROCEDURE hire_employee (ename CHAR, job CHAR, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER ); BEGIN INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job, mgr, SYSDATE, sal, comm, deptno ); END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM emp WHERE empno = emp_id; END fire_employee; END emp_actions;

    Packages offer several advantages: modularity, easier application design, information hiding, added functionality, and better performance.


    The Package Specification

    The package specification contains public declarations. The scope of these declarations is local to your database schema and global to the package. The specification lists the package resources available to applications. All information your application needs to use the resources is in the specification.

    Only subprograms and cursors have an underlying implementation or definition. So, if a specification declares only types, constants, variables, and exceptions, the package body is unnecessary.

    To reference the types, objects, and subprograms declared within a package specification, you use dot notation as follows:

    package_name.type_name package_name.object_name package_name.subprogram_name

    The Package Body

    The package body implements the package specification. That is, the package body contains the definition of every cursor and subprogram declared in the package specification. Keep in mind that subprograms defined in a package body are accessible outside the package only if their specification also appear in the package specification.

    The package body can also contain private declarations, which define types and objects necessary for the internal workings of the package. The scope of these declarations is local to the package body. Therefore, the declared types and objects are inaccessible except from within the package body. Unlike a package specification, the declarative part of a package body can contain subprogram bodies.

    Following the declarative part of a package body is the optional initialization part, which typically holds statements that initialize some of the variables previously declared in the package.

    The initialization part of a package plays a minor role because, unlike subprograms, a package cannot be called or passed parameters. As a result, the initialization part of a package is run only once, the first time you reference the package.


    Guidelines

    When writing packages, keep them as general as possible so they can be reused in future applications. Avoid writing packages that duplicate some feature already provided by ORACLE.

    Package specifications reflect the design of your application. So, define them before the package bodies. Place in a specification only the types, objects, and subprograms that must be visible to users of the package.

    To reduce the need for recompiling when code is changed, place as few items as possible in a package specification. Changes to a package body do not require ORACLE to recompile dependent procedures. However, changes to a package specification require ORACLE to recompile every stored subprogram that references the package.






  • Knowledge is that area of ignorance that we arrange and classify.

    Ambrose Bierce

    In Chapter 2, you learned about the PL/SQL scalar datatypes, which can store only one item of data. In this chapter, you learn about the composite datatypes TABLE and RECORD, which can store collections of data. You also learn how to reference and manipulate these collections as whole objects.


    PL/SQL Tables

    Objects of type TABLE are called PL/SQL tables, which are modeled as (but not the same as) database tables. For example, a PL/SQL table of employee names is modeled as a database table with two columns, which store a primary key and character data, respectively. Although you cannot use SQL statements to manipulate a PL/SQL table, its primary key gives you array-like access to rows. Think of the key and rows as the index and elements of a one-dimensional array.

    Like an array, a PL/SQL table is an ordered collection of elements of the same type. Each element has a unique index number that determines its position in the ordered collection. However, PL/SQL tables differ from arrays in two important ways. First, arrays have fixed lower and upper bounds, but PL/SQL tables are unbounded. So, the size of a PL/SQL table can increase dynamically. Second, arrays require consecutive index numbers, but PL/SQL tables do not. This characteristic, called sparsity, allows the use of meaningful index numbers. For example, you can use a series of employee numbers (such as 7369, 7499, 7521, 7566, ...) to index a PL/SQL table of employee names.

    Why Use PL/SQL Tables?

    PL/SQL tables help you move bulk data. They can store columns or rows of Oracle data, and they can be passed as parameters. So, PL/SQL tables make it easy to move collections of data into and out of database tables or between client-side applications and stored subprograms. You can even use PL/SQL tables of records to simulate local database tables.

    Also, with the Oracle Call Interface (OCI) or the Oracle Precompilers, you can bind host arrays to PL/SQL tables declared as the formal parameters of a subprogram. That allows you to pass host arrays to stored functions and procedures.

    Defining TABLE Types

    To create PL/SQL tables, you take two steps. First, you define a TABLE type, then declare PL/SQL tables of that type. You can define TABLE types in the declarative part of any block, subprogram, or package using the syntax

    where table_type_name is a type specifier used in subsequent declarations of PL/SQL tables.

    The INDEX BY clause must specify datatype BINARY_INTEGER, which has a magnitude range of -2147483647 .. 2147483647. If the element type is a record type, every field in the record must have a scalar datatype such as CHAR, DATE, or NUMBER.

    To specify the element type, you can use %TYPE to provide the datatype of a variable or database column. In the following example, you define a TABLE type based on the ename column:

    The next example shows that you can add the NOT NULL constraint to a TABLE type definition and so prevent the storing of nulls in PL/SQL tables of that type:

    An initialization clause is not required (or allowed).

    You can also use %ROWTYPE to specify the element type. In the following example, you define a TABLE type based on the emp table:

    In the final example, you use a RECORD type to specify the element type:

    Function Results

    The example below shows that you can specify a TABLE type in the RETURN clause of a function specification. That allows the function to return a PL/SQL table of the same type.

    Declaring PL/SQL Tables

    Once you define a TABLE type, you can declare PL/SQL tables of that type, as the following examples show:

    The identifiers sal_tab and emp_tab represent entire PL/SQL tables. Each element of sal_tab will store an employee salary. Each element of emp_tab will store a whole employee record.

    A PL/SQL table is unbounded; its index can include any BINARY_ INTEGER value. So, you cannot initialize a PL/SQL table in its declaration. For example, the following declaration is illegal:

    PL/SQL tables follow the usual scoping and instantiation rules. In a package, PL/SQL tables are instantiated when you first reference the package and cease to exist when you end the database session. In a block or subprogram, local PL/SQL tables are instantiated when you enter the block or subprogram and cease to exist when you exit.

    As Parameters

    You can declare PL/SQL tables as the formal parameters of functions and procedures. That way, you can pass PL/SQL tables to stored subprograms and from one subprogram to another. In the following example, you declare PL/SQL tables as the formal parameters of two packaged procedures:

    To define the behavior of formal parameters, you use parameter modes. The OUT and IN OUT modes let you return values to the caller of a subprogram when you exit. 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.

    Referencing PL/SQL Tables

    To reference elements in a PL/SQL table, you specify an index number using the syntax

    where index is an expression that yields a BINARY_INTEGER value or a value implicitly convertible to that datatype. In the following example, you reference an element in the PL/SQL table hiredate_tab:

    As the example below shows, the index number can be negative. (For an exception, see "Using Host Arrays with PL/SQL Tables" .)

    The following example shows that you can reference the elements of a PL/SQL table in subprogram calls:

    Assignments

    You can assign one PL/SQL table to another only if they have the same datatype. For example, the following assignment is legal:

    You can assign the value of an expression to a specific element in a PL/SQL table using the following syntax:

    In the next example, you assign the sum of variables salary and increase to an element in the PL/SQL table sal_tab:

    Note: Until an element is assigned a value, it does not exist. If you reference a nonexistent element, PL/SQL raises the predefined exception NO_DATA_FOUND.

    PL/SQL Tables of Records

    With a PL/SQL table of records, you use the following syntax to reference fields in a record:

    For example, the following IF statement references a field in the record stored by the first element of the PL/SQL table emp_tab:

    Function Results

    When calling a function that returns a PL/SQL table, you use the following syntax to reference elements in the table:

    For example, the following call to the function new_sals references the third element in the PL/SQL table sal_tab:

    If the function result is a PL/SQL table of records, you use the following syntax to reference fields in a record:

    For example, the following call to the function new_depts references the field loc in the record stored by the third element of the PL/SQL table dept_tab:

    Restriction 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:

    Instead, declare a local PL/SQL table to which you can assign the function result, then reference the PL/SQL table directly, as shown in the following example:

    Using PL/SQL Table Attributes

    Attributes are characteristics of an object. For example, a cursor has the attributes %FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT. Likewise, a PL/SQL table has the attributes EXISTS, COUNT, FIRST, LAST, PRIOR, NEXT, and DELETE. They make PL/SQL tables easier to use and your applications easier to maintain. To apply the attributes to a PL/SQL table, you use dot notation, as follows:

    The attributes EXISTS, PRIOR, NEXT, and DELETE take parameters. Each parameter must be an expression that yields a BINARY_INTEGER value or a value implicitly convertible to that datatype.

    DELETE acts like a procedure, which is called as a statement. However, the other PL/SQL table attributes act like a function, which is called as part of an expression.

    Using EXISTS

    EXISTS(n) returns TRUE if the nth element in a PL/SQL table exists. Otherwise, EXISTS(n) returns FALSE. You can use EXISTS to avoid the exception NO_DATA_FOUND, which is raised when you reference a nonexistent element. In the following example, PL/SQL executes the assignment statement only if the element sal_tab(i) exists:

    Using COUNT

    COUNT returns the number of elements that a PL/SQL table contains. For example, if the PL/SQL table ename_tab contains 50 elements, the following IF condition is true:

    COUNT is useful because the future size of a PL/SQL table is unconstrained and therefore unknown. Suppose you fetch a column of Oracle data into a PL/SQL table. How many elements does the PL/SQL table contain? COUNT gives you the answer.

    You can use COUNT wherever an integer expression is allowed. In the following example, you use COUNT to specify the upper bound of a loop range:

    Using FIRST and LAST

    FIRST and LAST return the first and last (smallest and largest) index numbers in a PL/SQL table. If the PL/SQL table is empty, FIRST and LAST return nulls. If the PL/SQL table contains only one element, FIRST and LAST return the same index number, as the following example shows:

    The next example shows that you can use FIRST and LAST to specify the lower and upper bounds of a loop range provided each element in that range exists:

    In fact, you can use FIRST or LAST wherever an integer expression is allowed. In this example, you use FIRST to initialize a loop counter:

    Using PRIOR and NEXT

    PRIOR(n) returns the index number that precedes index n in a PL/SQL table. NEXT(n) returns the index number that succeeds index n. If n has no predecessor, PRIOR(n) returns a null. Likewise, if n has no successor, NEXT(n) returns a null.

    PRIOR and NEXT do not wrap from one end of a PL/SQL table to the other. For example, the following statement assigns a null to n because the first element in a PL/SQL table has no predecessor:

    Note that PRIOR is the inverse of NEXT. For example, the following statement assigns index n to itself:

    You can use PRIOR or NEXT to traverse PL/SQL tables indexed by any series of integers. (Recall that index numbers need not be consecutive.) In the following example, the PL/SQL table is indexed by a series of employee numbers, which begins with 1000:

    Likewise, you can use PRIOR or NEXT to traverse PL/SQL tables from which some elements have been deleted, as the following generic example shows:

    Using DELETE

    This attribute has three forms. DELETE removes all elements from a PL/SQL table. DELETE(n) removes the nth element. If n is null, DELETE(n) does nothing. DELETE(m, n) removes all elements in the range m .. n. If m is larger than n or if m or n is null, DELETE(m, n) does nothing.

    DELETE lets you free the resources held by a PL/SQL table. DELETE(n) and DELETE(m, n) let you prune a PL/SQL table. Consider the following examples:

    If an element to be deleted does not exist, DELETE simply skips it; no exception is raised.

    Note: The amount of memory allocated to a PL/SQL table can increase or decrease dynamically. As you delete elements, memory is freed page by page. If you delete the entire PL/SQL table, all the memory is freed.

    Restriction

    Currently, you cannot use PL/SQL table attributes in a SQL statement. If you try, you get a compilation error, as the following example shows:

    Using PL/SQL Tables

    Mainly, you use PL/SQL tables to move bulk data into and out of database tables or between client-side applications and stored subprograms.

    Retrieving Oracle Data

    You can retrieve Oracle data into a PL/SQL table in three ways: the SELECT INTO statement lets you select a single row of data; the FETCH statement or a cursor FOR loop lets you fetch multiple rows.

    Using the SELECT INTO statement, you can select a column entry into a scalar element. Or, you can select an entire row into a record element. In the following example, you select a row from the database table dept into a record stored by the first element of the PL/SQL table dept_tab:

    Using the FETCH statement, you can fetch an entire column of Oracle data into a PL/SQL table of scalars. Or, you can fetch an entire table of Oracle data into a PL/SQL table of records. In the following example, you fetch rows from a cursor into the PL/SQL table of records emp_tab:

    After loading PL/SQL tables of records this way, you can use them to simulate local database tables.

    Instead of the FETCH statement, you can use a cursor FOR loop, which implicitly declares its loop index as a record, opens the cursor associated with a given query, repeatedly fetches rows of values into fields in the record, then closes the cursor. In the following example, you use a cursor FOR loop to fetch entire columns of Oracle data into the PL/SQL tables ename_tab and sal_tab:

    Alternatively, you can place the cursor FOR loop in a standalone procedure. For example, given the declaration

    you might use the following standalone procedure to fetch all rows from the database table emp into the PL/SQL table of records emp_tab:

    You can also use a cursor FOR loop to fetch Oracle data into packaged PL/SQL tables. For instance, given the declarations

    you might use the following block to fetch the database column empno into the public PL/SQL table empno_tab:

    Inserting Oracle Data

    You must use a loop to insert values from a PL/SQL table into a database column. For example, given the declarations

    you might use the following standalone procedure to insert values from the PL/SQL tables empno_tab and ename_tab into the database table emp:

    Restriction You cannot reference record variables in the VALUES clause. So, you cannot insert entire records from a PL/SQL table of records into rows in a database table. For example, the following INSERT statement is illegal:

    Instead, you must specify one or more fields in the record, as the following example shows:

    Using Host Arrays with PL/SQL Tables

    With the Oracle Call Interface or the Oracle Precompilers, you can bind host arrays of scalars (but not host arrays of structures) to PL/SQL tables declared as the formal parameters of a subprogram. That allows you to pass host arrays to stored functions and procedures.

    You can use a BINARY_INTEGER variable or compatible host variable to index the host arrays. Given the array subscript range m .. n, the corresponding PL/SQL table index range is always 1 .. n - m + 1. For example, if the array subscript range is 5 .. 10, the corresponding PL/SQL table index range is 1 .. (10 - 5 + 1) or 1 .. 6.

    To assign all the values in a host array to elements in a PL/SQL table, you can use a subprogram call. In the Pro*C example below, you pass the host array salary to a PL/SQL block. From the block, you call a local function that declares the PL/SQL table sal_tab as one of its formal parameters. The function call assigns all values in the actual parameter salary to elements in the formal parameter sal_tab.

    Conversely, you can use a subprogram call to assign all values in a PL/SQL table to corresponding elements in a host array. In the Pro*C example below, you call a standalone procedure (not shown), which declares three PL/SQL tables as OUT formal parameters. The corresponding actual parameters are host arrays. When the procedure finishes fetching a batch of employee data into the PL/SQL tables, it assigns all values in the PL/SQL tables to elements in the host arrays.

    Table 4 - 1 shows the legal datatype conversions between row values in a PL/SQL table and elements in a host array. For example, a host array of type VARCHAR2 is compatible with a PL/SQL table of type LONG, LONG RAW, RAW, or VARCHAR2.

    PL/SQL Table
    Host ArrayCHAR DATE LONG LONG RAW NUMBER RAW ROWID VARCHAR2
    CHARF _/
    CHARZ _/
    DATE _/
    DECIMAL _/
    DISPLAY _/
    FLOAT _/
    INTEGER _/
    LONG _/ _/
    LONG VARCHAR _/ _/ _/ _/
    LONG VARRAW _/ _/
    NUMBER _/
    RAW _/ _/
    ROWID _/
    STRING _/ _/ _/ _/
    UNSIGNED _/
    VARCHAR _/ _/ _/ _/
    VARCHAR2 _/ _/ _/ _/
    VARNUM _/
    VARRAW _/ _/
    Table 4 - 1. Legal Datatype Conversions

    ARRAYLEN Statement

    Suppose you pass a host array to a PL/SQL block for processing. By default, when binding the host array, the Oracle Precompilers use its declared dimension. However, you might not want to process the entire array, in which case you can use the ARRAYLEN statement to specify a smaller dimension. ARRAYLEN associates the host array with a host variable, which stores the smaller dimension.

    Let us repeat the first example above using ARRAYLEN to override the default dimension of the host array salary:

    Only 25 array elements are passed to the PL/SQL block because ARRAYLEN downsizes the host array from 100 to 25 elements. As a result, when the PL/SQL block is sent to Oracle for execution, a much smaller host array is sent along. This saves time and reduces network traffic.


    User-Defined Records

    You can use the %ROWTYPE attribute to declare a record that represents a row in a table or a row fetched from a cursor. But, you cannot specify the datatypes of fields in the record or declare fields of your own. The composite datatype RECORD lifts those restrictions.

    As you might expect, objects of type RECORD are called records. Records contain uniquely named fields, which can have different datatypes. Suppose you have various data about an employee such as name, salary, and hire date. These items are dissimilar in type but logically related. A record containing a field for each item lets you treat the data as a logical unit.

    Defining RECORD Types

    Records must be declared in two steps. First, you define a RECORD type, then declare user-defined records of that type. You can define RECORD types in the declarative part of any block, subprogram, or package using the syntax

    where record_type_name is a type specifier used in subsequent declarations of records and field stands for the following syntax:

    You can use the attributes %TYPE and %ROWTYPE to specify field types. In the following example, you define a RECORD type named DeptRecTyp:

    Notice that the field declarations are like variable declarations. Each field has a unique name and specific datatype.

    The next example shows that you can initialize a RECORD type. When you declare a record of type TimeTyp, its three fields assume an initial value of zero.

    You can add the NOT NULL constraint to any field declaration and so prevent the assigning of nulls to that field. Fields declared as NOT NULL must be initialized.

    Nested Records

    PL/SQL lets you define nested records. That is, a record can be the component of another record, as the following example shows:

    Function Results

    The example below shows that 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.

    Declaring Records

    Once you define a RECORD type, you can declare records of that type, as the following example shows:

    The identifier emp_rec represents an entire record.

    Like scalar variables, user-defined records can be declared as the formal parameters of procedures and functions. An example follows:

    Referencing Records

    To reference individual fields in a record, you use dot notation and the following syntax:

    For example, you reference the field hire_date in the record emp_rec as follows:

    You can assign the value of an expression to a specific field using the following syntax:

    In the next example, you convert an employee name to upper case:

    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, you can assign one record to another if they have the same datatype, as the following example shows:

    Records that have different datatypes cannot be assigned to each other even if their fields match exactly.

    Note: A user-defined record and a %ROWTYPE record always have different datatypes.

    Second, you can use the SELECT or FETCH statement to fetch column values into a record, as the example below shows. The column names must appear in the same order as the fields in your record.

    However, you cannot use the INSERT statement to insert user-defined records into a database table. So, the following statement is illegal:

    Also, you cannot assign a list of values to a record using an assignment statement. Therefore, the following syntax is illegal:

    Finally, records cannot be tested for equality, inequality, or nullity. For instance, the following IF conditions are illegal:

    Nested Records

    The example below shows that you can assign one nested record to another if they have the same datatype. Such assignments are allowed even if the parent records have different datatypes.

    Function Results

    When calling a function that returns a user-defined record, you use the following syntax to reference fields in the record:

    For example, the following call to the function nth_highest_sal references the field salary in the user-defined record emp_rec:

    To reference nested fields in a record returned by a function, you use the following syntax:

    For example, the following call to the function calendar_item references the nested field hours in the user-defined record meeting

    Restriction 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:

    You cannot just drop the empty parameter list because the following syntax is also illegal:

    Instead, declare a local user-defined record to which you can assign the function result, then reference its fields directly, as shown in the following example:

    Using Records

    The RECORD type lets you collect information about the attributes of something. The information is easy to manipulate because you can refer to the collection as a whole. In the following example, you collect accounting figures from the database tables assets and liabilities, then use ratio analysis to compare the performance of two subsidiary companies:

    Notice how easy it is to pass the collected figures to the function acid_test, which computes a financial ratio.




    Leave a Comment

    (0 Comments)

    Your email address will not be published. Required fields are marked *