An Introduction to PL/SQL

John C. Lennon, Dulcian, Inc.

Introduction

PL/SQL is Oracle’s procedural language extension to the SQL language, It has its origins in ADA, a high-level programming language developed for the US Department of Defense. ADA was named after a daughter of Lord Byron, Augusta ADA, Countess of Lovelace, a 19th century mathematician, whom many regard as the world’s first computer programmer because of her work with Charles Babbage’s Analytical Engine.

PL/SQL introduces 3GL-style procedural logic into the world of declarative logic. Its first incarnation was in 1991 as the ‘procedural option’ for Oracle RDBMS Version 6.0. At about the same time, PL/SQL debuted on the client side in SQL*Forms Version 3. On both client and server, it was very basic in its functionality, but it was still warmly welcomed by the Oracle development community. From these humble beginnings, PL/SQL has evolved into a powerful, complex, and flexible programming tool. Unfortunately, the client-side versions did not kept pace with the server versions for some years. Only PL/SQL Versions 1.x were supported, ­ much to the frustration of many developers. However, since the introduction of Developer/2000 Release 2, the full range of features offered by PL/SQL Version 2.3 and now version 8 are also supported by Oracle’s development tools.

Today, PL/SQL is an extremely powerful structured language, which is not only tightly integrated with SQL but also incorporates many of the advanced features of other procedural languages. It provides the foundation for distributing and processing transactions across the network for both client/server and Web applications. Despite its structured approach, PL/SQL it is also a language that , is very easy to abuse and misuse. It is possible to produce code that works, but is highly inefficient and virtually impossible to maintain.

As the development environment leans towards the web, PL/SQL may be is in its death throes; However, there is a lot of PL/SQL used in production applications, which will  not be going away any time soon. It is still the language of choice for many Oracle developers..

Structure

PL/SQL is an algorithmic, block-structured language. A block is the basic unit from which all PL/SQL programs are built. A block can be named (functions and procedures) or anonymous. An anonymous block can exist only within a named block, a SQL script, or a trigger. A block consists of between one and four sections, as illustrated in Table 1.

Header	For named blocks only. Includes a return declaration for functions and optionally IN and/or OUT parameter declarations.
Declaration Section	Declares variables, cursors etc. to be used in the block.
BEGIN	Executable code
Exception Section (optional)	Exception (error) handling
END;

Table 1: Sections of a block

Note that only the executable section is mandatory and only named blocks have a header.

Sections of the PL/SQL Block

The following are descriptions of the PL/SQL block sections.

The Header

For named blocks the header contains the type and name of the block and optionally the names and data types of parameters (arguments). A procedure may have IN, OUT, or IN/OUT parameters. A function normally has only IN parameters. If a function is to be embedded in SQL, then OUT and IN/OUT parameters are not permitted. The header for a function must end with a RETURN statement declaring the datatype of the return value.

Declaration section

The declaration section is where variables, constants, cursors, and other objects such as PL/SQL tables are declared.

Executable section

The executable section contains the action(s) to be performed, i.e. the executable PL/SQL statements. There must be at least one executable statement in this section.

 

Exception section

The exception section traps warning and error conditions and programmer-defined exceptions. The section is optional. If it is omitted, exceptions will be trapped by the exception section in an enclosing block, if there is one. Otherwise,  PL/SQL will raise an unhandled exception error.

Procedures and Functions

As mentioned earlier, a function or procedure is a named PL/SQL block. It may optionally contain nested anonymous blocks that should reflect the logical flow of the program and/or provide for finer control of exception handling.

Code samples 1 and 2 show the same function constructed in two different ways. Sample 1 has single block and Sample 2 has a second, nested block. In Sample 2 the cursor cur_Salary is declared in the outer, or enclosing block.

 

FUNCTION get_salary(p_Employee_ID IN employee.salary%TYPE

 RETURN NUMBER

IS

CURSOR cur_Salary IS

  SELECT salary

    FROM employee

   WHERE employee_id = p_Employee_ID;

 v_Salary employee.salary%TYPE;

 BEGIN

   OPEN cur_Salary;

     FETCH cur_Salary INTO v_Salary;

   CLOSE cur_Salary;

   RETURN v_Salary;

END get_salary;

Code Sample 1: Single block

 

FUNCTION get_salary(p_Employee_ID IN employee.salary%TYPE

 RETURN NUMBER

IS

CURSOR cur_Salary IS

  SELECT salary

    FROM employee

   WHERE employee_id = p_Employee_ID;

BEGIN

  DECLARE

     v_Salary employee.salary%TYPE;

  BEGIN

   OPEN cur_Salary;

     FETCH cur_Salary INTO v_Salary;

   CLOSE cur_Salary;

   RETURN v_Salary;

  END;

Code Sample 2: Nested blocks

The inner, anonymous, block can open and close the cursor and fetch from it, because the block is within the scope of the cursor. The variable v_Salary is declared within the inner block, so that its scope is limited to that block. Any attempt to refer to v_Salary in the outer block, for instance by moving the RETURN statement between the two END statements, would result in an error. A procedure is a stand-alone executable module and is called as an executable PL/SQL statement. It may have no parameters (arguments) or any combination of IN, OUT, and IN/OUT parameters. Parameters may be any valid PL/SQL datatype.

A function differs from a procedure in that it has a return value and is not a stand-alone executable statement. A function is called as part of an executable statement or embedded in SQL. Like a procedure, a function may or may not have parameters ­ but normally it has only IN parameters. If a function is to be embedded in SQL, it may not have OUT parameters. It is mandatory that functions return a value, even if that value is NULL. Like parameters, the return value of a function may be any valid datatype. A function can be used in a PL/SQL statement in place of an expression having the same datatype as the return value. Code Examples 1 and 2 are examples of functions. Code Example 3 is an example of a simple procedure.

PROCEDURE Update_Salary(
           p_Employee_ID              IN employee.employee_id%TYPE
           ,p_Salary     IN employee.salary%TYPE)

IS

BEGIN

UPDATE    employee
    SET salary = p_Salary
WHERE  employee_id = p_Employee_id;
END Update_Salary;

Code Example 3: Simple Procedure

Packages

Packages provide a means of encapsulating logically related PL/SQL objects, such as functions, procedures, cursors, variables, etc, thus promoting the concept of structured programming. Packages provide considerable performance enhancement because, when any element of the package is referenced for the first time, the entire package (compiled and validated) is loaded into memory in the SGA and is available to all users without further disk access. Packages also minimize cascading dependencies, thus avoiding unnecessary compilation. The advantages of packages are as follows:

·            Modularity

·            Information Hiding

·            Top-down design

·            Global data

·            Better Performance

·            Overloading

 

The application developer can decide which elements of the package are public and accessible and which are hidden, thus protecting the integrity of the package and minimizing the effects of changes. A package specification can be compiled without its body, allowing other modules that reference it to compile before it is complete. Because a package has the access privileges of its owner, users can be given strictly controlled access to data that would otherwise be hidden from them.

A very useful feature of packages is overloaded names. This allows more than one function or procedure to have the same name, provided that the number and/or datatypes of the parameters of each are different.

A package consists of two parts: ­ the specification and the body. Objects that are declared in the package specification are public and can be referenced from anywhere, for example from another package. Objects that are declared only in the body of the package are private and can be referenced only by other elements of the package. Values assigned to public objects, such as variables, are persistent and can therefore be regarded as global. Functions and procedures are always defined in the package body and optionally may be declared in the specification to make them public. Code Example 4 illustrates a package with a public function and a public procedure.

 

PACKAGE Employee_Pkg      /* PACKAGE SPEC for employee table */

IS

FUNCTION Get_Salary(p_Emp1oyee_Id employee.employee_id%TYPE

                   ,p_Sa1ary      emp1oyee.salary%TYPE)

RETURN NUMBER;

PROCEDURE Update_Salary(p_Employee_Id IN employee.employee.id%TYPE

                       ,p_Salary      IN employee.salary%TYPE)

END Emp1oyee_Pkg:

       

PACKAGE BODY Employee_Pkg /* PACKAGE BODY for employee table */

IS

 FUNCTION Get_Salary(p_Employee_Id employee.employee_id%TYPE

                    ,p_Salary      emp1oyee.salary%TYPE)

 RETURN NUMBER

 IS

 BEGIN

 ....................................

 ....................................

 END Get_Sa1ary:

 PROCEDURE Update_Salary(p_Employee_Id IN employee.employee.id%TYPE

                        ,p_Salary      IN employee.salary%TYPE)

 IS

 BEGIN

 ....................................

 ....................................

 END Get_Sa1ary;

END Employee__Pkg;

Code Example 4: Sample package

Variables and Constants

Variables And Constants
The vast majority of PL/SQL programs require a means of storing data internally for use in the program. The data may be retrieved from the database or it may consist of derived, calculated, or assigned values. The internal storage is provided by variables and constants. Variables may be scalar, i.e. consisting of only a single value, or a composite structure such as a record or a PL/SQL table. Variables must be declared in the declaration section of a PL/SQL block and, optionally,  may be declared with an initial value assigned to them. If no value is assigned to it, a variable will have an initial value of NULL.

A variable has a name, a datatype, and a value or values. The name is a pointer to the location in memory where the value or values reside and the datatype governs the type of information that can be stored in the variable. Each element of a composite variable may have the same or a different datatype. 

Constants are identical to variables in almost every way except that a value must be assigned when the constant is declared. As the name suggests,  the value of a constant may not be changed from the initial value. Examples of declarations of  variables and constants are shown in Code Examples 5 and 6.

DECLARE
 v_Customer_Name customer.name%TYPE;
 c_Maximum_Orders CONSTANT NUMBER(2) := 25;
BEGIN
 --------------------
 --------------------
END;

Code Example 5: Declarations in an Anonymous Block

 

Declaration in a named block looks like:
PROCEDURE Process_Order IS
 v_Customer_Name customer.name%TYPE;
 c_Maximum_Orders CONSTANT NUMBER(2) := 25;
BEGIN
 --------------------
 --------------------
END Process_Order;

Code Example 6: Declarations in a named block

 

 

Variable Names

In common with most other Oracle objects, the name of a PL/SQL variable or constant can be up to 30 characters in length. The first character must be a letter, which may then be followed by letters, numerals, underscores, $, or #. This provides the flexibility to choose names that are descriptive of what the variable or constant represents as well as its context. In many cases, descriptive names for variables and other PL/SQL objects can,  make programs to some extent self-documenting, thus avoiding the time, effort, and even clutter of extensive commenting.

 Datatypes

The datatype controls the storage format, the valid values, and restrictions on use of a variable or constant. Oracle provides a wealth of datatypes, some of which the average developer is unlikely ever to use. There are four classes of datatype ­ Boolean, character, number, and date/time. The datatype ROWID, which stores a binary value and really fits into no class, has been included in the character class because it can, for most purposes, be treated as if it is of type CHAR. PL/SQL8 and Oracle8 have additional datatypes for NLS multi-byte character sets and large object (LOB) datatypes, which are considered beyond the scope of this paper.

The Boolean Datatype

A Boolean datatype is a logical datatype, and elements declared as Boolean may have only the values TRUE, FALSE, or NULL. The Oracle RDBMS does not support this datatype and it is valid only in PL/SQL. Booleans are particularly useful as the return value of a function, often enabling otherwise complex code to be hidden and replaced with a simple and elegant statement. An example of using a Boolean return value is shown in the Code Example 7.

IF Salary_Exceeds_Grade_Max (…….) THEN
 dbms_output.put_line('The salary entered exceeds

                                     the maximum for the grade');
END IF;

 

Code Example 7: Using a Boolean return value

 

In this example, the function might look up the maximum salary for the employee grade, apply possibly complex business rules such as exceptions for long service, and return either TRUE or FALSE. The name of the function makes its purpose clear and no further explanation is needed.

Character Datatypes

As can be seen from Table 2, the precision of PL/SQL character variables is very different from that of the equivalent database columns. Care must therefore be exercised when using PL/SQL variables to retrieve values from or insert values into database tables. For this reason alone, it is always advisable to declare elements that match up with database table columns using the %TYPE attribute.

 

Datatype	Maximum length (bytes) PL/SQL	Maximum length (bytes) in Oracle Tables
CHAR	32767	255  (2000 in ORACLE8)
CHARACTER	32767	255  (2000 in ORACLE8)
LONG	32760	2 Gigabytes
LONG RAW	32760	2 Gigabytes
VARCHAR	32767	2000  (4000 in ORACLE8)
VARCHAR2	32767	2000  (4000 in ORACLE8)
RAW	32767	255  (2000 in ORACLE8)
ROWID	18	18

Table 2: Character Datatypes

 

char and character

The two datatypes, CHAR and CHARACTER, are synonymous. Prior to Oracle7, these datatypes were of variable length. However, in order to comply with ANSI standards, they are now fixed length and the VARCHAR2 datatype, described later in this section, has replaced them as the normally preferred variable-length character datatype. Because of the inherent difficulties encountered when working with fixed-length strings, the use of these datatypes should be avoided except when absolutely necessary. An example of this is when working with DB2 data sources where there is a specific requirement for fixed-length strings.

If a value from a database column of datatype VARCHAR or VARCHAR2 (variable length) is FETCHED into a CHAR variable, the value will be padded to the right with spaces to the length of the variable. Conversely, if a value in a CHAR variable is INSERTed into a VARCHAR2 database column, any trailing spaces will be discarded.

During an upgrade from RDBMS Version 6 to Oracle7, CHAR table columns are automatically converted to VARCHAR2.

long

The PL/SQL LONG character datatype is very different from its database column counterpart. Its maximum length is 32,760 bytes as compared with 2GB, and character functions such as INSTR and SUBSTR, which are not permitted for a column, may be applied to it. Attempting to fetch a LONG database column value with a length greater than 32,760 bytes into a LONG PL/SQL variable will result in an error ­ ³ORA-06502: PL/SQL: numeric or value error². There is no automatic truncation and, because SUBSTR cannot be applied to the database column, no means of working around this restriction. Because the VARCHAR2 data type has a maximum length that¹s 7 bytes longer and can be manipulated and used in exactly the same manner, there is no point in using the LONG datatype in PL/SQL.

Long Raw

The LONG RAW datatype is exactly the same as LONG except that Oracle will not attempt to perform character set conversions when moving data between different platforms.

varchar and varchar2

At the time of writing, VARCHAR and VARCHAR2 are synonymous. VARCHAR is intended to provide compatibility with IBM and ANSI relational databases. However, it is likely that the definition of VARCHAR will change in a future version of the ANSI SQL standards. For this reason, Oracle suggests that VARCHAR should be avoided whenever possible.
VARCHAR2 is the character datatype most commonly used in PL/SQL programming. VARCHAR2 variables can be manipulated with a comprehensive set of built-in functions, similar to those found in SQL. VARCHAR2 elements should always be declared with a precision specified (1 to ­32,767). PL/SQL Versions 1.x permit declarations without a precision. This results in an element with a maximum length of 1one and can lead to unexpected errors showing up at run-time. Version 2 upwards will not compile without a precision being specified.

If a value from a database column of datatype CHAR (fixed length) is FETCHED into a VARCHAR2 variable, any trailing spaces will be automatically discarded.

raw

The RAW datatype is similar to VARCHAR2, except that Oracle will not attempt to perform character set conversions when moving data between different platforms and the maximum length of a RAW database column is only 255 bytes (2,000 in Oracle8).

rowid

ROWID is not strictly a character datatype but a binary value that identifies the exact physical location of a row in a table. It is internally created and maintained. The value of a ROWID can be examined by assigning it to an 18-byte character string, but there is usually no reason to do this. ROWID can be used to access a record in a database and is normally the fastest possible method of doing so. ROWID is internal to Oracle and is not part of the ANSI standard. Therefore, it cannot be used in programs intended to run against non-Oracle databases.

Numeric Datatypes
There are principally only two numeric datatypes in PL/SQL, NUMBER and BINARY_INTEGER. As shown in Figures 3 and 4, all other numeric datatypes are sub-types of these two and provide compatibility with ANSI SQL and IBM datatypes.

Number

The NUMBER datatype is used for both fixed and floating-point numbers. When a NUMBER element is declared, a precision and scale may be specified. If they are omitted, the element will be floating point. The precision of a NUMBER is the total number of digits, with a maximum of 38. If a precision is not specified, it defaults to the maximum. The scale must be in the range minus 84 to plus 127, and defines the number of digits to the left or right of the decimal point at which rounding occurs. If the scale is zero, then rounding will be to the nearest whole number.

 

binary integer

The BINARY_INTEGER datatype allows for storage of signed integers in the range ­231 to +231. Because the values assigned to this datatype are represented as signed binary numbers, it is possible that there could be some performance advantage over NUMBER datatypes. This is because PL/SQL can use them in calculations without any conversion. NATURAL and POSITIVE are both sub-types of BINARY_INTEGER and have a maximum value of 231 and minimum values of zero and 1one respectively.

 

The DATE Datatype

Both PL/SQL and the RDBMS provide a true DATE datatype that stores both date and time. Oracle provides a collection of powerful date functions that allow mathematical and formatting operations on dates.  The valid range of dates supported by Oracle is 1 January 4712 BC to 31 December 4712 AD (31 December 9999 AD in Oracle8) in one-second increments. If the time element is omitted, it defaults to midnight.

Built-In Functions

PL/SQL provides a wide range of very powerful built-in functions, which can be categorized as character, number, date, and miscellaneous. These almost exactly duplicate the range of functions found in Oracle’s implementation of the SQL language ­ the notable exception being the DECODE function which is not available in PL/SQL.

Character Functions

There are several types of character functions. Each type is discussed briefly below.

ASCII

ASCII returns the number that represents a single character in the current database character set. It should be noted that, despite the name of the function, the character set is not necessarily ASCII, but is dependant on the operating system character set. If more than one character is passed the second and subsequent characters will be ignored.

CHR

The CHR function is the inverse of ASCII. It returns a single character corresponding to the number in the character set passed as the parameter. It is especially useful for embedding a non-printing character such as a linefeed in a string.

CONCAT

CONCAT is not a particularly useful function. It concatenates two strings passed as parameters. The concatenation operator (||) is easier to use and more powerful than CONCAT.

INITCAP

INITCAP reformats the string passed as a parameter. It sets the first letter of each Œword¹ to upper case and the remaining letters to lower case. A word is a string of characters separated by a space or non-alphanumeric character, such as #, $, *, _, etc.

Unfortunately INITCAP lacks real intelligence, which limits its usefulness. It always follows arbitrary rules, which will sometimes not deliver the desired results as illustrated below:

 

 

 

INSTR

INSTR searches a character string to find an occurrence within it of a second string. If found the position of the first character of the second string within the first string is returned. If it is not found, then 0 is returned.

 

In addition to the two mandatory parameters, INSTR has two optional numeric parameters ­ start_position and nth_occurence. The default for both is 1, i.e. starting at the first character and searching for the first occurrence.
Specifying a negative number for the start position will cause the search to begin start_position characters from the end of the string.

 

length

The LENGTH function returns the length of the specified string in characters. It always returns a positive number or NULL, never zero. A string variable with no value assigned to it is NULL and therefore has no length. If the string is of a fixed length, CHAR datatype LENGTH will always return the declared length of the variable. To find the length the value in  this type of variable the function RTRIM, which is described later in this paper, can be used to remove the trailing blanks.

lower  
The LOWER function will convert all the alphabetical characters in a string to lower case. It has no effect on the length of the string or on non-alphabetical characters in the string.

 

 

LOWER and its antonym, UPPER, are particularly useful for eliminating case-sensitivity problems when comparing character strings.

 

Lpad

It is sometimes necessary to format a string with leading spaces or some other character(s). LPAD returns the string passed as the first parameter padded to the total length specified in the second parameter using the character or characters specified in the third parameter. The third parameter is optional and defaults to a single blank space. If the existing length of the string is the same as the length specified LPAD will have no effect. If the length of the string is less, the string will be truncated to the specified length.

 

LTRIM
The LTRIM function is the opposite of LPAD. It removes characters from the leading portion of a string passed as the first parameter. It is most commonly used to remove leading spaces, and a single space is the default for the optional second parameter. If one or more characters are passed as a list to the second parameter, LTRIM will remove every occurrence of each character in the list (not the pattern) from the leading portion of the string, until a character not in the list is encountered.

 

REPLACE
The REPLACE function returns the string passed as the first parameter with all occurrences of the pattern of characters passed as the second parameter replaced with the pattern of characters in third, optional, parameter. This third parameter has a default of NULL.

 

RPAD
The RPAD function is similar to the LPAD function except that it adds characters to the end of a string instead of the beginning. The string of characters passed as the first parameter is returned padded to the length specified in the second parameter with the pattern of characters passed as the third, optional, parameter. The default for the third parameter is a single space. If the string to be padded is the same length as the value passed as the second parameter it will be returned unchanged. If it is longer it will be truncated to the specified length.

 

RTRIM
RTRIM is the opposite of RPAD and similar to LTRIM. The function removes characters from the right-hand portion of a string. The string passed as the first parameter is returned with all characters contained in the string passed as the second parameter removed from the right of the last character not found in the remove string. The second parameter is optional and defaults to a single space.

 
SOUNDEX
The SOUNDEX function returns a character string, which is a phonetic representation of the string passed as the parameter. SOUNDEX uses the first five consonants in the string to generate the return value. All vowels, except where the first character is a vowel, are ignored. SOUNDEX is not case sensitive, both upper and lower case letters will return the same value. A common misapprehension is that when SOUNDEX is used in a SELECT, the LIKE operator should be used. This is not the case, ‘=’ is the correct syntax.

 

SUBSTR
The SUBSTR (sub-string) is probably the most used character functions. It returns the portion of the string passed as the first parameter specified by a starting position and a length passed as the second and third parameters. The last parameter (length) is optional and, if it is omitted, the characters from the start position to the end of the string are returned. If the start position exceeds the length of the string, the return value will be NULL. If it is zero it will default to 1, and, if it is negative, SUBSTR will count backwards from the end of the string to find the start position. If the length specified is less than 1, the return value will be NULL.

 

 

 

 

TRANSLATE
The TRANSLATE function is similar to the REPLACE function ­ the difference being that while REPLACE works with sets of characters TRANSLATE replaces single characters at a time. The string passed as the first parameter is returned with each character specified in the second parameter replaced by the character in the corresponding position in the third parameter. If there is no corresponding character, a NULL will be substituted. All three parameters are mandatory; there are no default values. If a NULL is passed as the value for any parameter the return value will be NULL.

 

 

 

 

upper

The UPPER function works in exactly the same way as LOWER except that it converts all the alphabetical characters in a string to upper case.

 

Date Functions

Pl/SQL provides eight functions for the manipulation of dates. These are in addition to the simple arithmetic DATE + n which adds n days to the specified date and DATE – n, which subtracts n days. The following examples assume that the default date format is ‘DD-MON-YYYY’. (E.G. ’20-JUN-2000’).

ADD_MONTHS

The ADD_MONTHS function returns a new date with the specified number of months added to the specified date. add_months is an overloaded function so the date and the number of months can be specified in either order, but both arguments are required. If the number of months parameter is positive, add_months returns a date for that number of months into the future. If the number is negative, add_months returns a date for that number of months in the past. add_months always shifts the date by whole months. A fractional value for the number of months parameter can be passed, but add_months will always round down to the next lowest integer. If the date passed is the last day of a month the last day of the resulting month is returned. If the day number of the input date is greater than the last day of the resulting month the date of  the last day in the new month is returned.

 

LAST_DAY

The LAST_DAY function returns the date of the last day of the month for  the date passed as the parameter. This function is useful because the number of days in a month varies from month to month and, in the case of February, from year to year.

 

MONTHS_BETWEEN

The months_between function returns the number of months between two dates.  If date l and date 2 are in different months and at least one of the dates is not the last day of the month, months_between returns a fractional number. It should be noted that the fractional component is always calculated on a 31-day month basis, regardless of the actual number of days in a month, and must therefore be used with care. Differences in the time component of date l and date 2 are also taken into account. If date l comes after date 2, then a positive number is returned. If date l comes before date 2, then the number returned is negative. If the two dates are in the same month then zero will be returned. If both dates are the last day of their respective months the fractional component is ignored and a whole number will be returned.

 

NEW TIME

The new_time function takes the date and time passed as the first parameter and converts it from the tome zone passed as the second  parameter to the date and time in the time zone the third parameter. New_Time is very limited in its usefulness because it can be used only to convert to and from time in the western hemisphere and Greenwich Mean Time (now more correctly know as UTC). New_Time also does not take into account those places, such as Arizona, that do not observe daylight savings time. A further complication is that there is no international agreement for the dates on which time changes occur.

 

The valid time zones for New_Time (which are not case sensitive) are shown in Table 3.

 

Table 3: Time Zones

 

NEXT_DAY

The Next_Day function returns the next occurrence of the day of the week specified in the second parameter after the date passed as the first parameter. The name of the day can be either the full or the abbreviated name and it is not case sensitive. It must however be in the language specified by NLS_DATE_LANGUAGE for the current session. If the date passed falls on the specified day of the week the next occurrence i.e., seven days into the future will be returned.

 

ROUND

Round is an overloaded function and the argument to it can be either a date or a number. In its date version it returns a date rounded as specified by the format mask (see Table 4) in its second argument. The time component of the date is always rounded to midnight. If no format mask is specified it defaults to DAY and returns the same day if the time component is noon or earlier, the next day if it is later.

 

Table 4: Format Masks for Round and Trunc

 

TRUNC

TRUNC is also an overloaded function with a numeric equivalent. In its date version it returns a date truncated as specified by the format mask (see Figure 12) in its second argument. If no format mask is specified it defaults to DAY and returns the date with the time component set to midnight. This is particularly useful for comparing dates when the time component needs to be ignored.

 

SYSDATE

Because it takes no parameters SYSDATE is often regarded, quite reasonably, as a variable. It is however a function that returns the current date and time to the nearest second from the database.

 

Numeric Functions

The numeric functions provided by PL/SQL are the normal functions provided by most programming languages and mainly self-explanatory. They are therefore not covered here in the same detail as the character, date and conversion functions. The list of numeric functions and brief descriptions of their functionality can be found in Table 5.

 

Table 5: Numeric Functions

 

 

Conversion Functions

PL/SQL provides a number of conversion functions. The most commonly used are described here. The format models for these functions can be found in the Appendix.

 

TO_CHAR

To_Char is an overloaded function and can be used to convert both dates and numbers to variable length strings.

Date Conversion

This function has three parameters. The date, which is mandatory, a format mask and the NLS_DATE_LANGUAGE, both of which are optional. If the format mask is omitted then the format specified in NLS_LANGUAGE for the session is used. If the NLS_DATE_LANGUAGE parameter is specified then a format must also be provided.

 

Number Conversion

This version of the TO_CHAR function also has three parameters: the number to be converted (which is mandatory), a format mask, and the NLS_LANGUAGE, both of which are optional. If the format mask is omitted then the default format specified in NLS_LANGUAGE for the session is used. If the NLS_LANGUAGE parameter is specified then a format must also be provided.

 

TO_DATE

The TO_DATE function converts a number or a character string to a date datatype. This function has three parameters. The character string or number, which is mandatory, a format mask and the NLS_DATE_LANGUAGE, both of which are optional. If the format mask is omitted then the format specified in NLS_LANGUAGE for the session is used. If the NLS_DATE_LANGUAGE parameter is specified then a format must also be provided. The only number that can be used in the first parameter is a valid Julian date and in this case the J format mask must be specified. If the first parameter is a character string it must be a valid date in the format specified by the format mask.

 

TO_NUMBER

The TO_NUMBER function converts a character string to a number. This function has three parameters. The character string to be converted, which is mandatory, a format mask and an NLS_LANGUAGE parameter, both of which are optional. If the format mask is omitted then the format specified in NLS_LANGUAGE for the session is used. If the NLS_LANGUAGE parameter is specified then a format must also be provided. The NLS_LANGUAGE parameter can contain up to three values, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY and NLS_ISO_CURRENCY.

 

 

Miscellaneous Functions

The following are some additional miscellaneous functions.

GREATEST

The greatest function accepts a list of values as its input parameter and returns the greatest value in that list. There is no upper limit on the number of values can be passed GREATEST. It is an overloaded function and can accept the datatypes Date, Number and Varchar2. The return datatype is the same as the input datatype. The datatypes of all the values in the list must be compatible.

 

LEAST

LEAST is the antonym of GREATEST. It functions in exactly the same way except that it returns the least or lowest value from the list.

 

 

NVL

NVL accepts two parameters and returns the value of the second parameter if the value of the first parameter is NULL, otherwise it returns the value of the first parameter. This is particularly useful for comparing values in variables where one or both potentially has a NULL value. NVL is an overloaded function and will accept any datatype, but both parameters must of the same datatype. (The following examples assume that the variable "myvar" has a null value).

 

 

PL/SQL Control Structure

PL/SQL has a number of control structures, including:

·            Conditional controls

·             Sequential controls

·             Iterative or loop controls.

·            Exception or error controls

It is these controls, used singly or together, that allow the PL/SQL developer to direct the flow of execution through the program.

Conditional Controls

There are three levels of conditional controls or IF statements, which are similar to those found in most 3GL languages. These controls are:

·            IF....THEN....END IF

·            IF....THEN...ELSE....END IF

·            IF....THEN...ELSIF....THEN....ELSE....END IF

The statements between the conditions should always be indented for clarity and appropriate control should always be used. For instance, in the case of multiple or nested IF....THEN....END IF statements it is probably appropriate to use

IF...THEN..ELSE...END IF
or
IF....THEN...ELSIF....THEN....ELSIF….ELSE....END IF.

Multiple IF constructs may be nested and each logical level should also be indented. Again, consideration should be given as to whether the correct control is being used. Often, nested IF controls can be simplified by the use of AND or OR. Some examples are shown in Figure 1.

 

Figure 1: Conditional Controls

Sequential controls

 PL/SQL offers two sequential controls ­ GOTO and NULL.

GOTO branches unconditionally to a named label. The label name has to be enclosed in double angle brackets, as in <<my_label>>. The use of GOTO should be avoided at almost any cost. It is the start of the slippery slope that ends in unstructured spaghetti code that is difficult, if not impossible, to understand. There is almost always a better way to do something than with a GOTO.

NULL is used simply to tell PL/SQL to do nothing. It might for instance be used in an exception section to ignore a particular exception condition.

 

Iterative or look controls

There are four types of iterative controls in PL/SQL:

1.          The simple infinite loop.

2.          The WHILE loop.

3.          The numeric FOR loop.

4.          The cursor FOR loop.

It is preferable avoid using the simple loop. Rather  use a WHILE or FOR loop, which has a definite start and end point. In most cases one of these is better suited for the task and may even result in less coding. There are two methods for exiting a simple loop, EXIT and EXIT WHEN. The EXIT statement should be avoided. EXIT has to be within an IF-THEN construct and EXIT WHEN provides the same conditional logic. FOR loops should always be allowed to complete the specified iterations. EXIT and EXIT WHEN should never be used to jump out of a FOR loop. A RETURN statement must never be used to exit any loop.

If a loop has a lot of code within it or if loops are nested, labels may be used to clearly identify the beginning and end of each loop. These labels are created using double angle brackets and can then be used in the END and EXIT WHEN statements of the loop, as shown in Code Example   .

<<outer_loop>>
LOOP
  <<inner_loop>>

 LOOP

    my_var := Do_Something;

    EXIT inner_loop WHEN my_var = TRUE;

 END inner_loop;

  my_var2 := Do_Something_Different

  EXIT outer_loop WHEN my_var2 = TRUE;

Exit outer_loop;

 

Exception or error controls

One of the things that PL/SQL has inherited from ADA is very powerful exception or error handling. There are four types of exception:

·            Named system exceptions

·            Named developer-defined exceptions.

·            Unnamed system exceptions.

·            Unnamed developer-defined exceptions (raise application error)

Excluding functions that are to be embedded in SQL, as mentioned earlier, PL/SQL modules should normally contain one or more exception handling sections. PL/SQL makes no distinction between system exceptions and developer-defined exceptions and they are handled in the same way by the exception handling section.

When declaring named developer-defined exceptions, care should be taken not to use the same name as a named system exception. The code will compile without error, but, when a run-time error occurs, the named system exception will take precedence. There should always be a WHEN OTHERS clause at the highest level of exception handling.

Conclusion

PL/SQL is a powerful and complex programming language. This paper provides only a beginning to understanding and mastering its intricacies. There is only so much that can be covered in the limited time and space available. The use of cursors to interact with the database, using packages to improve performance and efficiency, using PL/SQL tables and other collections are just a small part of what remains for the reader to learn.

References

The PL/SQL Users Guide and Reference, ©1997, Oracle Corporation.
Oracle PL/SQL Programming by Steven Feuerstein, ©1995 and 1997, O’Reilly and Associates.
Getting the Best from PL/SQL by John C. Lennon, ©1999 and 2000, Oracle Update, Xephon plc.

About The Author

John Lennon is a Project Manager with Dulcian, Inc. (http://www.dulcian.com/).  Originally from England; John resided for some years in South Africa before moving to the United States in 1992. He has many years of experience with applications development, primarily in engineering and utility environments. John has been working with Oracle products for over a decade. He has presented at user group meetings, international and regional Oracle users’ conferences, including previous ODTUG conferences, and has published articles in Oracle technical journals in the United States and Great Britain.

 

John C. Lennon
e-mail: jlennon@dulcian.com   
website: http://u1.lvcm.com/johnlennon

APPENDIX

 

Date Format Models

Format Mask	Description
SCC or CC	The century. If the SCC format is used BC dates are prefaced with a negative sign.
SYYYY or YYYY	The four-digit year. If the SYYYY format is used  BC dates are prefaced with a negative sign.
IYYY	The four-digit ISO standard year.
YYY or YY or Y	The last three, two, or one digits of the year.
IYY or IY or I	The last three, two, or one digits of the ISO year.
Y.YYY	The four-digit year with a comma.
YEAR or SYEAR Year or SYear	The year spelled out in uppercase or title case. The S prefix places a negative sign in front of B.C. dates. The language is always English.
BC or AD	The BC or AD indicator, without periods.
B.C. or A.D.	The BC or AD indicator, with periods.
Q	The quarter of the year from 1 through 4.
MM	The number of the month in the year, from 01 through 12.
RM	The Roman numeral representation of the month number.
MONTH or Month	The name of the month in uppercase or title case in the language set by NLS_LANGUAGE
MON or Mon	The abbreviated name of the month in uppercase or title case in the language set by NLS_LANGUAGE
WW	The week in the year, from 1 through 53.
IW	The ISO week in the year, from 1 through 52 or 53.
W	The week in the month, from 1 through 5.
DDD	The day in the year, from 1 through 366.
DD	The day in the month, from 1 through 31.
D	The day in the week, from 1 through 7. The starting day is determined by NLS_TERRITORY.
DAY or Day	The name of the day in uppercase or title case format.
DY	The 3 character abbreviated name of the day in the language set by NLS_LANGUAGE.
J	The Julian day of the date; i.e., the number of days since 1 January  4712 BC
AM or PM	The meridian indicator without periods.
A.M. or P.M.	The meridian indicator with periods.
HH or HH12	The hour in the day, from 1 through 12.
HH24	The hour in the day, from 0 through 23.
MI	The minutes component of the date/time, from 0 through 59.
SS	The seconds component of the date/time, from 0 through 59.
sssss	The number of seconds since midnight of the time component.
TH	Suffix that converts a number to its ordinal format. The language is always English.
SP	Suffix that converts a number to its spelled format. The language is always English.
SPTH	Suffix that converts a number to its spelled and ordinal format. The language is always English.
Other text	Any text in quotes will be reproduced in the formatted output of the conversion.

 

APPENDIX (Continued)

 

Number Format Models