Make your own free website on Tripod.com

SQL & PL/SQL
Back Home Up Next

 

  Theory

Projects

Articles

 Written Books

Report

Technical Report

Survey Reports

Workshop

Presentations

Tutorials

Algorithms

Contact Me

Yourself

 

PL/SQL Fundamentals

Character Set
Lexical Units
Datatypes
User-Defined Subtypes
Datatype Conversion
Declarations
Naming Conventions
Scope and Visibility
Assignments
Expressions and Comparisons
Built-In Functions

There are six essentials in painting. The first is called spirit; the second, rhythm; the third, thought; the fourth, scenery; the fifth, the brush; and the last is the ink.

Ching Hao

Character Set

You write a PL/SQL program as lines of text using a specific set of characters. The PL/SQL character set includes
the upper and lowercase letters A .. Z, a .. z
the numerals 0 .. 9
tabs, spaces, and carriage returns
the symbols ( ) + - * / < > = ! ~ ; : . ' @ % , " # $ ^ & _ | { } ? [ ]

PL/SQL is not case sensitive, so lowercase letters are equivalent to corresponding uppercase letters except within string and character literals.

Lexical Units

A line of PL/SQL text contains groups of characters known as lexical units, which can be classified as follows:
delimiters (simple and compound symbols)
identifiers, which include reserved words
literals
comments

For example, the line

bonus := salary * 0.10;  -- compute bonus

contains the following lexical units:
identifiers bonus and salary
compound symbol :=
simple symbols * and ;
numeric literal 0.10
comment - - compute bonus

To improve readability, you can separate lexical units by spaces. In fact, you must separate adjacent identifiers by a space or punctuation. For example, the following line is illegal because the reserved words END and IF are joined:

IF x > y THEN high := x; ENDIF;  -- illegal

However, you cannot embed spaces in lexical units except for string literals and comments. For example, the following line is illegal because the compound symbol for assignment (:=) is split:

count : = count + 1;  -- illegal

To show structure, you can divide lines using carriage returns and indent lines using spaces or tabs. Compare the following IF statements for readability:

IF x>y THEN max:=x;ELSE max:=y;END IF;     |     IF x > y THEN

                                           |         max := x;

                                           |     ELSE

                                           |         max := y;

                                           |     END IF;

Delimiters

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.

Simple Symbols

Simple symbols consist of one character; a list follows:

+ addition operator
- subtraction/negation operator
* multiplication operator
/ division operator
= relational operator
< relational operator
> relational operator
( expression or list delimiter
) expression or list delimiter
; statement terminator
% attribute indicator
, item separator
. component selector
@ remote access indicator
' character string delimiter
" quoted identifier delimiter
: host variable indicator

Compound Symbols

Compound symbols consist of two characters; a list follows:

** exponentiation operator
<> relational operator
!= relational operator
~= relational operator
^= relational operator
<= relational operator
>= relational operator
:= assignment operator
=> association operator
.. range operator
|| concatenation operator
<< (beginning) label delimiter
>> (ending) label delimiter
- - single-line comment indicator
/* (beginning) multi-line comment delimiter
*/ (ending) multi-line comment delimiter

Identifiers

You use identifiers to name PL/SQL program objects and units, which include constants, variables, exceptions, cursors, cursor variables, subprograms, and packages. Some examples of identifiers follow:

X

t2

phone#

credit_limit

LastName

oracle$number

An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs. Other characters such as hyphens, slashes, and spaces are illegal, as the following examples show:

mine&yours    -- illegal ampersand

debit-amount  -- illegal hyphen

on/off        -- illegal slash

user id       -- illegal space

The next examples show that adjoining and trailing dollar signs, underscores, and number signs are legal:

money$$$tree

SN## 

try_again_

You can use upper, lower, or mixed case to write identifiers. PL/SQL is not case sensitive except within string and character literals. So, if the only difference between identifiers is the case of corresponding letters, PL/SQL considers the identifiers to be the same, as the following example shows:

lastname

LastName  -- same as lastname

LASTNAME  -- same as lastname and LastName

The length of an identifier cannot exceed 30 characters. But, every character, including dollar signs, underscores, and number signs, is significant. For example, PL/SQL considers the following identifiers to be different:

lastname

last_name

Identifiers should be descriptive. So, use meaningful names such as credit_limit and cost_per_thousand. Avoid obscure names such as cr_lim and cpm.

Reserved Words

Some identifiers, called reserved words, have a special syntactic meaning to PL/SQL and so should not be redefined. For example, the words BEGIN and END, which bracket the executable part of a block or subprogram, are reserved. As the next example shows, if you try to redefine a reserved word, you get a compilation error:

DECLARE

   end BOOLEAN;  -- illegal; causes compilation error

However, you can embed reserved words in an identifier, as the following example shows:

DECLARE

   end_of_game BOOLEAN;  -- legal

Often, reserved words are written in upper case to promote readability. However, like other PL/SQL identifiers, reserved words can be written in lower or mixed case. For a list of reserved words, see Appendix E.

Predefined Identifiers

Identifiers globally declared in package STANDARD, such as the exception INVALID_NUMBER, can be redeclared. However, redeclaring predefined identifiers is error prone because your local declaration overrides the global declaration.

Quoted Identifiers

For flexibility, PL/SQL lets you enclose identifiers within double quotes. Quoted identifiers are seldom needed, but occasionally they can be useful. They can contain any sequence of printable characters including spaces but excluding double quotes. Thus, the following identifiers are legal:

"X+Y"

"last name"

"on/off switch"

"employee(s)"

"*** header info ***"

The maximum length of a quoted identifier is 30 characters not counting the double quotes.

Using PL/SQL reserved words as quoted identifiers is allowed but not recommended. It is poor programming practice to reuse reserved words.

Some PL/SQL reserved words are not reserved by SQL. For example, you can use the PL/SQL reserved word TYPE in a CREATE TABLE statement to name a database column. But, if a SQL statement in your program refers to that column, you get a compilation error, as the following example shows:

SELECT acct, type, bal INTO ...  -- causes compilation error

To prevent the error, enclose the uppercase column name in double quotes, as follows:

SELECT acct, "TYPE", bal INTO ...

The column name cannot appear in lower or mixed case (unless it was defined that way in the CREATE TABLE statement). For example, the following statement is invalid:

SELECT acct, "type", bal INTO ...  -- causes compilation error

Alternatively, you can create a view that renames the troublesome column, then use the view instead of the base table in SQL statements.

Literals

A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. The numeric literal 147 and the Boolean literal FALSE are examples.

Numeric Literals

Two kinds of numeric literals can be used in arithmetic expressions: integers and reals. An integer literal is an optionally signed whole number without a decimal point. Some examples follow:

030   6   -14   0   +32767

A real literal is an optionally signed whole or fractional number with a decimal point. Several examples follow:

6.6667   0.0   -12.0   3.14159   +8300.00   .5   25.

PL/SQL considers numbers such as 12.0 and 25. to be reals even though they have integral values.

Numeric literals cannot contain dollar signs or commas, but can be written using scientific notation. Simply suffix the number with an E (or e) followed by an optionally signed integer. A few examples follow:

2E5   1.0E-7   3.14159e0   -1E38   -9.5e-3

E stands for "times ten to the power of." As the next example shows, the number after E is the power of ten by which the number before E must be multiplied:

5E3 = 5 X 103 = 5 X 1000 = 5000

The number after E also corresponds to the number of places the decimal point shifts. In the last example, the implicit decimal point shifted three places to the right; in the next example, it shifts three places to the left:

5E-3 = 5 X 10-3 = 5 X 0.001 = 0.005

Character Literals

A character literal is an individual character enclosed by single quotes (apostrophes). Several examples follow:

'Z'   '%'   '7'   ' '   'z'   '('

Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols. PL/SQL is case sensitive within character literals. For example, PL/SQL considers the literals 'Z' and 'z' to be different.

The character literals '0' .. '9' are not equivalent to integer literals, but can be used in arithmetic expressions because they are implicitly convertible to integers.

String Literals

A character value can be represented by an identifier or explicitly written as a string literal, which is a sequence of zero or more characters enclosed by single quotes. Several examples follow:

'Hello, world!'

'XYZ Corporation'

'10-NOV-91'

'He said "Life is like licking honey from a thorn."'

'$1,000,000'

All string literals except the null string ('') have datatype CHAR.

Given that apostrophes (single quotes) delimit string literals, how do you represent an apostrophe within a string? As the next example shows, you write two single quotes, which is not the same as writing a double quote:

'Don''t leave without saving your work.'

PL/SQL is case sensitive within string literals. For example, PL/SQL considers the following literals to be different:

'baker'

'Baker'

Boolean Literals

Boolean literals are the predefined values TRUE and FALSE and the non-value NULL, which stands for a missing, unknown, or inapplicable value. Remember, Boolean literals are values, not strings. For example, TRUE is no less a value than the number 25.

Comments

The PL/SQL compiler ignores comments, but you should not. Adding comments to your program promotes readability and aids understanding. Generally, you use comments to describe the purpose and use of each code segment. PL/SQL supports two comment styles: single-line and multi-line.

Single-Line

Single-line comments begin with a double hyphen (- -) anywhere on a line and extend to the end of the line. A few examples follow:

-- begin processing

SELECT sal INTO salary FROM emp  -- get current salary

   WHERE empno = emp_id;

bonus := salary * 0.15;  -- compute bonus amount

Notice that comments can appear within a statement at the end of a line.

While testing or debugging a program, you might want to disable a line of code. The following example shows how you can "comment-out" the line:

-- DELETE FROM emp WHERE comm IS NULL;

Multi-line

Multi-line comments begin with a slash-asterisk (/*), end with an asterisk-slash (*/), and can span multiple lines. An example follows:

/* Compute a 15% bonus for top-rated employees. */

IF rating > 90 THEN

   bonus := salary * 0.15 /* bonus is based on salary */

ELSE

   bonus := 0;

END IF;

The next three examples illustrate some popular formats:

/* The following line computes the area of a circle using pi, 

   which is the ratio between the circumference and diameter. */

area := pi * radius**2;


/**************************************************************

 * The following line computes the area of a circle using pi, * 

 * which is the ratio between the circumference and diameter. * 

 **************************************************************/

area := pi * radius**2;


/*

  The following line computes the area of a circle using pi,

  which is the ratio between the circumference and diameter.

*/

area := pi * radius**2;

You can use multi-line comment delimiters to comment-out whole sections of code, as the following example shows:

 

/* 

OPEN c1;

LOOP

   FETCH c1 INTO emp_rec;

   EXIT WHEN c1%NOTFOUND;

   ...

END LOOP;   CLOSE c1; 

*/

Restrictions

You cannot nest comments. Also, you cannot use single-line comments in a PL/SQL block that will be processed dynamically by an Oracle Precompiler program because end-of-line characters are ignored. As a result, single-line comments extend to the end of the block, not just to the end of a line. So, use multi-line comments instead.

Datatypes

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. A reference type contains values, called pointers, that designate other program objects.

shows the predefined datatypes available for your use. An additional scalar type, MLSLABEL, is available with Trusted Oracle, a specially secured version of Oracle. The scalar types fall into four families, which store number, character, date/time, or Boolean data, respectively.

BINARY_INTEGER

You use the BINARY_INTEGER datatype to store signed integers. Its magnitude range is -2147483647 .. 2147483647. Like PLS_INTEGER values, BINARY_INTEGER values require less storage than NUMBER values. However, most BINARY_INTEGER operations are slower than PLS_INTEGER operations. For more information, see "PLS_INTEGER" [*].

Subtypes

A base type is the datatype from which a subtype is derived. A subtype associates a base type with a constraint and so defines a subset of values. For your convenience, PL/SQL predefines the following BINARY_INTEGER subtypes:
NATURAL (0 .. 2147483647)
NATURALN (0 .. 2147483647)
POSITIVE (1 .. 2147483647)
POSITIVEN (1 .. 2147483647)

NUMBER

You use the NUMBER datatype to store fixed or floating-point numbers of virtually any size. You can specify precision, which is the total number of digits, and scale, which determines where rounding occurs. The syntax follows:

NUMBER[(precision, scale)]

You cannot use constants or variables to specify precision and scale; you must use integer literals.

The maximum precision of a NUMBER value is 38 decimal digits; the magnitude range is 1.0E-129 .. 9.99E125. If you do not specify the precision, it defaults to the maximum value supported by your system.

Scale can range from -84 to 127. For instance, a scale of 2 rounds to the nearest hundredth (3.456 becomes 3.46). Scale can be negative, which causes rounding to the left of the decimal point. For example, a scale of -3 rounds to the nearest thousand (3456 becomes 3000). A scale of zero rounds to the nearest whole number. If you do not specify the scale, it defaults to zero.

Subtypes

The NUMBER subtypes below have the same range of values as their base type. For example, DECIMAL is just another name for NUMBER.
DEC
DECIMAL
DOUBLE PRECISION
INTEGER
INT
NUMERIC
REAL
SMALLINT

FLOAT is another subtype of NUMBER. However, you cannot specify a scale for FLOAT variables; you can only specify a binary precision. The maximum precision of a FLOAT value is 126 binary digits, which is roughly equivalent to 38 decimal digits.

You can use these subtypes for compatibility with ANSI/ISO and IBM types or when you want an identifier more descriptive than NUMBER.

PLS_INTEGER

You use the PLS_INTEGER datatype to store signed integers. Its magnitude range is -2147483647 .. 2147483647. PLS_INTEGER values require less storage than NUMBER values. Also, PLS_INTEGER operations use machine arithmetic, so they are faster than NUMBER and BINARY_INTEGER operations, which use library arithmetic. For better performance, use PLS_INTEGER for all calculations that fall within its magnitude range.

Although PLS_INTEGER and BINARY_INTEGER are both integer types with the same magnitude range, they are not fully compatible. When a PLS_INTEGER calculation overflows, an exception is raised. However, when a BINARY_INTEGER calculation overflows, no exception is raised if the result is assigned to a NUMBER variable.

Because of this small semantic difference, you might want to continue using BINARY_INTEGER in old applications for compatibility. In new applications, always use PLS_INTEGER for better performance.

CHAR

You use the CHAR datatype to store fixed-length (blank-padded if necessary) character data. How the data is represented internally depends on the database character set, which might be 7-bit ASCII or EBCDIC Code Page 500, for example.

The CHAR datatype takes an optional parameter that lets you specify a maximum length up to 32767 bytes. The syntax follows:

CHAR[(maximum_length)]

You cannot use a constant or variable to specify the maximum length; you must use an integer literal. If you do not specify the maximum length, it defaults to 1.

Remember, you specify the maximum length of a CHAR(n) variable in bytes, not characters. So, if a CHAR(n) variable stores multi-byte characters, its maximum length is less than n characters.

Although the maximum length of a CHAR(n) variable is 32767 bytes, the maximum width of a CHAR database column is 255 bytes. Therefore, you cannot insert values longer than 255 bytes into a CHAR column. You can insert any CHAR(n) value into a LONG database column because the maximum width of a LONG column is 2147483647 bytes or 2 gigabytes. However, you cannot select a value longer than 32767 bytes from a LONG column into a CHAR(n) variable.

Subtype

The CHAR subtype CHARACTER has the same range of values as its base type. That is, CHARACTER is just another name for CHAR. You can use this subtype for compatibility with ANSI/ISO and IBM types or when you want an identifier more descriptive than CHAR.

LONG

You use the LONG datatype to store variable-length character strings. The LONG datatype is like the VARCHAR2 datatype, except that the maximum length of a LONG value is 32760 bytes.

You can insert any LONG value into a LONG database column because the maximum width of a LONG column is 2147483647 bytes. However, you cannot select a value longer than 32760 bytes from a LONG column into a LONG variable.

LONG columns can store text, arrays of characters, or even short documents. You can reference LONG columns in UPDATE, INSERT, and (most) SELECT statements, but not in expressions, SQL function calls, or certain SQL clauses such as WHERE, GROUP BY, and CONNECT BY. For more information, see Oracle7 Server SQL Reference.

RAW

You use the RAW datatype to store binary data or byte strings. For example, a RAW variable might store a sequence of graphics characters or a digitized picture. Raw data is like character data, except that PL/SQL does not interpret raw data. Likewise, Oracle does no character set conversions (from 7-bit ASCII to EBCDIC Code Page 500, for example) when you transmit raw data from one system to another.

The RAW datatype takes a required parameter that lets you specify a maximum length up to 32767 bytes. The syntax follows:

RAW(maximum_length)

You cannot use a constant or variable to specify the maximum length; you must use an integer literal.

Although the maximum length of a RAW variable is 32767 bytes, the maximum width of a RAW database column is 255 bytes. Therefore, you cannot insert values longer than 255 bytes into a RAW column. You can insert any RAW value into a LONG RAW database column because the maximum width of a LONG RAW column is 2147483647 bytes. However, you cannot select a value longer than 32767 bytes from a LONG RAW column into a RAW variable.

LONG RAW

You use the LONG RAW datatype to store binary data or byte strings. LONG RAW data is like LONG data, except that LONG RAW data is not interpreted by PL/SQL. The maximum length of a LONG RAW value is 32760 bytes.

You can insert any LONG RAW value into a LONG RAW database column because the maximum width of a LONG RAW column is 2147483647 bytes. However, you cannot select a value longer than 32760 bytes from a LONG RAW column into a LONG RAW variable.

ROWID

Internally, every Oracle database table has a ROWID pseudocolumn, which stores binary values called rowids. Rowids uniquely identify rows and provide the fastest way to access particular rows. You use the ROWID datatype to store rowids in a readable format. The maximum length of a ROWID variable is 256 bytes.

When you select or fetch a rowid into a ROWID variable, you can use the function ROWIDTOCHAR, which converts the binary value to an 18-byte character string and returns it in the format

BBBBBBBB.RRRR.FFFF

where BBBBBBBB is the block in the database file, RRRR is the row in the block (the first row is 0), and FFFF is the database file.

These numbers are hexadecimal. For example, the rowid

0000000E.000A.0007

points to the 11th row in the 15th block in the 7th database file.

Typically, ROWID variables are compared to the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement to identify the latest row fetched from a cursor. For an example, see "Fetching Across Commits" [*].

VARCHAR2

You use the VARCHAR2 datatype to store variable-length character data. How the data is represented internally depends on the database character set.

The VARCHAR2 datatype takes a required parameter that specifies a maximum length up to 32767 bytes. The syntax follows:

VARCHAR2(maximum_length)

You cannot use a constant or variable to specify the maximum length; you must use an integer literal.

Remember, you specify the maximum length of a VARCHAR2(n) variable in bytes, not characters. So, if a VARCHAR2(n) variable stores multi-byte characters, its maximum length is less than n characters.

Although the maximum length of a VARCHAR2(n) variable is 32767 bytes, the maximum width of a VARCHAR2 database column is 2000 bytes. Therefore, you cannot insert values longer than 2000 bytes into a VARCHAR2 column. You can insert any VARCHAR2(n) value into a LONG database column because the maximum width of a LONG column is 2147483647 bytes. However, you cannot select a value longer than 32767 bytes from a LONG column into a VARCHAR2(n) variable.

Some important semantic differences between the CHAR and VARCHAR2 base types are described in Appendix C.

Subtypes

The VARCHAR2 subtypes below have the same range of values as their base type. For example, VARCHAR is just another name for VARCHAR2.
STRING
VARCHAR

You can use these subtypes for compatibility with ANSI/ISO and IBM types. However, the VARCHAR datatype might change to accommodate emerging SQL standards. So, it is a good idea to use VARCHAR2 rather than VARCHAR.

BOOLEAN

You use the BOOLEAN datatype to store the values TRUE and FALSE and the non-value NULL. Recall that NULL stands for a missing, unknown, or inapplicable value.

The BOOLEAN datatype takes no parameters. Only the values TRUE and FALSE and the non-value NULL can be assigned to a BOOLEAN variable. You cannot insert the values TRUE and FALSE into a database column. Furthermore, you cannot select or fetch column values into a BOOLEAN variable.

DATE

You use the DATE datatype to store fixed-length date values. The DATE datatype takes no parameters. Valid dates for DATE variables include January 1, 4712 BC to December 31, 4712 AD.

When stored in a database column, date values include the time of day in seconds since midnight. The date portion defaults to the first day of the current month; the time portion defaults to midnight.

MLSLABEL

With Trusted Oracle, you use the MLSLABEL datatype to store variable-length, binary operating system labels. Trusted Oracle uses labels to control access to data. For more information, see Trusted Oracle7 Server Administrator's Guide.

You can use the MLSLABEL datatype to define a database column. Also, you can use the %TYPE and %ROWTYPE attributes to reference the column. However, with standard Oracle, such columns can store only nulls.

With Trusted Oracle, you can insert any valid operating system label into a column of type MLSLABEL. If the label is in text format, Trusted Oracle converts it to a binary value automatically. The text string can be up to 255 bytes long. However, the internal length of an MLSLABEL value is between 2 and 5 bytes.

With Trusted Oracle, you can also select values from a MLSLABEL column into a character variable. Trusted Oracle converts the internal binary value to a VARCHAR2 value automatically.

User-Defined Subtypes

Each PL/SQL base type specifies a set of values and a set of operations applicable to objects of that type. Subtypes specify the same set of operations as their base type but only a subset of its values. Thus, a subtype does not introduce a new type; it merely places an optional constraint on its base type.

PL/SQL predefines several subtypes in package STANDARD. For example, PL/SQL predefines the subtype CHARACTER, as follows:

SUBTYPE CHARACTER IS CHAR; 

The subtype CHARACTER specifies the same set of values as its base type CHAR. Thus, CHARACTER is an unconstrained subtype.

Subtypes can increase reliability, provide compatibility with ANSI/ISO and IBM types, and improve readability by indicating the intended use of constants and variables.

Defining Subtypes

You can define your own subtypes in the declarative part of any PL/SQL block, subprogram, or package using the syntax

SUBTYPE subtype_name IS base_type; 

where subtype_name is a type specifier used in subsequent declarations and base_type stands for the following syntax:

{  cursor_name%ROWTYPE

 | cursor_variable_name%ROWTYPE

 | plsql_table_name%TYPE 

 | record_name%TYPE

 | scalar_type_name 

 | table_name%ROWTYPE 

 | table_name.column_name%TYPE 

 | variable_name%TYPE} 

For example, all of the following subtype definitions are legal:

DECLARE

   SUBTYPE EmpDate IS DATE;           -- based on DATE type

   SUBTYPE Counter IS NATURAL;        -- based on NATURAL subtype

   TYPE NameTab IS TABLE OF VARCHAR2(10)

      INDEX BY BINARY_INTEGER;

   SUBTYPE EnameTab IS NameTab;       -- based on TABLE type

   TYPE TimeTyp IS RECORD (minute INTEGER, hour INTEGER);

   SUBTYPE Clock IS TimeTyp;          -- based on RECORD type

   SUBTYPE ID_Num IS emp.empno%TYPE;  -- based on column type

   CURSOR c1 IS SELECT * FROM dept;

   SUBTYPE Dept_Rec IS c1%ROWTYPE;    -- based on cursor rowtype

However, you cannot specify a constraint on the base type. For example, the following definitions are illegal:

DECLARE 

   SUBTYPE Accumulator IS NUMBER(7,2); -- illegal; must be NUMBER

   SUBTYPE Delimiter IS CHAR(1);       -- illegal; must be CHAR

   SUBTYPE Word IS VARCHAR2(15);       -- illegal 

Although you cannot define constrained subtypes directly, you can use a simple workaround to define size-constrained subtypes indirectly. Simply declare a size-constrained variable, then use %TYPE to provide its datatype, as shown in the following example:

DECLARE 

   temp VARCHAR2(15);

   SUBTYPE Word IS temp%TYPE; -- maximum size of Word is 15

Likewise, if you define a subtype using %TYPE to provide the datatype of a database column, the subtype adopts the size constraint (if any) of the column. However, the subtype does not adopt other kinds of constraints such as NOT NULL.

Using Subtypes

Once you define a subtype, you can declare objects of that type. In the example below, you declare two variables of type Counter. Notice how the subtype name indicates the intended use of the variables.

DECLARE 

   SUBTYPE Counter IS NATURAL; 

   rows      Counter;

   employees Counter; 

The following example shows that you can constrain a user-defined subtype when declaring variables of that type:

DECLARE 

   SUBTYPE Accumulator IS NUMBER; 

   total Accumulator(7,2); 

Subtypes can increase reliability by detecting out-of-range values. In the example below, you restrict the subtype Scale to storing integers in the range -9 .. 9. If your program tries to store a number outside that range in a Scale variable, PL/SQL raises an exception.

DECLARE 

   temp NUMBER(1,0);

   SUBTYPE Scale IS temp%TYPE;

   x_axis Scale;  -- magnitude range is -9 .. 9

   y_axis Scale;

BEGIN

   x_axis := 10;  -- raises VALUE_ERROR

Type Compatibility

An unconstrained subtype is interchangeable with its base type. For example, given the following declarations, the value of amount can be assigned to total without conversion:

DECLARE 

   SUBTYPE Accumulator IS NUMBER; 

   amount NUMBER(7,2); 

   total  Accumulator; 

BEGIN

   ...

   total := amount;

   ...

END;

Different subtypes are interchangeable if they have the same base type. For instance, given the following declarations, the value of finished can be assigned to debugging:

DECLARE 

   SUBTYPE Sentinel IS BOOLEAN; 

   SUBTYPE Switch IS BOOLEAN; 

   finished  Sentinel; 

   debugging Switch; 

BEGIN

   ...

   debugging := finished;

   ...

END;

Different subtypes are also interchangeable if their base types are in the same datatype family. For example, given the following declarations, the value of verb can be assigned to sentence:

DECLARE 

   SUBTYPE Word IS CHAR; 

   SUBTYPE Text IS VARCHAR2; 

   verb     Word; 

   sentence Text; 

BEGIN

   ...

   sentence := verb;

   ...

END;

Datatype Conversion

Sometimes it is necessary to convert a value from one datatype to another. For example, if you want to examine a rowid, you must convert it to a character string. PL/SQL supports both explicit and implicit (automatic) datatype conversion.

Explicit Conversion

To specify conversions explicitly, you use built-in functions that convert values from one datatype to another. Table 2 - 1 shows which function to use in a given situation. For example, to convert a CHAR value to a NUMBER value, you use the function TO_NUMBER. For more information about these functions, see Oracle7 Server SQL Reference.

To          
From CHAR DATE NUMBER RAW ROWID
CHAR   TO_DATE TO_NUMBER HEXTORAW CHARTOROWID
DATE TO_CHAR        
NUMBER TO_CHAR TO_DATE      
RAW RAWTOHEX        
ROWID ROWIDTOCHAR        

Implicit Conversion

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. In the example below, the CHAR variables start_time and finish_time hold string values representing the number of seconds past midnight. The difference between those values must be assigned to the NUMBER variable elapsed_time. So, PL/SQL converts the CHAR values to NUMBER values automatically.

DECLARE

   start_time   CHAR(5);

   finish_time  CHAR(5);

   elapsed_time NUMBER(5);

BEGIN

   /* Get system time as seconds past midnight. */

   SELECT TO_CHAR(SYSDATE,'SSSSS') INTO start_time FROM sys.dual;

   -- do something

   /* Get system time again. */

   SELECT TO_CHAR(SYSDATE,'SSSSS') INTO finish_time FROM sys.dual;

   /* Compute elapsed time in seconds. */

   elapsed_time := finish_time - start_time;

   INSERT INTO results VALUES (elapsed_time, ...);

END;

Before assigning a selected column value to a variable, PL/SQL will, if necessary, convert the value from the datatype of the source column to the datatype of the variable. This happens, for example, when you select a DATE column value into a VARCHAR2 variable. Likewise, before assigning the value of a variable to a database column, PL/SQL will, if necessary, convert the value from the datatype of the variable to the datatype of the target column.

If PL/SQL cannot determine which implicit conversion is needed, you get a compilation error. In such cases, you must use a datatype conversion function. Table 2 - 2 shows which implicit conversions PL/SQL can do.

To                  
From BIN_INT CHAR DATE LONG NUMBER PLS_INT RAW ROWID VARCHAR2
BIN_INT   _/   _/ _/ _/     _/
CHAR _/   _/ _/ _/ _/ _/ _/ _/
DATE   _/   _/         _/
LONG   _/         _/   _/
NUMBER _/ _/   _/   _/     _/
PLS_INT _/ _/   _/ _/       _/
RAW   _/   _/         _/
ROWID   _/             _/
VARCHAR2 _/ _/ _/ _/ _/ _/ _/ _/  

Implicit versus Explicit Conversion

Generally, it is poor programming practice to rely on implicit datatype conversions because they can hamper performance and might change from one software release to the next. Also, implicit conversions are context sensitive and therefore not always predictable. Instead, use datatype conversion functions. That way, your applications will be more reliable and easier to maintain.

DATE Values

When you select a DATE column value into a CHAR or VARCHAR2 variable, PL/SQL must convert the internal binary value to a character value. So, PL/SQL calls the function TO_CHAR, which returns a character string in the default date format. To get other information such as the time or Julian date, you must call TO_CHAR with a format mask.

A conversion is also necessary when you insert a CHAR or VARCHAR2 value into a DATE column. So, PL/SQL calls the function TO_DATE, which expects the default date format. To insert dates in other formats, you must call TO_DATE with a format mask.

RAW and LONG RAW Values

When you select a RAW or LONG RAW column value into a CHAR or VARCHAR2 variable, PL/SQL must convert the internal binary value to a character value. In this case, PL/SQL returns each binary byte of RAW or LONG RAW data as a pair of characters. Each character represents the hexadecimal equivalent of a nibble (half a byte). For example, PL/SQL returns the binary byte 11111111 as the pair of characters 'FF'. The function RAWTOHEX does the same conversion.

A conversion is also necessary when you insert a CHAR or VARCHAR2 value into a RAW or LONG RAW column. Each pair of characters in the variable must represent the hexadecimal equivalent of a binary byte. If either character does not represent the hexadecimal equivalent of a nibble, PL/SQL raises an exception.

Declarations

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 you can reference it. A couple of examples follow:

birthday  DATE;

emp_count SMALLINT := 0;

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 next examples show that the expression following the assignment operator can be arbitrarily complex and can refer to previously initialized variables:

pi     REAL := 3.14159;

radius REAL := 1;

area   REAL := pi * radius**2;

By default, variables are initialized to NULL. For example, the following declarations are equivalent:

birthday DATE;

birthday DATE := NULL;

In constant declarations, the keyword CONSTANT must precede the type specifier, as the following example shows:

credit_limit CONSTANT REAL := 5000.00;

This declaration names a constant of type REAL and assigns an initial (also final) value of 5000 to the constant. A constant must be initialized in its declaration. Otherwise, you get a compilation error when the declaration is elaborated. (The processing of a declaration by the PL/SQL compiler is called elaboration.)

Using DEFAULT

If you prefer, you can use the reserved word DEFAULT instead of the assignment operator to initialize variables and constants. For example, the declarations

tax_year SMALLINT := 95;

valid    BOOLEAN := FALSE;

can be rewritten as follows:

tax_year SMALLINT DEFAULT 95;

valid    BOOLEAN DEFAULT FALSE;

You can also use DEFAULT to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.

Using NOT NULL

Besides assigning an initial value, declarations can impose the NOT NULL constraint, as the following example shows:

acct_id INTEGER(4) NOT NULL := 9999;

You cannot assign nulls to a variable defined as NOT NULL. If you try, PL/SQL raises the predefined exception VALUE_ERROR. The NOT NULL constraint must be followed by an initialization clause. For example, the following declaration is illegal:

acct_id INTEGER(5) NOT NULL;  -- illegal; not initialized

Recall that the subtypes NATURALN and POSITIVEN are predefined as NOT NULL. For instance, the following declarations are equivalent:

emp_count NATURAL NOT NULL := 0;

emp_count NATURALN := 0;

In NATURALN and POSITIVEN declarations, the type specifier must be followed by an initialization clause. Otherwise, you get a compilation error. For example, the following declaration is illegal:

line_items POSITIVEN;  -- illegal; not initialized

Using %TYPE

The %TYPE attribute provides the datatype of a variable or database column. In the following example, %TYPE provides the datatype of a variable:

credit REAL(7,2);

debit  credit%TYPE;

Variables declared using %TYPE are treated like those declared using a datatype specifier. For example, given the previous declarations, PL/SQL treats debit like a REAL(7,2) variable.

The next example shows that a %TYPE declaration can include an initialization clause:

balance         NUMBER(7,2);

minimum_balance balance%TYPE := 10.00;

The %TYPE attribute is particularly useful when declaring variables that refer to database columns. You can reference a table and column, or you can reference an owner, table, and column, as in

my_dname scott.dept.dname%TYPE;

Using %TYPE to declare my_dname has two advantages. First, you need not know the exact datatype of dname. Second, if the database definition of dname changes, the datatype of my_dname changes accordingly at run time.

Note, however, that a NOT NULL column constraint does not apply to variables declared using %TYPE. In the next example, even though the database column empno is defined as NOT NULL, you can assign a null to the variable my_empno:

DECLARE

   my_empno emp.empno%TYPE; request)

Examples

declare handle number;

begin

    handle := ctx_ling.submit(500);

end;

Expressions and Comparisons

Expressions are constructed using operands and operators. An operand is a variable, constant, literal, or function call that contributes a value to an expression. An example of a simple arithmetic expression follows:
-X / 2 + 3

Unary operators such as the negation operator (-) operate on one operand; binary operators such as the division operator (/) operate on two operands. PL/SQL has no ternary operators.

The simplest expressions consist of a single variable, which yields a value directly. PL/SQL evaluates (finds the current value of) an expression by combining the values of the operands in ways specified by the operators. This always yields a single value and datatype. PL/SQL determines the datatype by examining the expression and the context in which it appears.

Operator Precedence

The operations within an expression are done in a particular order depending on their precedence (priority). Table 2 - 3 shows the default order of operations from first to last (top to bottom). 
Operator Operation
**, NOT exponentiation, logical negation
+, - identity, negation
*, / multiplication, division
+, -, || addition, subtraction, concatenation
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN comparison
AND conjunction
OR inclusion

Operators with higher precedence are applied first. For example, both of the following expressions yield 8 because division has a higher precedence than addition:

5 + 12 / 4
12 / 4 + 5

Operators with the same precedence are applied in no particular order. You can use parentheses to control the order of evaluation. For example, the following expression yields 7, not 11, because parentheses override the default operator precedence:

(8 + 6) / 2

In the next example, the subtraction is done before the division because the most deeply nested subexpression is always evaluated first:

100 + (20 / 5 + (7 - 3))

The following example shows that you can always use parentheses to improve readability, even when they are not needed:

(salary * 0.05) + (commission * 0.25)

Logical Operators

The logical operators AND, OR, and NOT follow the tri-state logic of the truth tables in Figure 2 - 3. AND and OR are binary operators; NOT is a unary operator. 
NOT TRUE FALSE NULL
  FALSE TRUE NULL

 

AND TRUE FALSE NULL
TRUE TRUE FALSE NULL
FALSE FALSE FALSE FALSE
NULL NULL FALSE NULL

 

OR TRUE FALSE NULL
TRUE TRUE TRUE TRUE
FALSE TRUE FALSE NULL
NULL TRUE NULL NULL

Comparison Operators

Comparison operators compare one expression to another. The result is always TRUE, FALSE, or NULL. Typically, you use comparison operators in the WHERE clause of SQL data manipulation statements and in conditional control statements.

Relational Operators

The relational operators allow you to compare arbitrarily complex expressions. The following list gives the meaning of each operator:
Operator Meaning
= is equal to
<>, !=, ~= is not equal to
< is less than
> is greater than
<= is less than or equal to
>= is greater than or equal to

IS NULL Operator

The IS NULL operator returns the Boolean value TRUE if its operand is null or FALSE if it is not null. Comparisons involving nulls always yield NULL. Therefore, to test for nullity (the state of being null), do not use the statement 
IF variable = NULL THEN ...

Instead, use the following statement:

IF variable IS NULL THEN ...

LIKE Operator

You use the LIKE operator to compare a character value to a pattern. Case is significant. LIKE returns the Boolean value TRUE if the character patterns match or FALSE if they do not match. 

The patterns matched by LIKE can include two special-purpose characters called wildcards. An underscore (_) matches exactly one character; a percent sign (%) matches zero or more characters. For example, if the value of ename is 'JOHNSON', the following expression yields TRUE:

ename LIKE 'J%SON'

BETWEEN Operator

The BETWEEN operator tests whether a value lies in a specified range. It means "greater than or equal to low value and less than or equal to high value." For example, the following expression yields FALSE: 
45 BETWEEN 38 AND 44

IN Operator

The IN operator tests set membership. It means "equal to any member of." The set can contain nulls, but they are ignored. For example, the following statement does not delete rows in which the ename column

is null: 
DELETE FROM emp WHERE ename IN (NULL, 'KING', 'FORD');

Furthermore, expressions of the form

value NOT IN set

yield FALSE if the set contains a null. For example, instead of deleting rows in which the ename column is not null and not 'KING', the following statement deletes no rows:

DELETE FROM emp WHERE ename NOT IN (NULL, 'KING');

 

Concatenation Operator

The concatenation operator (||) appends one string to another. For example, the expression 
'suit' || 'case'

returns the value 'suitcase'.

If both operands have datatype CHAR, the concatenation operator returns a CHAR value. Otherwise, it returns a VARCHAR2 value.

Boolean Expressions

PL/SQL lets you compare variables and constants in both SQL and procedural statements. These comparisons, called Boolean expressions, consist of simple or complex expressions separated by relational operators. Often, Boolean expressions are connected by the logical operators AND, OR, and NOT. A Boolean expression always yields TRUE, FALSE, or NULL. 

In a SQL statement, Boolean expressions let you specify the rows in a table that are affected by the statement. In a procedural statement, Boolean expressions are the basis for conditional control. There are three kinds of Boolean expressions: arithmetic, character, and date.

Arithmetic

You can use the relational operators to compare numbers for equality or inequality. Comparisons are quantitative; that is, one number is greater than another if it represents a larger quantity. For example, given 

the assignments
number1 := 75;

number2 := 70;

the following expression yields TRUE:

number1 > number2

Character

Likewise, you can compare character values for equality or inequality. Comparisons are based on the collating sequence used for the database character set. A collating sequence is an internal ordering of the character set, in which a range of numeric codes represents the individual characters. One character value is greater than another if its internal numeric value is larger. For example, given the assignments
string1 := 'Kathy';

string2 := 'Kathleen';

the following expression yields TRUE:

string1 > string2

However, there are semantic differences between the CHAR and VARCHAR2 base types that come into play when you compare character values. For more information, refer to Appendix C.

Date

You can also compare dates. Comparisons are chronological; that is, one date is greater than another if it is more recent. For example, given 

the assignments
date1 := '01-JAN-91';

date2 := '31-DEC-90';

the following expression yields TRUE:

date1 > date2

Guidelines

In general, do not compare real numbers for exact equality or inequality. Real numbers are stored as approximate values. So, for example, the following IF condition might not yield TRUE:
count := 1;

IF count = 1.0 THEN ...

It is a good idea to use parentheses when doing comparisons. For example, the following expression is illegal because 100 < tax yields TRUE or FALSE, which cannot be compared with the number 500:

100 < tax < 500  -- illegal

The debugged version follows:

(100 < tax) AND (tax < 500)

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 has the datatype BOOLEAN, the IF statement

IF done = TRUE THEN ...

can be simplified as follows:

IF done THEN ...

Handling Nulls

When working with nulls, you can avoid some common mistakes by keeping in mind the following rules: 
comparisons involving nulls always yield NULL
applying the logical operator NOT to a null yields NULL
in conditional control statements, if the condition yields NULL, its associated sequence of statements is not executed
In the example below, you might expect the sequence of statements to execute because x and y seem unequal. But, nulls are indeterminate. Whether or not x is equal to y is unknown. Therefore, the IF condition yields NULL and the sequence of statements is bypassed.
x := 5;

y := NULL;

...

IF x != y THEN  -- yields NULL, not TRUE

   sequence_of_statements;  -- not executed

END IF;

In the next example, you might expect the sequence of statements to execute because a and b seem equal. But, again, that is unknown, so the IF condition yields NULL and the sequence of statements is bypassed.

a := NULL;

b := NULL;

...

IF a = b THEN  -- yields NULL, not TRUE

   sequence_of_statements;  -- not executed

END IF;

NOT Operator

Recall that applying the logical operator NOT to a null yields NULL. Thus, the following two statements are not always equivalent: 
IF x > y THEN     |     IF NOT x > y THEN

   high := x;     |         high := y;

ELSE              |     ELSE

   high := y;     |         high := x;

END IF;           |     END IF;

The sequence of statements in the ELSE clause is executed when the IF condition yields FALSE or NULL. So, if either or both x and y are null, the first IF statement assigns the value of y to high, but the second IF statement assigns the value of x to high. If neither x nor y is null, both IF statements assign the same value to high.

Zero-Length Strings

PL/SQL treats any zero-length string like a null. This includes values returned by character functions and Boolean expressions. For example, the following statements assign nulls to the target variables:
null_string := TO_VARCHAR2('');

zip_code := SUBSTR(address, 25, 0);

valid := (name != '');

So, use the IS NULL operator to test for null strings, as follows:

IF my_string IS NULL THEN ...

Concatenation Operator

The concatenation operator ignores null operands. For example, the expression 
'apple' || NULL || NULL || 'sauce'

returns the value 'applesauce'.

Functions

If a null argument is passed to a built-in function, a null is returned except in the following cases.

The function DECODE compares its first argument to one or more search expressions, which are paired with result expressions. Any search or result expression can be null. If a search is successful, the corresponding result is returned. In the following example, if the column rating is null, DECODE returns the value 1000:

SELECT DECODE(rating, NULL, 1000, 'C', 2000, 'B', 4000, 'A', 5000)

   INTO credit_limit FROM accts WHERE acctno = my_acctno;

The function NVL returns the value of its second argument if its first argument is null. In the example below, if hire_date is null, NVL returns the value of SYSDATE. Otherwise, NVL returns the value of hire_date:

start_date := NVL(hire_date, SYSDATE);

The function REPLACE returns the value of its first argument if its second argument is null, whether the optional third argument is present or not. For instance, after the assignment

new_string := REPLACE(old_string, NULL, my_string);

the values of old_string and new_string are the same.

If its third argument is null, REPLACE returns its first argument with every occurrence of its second argument removed. For example, after the assignments

 

syllabified_name := 'Gold-i-locks';

name := REPLACE(syllabified_name, '-', NULL);

the value of name is 'Goldilocks'.

If its second and third arguments are null, REPLACE simply returns its first argument.

Built-In Functions

PL/SQL provides more than 75 powerful functions to help you manipulate data. These built-in functions fall into the following categories: 
error-reporting
number
character
conversion
date
miscellaneous
Error Number Character Conversion Date Misc
SQLCODE ABS ASCII CHARTOROWID ADD_MONTHS DECODE
SQLERRM ACOS CHR CONVERT LAST_DAY DUMP
  ASIN CONCAT HEXTORAW MONTHS_BETWEEN GREATEST
  ATAN INITCAP RAWTOHEX NEW_TIME GREATEST_LB
  ATAN2 INSTR ROWIDTOCHAR NEXT_DAY LEAST
  CEIL INSTRB TO_CHAR ROUND LEAST_LB
  COS LENGTH TO_DATE SYSDATE NVL
  COSH LENGTHB TO_LABEL TRUNC UID
  EXP LOWER TO_MULTI_BYTE   USER
  FLOOR LPAD TO_NUMBER   USERENV
  LN LTRIM TO_SINGLE_BYTE   VSIZE
  LOG NLS_INITCAP      
  MOD NLS_LOWER      
  POWER NLS_UPPER      
  ROUND NLSSORT      
  SIGN REPLACE      
  SIN RPAD      
  SINH RTRIM      
  SQRT SOUNDEX      
  TAN SUBSTR      
  TANH SUBSTRB      
  TRUNC TRANSLATE      
    UPPER      

 

PL/SQL User's Guide and Reference



Overview
Main Features
Architecture
Advantages of PL/SQL

The limits of my language mean the limits of my world.

Ludwig Wittgenstein

The main features of PL/SQL and points out the advantages they offer. It also acquaints you with the basic concepts behind PL/SQL and the general appearance of PL/SQL programs.

PL/SQL bridges the gap between database technology and procedural programming languages.

Main Features

A good way to get acquainted with PL/SQL is to look at a sample program. The program below processes an order for tennis rackets. First, it declares a variable of type NUMBER to store the quantity of tennis rackets on hand. Then, it retrieves the quantity on hand from a database table named inventory. If the quantity is greater than zero, the program updates the table and inserts a purchase record into another table named purchase_record. Otherwise, the program inserts an out-of-stock record into the purchase_record table.

-- available online in file EXAMP1

DECLARE

   qty_on_hand  NUMBER(5);

BEGIN

   SELECT quantity INTO qty_on_hand FROM inventory

      WHERE product = 'TENNIS RACKET'

      FOR UPDATE OF quantity;

   IF qty_on_hand > 0 THEN  -- check quantity

      UPDATE inventory SET quantity = quantity - 1

         WHERE product = 'TENNIS RACKET';

      INSERT INTO purchase_record

         VALUES ('Tennis racket purchased', SYSDATE);

   ELSE

      INSERT INTO purchase_record

         VALUES ('Out of tennis rackets', SYSDATE);

   END IF;

   COMMIT;

END;

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 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.

Block Structure

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. Thus, PL/SQL supports the divide-and- conquer approach to problem solving called stepwise refinement.

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.

A PL/SQL block has three parts: a declarative part, an executable part, and an exception-handling part. (In PL/SQL, a warning or error condition is called an exception.) Only the executable part is required.

The order of the parts is logical. First comes the declarative part, in which objects can be declared. Once declared, objects can be manipulated in the executable part. Exceptions raised during execution can be dealt with in the exception-handling part.

You can nest sub-blocks in the executable and exception-handling parts of a PL/SQL block or subprogram but not in the declarative part. Also, you can define local subprograms in the declarative part of any block. However, you can call local subprograms only from the block in which they are defined.

Variables and Constants

PL/SQL allows you to declare constants and variables, then use them in SQL and procedural statements anywhere an expression can be used. However, forward references are not allowed. So, you must declare a constant or variable before referencing it in other statements, including other declarative statements.

Declaring Variables

Variables can have any SQL datatype, such as CHAR, DATE, and NUMBER, or any PL/SQL datatype, such as BOOLEAN and BINARY_INTEGER. For example, assume that you want to declare a variable named part_no to hold 4-digit numbers and a variable named in_stock to hold the Boolean value TRUE or FALSE. You declare these variables as follows:

part_no  NUMBER(4);

in_stock BOOLEAN;

You can also declare records and PL/SQL tables using the RECORD and TABLE composite datatypes.

Assigning Values to a Variable

You can assign values to a variable in two ways. The first way uses the assignment operator (:=), a colon followed by an equal sign. You place the variable to the left of the operator and an expression to the right. Some examples follow:

tax := price * tax_rate;

bonus := current_salary * 0.10;

amount := TO_NUMBER(SUBSTR('750 dollars', 1, 3));

valid := FALSE;

The second way to assign values to a variable is to select or fetch database values into it. In the following example, you have Oracle compute a 10% bonus when you select the salary of an employee:

SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;

Then, you can use the variable bonus in another computation or insert its value into a database table.

Declaring Constants

Declaring a constant is like declaring a variable except that you must add the keyword CONSTANT and immediately assign a value to the constant. Thereafter, no more assignments to the constant are allowed. In the following example, you declare a constant named credit_limit:

credit_limit CONSTANT REAL := 5000.00;

Cursors

Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information. There are two kinds of cursors: implicit and explicit. PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row. For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. An example follows:

DECLARE

   CURSOR c1 IS

      SELECT empno, ename, job FROM emp WHERE deptno = 20;

The set of rows returned by a multi-row query is called the result set. Its size is the number of rows that meet your search criteria. As Figure 1 - 2 shows, an explicit cursor "points" to the current row in the result set. This allows your program to process the rows one at a time.

Multi-row query processing is somewhat like file processing. For example, a COBOL program opens a file, processes records, then closes the file. Likewise, a PL/SQL program opens a cursor, processes rows returned by a query, then closes the cursor. Just as a file pointer marks the current position in an open file, a cursor marks the current position in a result set.

Use the OPEN, FETCH, and CLOSE statements to control a cursor. The OPEN statement executes the query associated with the cursor, identifies the result set, and positions the cursor before the first row. The FETCH statement retrieves the current row and advances the cursor to the next row. When the last row has been processed, the CLOSE statement disables the cursor.

Cursor FOR Loops

In most situations that require an explicit cursor, you can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH, and CLOSE statements.

A cursor FOR loop implicitly declares its loop index as a record that represents a row in a database table, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, then closes the cursor when all rows have been processed. In the following example, the cursor FOR loop implicitly declares emp_rec as a record:

DECLARE

   CURSOR c1 IS

      SELECT ename, sal, hiredate, deptno FROM emp;

   ...

BEGIN

   FOR emp_rec IN c1 LOOP

      ...

      salary_total :=  salary_total + emp_rec.sal;

   END LOOP;

END;

You use dot notation to reference individual fields in the record.

Cursor Variables

Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. But, unlike a cursor, a cursor variable can be opened for any type-compatible query. It is not tied to a specific query. Cursor variables are true PL/SQL variables, to which you can assign new values and which you can pass to subprograms stored in an Oracle database. This gives you more flexibility and a convenient way to centralize data retrieval.

Typically, you open a cursor variable by passing it to a stored procedure that declares a cursor variable as one of its formal parameters. The following packaged procedure opens the cursor variable generic_cv for the chosen query:

CREATE PACKAGE BODY emp_data AS

   PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,

                      choice     IN NUMBER) IS

   BEGIN

      IF choice = 1 THEN

         OPEN generic_cv FOR SELECT * FROM emp;

      ELSIF choice = 2 THEN

         OPEN generic_cv FOR SELECT * FROM dept;

      ELSIF choice = 3 THEN

         OPEN generic_cv FOR SELECT * FROM salgrade;

      END IF;

   END open_cv;

END emp_data;

Attributes

PL/SQL variables and cursors have attributes, which are properties that let you reference the datatype and structure of an object without repeating its definition. Database columns and tables have similar attributes, which you can use to ease maintenance.

%TYPE

The %TYPE attribute provides the datatype of a variable or database column. This is particularly useful when declaring variables that will hold database values. For example, assume there is a column named title in a table named books. To declare a variable named my_title having the same datatype as the column title, you use dot notation and the %TYPE attribute, as follows:

my_title books.title%TYPE;

Declaring my_title with %TYPE has two advantages. First, you need not know the exact datatype of title. Second, if you change the database definition of title (make it a longer character string, for example), the datatype of my_title changes accordingly at run time.

%ROWTYPE

In PL/SQL, records are used to group data. A record consists of a number of related fields in which data values can be stored. The %ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable.

Columns in a row and corresponding fields in a record have the same names and datatypes. In the example below, you declare a record named dept_rec. Its fields have the same names and datatypes as the columns in the dept table.

DECLARE

   dept_rec dept%ROWTYPE;  -- declare record variable

You use dot notation to reference fields, as the following example shows:

my_deptno := dept_rec.deptno;

If you declare a cursor that retrieves the last name, salary, hire date, and job title of an employee, you can use %ROWTYPE to declare a record that stores the same information, as follows:

DECLARE

   CURSOR c1 IS SELECT ename, sal, hiredate, job FROM emp;

   emp_rec c1%ROWTYPE;  -- declare record variable that

                        -- represents a row in the emp table

When you execute the statement

FETCH c1 INTO emp_rec;

the value in the ename column of the emp table is assigned to the ename field of emp_rec, the value in the sal column is assigned to the sal field, and so on. Figure 1 - 3 shows how the result might appear.

%ROWTYPE Record

Control Structures

Control structures are the most important PL/SQL extension to SQL. Not only does PL/SQL let you manipulate Oracle data, it lets you process the data using conditional, iterative, and sequential flow-of-control statements such as IF-THEN-ELSE, FOR-LOOP, WHILE-LOOP, EXIT-WHEN, and GOTO. Collectively, these statements can handle any situation.

Conditional Control

Often, it is necessary to take alternative actions depending on circumstances. The IF-THEN-ELSE statement lets you execute a sequence of statements conditionally. The IF clause checks a condition; the THEN clause defines what to do if the condition is true; the ELSE clause defines what to do if the condition is false or null.

Consider the program below, which processes a bank transaction. Before allowing you to withdraw $500 from account 3, it makes sure the account has sufficient funds to cover the withdrawal. If the funds are available, the program debits the account; otherwise, the program inserts a record into an audit table.

-- available online in file EXAMP2

DECLARE

   acct_balance NUMBER(11,2);

   acct         CONSTANT NUMBER(4) := 3;

   debit_amt    CONSTANT NUMBER(5,2) := 500.00;

BEGIN

   SELECT bal INTO acct_balance FROM accounts

      WHERE account_id = acct

      FOR UPDATE OF bal;
   IF acct_balance >= debit_amt THEN

      UPDATE accounts SET bal = bal - debit_amt

         WHERE account_id = acct;

   ELSE

      INSERT INTO temp VALUES

         (acct, acct_balance, 'Insufficient funds');

             -- insert account, current balance, and message

   END IF;

   COMMIT;

END;

A sequence of statements that uses query results to select alternative actions is common in database applications. Another common sequence inserts or deletes a row only if an associated entry is found in another table. You can bundle these common sequences into a PL/SQL block using conditional logic. This can improve performance and simplify the integrity checks built into Oracle Forms applications.

Iterative Control

LOOP statements let you execute a sequence of statements multiple times. You place the keyword LOOP before the first statement in the sequence and the keywords END LOOP after the last statement in the sequence. The following example shows the simplest kind of loop, which repeats a sequence of statements continually:

LOOP

   -- sequence of statements

END LOOP;

The FOR-LOOP statement lets you specify a range of integers, then execute a sequence of statements once for each integer in the range. For example, suppose that you are a manufacturer of custom-made cars and that each car has a serial number. To keep track of which customer buys each car, you might use the following FOR loop:

FOR i IN 1..order_qty LOOP

   UPDATE sales SET custno = customer_id

      WHERE serial_num = serial_num_seq.NEXTVAL;

END LOOP;

The WHILE-LOOP statement associates a condition with a sequence of statements. Before each iteration of the loop, the condition is evaluated. If the condition yields TRUE, the sequence of statements is executed, then control resumes at the top of the loop. If the condition yields FALSE or NULL, the loop is bypassed and control passes to the next statement.

In the following example, you find the first employee who has a salary over $4000 and is higher in the chain of command than employee 7902:

-- available online in file EXAMP3

DECLARE

   salary         emp.sal%TYPE;

   mgr_num        emp.mgr%TYPE;

   last_name      emp.ename%TYPE;

   starting_empno CONSTANT NUMBER(4) := 7902;

BEGIN

   SELECT sal, mgr INTO salary, mgr_num FROM emp

      WHERE empno = starting_empno;

   WHILE salary < 4000 LOOP

      SELECT sal, mgr, ename INTO salary, mgr_num, last_name

         FROM emp WHERE empno = mgr_num;

   END LOOP;

   INSERT INTO temp VALUES (NULL, salary, last_name);

   COMMIT;

END;

The EXIT-WHEN statement lets you complete a loop if further processing is impossible or undesirable. When the EXIT statement is encountered, the condition in the WHEN clause is evaluated. If the condition yields TRUE, the loop completes and control passes to the next statement. In the following example, the loop completes when the value of total exceeds 25,000:

LOOP

   ...

   total := total + salary;

   EXIT WHEN total > 25000;  -- exit loop if condition is true

END LOOP;

-- control resumes here

Sequential Control

The GOTO statement lets you branch to a label unconditionally. The label, an undeclared identifier enclosed by double angle brackets, must precede an executable statement or a PL/SQL block. When executed, the GOTO statement transfers control to the labeled statement or block, as the following example shows:

IF rating > 90 THEN

   GOTO calc_raise;  -- branch to label

END IF;

...

<<calc_raise>>

IF job_title = 'SALESMAN' THEN  -- control resumes here

   amount := commission * 0.25;

ELSE

   amount := salary * 0.10;

END IF;

PL/SQL Tables

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. But, unlike an array, a PL/SQL table is unbounded. So, its size can increase dynamically. Also, it does not require consecutive index numbers. So, it can be indexed by any series of integers.

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 use a cursor FOR loop to fetch an entire column or table of Oracle data into a PL/SQL table. In the following example, you fetch a table of data into the PL/SQL table dept_tab:

DECLARE

   TYPE DeptTabTyp IS TABLE OF dept%ROWTYPE

      INDEX BY BINARY_INTEGER;

   dept_tab DeptTabTyp;

   n BINARY_INTEGER := 0;

BEGIN

   FOR dept_rec IN (SELECT * FROM dept) LOOP

      n := n + 1;

      dept_tab(n) := dept_rec;

   END LOOP;

   ...

END;

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, with a user-defined record, you can declare fields of your own.

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. Consider the following example:

DECLARE

   TYPE TimeTyp IS RECORD (minute SMALLINT, hour SMALLINT);

   TYPE MeetingTyp IS RECORD (

      day     DATE,

      time    TimeTyp,  -- nested record

      place   VARCHAR2(20),

      purpose VARCHAR2(50));

Modularity

Modularity lets you break an application down into manageable, well-defined logic modules. Through successive refinement, you can reduce a complex problem to a set of simple problems that have easy-to-implement solutions. PL/SQL meets this need with program units. Besides blocks and subprograms, PL/SQL provides the package, which allows you to group related program objects into larger units.

Subprograms

PL/SQL has two types of subprograms called procedures and functions, which can take parameters and be invoked (called). As the following example shows, a subprogram is like a miniature program, beginning with a header followed by an optional declarative part, an executable part, and an optional exception-handling part:

PROCEDURE award_bonus (emp_id NUMBER) IS

   bonus        REAL;

   comm_missing EXCEPTION;

BEGIN

   SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;

   IF bonus IS NULL THEN

      RAISE comm_missing;

   ELSE

      UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;

   END IF;

EXCEPTION

   WHEN comm_missing THEN

      ...

END award_bonus;

When called, this procedure accepts an employee number. It uses the number to select the employee's commission from a database table and, at the same time, compute a 15% bonus. Then, it checks the bonus amount. If the bonus is null, an exception is raised; otherwise, the employee's payroll record is updated.

Packages

PL/SQL lets you bundle logically related types, program objects, and subprograms into a package. Each package is easy to understand and the interfaces between packages are simple, clear, and well defined. This aids application development.

Packages usually have two parts: a specification and a body. The specification is the interface to your applications; it declares the types, constants, variables, exceptions, cursors, and subprograms available for use. The body defines cursors and subprograms and so implements the specification.

In the following example, you package two employment procedures:

CREATE PACKAGE emp_actions AS  -- package specification

   PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...);

   PROCEDURE fire_employee (emp_id NUMBER);

END emp_actions;



CREATE PACKAGE BODY emp_actions AS  -- package body

   PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS

   BEGIN

      INSERT INTO emp VALUES (empno, ename, ...);

   END hire_employee;

   PROCEDURE fire_employee (emp_id NUMBER) IS

   BEGIN

      DELETE FROM emp WHERE empno = emp_id;

   END fire_employee;

END emp_actions;

Only the declarations in the package specification are visible and accessible to applications. Implementation details in the package body are hidden and inaccessible.

Packages can be compiled and stored in an Oracle database, where their contents can be shared by many applications. When you call a packaged subprogram for the first time, the whole package is loaded into memory. So, subsequent calls to related subprograms in the package require no disk I/O. Thus, packages can enhance productivity and improve performance.

Information Hiding

With information hiding, you see only the details that are relevant at a given level of algorithm and data structure design. Information hiding keeps high-level design decisions separate from low-level design details, which are more likely to change.

Algorithms

You implement information hiding for algorithms through top-down design. Once you define the purpose and interface specifications of a low-level procedure, you can ignore the implementation details. They are hidden at higher levels. For example, the implementation of a procedure named raise_salary is hidden. All you need to know is that the procedure will increase a specific employee salary by a given amount. Any changes to the definition of raise_salary are transparent to calling applications.

Data Structures

You implement information hiding for data structures though data encapsulation. By developing a set of utility subprograms for a data structure, you insulate it from users and other developers. That way, other developers know how to use the subprograms that operate on the data structure but not how the structure is represented.

With PL/SQL packages, you can specify whether types, program objects, and subprograms are public or private. Thus, packages enforce data encapsulation by letting you put type declarations in a black box. A private type definition is hidden and inaccessible. Only the package, not your application, is affected if the definition changes. This simplifies maintenance and enhancement.

Error Handling

PL/SQL makes it easy to detect and process predefined and user-defined error conditions called exceptions. 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. To handle raised exceptions, you write separate routines called exception handlers.

Predefined exceptions are raised implicitly by the runtime system. For example, if you try to divide a number by zero, PL/SQL raises the predefined exception ZERO_DIVIDE automatically. You must raise user-defined exceptions explicitly with the RAISE statement.

You can define exceptions of your own in the declarative part of any PL/SQL block or subprogram. In the executable part, you check for the condition that needs special attention. If you find that the condition exists, you execute a RAISE statement. In the example below, you compute the bonus earned by a salesperson. The bonus is based on salary and commission. So, if the commission is null, you raise the exception comm_missing.

DECLARE

   salary       NUMBER(7,2);

   commission   NUMBER(7,2);

   comm_missing EXCEPTION;  -- declare exception

BEGIN

   SELECT sal, comm INTO salary, commission FROM emp

      WHERE empno = :emp_id;

   IF commission IS NULL THEN

      RAISE comm_missing;  -- raise exception

   ELSE

       :bonus := (salary * 0.05) + (commission * 0.15);

   END IF;

EXCEPTION  -- begin exception handlers

   WHEN comm_missing THEN

       -- process error

END;

The variables emp_id and bonus are declared and assigned values in a host environment. For more information about host variables, see "Oracle Precompiler Environment" [*].

Architecture

The PL/SQL runtime system is a technology, not an independent product. Think of this technology as an engine that executes PL/SQL blocks and subprograms. The engine can be installed in an Oracle Server or in an application development tool such as Oracle Forms or Oracle Reports. So, PL/SQL can reside in two environments:
the Oracle Server
Oracle tools

These two environments are independent. PL/SQL might be available in the Oracle Server but unavailable in tools, or the other way around. In either environment, the PL/SQL engine accepts as input any valid PL/SQL block or subprogram. Figure 1 - 4 shows the PL/SQL engine processing an anonymous block.

The PL/SQL engine executes procedural statements but sends SQL statements to the SQL Statement Executor in the Oracle Server.

 

In the Oracle Server

Application development tools that lack a local PL/SQL engine must rely on Oracle to process PL/SQL blocks and subprograms. When it contains the PL/SQL engine, an Oracle Server can process PL/SQL blocks and subprograms as well as single SQL statements. The Oracle Server passes the blocks and subprograms to its local PL/SQL engine.

Anonymous Blocks

Anonymous PL/SQL blocks can be embedded in an Oracle Precompiler or OCI program. At run time, the program, lacking a local PL/SQL engine, sends these blocks to the Oracle Server, where they are compiled and executed. Likewise, interactive tools such as SQL*Plus and Server Manager, lacking a local PL/SQL engine, must send anonymous blocks to Oracle.

Stored Subprograms

Named PL/SQL blocks (subprograms) can be compiled separately and stored permanently in an Oracle database, ready to be executed. A subprogram explicitly CREATEd using an Oracle tool is called a stored subprogram. Once compiled and stored in the data dictionary, it is a database object, which can be referenced by any number of applications connected to that database.

Stored subprograms defined within a package are called packaged subprograms; those defined independently are called standalone subprograms. (Subprograms defined within another subprogram or within a PL/SQL block are called local subprograms. They cannot be referenced by other applications and exist only for the convenience of the enclosing block.)

Stored subprograms offer higher productivity, better performance, memory savings, application integrity, and tighter security. For example, by designing applications around a library of stored procedures and functions, you can avoid redundant coding and increase your productivity.

You can call stored subprograms from a database trigger, another stored subprogram, an Oracle Precompiler application, an OCI application, or interactively from SQL*Plus or Server Manager. For example, you might call the standalone procedure create_dept from SQL*Plus as follows:

SQL> EXECUTE create_dept('FINANCE', 'NEW YORK');

Subprograms are stored in parsed, compiled form. So, when called, they are loaded and passed to the PL/SQL engine immediately. Moreover, stored subprograms take advantage of the Oracle shared memory capability. Only one copy of a subprogram need be loaded into memory for execution by multiple users.

Database Triggers

A database trigger is a stored subprogram associated with a table. You can have Oracle automatically fire the database trigger before or after an INSERT, UPDATE, or DELETE statement affects the table. One of the many uses for database triggers is to audit data modifications. For example, the following database trigger fires whenever salaries in the emp table are updated:

CREATE TRIGGER audit_sal

   AFTER UPDATE OF sal ON emp

   FOR EACH ROW

BEGIN

   INSERT INTO emp_audit VALUES ...

END;

You can use all the SQL data manipulation statements and any procedural statement in the executable part of a database trigger.

In Oracle Tools

When it contains the PL/SQL engine, an application development tool can process PL/SQL blocks. The tool passes the blocks to its local PL/SQL engine. The engine executes all procedural statements at the application site and sends only SQL statements to Oracle. Thus, most of the work is done at the application site, not at the server site.

Furthermore, if the block contains no SQL statements, the engine executes the entire block at the application site. This is useful if your application can benefit from conditional and iterative control.

Frequently, Oracle Forms applications use SQL statements merely to test the value of field entries or to do simple computations. By using PL/SQL instead, you can avoid calls to the Oracle Server. Moreover, you can use PL/SQL functions to manipulate field entries.

Advantages of PL/SQL

PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:
support for SQL
higher productivity
better performance
portability
integration with Oracle

Support for SQL

SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like commands such as INSERT, UPDATE, and DELETE make it easy to manipulate the data stored in a relational database.

SQL is non-procedural, meaning that you can state what you want done without stating how to do it. Oracle determines the best way to carry out your request. There is no necessary connection between consecutive statements because Oracle executes SQL statements one at a time.

PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction control commands, as well as all the SQL functions, operators, and pseudocolumns. So, you can manipulate Oracle data flexibly and safely.

Higher Productivity

PL/SQL adds functionality to non-procedural tools such as Oracle Forms and Oracle Reports. With PL/SQL in these tools, you can use familiar procedural constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger. You need not use multiple trigger steps, macros, or user exits. Thus, PL/SQL increases productivity by putting better tools in your hands.

Moreover, PL/SQL is the same in all environments. As soon as you master PL/SQL with one Oracle tool, you can transfer your knowledge to other tools, and so multiply the productivity gains. For example, scripts written with one tool can be used by other tools.

Better Performance

Without PL/SQL, Oracle must process SQL statements one at a time. Each SQL statement results in another call to Oracle and higher performance overhead. In a networked environment, the overhead can become significant. Every time a SQL statement is issued, it must be sent over the network, creating more traffic.

However, with PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce communication between your application and Oracle. As if your application is database intensive, you can use PL/SQL blocks and subprograms to group SQL statements before sending them to Oracle for execution.

PL/SQL also improves performance by adding procedural processing power to Oracle tools. Using PL/SQL, a tool can do any computation quickly and efficiently without calling on Oracle. This saves time and reduces network traffic.

Portability

Applications written in PL/SQL are portable to any operating system and platform on which Oracle runs. In other words, PL/SQL programs can run anywhere Oracle can run; you need not tailor them to each new environment. That means you can write portable program libraries, which can be reused in different environments.

Integration with Oracle

Both PL/SQL and Oracle are based on SQL. Moreover, PL/SQL supports all the SQL datatypes. Combined with the direct access that SQL provides, these shared datatypes integrate PL/SQL with the Oracle data dictionary.

The %TYPE and %ROWTYPE attributes further integrate PL/SQL with the data dictionary. For example, you can use the %TYPE attribute to declare variables, basing the declarations on the definitions of database columns. If a definition changes, the variable declaration changes accordingly at run time. This provides data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.