Database Tutorial

You can use anonymous block programs in scripts or nested inside other program units. They have scope only in the context of the program unit or script where you put them. You can’t call anonymous blocks from other blocks by name, because they haven’t got a name. All variables are passed to these local blocks by reference, which is the default in PL/SQL’s scope model. This means an anonymous block program unit can reference any variables in the external or calling scope. The single exception to this rule occurs when you declare a local variable that replaces a calling scope variable.

You can store named block programs directly as functions or procedures in a schema. These functions and procedures are schema-level named programs. Their scope is the most flexible for three reasons: You can call them from the schema where they’re defined. Alternatively, you can call them from another schema that’s been granted the execute privilege on the named program unit. Lastly, you can call schema-level programs across a DB_LINK. DB_LINKs support calls between two database instances across the network. Schema-level named programs present an interesting comparative paradigm, because they act as public access methods in the scope of the schema but protected access methods in the scope of the database.

Beyond schema level functions and procedures, you can create packages, which consist of a package specification and a body. These are containers for groups of related functions and procedures. You can store named block programs inside packages in two ways: One way requires that you publish the package function or procedure. You do this by defining the function or procedure specification inside the package specification. This makes package functions and procedures callable from other programs in the same schema or from other programs in schema where other users have the execute privilege on the package. These are package functions and procedures. They’re closest to static methods with a protected access scope. You can also store functions and procedures exclusively in package bodies. This limits them to internal package access only, and you call these package-level programs units. They’re most like private access methods in object-oriented languages such as Java.

You can store named block programs inside user-defined types (UDTs). Like named block programs inside packages, you can deploy these as published or unpublished methods. You can also make these static or instance methods. Static methods are available without an existing instance, while instance methods require that you first create an instance of the UDT in memory. Static methods act like package functions and procedures, while instance methods act like object type functions and procedures.

Finally, you can store named block programs inside the declaration block of anonymous or named block programs. These named block programs are known as local named block programs. You can call them only from within the scope of their host program. They can’t see other locally named programs unless these other programs are declared before the local programs in the same runtime scope. You can fix this by adopting forward references before declaring local functions. A forward reference is a stub for a named block. The stub includes the subroutine name, parameter list, and any return type. Local named blocks are like package-level program units or private access methods in object-oriented languages such as Java.

a := b + c;

VARIABLE variable_name datatype_name

SQL> VARIABLE my_string VARCHAR2(30)

BEGIN
  :my_string := 'A string literal.';
END;
/

SELECT :my_string FROM dual;

BEGIN
  dbms_output.put_line(:my_string);
END;
/

a := '&string_in';

schema_name.table_name
package_name.function_name
object_name.member_method_name
cursor_name.cursor_attribute

a := 'Glued'||' '||'together.';

a := 'A string literal.';

-- This is a single line comment.

/* This is line one.
   This is line two. */

CREATE TABLE "Demo"
("Demo_ID" NUMBER
, demo_value VARCHAR2(10));

INSERT INTO "Demo1" VALUES
(1,'One Line ONLY.');

BEGIN
FOR i IN (SELECT "Demo_ID", demo_id FROM "Demo") LOOP
dbms_output.put_line(i."Demo_ID");
    dbms_output.put_line(i.demo_id);
  END LOOP;
END;
/

a := 3**3;

a := 8**(1/3);

Anonymous Blocks

Here is the basic prototype for an anonymous block PL/SQL program:

[DECLARE]
  declaration_statements
BEGIN
  execution_statements
[EXCEPTION
  exception_statements]
END; /

The DECLARE statement starts the optional declaration block. The BEGIN statement ends any declaration block and begins the execution block. The optional EXCEPTION statement may end the execution block; the END statement ends either the execution or optional exception block.

Prototypes are great, but they don’t show you how to implement the code. You’ll now see how to write a simple Hello World anonymous block program in PL/SQL. You can run the command from a file, the SQL*Plus command line, SQL*Developer, or a commercial tool developed for Oracle 11g, such as Quest’s Toad.

Most programmers use tools, but the command line is the closest environment to what you’ll embed in C#, C++, Java, or PHP programs. Anonymous PL/SQL blocks can be embedded in your application code just like SQL statements. Throughout this book, we use display code and code interactions at the command line.

Static Anonymous Block

Two small details can cause you grief in PL/SQL. The first is that as a strongly blocked language, it requires you to include at least a single statement in each and every block. The second detail is that you’ll need to manage output from your PL/SQL programs by enabling a SQL*Plus formatting environment variable SERVEROUTPUT.

For example, the following might look like a complete program, but it isn’t. It doesn’t have at least one statement in the block to make it work:

SQL> BEGIN
  2  END;
  3  /

You might wonder why a forward slash (/) is included on the line below the program unit since there’s a semicolon after the END. The semicolon terminates the anonymous block and a forward slash executes the program by sending it to the PL/SQL runtime engine. This program fails with the following error, which basically says it got to the end of the block without finding a single statement:

END;
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe

The minimal programming unit for an anonymous block includes one statement in the execution block. Conveniently, PL/SQL provides a null statement. You can use null statements in blocks to make sure you have basic block control logic correct before you write detailed block logic. Here’s the smallest working anonymous block program.

SQL> BEGIN
  2    NULL;
  3  END;
  4  /

You’ll want to remember this, because if you don’t, it can cost you many fruitless hours of debugging.

You didn’t need the SQL*Plus environment variable SERVEROUTPUT in this basic block test, because nothing was being output from the program. The Hello World program requires that you output a line of text, and that means you must enable SERVEROUTPUT before running your PL/SQL block.

If you “fat finger” something while typing a SQL or PL/SQL statement, you can start over by pressing three keys: enter for a line return, a period, and then enter again to add another line return to abort your active statement.

The Hello World program prints text by calling a stored procedure from a standard package in the Oracle database. The DBMS_OUTPUT.PUT_LINE call is more or less similar to echo in scripting languages or the System.out.println() static method call in Java. Moreover, this is PL/SQL’s way of sending messages to standard out (STDOUT). Standard out is the output stream of a programming envirnonment and it typically prints text to the console (monitor). You can also redirect standard out to send text to a file.

The process of running a Hello World program looks like this:

SQL> SET SERVEROUTPUT ON SIZE UNLIMITED
SQL> BEGIN
  2    dbms_output.put_line('Hello World.');
  3  END;
  4  /

It prints this:

Hello World.

Reserved words and keywords in Oracle are case-insensitive. We’ve chosen to follow the most common use case for capitalization. This should more-or-less mimic what you’d find in any Generic Syntax Highlighter (GeSHi) editor. String literals in PL/SQL are enclosed by single quotes (apostrophes), as they are in SQL. String literals between the quotes are case-sensitive, and you back-quote a single quote with another single quote.

This section demonstrates how to handle output, but that’s it. The Hello Somebody program in the next section shows you how to handle both input and output from an anonymous program.

Dynamic Anonymous Block

The Hello Somebody program prints text by calling the same stored procedure from the Hello World program. This section focuses on how input parameters work in PL/SQL anonymous block program units. Input parameters are unknowns before they arrive at any program. This is a crucial fact.

You need to plan for both good and bad input parameters. This can be tricky, because a declaration block acts like a header file does in a C or C++ program. Your exception block can’t capture runtime errors in the declaration block. The program simply fails with an unhandled exception. In this section, you’ll learn how to avoid this forever by always making assignments in the execution block. The trick revolves around your understanding what it means to define a variable versus declare a variable.

You define a variable by assigning it a name and data type. You declare a variable by defining it and assigning it a value. In some programming languages, assignment is called initialization. The rule of thumb on whether you call it one or the other depends on the data type. You typically assign values to scalar variables. Scalar variables hold the value of only one thing and are primitives in Java. On the other hand, you initialize object types. The key object types in Oracle are collections and user-defined object types. Object types are also known as composite data types because they can contain multiple things.

You assign input parameters to anonymous block programs by using substitution variables. SQL*Plus supports the use of substitution variables, which are prefaced by an ampersand (&), in the interactive and batch console. Substitution variables are variable-length strings or numbers. You should never assign dynamic values in the declaration block.

The following program defines a variable, assigns it a value, and prints it:

SQL> DECLARE
  2    lv_whom VARCHAR2(30);
  3  BEGIN
  4    lv_whom := '&input';
  5    dbms_output.put_line('Hello '|| lv_whom ||'.');
  6  END;
  7  /

You might not notice the single quotes around the substitution variable, but they’re critical. When the program is run without a valid string value and no quotes, the engine parses over the null value and excludes the right operand in an assignment. The program would throw a PLS-00103 exception because there is no right operand, whereas the engine interprets two single quotes without anything between them as a null string (at least in Oracle this is an implicit assignment of a null value to a string data type).

Notice that no example here includes the assignment being made in the declaration block. That would be bad coding practice. By extension, you should avoid assigning actual parameters to local variables in their declaration blocks. Otherwise, your code might fail at runtime because in functions and procedures formal parameters have no size constraints in PL/SQL.

Although constants aren’t really too useful in anonymous block programs, this is a great place to show you the syntax. The CONSTANT reserved word is placed between the variable name and the data type. You must declare a constant in your declaration block, which means it is both defined and assigned an immutable value.

The preceding anonymous block is recycled to include a constant on line 2, as follows:

SQL> DECLARE
  2    lv_hello CONSTANT VARCHAR2(5) := 'Hello';
  3    lv_whom VARCHAR2(30);
  4  BEGIN
  5    lv_whom := '&input';
  6    dbms_output.put_line(lv_hello ||' '|| lv_whom ||'.');
  7  END;
  8  /

An alternative method for processing interactive session-level variables involves what Oracle calls bind variables. You preface bind variables with a colon (:) inside a PL/SQL block. You can define bind variables in the scope of a SQL*Plus session. In that scope, only the CHAR, CLOB, NCHAR, NCLOB, NUMBER, NVARCHAR2, REFCURSOR, and VARCHAR2 data types are available. The term “bind variable” also applies to the handling of placeholders in Native Dynamic SQL (NDS) and for read-write hooks into the database Private Global Area (PGA). You also use read-write hooks to manage large objects (LOBs) and read hooks to read reference cursors.

You can create SQL*Plus session variables by using the VARIABLE keyword. Like the example with substitution variables, you should never assign values to bind variables in the declaration block.

The following program defines a session-level bind variable The colon doesn’t precede the definition of the variable in the session. You use the colon only inside the PL/SQL program scope. This allows the PL/SQL runtime engine to reach out and access the variable in the SQL*Plus session scope. You use a PL/SQL block to assign a value to the bind variable. Then you can access the bind variable from any subsequent program for the duration of the connected session.

SQL> VARIABLE bv VARCHAR2(30)
SQL> BEGIN
  2    :bv := 'Sam';
  3  END;
  4  /

After assigning a value to the session-level bind variable, you can use it as a right operand:

SQL> DECLARE
  2    lv_whom VARCHAR2(30);
  3  BEGIN
  4    lv_whom := :bv;
  5    dbms_output.put_line('Hello '|| lv_whom ||'.');
  6  END;
  7  /

This prints the following:

Hello Sam.

You could put any of these anonymous block programs in a file and run them from the SQL*Plus command line.

Nested Blocks

You can put unnamed blocks inside other blocks. These nested blocks can’t have names. You could rewrite the preceding example with a nested block like this:

SQL> DECLARE
  2    -- Declare local variables.
  3    lv_salutation VARCHAR2(5) := 'Hello';
  4    lv_whom VARCHAR2(30);
  5  BEGIN
  6    -- Assign the external bind variable value.
  7    lv_whom := :bv;
  8    -- Nested anonymous block.
  9    DECLARE
 10      /* Declare nested local variable, overwriting external
 11         local variable in the process. */
 12      lv_salutation VARCHAR2(7) := 'Goodbye';
 13    BEGIN
 14      -- Print message to standard out.
 15      dbms_output.put_line(lv_salutation ||' '|| lv_whom ||'.');
 16    END;
 17    -- Print message to standard out.
 18    dbms_output.put_line(lv_salutation ||' '|| lv_whom ||'.');
 19  END;
 20  /

Notice that lv_salutation is declared in both the outer and inner anonymous blocks. The physical size of the outer block lv_salutation variable is five characters long, and the value is “Hello”, whereas the inner block uses a physical size of seven characters and a value of “Goodbye”. The second declaration replaces both the size and value, which lasts for the duration of the nested block. You can see this because both messages are printed to standard out with the dbms_output.put_line procedure.
Here’s the output:

Goodbye Sam.
Hello Sam.

Although the two local variables share the same name, they share different physical sizes and values. This example demonstrates scope and the ability of subordinate blocks to override existing variable data types and contents.

You can also nest named blocks inside other named blocks or anonymous blocks. I’ve included this later in the formal discussion of functions and procedures to illustrate the nesting of local functions and procedures in anonymous blocks. The forward reference to hector in the jack procedure lets you see the concept of forward-referencing stubs.

The problem with nested named blocks, however, is they’re not published blocks. This means that one might call another before the one being called is defined. This type of design problem is known as a scope error. The scope of the called program is unknown until after the call is made. It raises a PLS-00313 exception and results in a compile-time error.

SQL> DECLARE
  2    PROCEDURE jack IS
  3    BEGIN
  4      dbms_output.put_line(hector||' World!');
  5    END jack;
  6    FUNCTION hector RETURN VARCHAR2 IS
  7    BEGIN
  8      RETURN 'Hello';
  9    END hector;
 10  BEGIN
 11    jack;
 12  END;
 13  /

Lines 2 through 5 define a local procedure, jack. Inside procedure jack is a call on line 4 to the function hector. The function isn’t defined at this point in the anonymous block, and it raises an out-of-scope error:

    dbms_output.put_line(hector||' World!');
                         *
ERROR at line 4:
ORA-06550: line 4, column 26:
PLS-00313: 'B' not declared in this scope
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored

This is a compile-time error, because all anonymous block programs are parsed before they’re executed. Parsing is a compile-time process. Parsing recognizes identifiers, which are reserved words, predefined identifiers, quoted identifiers, user-defined variables, subroutines, or UDTs. Named blocks are identifiers. Function hector isn’t recognized as an identifier, because PL/SQL reads identifiers into memory from top to bottom. This is a single-pass parsing process. Under a single-pass parser, function hector isn’t defined before it’s called in procedure jack.

You can fix this by using forward references. A forward reference to a function or procedure requires only the signature of the function or procedure rather than the signature and implementation. Forward references are equivalent to the concept of an interface in Java. These prototypes are stubs in PL/SQL. The stub lets the compilation accept the identifier name of a named block before you implement the block.

The following provides forward references for all local functions and procedures. I recommend that you always provide these stubs in your programs when you implement local scope named blocks.

SQL> DECLARE
  2    PROCEDURE jack;
  3    FUNCTION hector RETURN VARCHAR2;
  4    PROCEDURE jack IS
  5    BEGIN
  6      dbms_output.put_line(b||' World!');
  7    END jack;
  8    FUNCTION hector RETURN VARCHAR2 IS
  9    BEGIN
 10      RETURN 'Hello';
 11    END hector;
 12  BEGIN
 13    jack;
 14  END;
 15  /

Lines 2 and 3 provide the stubs to procedure jack and function hector. This program passes the compile time validation because it’s able to resolve all symbols from top to bottom of the anonymous block. Nested blocks are very useful, but you need to use them correctly.

The biggest risk of locally named PL/SQL blocks is that they replace schema-level named functions and procedures. To avoid this, you should consider a naming convention (like an lv_ preface) for locally named blocks that ensures they won’t replace schema-level names in your stored programs.

Functions and procedures can accept parameters of scalar, composite, and collection data types. Scalar data types are SQL-based data types, and you can use them in SQL and PL/SQL contexts.

Composite data types can be defined as PL/SQL record types or as SQL object types, and varray and nested table collections can use either. Associative arrays can use only SQL scalar data types and PL/SQL record data types.

The following table shows you how you can use these composite data types as parameters in functions or procedures, and when and where you can use them as return data types in functions. The most notable issue raised by this distinction is that PL/SQL has two different deployment strategies: One involves writing programs to support other PL/SQL programs, and the other involves writing programs to support SQL.

The following sections address function and procedure architecture and development.

Function Architecture

Before you see the details of how you implement and use functions, you should first understand what a function is and how it works. Functions are black boxes, production facilities in which you add raw things and take processed things out.

The two major function architectures, pass-by-value and pass-by-reference models, are used in different ways. You choose the former when you want a standalone behavior and the latter when functions act as subroutines inside the transaction scope of another program unit.

Pass-by-Value Functions

A pass-by-value function receives values when they’re called. The functions returns a single thing upon completion. The tricky parts with this type of function are the data types of the inputs and outputs. Inputs are formal parameters and have only one mode in pass-by-value programs, and that’s an IN-only mode. An IN-only mode means that you send a copy of either a variable value or a literal value into the function as a raw input. These copies are actual parameters or call parameters. All raw materials (call parameters) are consumed during the production of the finished goods—or the return value of this type of function. The return type value of the function must be assigned to a variable in a PL/SQL block, but it can also be returned as an expression in a SQL query.

The following illustration depicts how a pass-by-value function works. What’s hidden in this context? The hidden element of a stored program in an Oracle database is the back door that lets a function transact against the database. This means a function’s black box can contain an INSERT, UPDATE, or DELETE statement. Actually, it can contain a set of statements. The collection of statements can collectively be a transaction. This back door to a transactional database is available only when you use the function in an exclusively PL/SQL scope.

The following shows you how to write a pass-by-value function that is deterministic, which means it doesn’t have a dependence on any data in the database:

SQL> CREATE OR REPLACE FUNCTION pv
  2  ( future_value    NUMBER
  3  , periods         NUMBER
  4  , interest NUMBER )
  5  RETURN NUMBER DETERMINISTIC IS
  6  BEGIN
  7    RETURN future_value / ((1 + interest/100)**periods);
  8  END pv;
  9  /

Pass-by-Reference Functions

When you call a pass-by-reference function, you send one or more references to local variables as actual parameters. Formal parameters, therefore, can have any one of three possible modes: IN, OUT, and IN OUT.

The following list describes the three modes of subroutine parameters:

• IN The IN mode, the default mode, means you send a copy as the actual parameter. Any formal parameter defined without an explicit mode of operation is implicitly an IN– only mode parameter. It means a formal parameter is read-only. When you set a formal parameter as read-only, you can’t alter it during the execution of the subroutine. You can assign a default value to a parameter, making the parameter optional. You use the IN mode for all formal parameters when you want to define a pass-by-value subroutine.
• OUT The OUT mode means you send a reference, but a null as an initial value. An OUT mode formal parameter is write-only. When you set a formal parameter as write-only, no initial physical size is allocated to the variable. You allocate the physical size and value inside your subroutine. You can’t assign a default value, which would make an OUT mode formal parameter optional. If you attempt that, you raise a PLS-00230 error. The error says that an OUT or IN OUT mode variable cannot have a default value. Likewise, you cannot pass a literal as an actual parameter to an OUT mode variable, because that would block writing the output variable. If you attempt to send a literal, you’ll raise an ORA-06577 error with a call from SQL*Plus, and a PLS-00363 error inside a PL/SQL block. The SQL*Plus error message states the output parameter is not a bind variable, which is a SQL*Plus session variable. The PL/SQL error tells you that the expression (or, more clearly, literal) cannot be an assignment target. You use an OUT mode with one or more formal parameters when you want a write-only pass-by-reference subroutine.
• IN OUT The IN OUT mode means you send a reference and starting value. A formal parameter is read-write. When you set a formal parameter as read-write, the physical size of the actual parameter is provided. While you can change the contents of the variable inside the subroutine, you can’t change or exceed the actual parameter’s allocated size. The IN OUT mode restrictions on default values and literal values mirror those of the OUT mode.

Although you can call a pass-by-reference function by using session-level variables, that’s really not the functions’ role. Pass-by-reference functions belong as components in the scope of either pass-by-value functions or stored procedures. The next illustration shows the generalized format of pass-by-reference functions.

Interestingly, raw materials (call parameters) aren’t fully consumed in pass-by-reference functions, as they are in pass-by-value functions. While IN-only mode parameters are fully consumed, IN OUT mode variables are returned generally in an altered state. OUT mode variables are the result of some processing inside the black box that you opt not to return through the function’s formal return type.

Pass-by-reference functions can exhibit many of the behaviors we’ve worked through earlier in the chapter. As discussed, they can have IN, IN OUT, or OUT mode parameters. An IN mode parameter passes in a value that can change but is consumed wholly. An IN OUT mode parameter passes in a reference and value, and the value can change before it is returned to the calling program. An OUT mode parameter passes in nothing but can return something.

A simple example of this concept is the following program. It takes only one input parameter and one input and output parameter. The input and output parameter increments each time you call the program.

SQL> CREATE OR REPLACE FUNCTION adding
  2  ( a IN     NUMBER
  3  , b IN OUT NUMBER ) RETURN NUMBER IS
  4  BEGIN
  5    b := b + 1;
  6  RETURN a + b;
  7  END;
  8  /

We’ll use bind variables to keep this as simple as possible to illustrate the approach. You have to define them in the session, and then assign values to bind variables inside a PL/SQL block, like this:

SQL> VARIABLE one NUMBER
SQL> VARIABLE two NUMBER
SQL> BEGIN
  2    :one := 1;
  3    :two := 0;
  4  END;
  5  /

We’ll also need an output variable, like this one:

SQL> VARIABLE output NUMBER

Now we can call the pass-by-reference function with the bind variables:

SQL> VARIABLE output NUMBER
SQL> CALL adding(:one,:two) INTO :output;
SQL> CALL adding(:one,:two) INTO :output;

We can then query the two bind variables, like this:

SQL> SELECT :one, :two FROM dual;

The query prints this:

      :ONE       :TWO
---------- ----------
         1          2

After two calls, the input-only variable is the same, but the input and output variable has grown by two. That would continue as long as we call it. The most useful way to use pass-by-reference functions is when you want to ensure that something happens and returns the changed values.

Procedure Architecture

A procedure is essentially a function with a void return type. As such, you can’t use it as a right operand because it doesn’t have a return value. Procedures, like functions, are black boxes.

Procedures provide a named subroutine that you call within the scope of a PL/SQL block. Although the behavior differs slightly whether you pass call parameters by value or reference, the inputs and outcomes are the only way to exchange values between the calling block and the procedure.

The nature of the call parameters provides you with two procedure architectures. They are a pass-by-value or pass-by-reference model. A pass-by-value model accepts values to perform a task, while a pass-by-value model accepts values or references to perform a task. Reference variable values can change inside a procedure like this, and can return altered values to the external variable references. You chose a pass-by-value model when you want a delegation behavior, and a pass-by-reference model when you want a shared or cooperative processing behavior.

In a delegation behavior, the inputs are consumed by the subroutine and nothing is returned to the calling scope. Shared or cooperative processing means that a subroutine performs an operation on one or more calling scope variables. Cooperative processing doesn’t consume all inputs but refines some of them. It acts like an oil refinery that processes crude oil, additives, and chemicals into fuel.

Pass-by-Value Procedures

A pass-by-value procedure receives values when they’re called. They return nothing tangible to the calling scope block, but they can interact with the database. Pass-by-value procedures implement a delegation model. Procedures are often used to group and control a series of DML statements in the scope of a single transaction.

The mode of all formal parameters is IN-only for pass-by-value procedures. This means they receive a copy of an external variable or a numeric or string literal when you call the procedure. Call parameters can’t be changed during the execution of a subroutine. You can transfer the contents from a call parameter to a local variable inside the procedure and then update that the local variable.

The following illustration depicts how a pass-by-value procedure works. What’s hidden in this context? The hidden element of any stored program is that it can change data in the database. This means a procedure’s black box can contain an INSERT, UPDATE, or DELETE statement. As mentioned, a procedure often contains one or more DML statements. These procedures frequently define a single transaction, which means all or nothing occurs.

A basic pass-by-value procedure simply takes a call parameter and performs some action with it. That action consumes the copy, because at the conclusion of the procedure the values no longer exist. It is possible that they were printed or that they were inserted or updated into database tables. They might also have simply filtered INSERT, UPDATE, or DELETE statements.

Here’s a small example pass-by-value program that works in the same transaction scope as the calling program. It takes only one parameter, which uses the default IN-only mode.

SQL> CREATE OR REPLACE PROCEDURE print_hello
  2  ( pv_whom VARCHAR2 ) IS
  3  BEGIN
  4    dbms_output.put_line('Hello '||pv_whom||'!');
  5  END;
  6  /

We can see the output by setting a SQL*Plus environment variable and printing it:

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> EXECUTE print_hello('there');
Hello there!

This section has shown you how to create a pass-by-value procedure.

Pass-by-Reference Procedures

You send one or more references to local variables as actual parameters when you call a pass-by-reference function. Therefore, formal parameters can have an IN (the default), IN OUT, or OUT mode. These modes were described earlier in the chapter.

Although you can call a pass-by-reference function by using session-level variables, that’s really not their role. Pass-by-reference functions belong as components inside other anonymous or named blocks. The next illustration shows you the generalized format of pass-by-reference functions.

IN OUT call parameters aren’t fully consumed by pass-by-reference procedures. Although IN-only mode parameters are fully consumed, OUT mode variables don’t have a value to consume. IN OUT mode variables are designed to submit a reference with a value and receive a replacement value at the conclusion of a subroutine, like a procedure.

OUT mode variables are the result of some processing inside the black box. They are generally derived by some algorithm that uses other call parameters and constant values inside the procedure. Sometimes OUT mode variables perform similar roles to a function’s formal return type.

The following demonstrates a pass-by-reference procedure that mimics the behavior of the preceding nested anonymous block program:

SQL> CREATE OR REPLACE PROCEDURE change_string
  2  ( pv_string IN OUT VARCHAR2 ) IS
  3  BEGIN
  4    pv_string := 'We''re inside the procedure.';
  5  END;
  6  /

The procedure has only one formal parameter and its mode is IN OUT, which means it is passed by reference. Line 4 in the stored procedure performs the same feature as line 5 in the nested anonymous block.

After you declare a local SQL*Plus bind variable, you can call the stored procedure as follows:

SQL> VARIABLE no_real_value VARCHAR2(50)
SQL> EXECUTE change_string(:no_real_value);

A scalar variable contains one and only one thing. In Java, primitives are scalar variables. Characters, integers, and various number data types are scalar variables in most programming languages. Strings are also scalar variables in the context of relational databases.

A composite variable contains more than one scalar or other composite variable in some type of data structure. Structures can be arrays, reference cursors, and user-defined types such as arrays, records, or objects.

Some data types are unconstrained, but others are constrained. Constrained data types derive specialized behavior from their generalized or unconstrained data type. For example, a user-defined data type of positive integers is a specialization of an integer data type. An unconstrained data type doesn’t place artificial limits on the range of a data type.

The program examples presented here demonstrate the assignment of string or numeric literal values to three base data types. Date, timestamp, or interval data types use the TO_CHAR or CAST built-in function to convert string literal values into valid dates or timestamps. Likewise, you’ll need to convert them back to strings to print them with the print_line procedure of the dbms_output package. After we show you how to work with the base data types, we’ll show you how to work with composite data types.

PL/SQL lets you explicitly or implicitly define data types. Implicit definitions rely on anchoring them to a table or column definition. You anchor data types to catalog objects with pseudo columns or a table. A %TYPE is a pseudo column that lets you anchor a data type to the definition of a column in a table.

Alternatively, a %ROWTYPE lets you anchor a record type to the definition of a table or view in the database catalog, or to a CURSOR structure in your PL/SQL block.

Text Data Types

There are several text data types. You probably use variable-length strings more frequently than the others, because they meet most needs. You can put 4000-byte text into the VARCHAR, VARCHAR2, and NVARCHAR2 data types in SQL, or dependent on the configuration of the database 32,767 bytes in the same data types. However, you should put larger text entries in the CLOB data type.

You have an alternative to variable-length data types in the CHAR, NCHAR, and CHARACTER data types. You use them when you want to allocate a fixed-size string. In most cases, you forfeit space for little if any tangible benefit. A perfect use case for a CHAR data type is a column that contains the two-character codes for U.S. states, because it won’t allocate space unnecessarily.

You assign literal values to variable or fixed-length data types the same way. In fact, you make assignments to a CLOB the same way when the string could really fit in an ordinary text data type. Entries longer than 32,767 bytes require the DBMS_LOB package.

This sample program shows you the assignment and subsequent space allocation for both variable and fixed-length data types:

SQL> DECLARE
  2    lv_fixed     CHAR(40)    := 'Something not quite long.';
  3    lv_variable  VARCHAR(40) := 'Something not quite long.';
  4    lv_clob      CLOB        := 'Something not quite long.';
  5  BEGIN
  6    dbms_output.put_line('Fixed Length   ['||LENGTH(lv_fixed)||']');
  7    dbms_output.put_line('Varying Length ['||LENGTH(lv_variable)||']');
  8    dbms_output.put_line('CLOB Length ['||LENGTH(lv_clob)||']');
  9  END;
 10  /

This program prints the space allocation sizes:

Fixed Length   [40]
Varying Length [25]
CLOB Length    [25]

The LONG and LONG RAW data types are provided only for backward compatibility. You should not use them. CLOB data types are the replacements for the soon to be deprecated LONG and LONG RAW data types.

Date Data Types

Dates are always complex in programming languages. The DATE data type is the base type for dates, times, and intervals. The following discussion shows you how to use dates. As previously discussed, Oracle has two default date masks, and they support implicit casting to DATE data types. They are a two-digit day, three-character month, two-digit year (DD-MON-RR); and a two- digit day, three-character month, four-digit year (DD-MON-YYYY). Any other string literal requires an overriding format mask with the TO_DATE built-in SQL function.

The next example shows you how to assign variables with implicit and explicit casting from conforming and nonconforming strings. Nonconforming strings rely on formatting masks.

SQL> DECLARE
  2    lv_date_1  DATE  := '28-APR-75';
  3    lv_date_2  DATE  := '29-APR-1975';
  4    lv_date_3  DATE  := TO_DATE('19750430','YYYYMMDD');
  5  BEGIN
  6    dbms_output.put_line('Implicit ['||lv_date_1||']');
  7    dbms_output.put_line('Implicit ['||lv_date_2||']');
  8    dbms_output.put_line('Explicit ['||lv_date_3||']');
  9  END;
 10  /

This program prints the following:

Implicit [28-APR-75]
Implicit [29-APR-75]
Explicit [30-APR-75]

When you want to see the four-digit year, you use the TO_CHAR built-in function with the appropriate format mask. Dates work differently in Oracle than they do in MySQL, but they work in PL/SQL as they do in SQL. Check back to the full discussion in Chapter 11. You can add a day simply by adding an integer to a date variable, as shown in the following program:

SQL> DECLARE
  2    lv_date  DATE  := '12-MAY-1975';
  3  BEGIN
  4    lv_date := lv_date + 3;
  5    dbms_output.put_line('Date ['||lv_date||']');
  6  END;
  7  /

This prints a date three days after the original date:

15-May-75

You can also work with parts of a day, because dates are really scalar numbers. The integer value sets the date and any fractional value sets the hours, minutes, and seconds. You use the TRUNC built-in function to round down a date to the base date or integer value. This is important
when you want to perform interval calculations about the number of elapsed days.

SQL> DECLARE
  2    lv_date_1  DATE  := SYSDATE;
  3    lv_date_2  DATE  := lv_date_1;
  4  BEGIN
  5    dbms_output.put_line(TO_CHAR(lv_date_1,'DD-MON-YY HH24:MI:SS'));
  6    dbms_output.put_line(TO_CHAR(TRUNC(lv_date_2),'DD-MON-YY HH24:MI:SS'));
  7  END;
  8  /

This example prints this:

30-APR-09 00:04:13
30-APR-09 00:00:00

As you can see from the results, the TRUNC built-in function reduces the scalar date to a whole integer. With the TRUNC command, you are able to calculate the number of days between two dates.

Numeric Data Types

Numbers are straightforward in PL/SQL. You assign integer and complex numbers the same way to all but the new IEEE 754-format data types.

The basic number data type is NUMBER. You can define a variable as an unconstrained or constrained NUMBER data type by qualifying the precision or scale. Precision constraints prevent the assignment of larger precision numbers to target variables. Scale limitations shave off part of the decimal value but allow assignment with a loss of value.

The following example demonstrates what happens when you assign a larger precision NUMBER data type value to a variable with a smaller precision. The first number between the opening parenthesis and comma defines the precision, or total number of digits, to the left and right of the decimal point. The second number between the comma and the closing parenthesis defines the scale, or total number of digits, to the right of the decimal point.

SQL> DECLARE
  2    lv_number1 NUMBER(6,2);
  3    lv_number2 NUMBER(15,2) := 21533.22;
  4  BEGIN
  5    lv_number1 := lv_number2;
  6    dbms_output.put_line(lv_number1);
  7  END;
  8  /

The assignment on line 5 throws the following exception:

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 5

The error is thrown because the physical digits of the NUMBER(6,2) data type can’t hold all the digits from the source variable. To eliminate the error, you need to change the precision value from 6 to 7. That allows the entire number to fit in the data type.

The next example leaves the precision at 6 but changes the decimal scale to 1. As mentioned, this change lets the assignment work. Unfortunately, you lose the precision of the hundredth decimal value by shaving it off.

SQL> DECLARE
  2    lv_number1 NUMBER(6,1);
  3    lv_number2 NUMBER(15,2) := 21533.22;
  4  BEGIN
  5    lv_number1 := lv_number2;
  6    dbms_output.put_line(lv_number1);
  7  END;
  8  /

Here’s how the value in lv_number1 after the assignment prints: 21533.2
You lose the entire decimal value when you assign a NUMBER data type with a decimal to any of the integer data types. Unlike Java and most other procedural programming languages, PL/SQL doesn’t require you to acknowledge this loss of precision by making the assignment explicit.

You can see the implicit casting of a NUMBER data type to an INTEGER data type in the following code:

SQL> DECLARE
  2    lv_number1 INTEGER;
  3    lv_number2 NUMBER(15,2) := 21533.22;
  4  BEGIN
  5    lv_number1 := lv_number2;
  6    dbms_output.put_line(lv_number1);
  7  END;
  8  /

The program would print this:

21533

Likewise, you could perform the same task on line 5 by using the FLOOR function before making the assignment between two variables (that use the NUMBER data type), like so:

  5 lv_number1 := FLOOR(lv_number2);

The FLOOR function effectively rounds down to the nearest integer value. It shows you explicitly how to shave off a decimal value.

You should avoid mixing and matching numeric data types to avoid the loss of mathematical value in your data. When you must mix numeric data types, you can prevent the loss of mathematical value during assignments by disallowing such assignments or qualifying in comments that you don’t care about the loss of information. The latter is a valid approach when you’re reporting in hundreds, thousands, and millions, provided you do the sum first before discarding the sum’s decimal value.

The new IEEE 754-format data types are single-precision and double-precision numbers. Their design supports scientific computing. The BINARY_FLOAT is a 32-bit floating point number, and the BINARY_DOUBLE is a 64-bit floating point number.

SQL> DECLARE
  2    lv_number1 BINARY_FLOAT;
  3    lv_number2 BINARY_DOUBLE := 89015698736543.4028234663852886E+038d;
  4  BEGIN
  5    dbms_output.put_line(lv_number2);
  6    lv_number1 := lv_number2;
  7    dbms_output.put_line(lv_number1);
  8  END;
  9  /

prints the following:

8.9015698736543403E+051
Inf

The output from this program shows you what happens when you assign a value from a BINARY_DOUBLE to a BINARY_FLOAT variable. The outcome might result in an error, but it most certainly won’t manifest itself during the assignment. Your program will probably throw an exception when you attempt to use the new variable. The Inf represents infinity or a value outside the range of values for the BINARY_FLOAT data type.

In this case, the BINARY_DOUBLE value is simply too large for a BINARY_FLOAT data type. The value assigned to the BINARY_FLOAT is infinity because the 64-bit value represents infinity within the scope of a 32-bit data type. Note that no error is raised during the assignment, and the implicit casting could break your program’s downstream logic.

IF Statement

The if or elsif statements work on a concept of Boolean logic. A Boolean variable or an expression, like a comparison of values, is the only criterion for an if or elsif statement. While this seems simple, it really isn’t, because truth or untruth has a third case in an Oracle database. A Boolean variable or expression can be true, false, or null. This is called three-valued logic.

You can manage three-valued logic by using the NVL built-in function. It allows you to impose an embedded check for a null and return the opposite of the logical condition you attempted to validate on lines 5 and 7 in the next program sample.

The following illustrates checking for truth of a Boolean and truth of an expression, ultimately printing the message that neither condition is true:

SQL> DECLARE
  2    lv_boolean BOOLEAN;
  3    lv_number  NUMBER;
  4  BEGIN
  5    IF NVL(lv_boolean,FALSE) THEN
  6      dbms_output.put_line('Prints when the variable is true.');
  7    ELSIF NVL((lv_number < 10),FALSE) THEN
  8      dbms_output.put_line('Prints when the expression is true.');
  9    ELSE
 10      dbms_output.put_line('Prints when both variables are null values.');
 11    END IF;
 12  END;
 13  /

This prints

Prints when variables are null values.

This always prints the else statement, because the variables are only defined, not declared. PL/SQL undeclared variables are always null values.

The NVL built-in function lets you create programs that guarantee behavior, which is most likely one of the critical things you should do as a developer. The guarantee becomes possible because you’re changing the rules and making natural three-valued logic behave as two-valued logic. Sometimes, that’s not possible, but oddly enough, when it isn’t possible, there’s a use case that will compel you to provide code for the null condition.

CASE Statement

The CASE statement appears very similar to a switch structure in many programming languages, but it doesn’t perform in the same way, because it doesn’t support fall through. Fall through is the behavior of finding the first true case and then performing all remaining cases. The CASE statement in PL/SQL performs like an if-elsif-else statement.

There are two types of CASE statements: the simple case and the searched case. You can use a CHAR, NCHAR, or VARCHAR2 data type in simple CASE statements and any Boolean expression in searched case statements.

The following program shows how to write a simple CASE statement. The selector variable is a VARCHAR2 variable assigned a value through a substitution variable.

SQL> DECLARE
  2    lv_selector VARCHAR2(20);
  3  BEGIN
  4    lv_selector := '&input';
  5    CASE lv_selector
  6      WHEN 'Apple' THEN
  7        dbms_output.put_line('Is it a red delicious apple?');
  8      WHEN 'Orange' THEN
  9        dbms_output.put_line('Is it a navel orange?');
 10      ELSE
 11        dbms_output.put_line('It''s a ['||lv_selector||']?');
 12      END IF;
 13  END;
 14  /

The WHEN clauses validate their values against the CASE selector on line 5. When one WHEN clause matches the selector, the program runs the instructions in that WHEN clause and exits the CASE block. The break statement found in languages such as C, C++, C#, and Java is implicitly present.

A searched CASE statement works different from a simple CASE because it doesn’t limit itself to an equality match of values. You can use a searched CASE to validate whether a number is in a range or in a set. The selector for a searched CASE is implicitly true and can be excluded unless you want to check for untruth. You provide a false selector value on line 2 if the WHEN clauses validate against a false condition, like this:

  2    CASE FALSE

The following program validates against truth:

SQL> BEGIN
  2    CASE
  3      WHEN (1 <> 1) THEN
  4        dbms_output.put_line('Impossible!');
  5      WHEN (3 > 2) THEN
  6        dbms_output.put_line('A valid range comparison.');
  7      ELSE
  8        dbms_output.put_line('Never reached.');
  9    END CASE;
 10  END;
 11  /

The range validation on line 5 is met, and it prints this:

A valid RANGE comparison.

Unlike the if and elsif statements, you don’t need to reduce the natural three-valued logic to two-valued logic. If a searched CASE statement’s WHEN clause isn’t met, it continues until one is met or the else statement is reached.

Iterative Structures

Iterative statements are blocks that let you repeat a statement or a set of statements. These statements come in two varieties: a guard-on-entry and guard-on-exit loop.

Three loop structures in PL/SQL let you implement iteration: the for, while, and simple loop structures. You can use them either with or without a cursor. A cursor is a PL/SQL structure that lets you access the result of a query row-by-row or as a bulk operation.

FOR Loop Statements

You can implement the for loop as a range or cursor loop. A range loop moves through a set of sequential numbers, but you need to know the beginning and ending values. It is a guard-on-exit looping structure. You can navigate through a for loop forward or backward by using an ascending integer range. Here’s an example:

SQL> BEGIN
  2    FOR i IN 0..9 LOOP
  3      dbms_output.put_line('['||i||']['||TO_CHAR(i+1)||']');
  4    END LOOP;
  5  END;
  6  /

The value of the iterator, i, is equal to the numbers in the inclusive range values. The iterator has a PLS_INTEGER data type. This program prints this:

[0][1]
[1][2]
[2][3]
 ...
[7][8]
[8][9]
[9][10]

Range for loops typically start with 1 and move to a higher number, but you can use a 0 (zero) as the low value in the range. A 0 is rarely used as a starting point, because arrays and cursors use 1-based numbering. The example shows you how to do it, but I want you to know that you shouldn’t do it.

The next range loop moves through the sequence from the highest to the lowest number, and it uses a 1-based number model. Notice that the only evidence of decrementing behavior is the REVERSE reserved word.

SQL> BEGIN
  2    FOR i IN REVERSE 1..9 LOOP
  3      dbms_output.put_line('['||i||']['||TO_CHAR(i+1)||']');
  4    END LOOP;
  5  END;
  6  /

Cursor for loops work with data sets returned by queries. Two static patterns are possible, in addition to an implicit dynamic cursor and a parameterized dynamic cursor. The first example shows you how to write a static cursor without a declaration block. You should write this type of code only when you’re doing a quick test program or standalone script.

SQL> BEGIN
  2    FOR i IN (SELECT item_title FROM item) LOOP
  3      dbms_output.put_line(i.item_title);
  4    END LOOP;
  5  END;
  6  /

Line 2 contains the static cursor inside parentheses. At runtime, the query becomes an implicit cursor. Implicit cursors like these should always be static queries. You should put queries into formal cursors, and then call them in the execution block, like this:

SQL> DECLARE
  2    CURSOR c IS
  3      SELECT item_title FROM item;
  4  BEGIN
  2    FOR i IN c LOOP
  3      dbms_output.put_line(i.item_title);
  4    END LOOP;
  5  END;
  6  /

The program declares a formal static cursor on lines 2 and 3. The for loop implicitly opens and fetches records from the cursor on line 5. This type of program is more readable than the preceding example. It is also adaptable if your requirements evolve from a static to dynamic cursor. Whether or not you define cursors with formal parameters, you can include variables in a formal cursor declaration.

The following shows you how to implement a cursor with a formal parameter. The formal parameter creates a dynamic cursor.

SQL> DECLARE
  2    lv_search_string VARCHAR2(60);
  3    CURSOR c (cv_search VARCHAR2) IS
  4      SELECT item_title
  5      FROM item
  6      WHERE REGEXP_LIKE(item_title,'^'||cv_search||'*+');
  7  BEGIN
  8    FOR i IN c ('&input') LOOP
  9      dbms_output.put_line(i.item_title);
 10    END LOOP;
 11  END;
 12  /

The lines of interest are 3, 6, and 8. Line 3 declares the formal parameter for a dynamic cursor. Line 6 shows the use of the formal parameter in the cursor. Line 8 shows the actual parameter calling the cursor. The call parameter is a substitution variable because the anonymous block then becomes dynamic. You can eliminate the formal parameter from the cursor on line 3 and replace the formal cursor parameter on line 6 with a substitution variable, but that’s a very poor coding practice. As a rule, you should always define formal parameters for dynamic cursors.

This concludes the basics of a for loop. A twist on the for loop involves the WHERE CURRENT OF clause, which is discussed in the next section.

WHERE CURRENT OF Clause

A big to do about nothing would be the WHERE CURRENT OF clause. In my opinion, bulk operations are generally the better solution. It’s important, however, to show an example in a workbook like this, so I’ve included two.

The first example shows you how to lock a row with the cursor and then update the same table in a for loop, as follows:

SQL> DECLARE
  2    CURSOR c IS
  3      SELECT * FROM item
  4      WHERE item_id BETWEEN 1031 AND 1040
  5      FOR UPDATE;
  6  BEGIN
  7    FOR I IN c LOOP
  8      UPDATE item SET last_updated_by = 3
  9      WHERE CURRENT OF c;
 10    END LOOP;
 11  END;
 12  /

Line 5 locks the rows with the FOR UPDATE clause until a COMMIT or ROLLBACK statement occurs in the current session or connection. Line 9 correlates the update to a row returned by the cursor.

The next example demonstrates how to use the WHERE CURRENT OF in a bulk operation. It’s an unavoidable forward reference to material covered later in this chapter.

SQL> DECLARE
  2    TYPE update_record IS RECORD
  3    ( last_updated_by  NUMBER
  4    , last_update_date DATE );
  5    TYPE update_table IS TABLE OF UPDATE_RECORD;
  6    updates UPDATE_TABLE;
  7    CURSOR c IS
  8      SELECT last_updated_by, last_update_date
  9      FROM item
 10      WHERE  item_id BETWEEN 1031 AND 1040
 11      FOR UPDATE;
 12  BEGIN
 13    OPEN c;
 14    LOOP
 15      FETCH c BULK COLLECT INTO updates LIMIT 5;
 16      EXIT WHEN updates.COUNT = 0;
 17      FORALL i IN updates.FIRST..updates.LAST
 18        UPDATE item
 19        SET last_updated_by = updates(i).last_updated_by
 20        ,   last_update_date = updates(i).last_update_date
 21    WHERE CURRENT OF c;
 22  END;
 23  /

Like the row-by-row example, the FOR UPDATE clause on line 9 locks the rows. The WHERE CURRENT OF clause on line 21 correlates the update to the rows returned by the bulk collected cursor.

Now that I’ve shown you how to do it, you might wonder why would you want to? The same thing can be accomplished by a correlated UPDATE statement, like this:

SQL> UPDATE item i1
  2  SET    last_updated_by = 3
  3  ,      last_update_date = TRUNC(SYSDATE)
  4  WHERE  EXISTS (SELECT NULL FROM item i2
  5                 WHERE  item_id BETWEEN 1031 AND 1040
  6                 AND    i1.ROWID = i2.ROWID);

In fact, Oracle’s documentation indicates that it recommends correlated UDPATE and DELETE statements over the use of the WHERE CURRENT OF clause. I must also recommend native SQL solutions when they’re available.

The range and cursor for loops are powerful iterative structures. Their beauty lies in their simplicity, and their curse lies in their implicit opening and closing of cursor resources. You should use these structures when access to the data is straightforward and row-by-row auditing isn’t required. When you need to perform row-by-row auditing, you should use a while or simple loop because they give you more control.

WHILE Loop Statements

A WHILE loop is a guard-on-entry loop: You need to manage both the entry and exit criteria of a while loop. Unlike the for loop, with the while loop you don’t need an index value because you can use other criteria to meet the control entrance and exit. If you use an index, the Oracle 11g CONTINUE statement can make control more complex, because it allows you to abort an iteration and return to the top of the loop:

SQL> DECLARE
  2    lv_counter NUMBER := 1;
  3  BEGIN
  4    WHILE (lv_counter < 5) LOOP
  5      dbms_output.put('Index at top ['||lv_counter||']');
  6      IF lv_counter >= 1 THEN
  7        IF MOD(lv_counter,2) = 0 THEN
  8          dbms_output.new_line();
  9          lv_counter := lv_counter + 1;
 10          CONTINUE;
 11        END IF;
 12        dbms_output.put_line('['||lv_counter||']');
 13      END IF;
 14      lv_counter := lv_counter + 1;
 15    END LOOP;
 16  END;
 17  /

This prints the following:

Index at top [1][1]
Index at top [2]
Index at top [3][3]
Index at top [4]

Only odd number counter values make it to the bottom of the loop, as illustrated by the second printing of the counter value. That’s because the CONTINUE statement prints a line return and returns control to the top of the loop. You could also do the same thing with the GOTO statement and label. You enclose labels inside guillemets, also known as angle brackets. They’re available in releases prior to Oracle 11g, and although it pains me to tell you about them, here’s an example:

SQL> DECLARE
  2     lv_counter NUMBER := 1;
  3  BEGIN
  4    WHILE (lv_counter < 5) LOOP
  5      dbms_output.put('Index at top ['||lv_counter||']');
  6      IF lv_counter >= 1 THEN
  7        IF MOD(lv_counter,2) = 0 THEN
  8          dbms_output.new_line();
  9          GOTO skippy;
 10        END IF;
 11        dbms_output.put_line('['||lv_counter||']');
 12      END IF;
 13      << skippy >>
 14      lv_counter := lv_counter + 1;
 15    END LOOP;
 16  END;
 17  /

The GOTO statement on line 9 skips to the incrementing instruction for the control variable on line 13. It is actually a bit easier to read than the CONTINUE statement shown earlier.

The GOTO statement should be avoided whenever possible, however. The CONTINUE should be used minimally and carefully. The while loop is powerful but can be tricky if you’re not careful when using a CONTINUE statement. A poorly coded while loop that contains a CONTINUE statement can cause an infinite loop.

Simple Loop Statements

The simple loop statement is anything but simple. You use it when you want to control everything that surrounds access to an explicit cursor. Some of these controls are provided through four built-in cursor attributes:

• %FOUND Returns TRUE only when a DML statement has changed or returned one or more rows
• %ISOPEN Returns TRUE when an explicit cursor is open and FALSE when an explicit cursor isn’t open or for any implicit cursor (Implicit cursors close immediately after running.)
• %NOTFOUND Returns TRUE when a DML statement fails to change or cursor fails to return at least one row
• %ROWCOUNT Returns the number of rows changed by a DML statement or the number of rows returned by a cursor or SELECT INTO statement

These attributes work with cursors or ordinary SQL statements. You access ordinary SQL statements by referring to SQL instead of a cursor name. A SELECT-INTO, INSERT, UPDATE, or DELETE statement is found when it processes rows and not found when it doesn’t. For example, the following anonymous block uses cursor attributes to manage printing log statements to the console:

SQL> BEGIN
  2    UPDATE   system_user
  3    SET      last_update_date = SYSDATE;
  4    IF SQL%FOUND THEN
  5      dbms_output.put_line('Updated ['||SQL%ROWCOUNT||']');
  6    ELSE
  7      dbms_output.put_line('Nothing updated!');
  8    END IF;
  9  END;
 10  /

The SQL%FOUND on line 4 checks whether a SQL statement was processed. As you may have surmised, SQL isn’t just an acronym in Oracle PL/SQL, it is a reserved word that links to an anonymous cursor. If SQL%FOUND returns TRUE, then line 5 prints the number of rows updated in the table.

A typical simple loop opens a cursor, fetches rows from a cursor, processes rows from a cursor, and closes a cursor. The following program demonstrates those steps and illustrates an anchored data type:

SQL> DECLARE
  2    lv_id    item.item_id%TYPE;   -- This is an anchored type.
  3    lv_title VARCHAR2(60);
  4    CURSOR c IS
  5      SELECT   item_id, item_title
  6      FROM     item;
  7  BEGIN
  8    OPEN c;
  9    LOOP
 10      FETCH c INTO lv_id, lv_title;
 11      EXIT WHEN c%NOTFOUND;
 12      dbms_output.put_line('Title ['||lv_title||']');
 13    END LOOP;
 14    CLOSE c;
 15  END;
 16  /

This program defines one variable by anchoring the data type to the definition of the item_id column in the item table. When the definition of the table changes, you don’t have to change your program because it will adjust automatically. Automatic adjustments of data type work best when changes are limited to size within a type and they collapse when changes of a column’s data type isn’t supported by implicit type casting. The second variable is explicitly assigned a data type, and any change to the table would require a change to the assigned data type. The first statement after you start a simple loop fetches a row of data, and the second, line 11, checks to make sure a row was fetched. Line 11 also exits the loop when no record is found, which is typically after all rows have been read or no rows were found.

You can extend the preceding model by creating a user-defined record structure and returning the row into a single record structure. Record structures are composite variables. The following example uses a %ROWTYPE pseudo attribute to anchor a catalog table definition to a local variable:

SQL> DECLARE
  2    lv_item_record item%ROWTYPE;
  3    CURSOR c IS
  4      SELECT   *
  5      FROM     item;
  6  BEGIN
  7    OPEN c;
  8    LOOP
  9      FETCH c INTO lv_item_record;
 10      EXIT WHEN c%NOTFOUND;
 11      dbms_output.put_line('Title ['||lv_item_record.item_title||']');
 12    END LOOP;
 13    CLOSE c;
 14  END;
 15  /

On line 11, the lv_item_record.item_title statement returns the value of a field in the row of data. The dot (.) between the local variable and column name is the component selector. You actually read this reference from right to left. It means the item_title is selected from the lv_item_record component, which is a local record structure variable.

You could also create a record type explicitly. This is often done when you want only a subset of the columns in a table and you don’t want to create a view or anchor the variable to a cursor. A local record set variable would be like the following:

TYPE item_record IS RECORD
( id    NUMBER
, title VARCHAR2(60));

The best approach simply lets you anchor a local variable to the SELECT-list returned by a CURSOR, which is a natural record structure. You could rewrite the program like this:

SQL> DECLARE
  2    CURSOR c IS
  3      SELECT *
  4      FROM item;
  5      lv_item_record c%ROWTYPE;
  6  BEGIN
  7    OPEN c;
  8    LOOP
  9      FETCH c INTO lv_item_record;
 10      EXIT WHEN c%NOTFOUND;
 11      dbms_output.put_line('Title ['||lv_item_record.item_title||']');
 12    END LOOP;
 13    CLOSE c;
 14  END;
 15  /

Line 5 declares a variable that anchors itself to the definition of a CURSOR. If you change the cursor, the variable automatically adjusts. This is the most flexible and least-coupled way to anchor a variable in PL/SQL.

You’ll encounter some glitches down the road with local types like these, because they’re limited exclusively to a PL/SQL context.

Oracle provides two built-in exception management functions. They are:

• SQLCODE Returns a negative number that maps to the Oracle predefined exceptions, but one special case, the NO_DATA_FOUND exception, returns 100.
• SQLERRM Is overloaded and provides the following behaviors: Returns the actual error as a negative integer; returns a user-defined exception when the number is positive or not found in the predefined Oracle exception list; and returns the actual number parameter as a negative integer with the Oracle-defined message.

The simplest exception handler uses the Oracle keyword OTHERS, and it catches all raised exceptions from the execution block:

SQL> DECLARE
  2    lv_letter  VARCHAR2(1);
  3    lv_phrase VARCHAR2(2) := 'AB';
  4  BEGIN
  5    lv_letter := lv_phrase;
  6  EXCEPTION
  7    WHEN OTHERS THEN
  8      dbms_output.put_line('Error:'||CHR(10)||SQLERRM);
  9  END;
 10  /

The assignment of a two-character string to a single-character string on line 5 raises (throws) an exception, which is caught by the exception handler and printed to console, like:

Error:
ORA-06502: PL/SQL: numeric OR VALUE error: character string
buffer too small

Oracle also provides a set of predefined exceptions in the STANDARD package. Standard error names can replace the OTHERS keyword. The VALUE_ERROR keyword could do so on line 7, as shown:

  7    WHEN VALUE_ERROR THEN

This would catch the ORA-06502 error but not any other exception, which means we would now need two error handlers. We’ll need one error handler for the specific “numeric or value error” and another for everything else, more or less a “catch all” handler. The new exception block would look like this:

  5  ...
  6  EXCEPTION
  7    WHEN VALUE_ERROR THEN   -- Specific error handler.
  8      dbms_output.put_line('Error:'||CHR(10)||SQLERRM);
  9    WHEN OTHERS THEN        -- General error handler.
 10      dbms_output.put_line('Error:'||CHR(10)||SQLERRM);
 11  END;
 12  /

Many developers use only the OTHERS as a “catch all,” but good coding practices recommend specific exception handlers. You should always place the specific exception handler before the OTHERS handler.

We also have the ability to define user-defined exceptions and write dynamic exceptions. The next two subsections discuss how.

User-Defined Exceptions

You can declare user-defined exceptions two ways. One way lets you declare an EXCEPTION variable and catch it by a user-defined exception number (oddly enough 1 is that number), and the other lets you map an exception name to a known Oracle error code.

SQL> DECLARE
  2    lv_error EXCEPTION;
  3  BEGIN
  4    RAISE lv_error;
  5    dbms_output.put_line('Can''t get here.');
  6  EXCEPTION
  7    WHEN OTHERS THEN
  8      IF SQLCODE = 1 THEN
  9        dbms_output.put_line('This is a ['||SQLERRM||']');
 10      END IF;
 11  END;
 12  /

The example declares a user-defined exception of lv_error on line 2 and raises it as an exception on line 4. The generic OTHERS exception traps the error on line 7, and the IF statement checks for a user-defined exception on line 8.

This program raises the exception and prints:

This is a [User-Defined Exception].

By default all user-defined exceptions have a SQLCODE value of 1. You could also replace the user-defined exception on line 4 with a call to a standard error specialized event.

  4    RAISE program_error;

A two-step declaration process lets you declare an exception and map it to a
number. The first step declares the variable and the second step maps the variable to an EXCEPTION_INIT precompiler instruction.

SQL> DECLARE
  2    lv_sys_context VARCHAR2(20);
  3    lv_error EXCEPTION;
  4    PRAGMA EXCEPTION_INIT(lv_error,-2003);
  5  BEGIN
  6    lv_sys_context := SYS_CONTEXT('USERENV','PROXY_PUSHER');
  7  EXCEPTION
  8    WHEN lv_error THEN
  9      dbms_output.put_line('This is a ['||SQLERRM||'].');
 10  END;
 11  /

ine 3 declares the local exception variable and line 4 maps the Oracle error code to the user-defined exception. Line 6 throws an error because it provides an invalid PROXY_PUSHER string as a call parameter to the SYS_CONTEXT function.

The preceding test program raises an exception and prints:

This is a [ORA-02003: invalid USERENV parameter].

The ORA-02003 is a real error code found in the SYS.STANDARD package. You can read the specification of that package to find a complete list of standard errors.

Dynamic User-Defined Exceptions

Dynamic user-defined exceptions let you raise a customized exception by assigning a number in the range of -20,000 to -20,999. The RAISE_APPLICATION_ERROR function provides this ability in Oracle.

The prototype is:

RAISE_APPLICATION_ERROR(error_number, error_message [, keep_errors])

The following program shows how to raise a dynamic user-defined exception:

SQL> DECLARE
  2    lv_error EXCEPTION;
  3    PRAGMA EXCEPTION_INIT(lv_error,-20001);
  4  BEGIN
  5    RAISE_APPLICATION_ERROR(-20001,'A less original message.');
  6  EXCEPTION
  7    WHEN lv_error THEN
  8      dbms_output.put_line(SQLERRM);
  9  END;
 10  /

Line 2 declares the exception variable and line 3 maps the error to a value in the range of available values. Line 5 throws the exception and line 7 catches the error.

Oracle 11g also provides a stack trace management function in the DBMS_UTILITY package. It’s the FORMAT_ERROR_BACKTRACE function. Handling errors is important and much more can be said about managing them in an EXCEPTION block.