Cover | Table of Contents

Table of Contents

Chapter 1: Oracle PL/SQL Language Pocket Reference

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Oracle PL/SQL Language Pocket Reference is a quick reference guide to the PL/SQL programming language, which provides procedural extensions to the SQL relational database language and a range of Oracle development tools. Where a package, program, or function is supported only for a particular version of the Oracle database (e.g., Oracle Database 11g), we indicate this in the text.

The purpose of this pocket reference is to help PL/SQL users find the syntax of specific language elements. It is not a self- contained user guide; basic knowledge of the PL/SQL programming language is assumed. For more information, see the following O'Reilly books:

Oracle PL/SQL Programming, Fourth Edition, by Steven Feuerstein with Bill Pribyl

Learning Oracle PL/SQL, by Bill Pribyl with Steven Feuerstein

Oracle PL/SQL Best Practices, Second Edition, by Steven Feuerstein

Oracle in a Nutshell, by Rick Greenwald and David C. Kreines

We are grateful to all those who helped in the preparation of this book. In particular, thanks to Bryn Llewellyn for his input on this latest revision as well as the third edition. Thanks as well to first-edition reviewers Eric J. Givler and Stephen Nelson and to second- and third-edition reviewer Jonathan Gennick. In addition, we appreciate all the good work by the O'Reilly crew in editing and producing this book.

UPPERCASE indicates PL/SQL keywords, as well as certain identifiers used by Oracle Corporation as built-in function and package names.

Italic indicates filenames and directories, as well as the first use of a term.

Constant width is used for code examples, literals, and identifiers.

Constant width bold indicates user input in examples showing an interaction.

[] enclose optional items in syntax descriptions.

{} enclose a list of items in syntax descriptions; you must choose one item from the list.

| separates bracketed list items in syntax descriptions.

This section summarizes the fundamental components of the PL/SQL language: characters, identifiers, literals, delimiters, use of comments and pragmas, and construction of statements and blocks.

The PL/SQL language is constructed from letters, digits, symbols, and whitespace, as defined in the following table:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Introduction

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Oracle PL/SQL Language Pocket Reference is a quick reference guide to the PL/SQL programming language, which provides procedural extensions to the SQL relational database language and a range of Oracle development tools. Where a package, program, or function is supported only for a particular version of the Oracle database (e.g., Oracle Database 11g), we indicate this in the text.

The purpose of this pocket reference is to help PL/SQL users find the syntax of specific language elements. It is not a self- contained user guide; basic knowledge of the PL/SQL programming language is assumed. For more information, see the following O'Reilly books:

Oracle PL/SQL Programming, Fourth Edition, by Steven Feuerstein with Bill Pribyl

Learning Oracle PL/SQL, by Bill Pribyl with Steven Feuerstein

Oracle PL/SQL Best Practices, Second Edition, by Steven Feuerstein

Oracle in a Nutshell, by Rick Greenwald and David C. Kreines

We are grateful to all those who helped in the preparation of this book. In particular, thanks to Bryn Llewellyn for his input on this latest revision as well as the third edition. Thanks as well to first-edition reviewers Eric J. Givler and Stephen Nelson and to second- and third-edition reviewer Jonathan Gennick. In addition, we appreciate all the good work by the O'Reilly crew in editing and producing this book.

UPPERCASE indicates PL/SQL keywords, as well as certain identifiers used by Oracle Corporation as built-in function and package names.

Italic indicates filenames and directories, as well as the first use of a term.

Constant width is used for code examples, literals, and identifiers.

Constant width bold indicates user input in examples showing an interaction.

[] enclose optional items in syntax descriptions.

{} enclose a list of items in syntax descriptions; you must choose one item from the list.

| separates bracketed list items in syntax descriptions.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

PL/SQL Language Fundamentals

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This section summarizes the fundamental components of the PL/SQL language: characters, identifiers, literals, delimiters, use of comments and pragmas, and construction of statements and blocks.

The PL/SQL language is constructed from letters, digits, symbols, and whitespace, as defined in the following table:

Type	Characters
Letters	A—Z, a—z
Digits	0—9
Symbols	˜!@#$%^&*( )_−+=|[ ]{ }:;"'< >,.?/ ^
Whitespace	space, tab, newline, carriage return

Characters are grouped together into four lexical units: identifiers, literals, delimiters, and comments.

Identifiers are names for PL/SQL objects such as constants, variables, exceptions, procedures, cursors, and reserved words. Identifiers have the following characteristics:

• Can be up to 30 characters in length
• Cannot include whitespace (space, tab, carriage return)
• Must start with a letter
• Can include a dollar sign ($), an underscore (_ ), and a pound sign (#)
• Are not case-sensitive

Using PL/SQL's reserved words as identifiers in your programs is not a good idea and can result in compilation or runtime errors that are difficult to troubleshoot.

Earlier editions of this book included a list of reserved words. However, Oracle Database 11g Release 1 has more than 1600 reserved words as listed in the V$RESERVED_WORDS data dictionary view. In our testing we determined that more than 650 of these could not be used as procedure names or variable names. Consult V$RESERVED_WORDS for the full list of unsupported identifiers, and avoid using these as program or variable names.

If you enclose an identifier within double quotes, all but the first of these rules are ignored. For example, the following declaration is valid:

DECLARE
   "1 ^abc"  VARCHAR2(100);
BEGIN
   IF "1 ^abc" IS NULL THEN ...
END;

Literals are specific values not represented by identifiers. For example, TRUE, 3.14159, 6.63E-34, 'Moby Dick', and NULL are all literals of type Boolean, number, or string. There are no complex datatype literals as their values are internal representations; complex types receive values through direct assignment or via constructors. Unlike the rest of PL/SQL, literals are case-sensitive. To embed single quotes within a string literal, place two single quotes next to each other.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Variables and Program Data

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

PL/SQL programs normally are used to manipulate database information. You commonly do this by declaring variables and data structures in your programs, and then working with that PL/SQL-specific data.

A variable is a named instantiation of a data structure declared in a PL/SQL block (either locally or in a package). Unless you declare a variable as a CONSTANT, its value can be changed at any time in your program.

The following table summarizes the different types of program data.

Type	Description
Scalar	Variables made up of a single value, such as a number, date, or Boolean.
Composite	Variables made up of multiple values, such as a record, collection, or instance of a user-defined object type. See the sections "," "," and "."
Reference	Logical pointers to values or cursors.
LOB	Variables containing large object (LOB) locators.

Scalar datatypes divide into four families: number, character, datetime, and Boolean. Subtypes further define a base datatype by restricting the values or size of the base datatype.

Numeric datatypes

Numeric datatypes represent real numbers, integers, and floating-point numbers. They are stored as NUMBER, PLS_INTEGER, and IEEE floating-point storage types.

Decimal numeric datatypes store fixed and floating-point numbers of just about any size. They include the subtypes NUMBER, DEC, DECIMAL, NUMERIC, FLOAT, REAL, and DOUBLE PRECISION. The maximum precision of a variable with type NUMBER is 38 digits, which yields a range of values from 1.0E-129 through 9.999E125.

Variables of type NUMBER can be declared with precision and scale, as follows:

NUMBER(precision, scale)

where precision is the number of digits, and scale is the number of digits to the right (positive scale) or left (negative scale) of the decimal point at which rounding occurs. Legal values for scale range from −84 to 127. The following table shows examples of precision and scale:

Declaration	Assigned value	Stored value
NUMBER	6.02	6.02
NUMBER(4)	8675	8675
NUMBER(4)	8675309	Error
NUMBER(12,5)	3.14159265	3.14159
NUMBER(12,−5)	8675309	8700000

Oracle provides a variety of datatypes to store 32-bit whole numbers: BINARY_INTEGER, INTEGER, INT, SMALLINT, NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE, and PLS_INTEGER. Prior to Oracle Database 10

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Conditional and Sequential Control

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

PL/SQL includes conditional (IF, CASE) structures as well as sequential control (GOTO, NULL) constructs.

There are several varieties of IF-THEN-ELSE and CASE structures.

IF-THEN combination

IF condition THEN
   executable statement(s)
END IF;

For example:

IF caller_type = 'VIP' THEN
   generate_response('GOLD');
END IF;

IF-THEN-ELSE combination

IF condition THEN
   TRUE sequence_of_executable_statement(s)
ELSE
   FALSE/NULL sequence_of_executable_statement(s)
END IF;

For example:

IF caller_type = 'VIP' THEN
   generate_response('GOLD');
ELSE
   generate_response('BRONZE');
END IF;

IF-THEN-ELSIF combination

IF condition-1 THEN
   statements-1
ELSIF condition-N THEN
 statements-N
[ELSE
   ELSE statements]
END IF;

For example:

IF caller_type = 'VIP' THEN
   generate_response('GOLD');
ELSIF priority_client THEN
   generate_response('SILVER');
ELSE
   generate_response('BRONZE');
END IF;

CASE statement

There are two types of CASE statements: simple and searched.

A simple CASE statement is similar to an IF-THEN-ELSIF structure. The statement has a switch expression immediately after the keyword CASE. The expression is evaluated and compared to the value in each WHEN clause. The first WHEN clause with a matching value is executed, and then control passes to the next statement following the END CASE. For example:

CASE region_id
   WHEN 'NE' THEN
      mgr_name := 'MINER';
   WHEN 'SE' THEN
      mgr_name := 'KOOI';
   ELSE mgr_name := 'LANE';
END CASE;

If a switch expression evaluates to NULL, the ELSE case is the only one that can possibly match; WHEN NULL will never match because the database performs an equality comparison on the expressions.

Both the CASE statement and the CASE expression (see the next section) should include an ELSE clause that will execute statements if no WHEN clause evaluates to TRUE, because PL/SQL's runtime engine will raise an exception if it finds no matching expression.

The searched CASE statement does not have a switch; instead, each WHEN clause has a complete Boolean expression. The first matching WHEN clause is executed, and control passes to the next statement following the END CASE; for example:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Loops

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The LOOP construct allows you to execute a sequence of statements repeatedly. There are three types of loops: simple (infinite), FOR, and WHILE.

You can use the EXIT statement to break out of the LOOP and pass control to the statement following the END LOOP. Use the CONTINUE statement (Oracle Database 11g), described later, to break out of the current loope iteration and pass control to the next loop iteration.

LOOP
   executable_statement(s)
END LOOP;

The simple loop should contain an EXIT or EXIT WHEN unless you want it to execute infinitely. Use the simple loop when you want the body of the loop to execute at least once. For example:

LOOP
   FETCH company_cur INTO company_rec;
   EXIT WHEN company_cur%ROWCOUNT > 5 OR
      company_cur%NOTFOUND;
   process_company(company_cur);
END LOOP;

FOR loop_index IN [REVERSE] lowest_number..highest_number
LOOP
   executable_statement(s)
END LOOP;

The PL/SQL runtime engine automatically declares the loop index a PLS_INTEGER variable; never declare a variable with that name yourself. The lowest_number and highest_number ranges can be variables, but are evaluated only once—on initial entry into the loop. The REVERSE keyword causes PL/SQL to start with the highest_number and decrement down to the lowest_number. For example, this code:

BEGIN
   FOR counter IN 1 .. 4
   LOOP
      DBMS_OUTPUT.PUT(counter);
   END LOOP;
   DBMS_OUTPUT.NEW_LINE;

   FOR counter IN REVERSE 1 .. 4
   LOOP
      DBMS_OUTPUT.PUT(counter);
   END LOOP;
   DBMS_OUTPUT.NEW_LINE;
END;

yields the following output:

1234
4321

FOR loop_index IN [cursor_name | (SELECT statement)]
LOOP
   executable_statement(s)
END LOOP;

The PL/SQL runtime engine automatically declares the loop index as a record of cursor_name%ROWTYPE; never declare a variable with that name yourself.

The cursor FOR loop automatically opens the cursor, fetches all rows identified by the cursor, and then closes the cursor. You can embed the SELECT statement directly in the cursor FOR loop or use a previously declared cursor; for example:

FOR emp_rec IN emp_cur
LOOP
   IF emp_rec.title = 'Oracle Programmer'
   THEN
      give_raise(emp_rec.emp_id,30)
   END IF;
END LOOP;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Database Interaction

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

PL/SQL is tightly integrated with the underlying SQL layer of the Oracle database. You can execute SQL statements (UPDATE, INSERT, DELETE, MERGE, and SELECT) directly in PL/SQL programs. You also can execute Data Definition Language (DDL) statements through the use of dynamic SQL. In addition, you can manage transactions with COMMIT, ROLLBACK, and other Data Control Language (DCL) statements.

Sequences are frequently used to generate keys in an Oracle database. To assign a sequence's NEXTVAL or CURRVAL to a PLSQL variable, select the pseudo-column from the table DUAL, like this:

SELECT my_sequence.NEXTVAL INTO my_variable FROM dual;

Starting with Oracle Database 11g, you can reference the sequence pseudo-columns anywhere in your programs in which a number expression can appear. For example:

my_variable := my_sequence.NEXTVAL;

The Oracle database provides a transaction model based on a unit of work. The PL/SQL language supports most, but not all, of the database model for transactions (you cannot, for example, specify ROLLBACK FORCE). A transaction begins with the first change to data and ends with either a COMMIT or a ROLLBACK. Transactions are independent of PL/SQL blocks. Transactions can span multiple PL/SQL blocks, or multiple transactions can be in a single PL/SQL block. The PL/SQL-supported transaction statements include COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION, and LOCK TABLE, described in the following sections.

COMMIT

COMMIT [WORK] [comment_text];

COMMIT makes the database changes permanent and visible to other database sessions. The WORK keyword is optional and aids only readability—it is rarely used. The COMMENT text is optional and can be up to 50 characters in length. It is germane only to in-doubt distributed (two-phase commit) transactions. The database statement COMMIT FORCE, also for distributed transactions, is not supported in PL/SQL.

ROLLBACK

ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];

ROLLBACK undoes the changes made in the current transaction either to the beginning of the transaction or to a savepoint. A savepoint is a named processing point in a transaction, created with the SAVEPOINT statement. Rolling back to a savepoint is a partial rollback of a transaction, wiping out all changes (and savepoints) that occurred later than the named savepoint.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Cursors in PL/SQL

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Every SQL statement executed by the Oracle database has a private SQL area that contains information about the SQL statement and the set of data returned. In PL/SQL, a cursor is a name assigned to a specific private SQL area for a specific SQL statement. There can be either static cursors, whose SQL statement is determined at compile time, or dynamic cursors, whose SQL statement is determined at runtime.

Static cursors are used only for DML statements (SELECT, INSERT, UPDATE, DELETE, MERGE, or SELECT FOR UPDATE). These static cursors may be explicitly declared and named or may appear inline as implicit cursors.

Dynamic cursors are used for any type of valid SQL statement including DDL (CREATE, TRUNCATE, ALTER) and DCL (GRANT, REVOKE). Dynamic cursors are implemented with the EXECUTE IMMEDIATE statement.

Explicit cursors are SELECT statements that are DECLAREd explicitly in the declaration section of the current block or in a package specification. Use OPEN, FETCH, and CLOSE in the execution or exception sections of your programs.

Declaring explicit cursors

To use an explicit cursor, you must first declare it in the declaration section of a block or package. There are three types of explicit cursor declarations:

• A cursor without parameters; for example:

  CURSOR company_cur
     IS
     SELECT company_id FROM company;
  

• A cursor that accepts arguments through a parameter list; for example:

  CURSOR company_cur (id_in IN NUMBER)
     IS
     SELECT name FROM company
      WHERE  company_id = id_in;
  

• A cursor header that contains a RETURN clause in place of the SELECT statement; for example:

  CURSOR company_cur (id_in IN NUMBER)
  RETURN company%ROWTYPE;
  

This last example shows that the cursor can be declared separately from its implementation—for example, the header in a package specification and the implementation in the package body. See the later section, "," for more information.

Opening explicit cursors

To open a cursor, use the following syntax:

OPEN cursor_name [(argument [,argument ...])];

where cursor_name is the name of the cursor as declared in the declaration section. The arguments are required if the definition of the cursor contains a parameter list.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Exception Handling

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

PL/SQL allows developers to raise and handle errors (exceptions) in a very flexible and powerful way. Each PL/SQL block can have its own exception section in which exceptions can be trapped and handled (resolved or passed on to the enclosing block). When an exception occurs (is raised) in a PL/SQL block, its execution section immediately terminates. Control is passed to the exception section. Every exception in PL/SQL has an error number and error message; some exceptions also have names.

Some exceptions have been predefined by Oracle in the STANDARD package or other built-in packages, such as UTL_FILE. See the following table for some of the most common predefined exceptions. You also can declare your own exceptions as follows:

DECLARE
   exception_name EXCEPTION;

Error	Named exception
ORA-00001	DUP_VAL_ON_INDEX
ORA-00051	TIMEOUT_ON_RESOURCE
ORA-00061	TRANSACTION_BACKED_ OUT
ORA-01001	INVALID_CURSOR
ORA-01012	NOT_LOGGED_ON
ORA-01017	LOGIN_DENIED
ORA-01403	NO_DATA_FOUND
ORA-01410	SYS_INVALID_ROWID
ORA-01422	TOO_MANY_ROWS
ORA-01476	ZERO_DIVIDE
ORA-01725	USERENV_COMMMITSCN_ ERROR
ORA-01722	INVALID_NUMBER
ORA-06500	STORAGE_ERROR
ORA-06501	PROGRAM_ERROR
ORA-06502	VALUE_ERROR
ORA-06504	ROWTYPE_MISMATCH
ORA-06511	CURSOR_ALREADY_OPEN
ORA-06530	ACCESS_INTO_NULL
ORA-06531	COLLECTION_IS_NULL
ORA-06532	SUBSCRIPT_OUTSIDE_LIMIT
ORA-06533	SUBSCRIPT_BEYOND_COUNT
ORA-06548	NO_DATA_NEEDED
ORA-06592	CASE_NOT_FOUND
ORA-30625	SELF_IS_NULL

An exception can be declared only once in a block, but nested blocks can declare an exception with the same name as an outer block. If this multiple declaration occurs, scope takes precedence over name when handling the exception. The inner block's declaration takes precedence over a global declaration.

When you declare your own exception, you must RAISE it explicitly. All declared exceptions have an error code of 1 and the error message "User-defined exception," unless you use the EXCEPTION_INIT pragma.

You can associate an error number with a declared exception with the PRAGMA EXCEPTION_INIT statement using the following syntax:

DECLARE
   exception_name EXCEPTION;
   PRAGMA EXCEPTION_INIT (

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Records in PL/SQL

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

A PL/SQL record is a data structure composed of multiple pieces of information called fields. To use a record, you must first define it and declare a variable of this type. There are three types of records: table-based, cursor-based, and programmer-defined.

Define and declare records either in the declaration section of a PL/SQL block or globally, via a package specification.

You do not have to explicitly define table-based or cursor-based records, as they are implicitly defined with the same structure as a table or a cursor. Variables of these types are declared via the %ROWTYPE attribute. The record's fields correspond to the table's columns or the columns in the SELECT list. For example:

DECLARE
   -- Declare table-based record for company table.
   comp_rec  company%ROWTYPE

   CURSOR comp_summary_cur IS
      SELECT c.company_id,SUM(s.gross_sales) gross
        FROM company c ,sales s
       WHERE c.company_id = s.company_id;

   -- Declare a cursor-based record.
   comp_summary_rec  comp_summary_cur%ROWTYPE;

Programmer-defined records must be explicitly defined with the TYPE statement in the PL/SQL declaration section or in a package specification. Variables of this type then can be declared as shown here:

DECLARE
   TYPE name_rectype IS RECORD(
      prefix       VARCHAR2(15)
      ,first_name  VARCHAR2(30)
      ,middle_name VARCHAR2(30)
      ,sur_name    VARCHAR2(30)
      ,suffix      VARCHAR2(10) );

   TYPE employee_rectype IS RECORD (
      emp_id       NUMBER(10) NOT NULL
      ,mgr_id      NUMBER(10)
      ,dept_no     dept.deptno%TYPE
      ,title       VARCHAR2(20)
      ,name        name_rectype
      ,hire_date   DATE := SYSDATE
      ,fresh_out   BOOLEAN );

   -- Declare a variable of this type.
   new_emp_rec employee_rectype;
BEGIN

Individual fields are referenced via dot notation:

record_name.field_name

For example:

employee.first_name

Individual fields within a record can be read from or written to. They can appear on either the left or right side of the assignment operator:

BEGIN
   insurance_start_date :=
      new_emp_rec.hire_date + 30;
   new_emp_rec.fresh_out := FALSE;
   ...

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Collections in PL/SQL

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

There are three types of collections in PL/SQL: associative arrays (formerly known as index-by tables or PL/SQL tables), nested tables, and VARRAYs.

Associative arrays

Single-dimension, unbounded collections of homogeneous elements available only in PL/SQL, not in the Oracle database. Associative arrays are initially sparse; they have nonconsecutive subscripts. There are two types of associative arrays: INDEX BY BINARY_INTEGER, which allows you to associate a value with a BINARY_INTEGER, and INDEX BY VARCHAR2, which allows you to associate a value with a text string.

Nested tables

Single-dimension, unbounded collections of homogeneous elements available in both PL/SQL and the Oracle database as a column of a table. Nested tables initially are dense (they have consecutive subscripts), but they can become sparse through deletions.

VARRAYs

Variable-size arrays. Single-dimension, bounded collections of homogeneous elements available in both PL/SQL and the Oracle database. VARRAYs are never sparse. Unlike nested tables, their element order is preserved when you store and retrieve them from the database.

The following table compares these similar collection types:

Characteristic	Associative array	Nested table	VARRAY
Dimensionality	Single	Single	Single
Usable in SQL?	No	Yes	Yes
Usable as a column datatype in a table?	No	Yes; data stored "out of line" (in a separate table)	Yes; data typically stored "inline" (in the same table)
Uninitialized state	Empty (cannot be NULL); elements are undefined	Atomically null; illegal to reference elements	Atomically null; illegal to reference elements
Initialization	Automatic, when declared	Via constructor, fetch, assignment	Via constructor, fetch, assignment
In PL/SQL, elements referenced by	BINARY_INTEGER (−2,147,483,647 .. 2,147,483,647) or character string (VARCHAR2); maximum length of VARCHAR2 is 30, minimum length is 1	Positive integer between 1 and 2,147,483,647 elements cannot be referenced unless they have been initialized via the constructor or with the EXTEND function	Positive integer between 1 and 2,147,483,647; elements cannot be referenced unless they have been initialized via the constructor or with the EXTEND function

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Built-in Functions and Packages

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Oracle Corporation supplies many built-in functions and packages. The following sections describe those used most often by PL/SQL developers.

The Oracle database provides dozens of built-in functions. Although most of these functions are available to both SQL and PLSQL, there are some notable exceptions. The DECODE function, for example, is not available to PL/SQL except within SQL statements included in your PL/SQL programs. The Oracle Database SQL Reference contains implementation details for the many functions defined to PL/SQL with the package STANDARD. You can view this package specification within the file stdspec.sql located in the ORACLE_HOME/rdbms/admin directory on the database server.

Conversion functions

Built-in function	Description
ASCIISTR(string)	Converts string in any character set to the ASCII version of string in the database character set.
CAST(expression AS datatype)	Converts expression to type datatype. (Use as a powerful substitute for TO_DATE.)
CHARTOROWID(string)	Converts string to a ROWID datatype.
CONVERT(string, destination_character_set, source_character_set)	Converts string from the source character set to the destination character set. (The default source character set is the database character set.)
FROM_TZ(timestamp_value, time_zone)	Adds time zone information to a TIMESTAMP value (converting it to a TIMESTAMP WITH TIME ZONE value).
HEXTORAW(string)	Converts string containing hexadecimal characters to its raw equivalent.
MULTISET	Maps a database table to a collection.
NUMTODSINTERVAL(n, interval_unit)	Converts numeric expression n to an INTERVAL DAY TO SECOND literal; interval_unit can be DAY, HOUR, MINUTE, or SECOND.
NUMTOYMINTERVAL(n, interval_unit)	Converts numeric expression n to an INTERVAL YEAR TO MONTH literal; interval_unit can be YEAR or MONTH.
RAWTOHEX(raw)	Converts raw value to its hexadecimal equivalent.
REFTOHEX(ref)	Converts ref expression into a string containing its hexadecimal equivalent.
ROWIDTOCHAR(rowid)	Converts rowid to a VARCHAR2(18) string equivalent.
ROWIDTONCHAR(rowid)	Converts rowid to an NVARCHAR2(18) string equivalent.
TABLE

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Stored Procedures and Functions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

PL/SQL allows you to create a variety of named program units, or containers, for your code. These include:

Procedure

A program that executes one or more statements

Function

A program that returns a value

Trigger

A program that executes in response to database changes

Package

A container for procedures, functions, and data structures

Object type

Oracle's version of an object-oriented class; object types can contain member procedures and functions

The following sections describe stored procedures and functions. Later sections describe triggers, packages, and object types.

Procedures are program units that execute one or more statements and can receive or return zero or more values through their parameter lists. The syntax of a procedure is:

CREATE [OR REPLACE] PROCEDURE name
   [ (parameter
 [,parameter]) ]
   [AUTHID { CURRENT_USER | DEFINER } ]
{ IS | AS }
   declaration_section
BEGIN
   executable_section
[EXCEPTION
   exception_section]
END [name];

Inside a PL/SQL executable section, a procedure is called as a standalone executable statement:

apply_discount(new_company_id, 0.15);

Many execution environments, such as SQL*Plus, also support ANSI SQL's CALL syntax:

CALL apply_discount(new_company_id, 0.15);

However, SQL*Plus programmers commonly invoke procedures with the SQL*Plus EXECUTE command:

EXECUTE apply_discount(new_company_id, 0.15);

or the equivalent anonymous block:

BEGIN
   apply_discount(new_company_id, 0.15);
END;

Functions are program units that execute zero or more statements and return a value through the RETURN clause. Functions also can receive or return zero or more values through their parameter lists. The syntax of a function is:

CREATE [OR REPLACE] FUNCTION name
   [ (parameter [,parameter]) ]
   RETURN return_datatype
   [AUTHID { CURRENT_USER | DEFINER } ]
   [DETERMINISTIC]
   [PARALLEL_ENABLE [partition_clause]]
   [PIPELINED [USING implementation_type]]
   [RESULT_CACHE [RELIES_ON (table_list)]]
   [AGGREGATE USING implementation_type]
{ IS | AS }
   [declaration_section]
BEGIN
   executable_section
[EXCEPTION
   exception_section]
END [name];

A function must have at least one RETURN statement in the execution section. The RETURN clause in the function header specifies the datatype of the returned value.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Triggers

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Triggers are programs that execute in response to changes in table data or certain database events. A predefined set of events can be "hooked" with a trigger, enabling you to integrate your own processing with that of the database. A triggering event fires or executes the trigger.

There are three types of triggering events:

DML events

Fire when an INSERT, UPDATE, or DELETE statement executes

DDL events

Fire when a CREATE, ALTER, or DROP statement executes

Database events

Fire when one of the predefined database-level events occurs

Complete lists of these events are included in later sections.

The syntax for creating a trigger on a DML event is:

CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF | FOR } trigger_event
   ON {table_or_view_reference |
     NESTED TABLE nested_table_column OF view}
     [REFERENCING [OLD AS old] [NEW AS new]
       [PARENT AS parent]]
[FOR EACH ROW ]
[FOLLOWS other_trigger] [DISABLE]
[COMPOUND TRIGGER]
[WHEN trigger_condition]
trigger_body;

The syntax for creating a trigger on a DDL or database event is:

CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER } trigger_event
   ON [ DATABASE | schema ]
 [FOLLOWS other_trigger][DISABLE]
[WHEN trigger_condition]
trigger_body;

Trigger events are listed in the following table:

Trigger event	Description
INSERT	Fires whenever a row is added to the table_ or_view_reference.
UPDATE	Fires whenever an UPDATE changes the table_or_view_reference. UPDATE triggers can additionally specify an OF clause to restrict firing to updates OF certain columns.
DELETE	Fires whenever a row is deleted from the table_or_view_reference. Does not fire on a TRUNCATE of the table.
ALTER	Fires whenever an ALTER statement changes a database object. In this context, objects are things such as tables or packages (found in ALL_OBJECTS). Can apply to a single schema or the entire database.
ANALYZE	Fires whenever the database collects or deletes statistics or validates the structure of a database object.
ASSOCIATE STATISTICS	Fires whenever the database associates a statistic type with a database object.
AUDIT	Fires whenever the database records an audit operation.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Packages

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

A package is a collection of PL/SQL objects that are grouped together. There are several benefits to using packages, including information hiding, object-oriented design, top-down design, object persistence across transactions, and improved performance.

Elements that can be placed in a package include procedures, functions, constants, variables, cursors, exception names, and TYPE statements (for associative arrays, records, REF CURSORs, etc.).

A package can have two parts: the specification and the body. The package specification is required and lists all the objects that are publicly available (i.e., may be referenced from outside the package) for use in applications. It also provides all the information a developer needs to use objects in the package; essentially, it is the package's API.

The package body contains all the code needed to implement procedures, functions, and cursors listed in the specification, as well as any private objects (accessible only to other elements defined in that package) and an optional initialization section.

If a package specification does not contain any procedures or functions, and no private code is needed, that package does not need to have a package body.

The syntax for the package specification is:

CREATE [OR REPLACE] PACKAGE package_name
[ AUTHID { CURRENT_USER | DEFINER } ]
{ IS | AS }
   [definitions of public TYPEs
   ,declarations of public variables, types, and objects
   ,declarations of exceptions
   ,pragmas
   ,declarations of cursors, procedures, and functions
   ,headers of procedures and functions]
END [package_name];

The syntax for the package body is:

CREATE [OR REPLACE] PACKAGE BODY package_name
   { IS | AS }
   [definitions of private TYPEs
   ,declarations of private variables, types, and objects
   ,full definitions of cursors
   ,full definitions of procedures and functions]
[BEGIN
   executable_statements
[EXCEPTION
   exception_handlers]]
END [package_name];

Specify the optional OR REPLACE to rebuild an existing package, preserving any EXECUTE privileges previously granted to other accounts. The declarations in the specifications cannot be repeated in the body. Both the execution section and the exception section are optional in a package body. If the execution section is present, it is called the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Calling PL/SQL Functions in SQL

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Stored functions can be called from SQL statements in a manner similar to built-in functions such as DECODE, NVL, or RTRIM. This is a powerful technique for incorporating business rules into SQL in a simple and elegant way, but there are several caveats and restrictions.

The most notable caveat is that stored functions executed from SQL are not by default guaranteed to follow the statement-level read consistency model of the database. Unless the SQL statement and any stored functions in that statement are in the same read-consistent transaction (even if they are read-only), each execution of the stored function may look at a different time-consistent set of data. To avoid this potential problem, you need to ensure read consistency programmatically by issuing the SET TRANSACTION READ ONLY or SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before executing your SQL statement containing the stored function. A COMMIT or ROLLBACK then needs to follow the SQL statement to end this read-consistent transaction.

The syntax for calling a stored function from SQL is the same as that used to reference it from PL/SQL:

[schema_name.][pkg_name.]func_name[@db_link]
   [parm_list]

schema_name is optional and refers to the user/owner of the function or package. pkg_name is optional and refers to the package containing the called function. func_name is required and is the function name. db_link is optional and refers to the database link name to the remote database containing the function. parm_list is optional, as are the parameters passed to the function.

The following are example calls to the GetTimestamp function in the time_pkg example seen earlier in the "" section:

-- Capture system events.
INSERT INTO v_sys_event (timestamp, event, qty_waits)
   SELECT time_pkg.GetTimestamp, event, total_waits
   FROM v$system_event

-- Capture system statistics.
INSERT INTO v_sys_stat (timestamp, stat#, value)
   SELECT time_pkg.GetTimestamp, statistic#, value
   FROM v$sysstat;

There are several requirements for calling stored functions in SQL:

• All parameters must be IN; no IN OUT or OUT parameters are allowed.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Object-Oriented Features

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In the Oracle database, an object type combines attributes (data structures) and methods (functions and procedures) into a single programming construct. The object type construct allows programmers to define their own reusable datatypes for use in PL/SQL programs and table and column definitions. An object type must be created in a database before it can be used in a PL/SQL program.

An instance of an object type is an object in the same way that a variable is an instance of a scalar type. Objects are either persistent (stored in the database) or transient (stored only in PL/SQL variables). Objects can be stored in a database as a row in a table (a row object) or as a column in a table. A table of row objects can be created with syntax such as this:

CREATE TABLE table_name OF object_type;

When stored in such a table, the object (row) has a system-generated Object IDentifier (OID) that is unique throughout the database.

An object type has two parts: a specification and a body. The specification is required and contains the attributes and method specifications. The syntax for creating the object type specification is:

CREATE [OR REPLACE] TYPE obj_type_name
[ AUTHID { CURRENT_USER | DEFINER } ]
{ { IS | AS } OBJECT | UNDER parent_type_name }
(
   attribute_name datatype,...,
   [ [ [NOT] OVERRIDING ] [ [NOT] FINAL ] [ [NOT]
   INSTANTIABLE ] method_spec,...,]
   [PRAGMA RESTRICT_REFERENCES(program_name, purities)]
)
[ [NOT] FINAL ]
[ [NOT] INSTANTIABLE ];

where method_spec is one of the following:

MEMBER { PROCEDURE | FUNCTION } program_spec

or:

STATIC { PROCEDURE | FUNCTION } program_spec

or:

{ ORDER | MAP } MEMBER FUNCTION comparison_function_spec

or:

[ FINAL ] [ INSTANTIABLE ] CONSTRUCTOR FUNCTION
  RETURNING SELF AS RESULT constructor_function_spec

Attribute specifications must appear before method specifications. Object attributes, like table columns, are defined with a name and a datatype. The name can be any legal identifier, and the datatype can be almost any datatype known to SQL other than LONG, LONG RAW, ROWID, and UROWID. Attributes can be declared using other programmer-defined object types or collection types, but not of the special types ANYTYPE, ANYDATA, or ANYDATASET. Attributes cannot be of datatypes unique to PL/SQL, such as BOOLEAN.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Compilation

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

PL/SQL compilation is an area that has seen several improvements in recent database versions. These capabilities include conditional compilation, informational warnings, optimization, and compilation to native code.

The following keywords are available when creating stored programs:

OR REPLACE

Used to rebuild an existing program unit, preserving privileges granted on it.

AUTHID

Defines whether the program will execute with the privileges of, and resolve names like, the object owner (DEFINER), or as the user executing the function (CURRENT_USER). The default AUTHID is DEFINER. See the section "" for additional information.

DETERMINISTIC

Required for function-based indexes. A function is DETERMINISTIC if it always returns the same value when called with the same parameters. Deterministic functions do not meaningfully reference package variables or the database. The built-in INITCAP is deterministic, but SYSDATE is not.

PARALLEL_ENABLED [(PARTITION in_parm BY {ANY HASH | RANGE}) ]

Tells the optimizer that a function is safe for parallel execution. The PARTITION BY clause is available only to functions that have a REF CURSOR IN parameter. This clause is used with table functions and tells the optimizer how the input can be partitioned.

PIPELINED

Used with table functions. Specifies that the results of this table function should be returned iteratively via the PIPE ROW statement. A pipelined function can start to return data as it is generated instead of all at once after processing is complete.

AGGREGATE USING

Required for aggregate functions. Tells the database that the function evaluates a group of rows and returns a single result. For example, the built-in function AVG is an aggregate function.

The following compiler settings are established at program creation time based on the database or session configuration and can be changed or retained during recompilation. The database stores these compiler settings on a program-by-program basis, so you can recompile your program later using the REUSE SETTINGS option. If you do not reuse the stored settings, or if you explicitly define one or more settings, your current session settings are used.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Java Language Integration

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Java programmers can write server-side classes that invoke SQL and PL/SQL using standard JDBC or SQLJ calls. PL/SQL programmers can call server-side Java methods by writing a PL/SQL cover or call spec for Java using Oracle database DDL.

Server-side Java in the database may be faster than PL/SQL for computer-intensive programs, but not as nimble for database access. PL/SQL is much more efficient for database-intensive routines because, unlike Java, it doesn't have to pay the overhead for converting SQL datatypes for use inside the stored program. Database programmers will want to continue to use PL/SQL for programs that perform a lot of database I/O and use Java for the best raw computation performance. Follow these steps to create a Java stored procedure ( JSP):

1. Write or otherwise obtain functional Java code. Having source code is not necessary, though, so you can use class libraries from third parties. The classes must, however, meet two requirements. Methods published to SQL and PL/SQL must be declared static; PL/SQL has no mechanism for instantiating nonstatic Java classes. In addition, the classes must not issue any GUI calls (for example, to AWT) at runtime.
   If you write your own JSP and it needs to connect to the database for access to tables or stored procedures, use standard JDBC and/or SQLJ calls in your code. Many JDBC and SQLJ reference materials are available to provide assistance in calling SQL or PL/SQL from Java, but be sure to review the product-specific documentation that ships with your tool.
2. Once you have the Java class in hand, either in source or .class file format, load it into the database. The database's loadjava command-line utility is a convenient way to accomplish the load. Refer to the Oracle Java Developer's Guide for further assistance with loadjava.
3. Create a call spec for the Java method, specifying the AS LANGUAGE JAVA clause of the CREATE statement (described in the "" section). You may create a function or procedure cover as appropriate.
4. Grant EXECUTE privileges on the new JSP using GRANT EXECUTE; PL/SQL routines can now call the JSP as if it were another PL/SQL module.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Return to Oracle PL/SQL Language Pocket Reference