Instructor's Notes

(If applicable)

• Lesson 2: PL/SQL Programming for Oracle 10g: Language Fundamentals
  The fundamental language elements in PL/SQL for Oracle 10g including:
• Statements
• Symbols
• Datatypes
• Variable declaration and assignment
• Declaring PL/SQL Records
• Variable scope
• Nested Blocks
• IF
• CASE
• Looping constructs
• Introduction to Arrays

Instructor's Notes

(If applicable)

• To some extent, this is a reference chapter. You’ll see relatively large lists of statements, datatypes and symbols. I recommend that you do not attempt to remember each one as you progress through this chapter. We’ll incorporate them into examples in the remainder of the course.
• However, do use this lesson to acquire a solid grasp of the basic format of the IF, CASE, loop statements, and a good understanding of arrays. But here again, you’ll see these statements used throughout the rest of the course.

Instructor's Notes

(If applicable)

• Shown here is a list of commonly used statements that can be used within a PL/SQL program. Refer to the Oracle 10g PL/SQL User's Guide and Reference for a complete list and description of each statement.
• Throughout the remainder of this course you will learn the purpose of and see examples of many of these statements.
• You may be wondering, “where are the rest of the SQL statements?” DDL statements such as CREATE, GRANT, DROP etc. Well, these are DDL statements and cannot be coded directly within PL/SQL. Use the EXECUTE IMMEDIATE statement to embed DDL statements.
• Mini-Workshop
• Access Chapter 13 of the Oracle 10g PL/SQL User's Guide and Reference (R2) and review the examples of the following LOOP statements:
• basic_loop_statement
• for_loop_statement
• cursor_for_loop_statement
• Access to the complete set of Oracle manuals is free at http://technet.oracle.com.

Instructor's Notes

(If applicable)

• PL/SQL symbols continued:


• Symbol Purpose
• . Component selector
• ( ) Used to control precedence. 5* (2+2)
• % Prefix for PL/SQL attributes. emp.name%TYPE
• .. Range operator for loops. FOR x in 1..5 LOOP
• @ Used to reference remote databases.
• select * from emp@hq;
• : Used to prefix a host variable.
• => The “arrow”. Use in procedure calls to assign
• value to a parameter.
• || Concatenation operator.
• << >> Label.

• Refer to Chapter 2 of the Oracle 10g PL/SQL User’s Guide and Reference for complete details on the PL/SQL symbols (see “delimiters”).

Instructor's Notes

(If applicable)

• Oracle10g introduces a new technique for identifying enclosing quotes (delimiters) in text literals. It is supported in SQL statements and PL/SQL statements. This makes coding strings that contain single quotation marks cleaner and less error-prone.
• Use the letter “q” (not case-sensitive) followed by the open quote and the quote delimiter character. The Oracle10g SQL Reference states that the quote delimiter character can be any character; however, I find it easiest to use a character that is not used in the literal itself.
• If the quote delimiter character is [, {, <, or (, then the closing delimiter must be ], }, > or ), respectively. Otherwise use the same exact character as the opening and closing quote delimiter character.
• Refer to the section on “Literals” in the Oracle10g SQL Reference manual for more information on what Oracle calls the “alternative quoting mechanism”.

Instructor's Notes

(If applicable)

• PL/SQL datatypes define the format of a variable, constant or parameter (parameters are used to pass values to and from a procedure or function).
• The slide contains a list of commonly used PL/SQL datatypes. In addition to the examples throughout the remainder of this course, refer to Chapter 3 of the PL/SQL User’s Guide and Reference for a complete list and description of the datatypes.
• Here is a brief description of the datatypes listed here:
• NUMBER. Specify number of digits (precision) and number of decimal places (scale) if you want to store fixed point numbers. Omit precision and scale for floating point numbers. NUMBER has many subtypes such as DECIMAL and INTEGER; refer to the Oracle documentation for more information.
• CHAR is fixed length character data. The default length of a CHAR variable is 1, the maximum length is 32767. The database character set determines the internal representation.
• VARCHAR2 is variable length character data. Maximum length is 32767. For better performance, Oracle pre-allocates 2000 bytes of storage for all VARCHAR2 definitions less than 2000 bytes (it eliminates “reallocations as different sized strings are allocated to it”, Tom Kyte, May 2004, http://asktom.oracle.com.)
• DATE holds a date/time value.
• TIMESTAMP contains a date/time value with fractional microseconds.


• Notes continue on the next page…

Instructor's Notes

(If applicable)

• TIMESTAMP WITH TIME ZONE adds a time zone component to a TIMESTAMP type.
• BOOLEAN variables contains TRUE, FALSE, or NULL and are use in conditional logic, e.g. CASE and IF statements. e.g. IF VALID_ORDER THEN….
• EXCEPTION is used to define a user-defined exception name, for example “customer_not_found”. Refer to the “Error Handling” lesson for more information.
• REF CURSOR is a pointer to a memory cursor. Useful for returning sets of rows to callers / clients.
• RECORD defines a composite type; a collection of other scalar or composite types.
• TABLE defines a PL/SQL unbounded array, officially called an “index-by table” or “associative array”. Refer the examples later in this lesson for more information.
• RAW and LONG RAW are binary data. LONG RAW has been deprecated in favor of BLOB.
• BLOB is best to use for large binary objects.
• LONG and CLOB are large character data objects, better to use newer CLOB.
• NCLOB is national character set long character object.
• BFILE is an external binary file.
• ROWID is a pointer to a table row.

Instructor's Notes

(If applicable)

• Variables are declared in the DECLARE section of the program. Required are name and datatype. The name, like all Oracle names, must follow these rules:
• 1 – 30 alpha-numeric characters
• 1st character must be alphabetic
• Valid special characters are _, $ and #
• Avoid reserved words such as SELECT
• In this example, V_LASTNAME is defined with the NOT NULL clause, so it can not contain a null value. An exception is raised if an initial value is not assigned (see the next page for an example). Therefore we must assign the variable an initial value ('SMITH‘ in this example). This is done with the assignment operator (:=) or the DEFAULT keyword.
• “NULL” is the PL/SQL no-operation statement. I use it here (see line 4) simply because the BEGIN block is required, yet I do not want it to do anything – I simply wanted to demonstrate the declaration and use of a variable.


• Notes continue on the next page…

Instructor's Notes

(If applicable)

• An exception is raised if a variable defined as NOT NULL is not given a default value:
• SQL> declare
• 2 x number not null;
• 3 begin
• 4 null;
• 5 end;
• 6 /
• x number not null;
• *
• ERROR at line 2:
• ORA-06550: line 2, column 3:
• PLS-00218: a variable declared NOT NULL must have an initialization assignment

Instructor's Notes

(If applicable)

• The examples define the following variables:
• V_HIREDATE1 as a DATE object. The default “value” is NULL.
• V_HIREDATE2 as a DATE initialized to the current date in SYSDATE.
• V_TOTAL_SALES as a NUMBER that can hold 8 total digits with 2 digits after the decimal. Will accept range of values from -999999.99 to 999999.99. The default “value” in this variable is NULL.
• V_TOTAL_VALUE1 as a NUMBER that can hold 8 total digits with 2 digits after the decimal. The default value is 0.
• V_TOTAL_VALUE2 as a NUMBER that can hold 8 total digits with 2 digits after the decimal. The default value is also 0.
• V_ORDER_VALID is a BOOLEAN initialized to FALSE. It can be used in CASE or IF statements, e.g. IF v_order_valid THEN …
• V_LASTNAME is a variable length character field up to 20 characters that cannot be NULL and is initialized to 'SMITH'.
• C_CO_NAME is a CONSTANT. Constants have their values defined when the variable is defined, and the value not be changed (an exception occurs if an attempt is made to change the value).
• See the supplied script DECLARING_VARIABLES.SQL for a working copy of this example.

Instructor's Notes

(If applicable)

• %TYPE is a great way to datatype a variable. Oracle will use the datatype of the table column, variable or cursor coded in the %TYPE specification. (You’ll learn more about cursors later.) It is flexible; if the datatype changes, the new datatype will be picked up when the code is recompiled – without a source code change.
• In this example variable V1 gets the same exact datatype as the LASTNAME column of the CUSTOMER table.
• This example includes the SELECT INTO statement (a form of implicit cursor). Some form of SELECT is required to access values in a database table (i.e. you cannot just compare to a table value in an IF or CASE statement without first selecting the data). You’ll learn more about SELECT INTO and other means of retrieving data later in this course.
• See the supplied script TYPE.SQL for a copy of the code shown here.

Instructor's Notes

(If applicable)

• A PL/SQL record provides a way to deal with separate, but related, fields (variables) as a single unit. A record often has a similar structure to a row in a database table.
• There are four ways to define PL/SQL records. We will see examples of table-based and programmer-defined records in the following pages.
• See the supplied script DEFINING_PLSQL_RECORDS.SQL for examples of the four techniques for declaring variables.

Instructor's Notes

(If applicable)

• A table-based record is defined with the %ROWTYPE attribute and creates a records that matches the structure of the table row. i.e., the record has the same number of fields as columns in the table and the fields have the same name and datatype.
• A PL/SQL record defined with %ROWTYPE:
• contains the same number of variables as columns in the table
• all datatypes match
• field names are the same as column names
• As we see in this example, the fields in the record are referenced with “dot notation”, for example “cust_rec.lastname”. Then we can use the entire record in the INSERT (and UPDATE) statements.
• As of Oracle 9i, a table-based record is a convenient structure for DML operations. Note that the VALUES clause in the INSERT statement references the record – eliminating the need to code each variable. The lack of parenthesis is correct syntax when a record is used.
• See the supplied script RECORDS.SQL for a working example of this code.

Instructor's Notes

(If applicable)

• A programmer defined PL/SQL record is a record where each field is explicitly defined by the programmer. You must first define a record type. Then, you must declare a variable of that type.
• The datatype of each field can be:
• Scalar types (NUMBER, VARCHAR2, DATE, etc.)
• %TYPE, %ROWTYPE or another programmer defined record (yes, Oracle supports nested records).
• Oracle objects (e.g. defined with CREATE TYPE object-name AS OBJECT)
• PL/SQL array (more on arrays later in this lesson).
• REF CURSOR, i.e. a pointer to a cursor. (more on REF CURSORS later in this lesson).
• Parameter descriptions:
• type_name Name of record type
• field Name of field (variable) in record
• datatype Datatype of field in record
• There is no limit on the number of fields within the record.

Instructor's Notes

(If applicable)

• This example shows that a programmer-defined record type can be packaged. Packaging the record makes the record global, i.e. accessible to any user with execute privilege on the package. Next, we see that a function can return a record – see the RETURN(cust_rec) statement. (Later you will learn how to return sets of records!)
• Here, I have packaged a record type called CUST_INFO_T. Then, I use that type in a function called CREATE_CUST. The function returns a record. Note the reference to the TYPE within the function requires the prefix of the package name, as in “CUSTPACK.CUST_INFO_T”.
• This example requires that a sequence called S1 has been created, for example:
• CREATE SEQUENCE s1;
• See the supplied script RECORD_IN_PACKAGE.SQL for a working example of this code.

Instructor's Notes

(If applicable)

• Here again we see the definition of a record based on a packaged TYPE. We also see the call to the function CREATE_CUST, where CREATE_CUST returns a record.

Instructor's Notes

(If applicable)

• Workshop – PL/SQL Records
• Code a function that accepts a customer number and returns a customer record populated with information about the customer. Use the SQL statement “SELECT * INTO record FROM CUSTOMER WHERE cust_no = parameter” to retrieve the customer row.
• Code an anonymous block that calls the function and uses the PUT_LINE procedure to display one or more customer attributes.

Instructor's Notes

(If applicable)

• The scope of a variable is where it can be referenced (i.e. used or seen). When a variable is declared, it is accessible throughout the PL/SQL block – and any embedded sub-blocks. A variable cannot be reference in:
• An outer (enclosing) block
• A called procedure or function (To accomplish this, use either use packaged variables or pass the procedure or function a value in the call. You will learn both techniques later in the course.)
• A calling procedure or function (To return a value from a procedure, use OUT type variables. Functions, by design, always return a value)
• (Using packages, we can create session (global) variables. This will be discussed in the lesson on packages later in this course.)

Instructor's Notes

(If applicable)

• A PL/SQL variable can be referenced in blocks nested within the block in which the variable is defined unless the nested block contains a variable of the same name; then the local version of the variable is referenced.
• In the example, there are two blocks. In the first, we declare a variable, V_CUSTNO. In the second, we declare another variable, V_STATE. In the inner block, we can reference the V_CUSTNO variable. Take note that the V_STATE variable cannot be referenced outside the inner block. For instance, the following code will generate a compile error:
• declare
• v_custno NUMBER := 100;
• begin
• dbms_output.put_line(v_custno) ;
• declare
• v_state CHAR(2):= ‘NY’ ;
• begin
• dbms_output.put_line (v_custno || v_state) ;
• end ;
• dbms_output.put_line(v_state) ;
• end ;
• ERROR at line 10:
• ORA-06550: line 10, column 25:
• PLS-00201: identifier 'V_STATE' must be declared
• ORA-06550: line 10, column 4:
• PL/SQL: Statement ignored

Instructor's Notes

(If applicable)

• Scalar SQL functions such as UPPER, LOWER, INITCAP and SUBSTR can be used in PL/SQL statements. However, the aggregate functions (AVG, MIN, MAX) and analytic functions (LEAD, LAG) are not permitted in PL/SQL statements. Of course, the aggregate and analytic functions can be used in SQL statements embedded in PL/SQL.
• Oracle also supplies error-related functions SQLERRM and SQLCODE for use in PL/SQL statements only. This slide shows an excerpt from a DECLARE block where we use these functions to initialize local variables. SQLERRM contains the text of the last error message; SQLCODE contains the Oracle error code. You will learn more about the use of these functions in the lesson on error handling later in this course.

Instructor's Notes

(If applicable)

• Like the Oracle 10g SQL compiler, the 10g PL/SQL compiler supports regular expressions.
• Oracle’s implementation is IEEE POSIX and Unicode Consortium compliant. Oracle10g provides many new operators such as:
• * - Matches zero or more occurrences
• + - Matches one or more occurrences
• ? - Matches zero or one occurrence
• ^ - Anchors to the beginning of the line
• $ - Anchors to the end of line
• Oracle 10g Release 2 adds support for Perl expressions, including, but not limited to:
• \d – Match a digit character
• \D – Match a non-digit character
• \w – Match a word character
• \W – Match a non-word character


• Notes for this slide continue on the next page…

Instructor's Notes

(If applicable)

• Notes continued from the previous page:
• Here is a simple demonstration of 10g Release 2 support for Perl regular expressions:
• 1 declare
• 2 lastname varchar2(50);
• 3 begin
• 4 if regexp_like(lastname, '\W') then null;
• 5 end if;
• 6* end;
• SYSTEM@orcl> /


• PL/SQL procedure successfully completed.
• Oracle10g provides a new condition called REGEXP_LIKE.
• Finally, 10g provides new functions REGEXP_INSTR, REGEXP_REPLACE and REGEXP_SUBSTR. These functions are similar to the 9i counterparts INSTR, REPLACE and SUBSTR – but they support regular expression operators.
• Refer to the Oracle10g PL/SQL User's Guide and Reference for more information about using regular expressions in PL/SQL.
• Refer to the supplied script plsql_regular_expressions.sql for an example of regular expressions in PL/SQL.

Instructor's Notes

(If applicable)

• The IF statement (and CASE statement) can be used for conditional processing. The IF construct must be terminated with END IF and followed by a semi-colon. You can optionally use ELSE to provide what to do if the condition is not met.
• You may nest IF statements. For example:
• IF V_sales_amt = 100 THEN
• …
• ELSE
• IF V_sales_amt < 200 THEN
• …
• ELSE
• …
• ENDIF;
• ENDIF:
• The use of ELSIF allows you to check multiple conditions within one construct.
• Caution: ELSIF is spelled “ELSIF”, not “ELSEIF”, and not “ELSE IF”. Believe it or not!
• There is no limit to the number of ELSIF conditions that can be included in a single construct.

Instructor's Notes

(If applicable)

• Oracle 9i introduced PL/SQL support for a CASE statement and CASE expression (8i introduced support for SQL-level CASE). Oracle supports two flavors of CASE, simple and searched.
• The simple case expression tests for an equal condition on the supplied value or expression. The first WHEN value that is equal causes Oracle to return the corresponding THEN value. If none of the WHEN values match the supplied expression, the ELSE value is returned. If the ELSE is not coded, NULL is returned.
• The searched case (as seen in the previous example) allows multiple comparison expressions (<, >, <=, >=, BETWEEN, LIKE, IN, IS NULL, etc.). The first TRUE expression causes Oracle to return the corresponding THEN value. If none of the WHEN values match the supplied expression, the ELSE value is returned. If the ELSE is not coded, a CASE not found exception is returned.
• The WHEN clauses are evaluated sequentially. The 1st TRUE WHEN causes the associated statement(s) to be executed; The CASE statement then ends (execution continues after the END CASE clause). If none of the WHEN expressions is true the ELSE statement (if any) will execute.
• The CASE statement raises a CASE_NOT_FOUND exception if an ELSE clause is not provided and none of the WHEN’s are TRUE.
• Only one THEN statement (or ELSE statement) is executed for each CASE statement. There is no “fall-through” as in the C language ‘switch’ statement.
• CASE is limited to 128 WHEN/THEN pairs (255 total values). This limit can be overcome by nesting CASE within CASE.

Instructor's Notes

(If applicable)

• Here is an example of a simple CASE statement. Notes:
• The label is optional but provides good documentation.
• Each THEN can have any number of statements, each terminated with a semi-colon.
• Only the 1st TRUE THEN is executed. Control is transferred to the END CASE after the 1st TRUE THEN is executed.
• If the ELSE is not provided and none of the THEN’s are TRUE, a CASE_NOT_FOUND exception is raised and control is automatically transferred to the EXCEPTION block, if coded.


• Restriction: The case-operand and the when-operands can be any datatype except BLOB, BFILE, an object type, a PL/SQL record, an index-by-table, a varray, or a nested table.


• See supplied script CASE1.SQL for a working example of this CASE expression.

Instructor's Notes

(If applicable)

• Here is an example of a Searched CASE expression. Note the absence of a CASE selector and the use of Boolean expressions in each WHEN clause.
• See supplied script CASE2.SQL for a working example of this CASE expression.

Instructor's Notes

(If applicable)

• A simple loop repeats the logic contained within the LOOP / END LOOP construct until an unconditional EXIT statement is executed or a condition specified by the EXIT WHEN clause is met. Be careful; an infinite loop will occur if the EXIT statement is not executed.
• Supplemental Notes
• PL/SQL also offers a “WHILE” loop:
• WHILE condition LOOP
• statement(s);
• END LOOP;


• The WHILE loop differs from a simple loop in that it checks the condition first before entering the loop and then continues to execute until the condition is met. When the WHILE condition is met, the loop exits. In other words, the WHILE loop repeats a sequence of statements until the controlling condition is no longer TRUE.

Instructor's Notes

(If applicable)

• Loops can be nested as shown in the example. Note how the inner loop, <<b>>, has the statement EXIT a WHEN X = 2. This indicates that when when the inner loop <<b>> meets the condition of X = 2, the EXIT WHEN will exit the outer loop A. The default is to exit the current loop.

Instructor's Notes

(If applicable)

• A numeric FOR loop allows you to implement a looping construct without having to be concerned with an EXIT WHEN as needed in the simple loop. The numeric FOR loop implicitly defines the index (i.e. loop counter) and exits the loop when the counter exceeds its upper bound. The general syntax of this statement is :
• FOR index IN [reverse] lower_bound..upper_bound LOOP
• statement(s);
• END LOOP;


• The index:
• Is implicitly declared in the FOR statement
• Cannot be changed
• Can be referenced
• And yes, you must actually type the two dots (..) between the lower_bound and upper_bound.

Instructor's Notes

(If applicable)

• Arrays (formally called “associative arrays” or “index-by tables”) are memory-based PL/SQL arrays, used to store lists of data in a PL/SQL program. This might be helpful for storing data that is repeatedly scanned – eliminating repetitive access to a database table. It can also be a useful structure for passing sets of data between PL/SQL programs (though a ref cursor may be better suited for that).
• Arrays are arrays of variables where the variable can be a scalar type, a variable defined with %TYPE or a PL/SQL record.
• Associative arrays are unbounded, meaning that they have is no limit to the number of elements in the array. (actually, the limit is -2,147,483,647 to +2,147,483,647, or 4.3 billion rows. However, we consider them to be unbounded because you’ll run out of memory before you will ever reach the limit.)
• Associate arrays are also considered sparse, in that they do not require a sequential number of rows. I.e. there can be gaps between element 1 and the second element; the second element might be 5.
• Associative arrays require an index. The index can be BINARY_INTEGER (a number) or, with Oracle 10g, a VARCHAR2 field.
• Arrays can be useful:
• load with table data and perform efficient lookups
• Passing data to another program

Instructor's Notes

(If applicable)

• Similar to a programmer-defined PL/SQL record, using an array is a two-step process. First, an array TYPE needs to be defined, then an instance of the array type is created, using the TYPE as the datatype.
• This example creates a simple array of VARCHAR2 fields called “array1”. Use an index when you reference an element in a PL/SQL table, for example “array1(v_index)”. A subscript can be a variable, an expression, a string (if the INDEX BY VARCHAR2 is specified in the TYPE declaration).
• This example uses three array methods: FIRST, COUNT and NEXT. These are functions that work on (i.e. provide information about or modify) the array. Turn to the next page for a description of these methods. (Note that in the Oracle 10g PL/SQL User’s Guide and Reference these are referred to as “collection methods”; arrays are one type of collection.)
• See the supplied script ARRAY.SQL for a working example of this code.

Instructor's Notes

(If applicable)

• These “methods” are Oracle-supplied procedures and functions that act on arrays. They are all invoked in one of the two ways:
• array.method if the method applies to the whole collection, e.g. array1.count.
• array.method(index) -- if it works on single element in the array, e.g. array1.delete(v_index).
• Here is a brief description of the methods. Refer to Chapter 5 of the Oracle 10g PL/SQL User’s Guide and Reference for a complete description.
• COUNT – returns the number of “used” entries in the collection. (For Varrays, that is equal to LAST)
• DELETE – Procedure to deletes a single row, all rows or a range of rows. When used without an argument, deletes all rows. Has several formats:
• array1.delete -- deletes all elements from the collection.
• array1.delete(4) -- deletes the fourth element.
• array1.delete(4,7) -- deletes elements 4 through 7.
• array1.delete(7,2) -- does nothing.
• array1.delete(-1) -- does nothing, not even raise an exception!
• If an element to be deleted is empty, DELETE does not raise an exception.
• EXISTS(n) – Function to determine to see if the element exists. Returns TRUE or FALSE.
• FIRST , LAST – Function that returns the index of the first row in a array.
• Notes continue on the next page…

Instructor's Notes

(If applicable)

• LAST – Function that returns the index to the last row in a array.
• NEXT - Returns the index of the next row in an array. Returns null when at last row of the array.
• PRIOR - Returns the index of the previous row in an array.
• TRIM – Procedure to remove elements at the end of a collection. Has two forms:
• array1.trim -- removes the last element from the array (nulls do count as valid elements).
• array1.trim(4) -- removes last 4 (including not null) elements from the end of the array. Calling with a number bigger than COUNT raises SUBSCRIPT_BEYOND_COUNT.

Instructor's Notes

(If applicable)

• Workshop – Arrays
• To practice your skills with PL/SQL arrays, write a PL/SQL procedure to do the following:
• Load a PL/SQL array with 100 hundred random values (use the DBMS_RANDOM.RANDOM() function). Use sequential numbers 1..100 for the index.
• Display the count of how many elements exist.
• Display the values of the first and last elements of the array.