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