Chapter One: Introduction to PL/SQL

1. PL/SQL:
 Stands for “Procedural Language extension to SQL”
 Is Oracle Corporation’s standard data access language for relational databases
 Seamlessly integrates procedural constructs with SQL
 Provides a block structure for executable units of code.
 Maintenance of code is made easier with such a well-defined structure.
 Provides procedural constructs such as:
 Variables, constants, and data types
 Control structures such as conditional statements and loops
 Reusable program units that are written once and executed many times
2. Benefits of PL/SQL
 Integration of procedural constructs with SQL
 Improved performance
 Modularized program development
 Integration with Oracle tools
 Portability
 Exception handling
3. PL/SQL Block Structure  BEGIN (mandatory)
 DECLARE (optional)  SQL statements
 Variables, cursors,  PL/SQL statements
 user-defined exceptions  EXCEPTION (optional)
 exceptions occur  Actions to perform when exceptions
 END; (mandatory) occur
4. Creating Procedure Creating Function Creating Anonymous
PROCEDURE name FUNCTION name [DECLARE]
IS RETURN data type
BEGIN
IS
BEGIN --statements
BEGIN
--statements --statements [EXCEPTION]
[EXCEPTION] RETURN value; END;
END; [EXCEPTION]
END;
5. To enable output in SQL Developer, execute the following command before running the PL/SQL
block:
 SET SERVEROUTPUT ON
6. Use a predefined Oracle package and its procedure in the anonymous block:
 DBMS_OUTPUT.PUT_LINE
 Chapter Two:
1. Variables can be used for:
 Temporary storage of data
 Manipulation of stored values
 Reusability
2. A variable name:
 Must start with a letter
 Can include letters or numbers
 Can include special characters (such as $, _, and #)
 Must contain no more than 30 characters
 Must not include reserved words
3. Variables are:
 Declared and (optionally) initialized in the declarative section
 Used and assigned new values in the executable section
 Passed as parameters to PL/SQL subprograms
 Used to hold the output of a PL/SQL subprogram
4. Syntax of Variable are:
 identifier [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT expr];
5. Types of Variables:
 PL/SQL variables:
 Scalar
 Reference
 Large object (LOB)
 Composite
 Non-PL/SQL variables:
 Bind variables
6. Guidelines for Declaring and Initializing PL/SQL Variables
 Follow consistent naming conventions.
 Use meaningful identifiers for variables.
 Initialize variables that are designated as NOT NULL and CONSTANT.
 Initialize variables with the assignment operator (:=) or the DEFAULT keyword:
 Declare one identifier per line for better readability and code maintenance.
 Avoid using column names as identifiers.
 Use the NOT NULL constraint when the variable must hold a value.
7. Scalar Data Types
 Hold a single value
 Have no internal components
8. Base Scalar Data Types
 CHAR [(maximum_length)]  BINARY_DOUBLE
 VARCHAR2 (maximum_length)  DATE
 NUMBER [(precision, scale)]  TIMESTAMP
 BINARY_INTEGER  TIMESTAMP WITH TIME ZONE
 PLS_INTEGER  TIMESTAMP WITH LOCAL TIME ZONE
 BOOLEAN  INTERVAL YEAR TO MONTH
 BINARY_FLOAT  INTERVAL DAY TO SECOND
9. %TYPE Attribute
 Is used to declare a variable according to:
 A database column definition
 Another declared variable
 Is prefixed with:
 The database table and column name
 The name of the declared variable
[Link] of %TYPE Attribute
 identifier table.column_name%TYPE;
[Link] Boolean Variables
 Only the TRUE, FALSE, and NULL values can be assigned to a Boolean variable.
 Conditional expressions use the logical operators AND and OR, and the unary operator NOT to
check the variable values.
 The variables always yield TRUE, FALSE, or NULL.
 Arithmetic, character, and date expressions can be used to return a Boolean value.
[Link] variables are:
 Created in the environment
 Also called host variables
 Created with the VARIABLE keyword*
 Used in SQL statements and PL/SQL blocks
 Accessed even after the PL/SQL block is executed
 Referenced with a preceding colon Values can be output using the PRINT command.
 Required when using SQL*Plus and SQL Developer
 Chapter 3: Writing Executable Statements Lexical units:
1. Lexical units:
 Are building blocks of any PL/SQL block
 Are sequences of characters including letters, numerals, tabs, spaces, returns, and
symbols
 Can be classified as:
 Identifiers: v_fname, c_percent
 Delimiters: ; , +, -
 Literals: John, 428, True
 Comments: --, /* */
2. Using Literals
 Character and date literals must be enclosed in single quotation marks.
 Numbers can be simple values or in scientific notation.
 Formatting Code: Statements can span several lines.
3. Commenting Code
 Prefix single-line comments with two hyphens (--).
 Place a block comment between the symbols /* and */.
4. SQL Functions in PL/SQL
 Available in procedural statements:
 Single-row functions
 Not available in procedural statements:
 DECODE
 Group functions
5. Data Type Conversion
 Converts data to comparable data types
 Is of two types:
 Implicit conversion
 Explicit conversion
 Functions:
 TO_CHAR
 TO_DATE
 TO_NUMBER
 TO_TIMESTAMP
6. PL/SQL blocks can be nested.
 An executable section (BEGIN … END) can contain nested blocks.
 An exception section can contain
nested blocks.
7. Operators in PL/SQL
 Logical
 Arithmetic Same as in SQL
 Concatenation
  Parentheses to control order of operations
 Exponential operator (**)
8. Programming Guidelines
 Make code maintenance easier by:
 Documenting code with comments
 Developing a case convention for the code
 Developing naming conventions for identifiers and other objects
 Enhancing readability by indenting

Chapter 4: Interacting with Oracle Database Server:

SQL Statements in PL/SQL Programs

1. Retrieve a row from the database by using the SELECT command.

2. Make changes to rows in the database by using DML commands.
3. Control a transaction with the COMMIT, ROLLBACK, or SAVEPOINT command.
4. SELECT Statements in PL/SQL
 Retrieve data from the database with a SELECT statement.
Syntax:
SELECT select_list
INTO {variable_name[, variable_name]...
| record_name}
FROM table
[WHERE condition];
 The INTO clause is required.
 Queries must return only one row.
5. Naming Conventions
 Use a naming convention to avoid ambiguity in the WHERE clause.
 Avoid using database column names as identifiers.
 Syntax errors can arise because PL/SQL checks the database first for a column in the table.
 The names of local variables and formal parameters take precedence over the names of
database tables.
 The names of database table columns take precedence over the names of local variables.
6. Using PL/SQL to Manipulate Data Make changes to database tables by using DML
commands:
 INSERT
 UPDATE
 DELETE
 MERGE
7. A cursor: is a pointer to the private memory area allocated by the Oracle Server. It is used to
handle the result set of a SELECT statement.
8. There are two types of cursors: implicit and explicit.
  Implicit: Created and managed internally by the Oracle Server to process SQL statements
 Explicit: Declared explicitly by the programmer
9. Using SQL cursor attributes, you can test the outcome of your SQL statements.

SQL%FOUND Boolean attribute that evaluates to TRUE if the most recent SQL statement
affected at least one row

SQL%NOTFOUND Boolean attribute that evaluates to TRUE if the most recent SQL statement did
not affect even one row

SQL%ROWCOUNT An integer value that represents the number of rows affected by the most
recent SQL statement

[Link] DML statements, transaction control statements, and DDL statements in PL/SQL
[Link] the INTO clause, which is mandatory for all SELECT statements in PL/SQL
[Link] between implicit cursors and explicit cursors
[Link] SQL cursor attributes to determine the outcome of SQL statements
Chapter 5: Writing Control Structures
1. Syntax of IF Statement
IF condition THEN
Statements;
[ELSIF condition THEN
Statements;]
[ELSE
Statements;]
END IF;
2. CASE Expressions
 A CASE expression selects a result and returns it.
 To select the result, the CASE expression uses expressions. The value returned by these
expressions is used to select one of several alternatives.
3. When you are working with nulls, you can avoid some common mistakes by keeping in mind
the following rules:
 Simple comparisons involving nulls always yield NULL.
 Applying the logical operator NOT to a null yields NULL.
 If the condition yields NULL in conditional control statements, its associated sequence of
statements is not executed.
4. LOOP Statements
 Loops repeat a statement (or a sequence of statements) multiple times.
 There are three loop types:
 Basic loop
 FOR loop
  WHILE loop

5. Syntax: of Basic Loops

LOOP
statement1;
EXIT [WHEN condition];
END LOOP;
6. Syntax of WHILE Loops
WHILE condition LOOP
statement1;
statement2;
END LOOP;
7. Use the WHILE loop to repeat statements while a condition is TRUE.
8. FOR Loops
 Use a FOR loop to shortcut the test for the number of iterations.
 Do not declare the counter; it is declared implicitly.
9. Syntax of FOR Loops
FOR counter IN [REVERSE]
lower_bound..upper_bound LOOP
statement1;
statement2;
END LOOP;
[Link] Loop Rules
 Reference the counter only within the loop; it is undefined outside the loop.
 Do not reference the counter as the target of an assignment.
 Neither loop bound should be NULL.
[Link] Use of Loops
 Use the basic loop when the statements inside the loop must execute at least once.
 Use the WHILE loop if the condition must be evaluated at the start of each iteration.
 Use a FOR loop if the number of iterations is known.
[Link] Loops and Labels
 You can nest loops to multiple levels.
 Use labels to distinguish between blocks and loops.
 Exit the outer loop with the EXIT statement that references the label.
[Link]/SQL CONTINUE Statement
[Link]
 Adds the functionality to begin the next loop iteration
 Provides programmers with the ability to transfer control to the next iteration of a loop
 Uses parallel structure and semantics to the EXIT statement
[Link]
 Eases the programming process
  May provide a small performance improvement over the previous programming
workarounds to simulate the CONTINUE statement
Chapter Six: Working with Composite Data Types
1. Composite Data Types
 Can hold multiple values (unlike scalar types)
 Are of two types:
 PL/SQL records
 PL/SQL collections
 Associative array (INDEX BY table)
 Nested table
 VARRAY
2. Use PL/SQL records when you want to store values of different data types but only one
occurrence at a time.
3. Use PL/SQL collections when you want to store values of the same data type.
4. PL/SQL Records
 Must contain one or more components (called fields) of any scalar, RECORD, or INDEX BY
table data type
 Are similar to structures in most third-generation languages (including C and C++)
 Are user-defined and can be a subset of a row in a table
 Treat a collection of fields as a logical unit
 Are convenient for fetching a row of data from a table for processing
5. %ROWTYPE Attribute
 Declare a variable according to a collection of columns in a database table or view.
 Prefix %ROWTYPE with the database table or view.
 Fields in the record take their names and data types from the columns of the table or
view.
 Syntax:
DECLARE
identifier reference%ROWTYPE;
6. Advantages of Using the %ROWTYPE Attribute
 The number and data types of the underlying database columns need not be known—
and, in fact, might change at run time.
 The %ROWTYPE attribute is useful when you want to retrieve a row with:
 The SELECT * statement
 Row-level INSERT and UPDATE statements
7. An associative array is a PL/SQL collection with two columns:
 Primary key of integer or string data type
 Column of scalar or record data type
8. Syntax of Associative Array :
TYPE type_name IS TABLE OF
 {column_type | variable%TYPE
[Link]%TYPE} [NOT NULL]
Table%ROWTYPE
INDEX BY PLS_INTEGER | BINARY_INTEGER
VARCHAR2 (<size>);
Identifier type_name;
The following methods make associative arrays easier to use:
• EXISTS • PRIOR
• COUNT • NEXT
• FIRST • DELETE
• LAST

Chapter 8: Handling Exceptions

1. An exception is a PL/SQL error that is raised during program execution.
2. An exception can be raised:
 Implicitly by the Oracle Server
 Explicitly by the program
3. An exception can be handled:
 By trapping it with a handler
 By propagating it to the calling environment
4. Exception Types
• Predefined Oracle Server Implicitly raised
• Non-predefined Oracle Server
Explicitly raised
• User-defined

5. Guidelines for Trapping Exceptions

 The EXCEPTION keyword starts the exception-handling section.
 Several exception handlers are allowed.
 Only one handler is processed before leaving the block.
 WHEN OTHERS is the last clause.
6. To trap Oracle Server error 01400 (“cannot insert NULL”):
7. SQLCODE: Returns the numeric value for the error code
8. SQLERRM: Returns the message associated with the error number
9. Trapping User-Defined Exceptions

Declare Raise Reference

Declarative Executable Exception-handling

section section section

Name the Explicitly raise the Handle the raised exception.

exception. exception by using
the RAISE statement.
[Link] can handle an exception or pass the exception to the enclosing block.
11.RAISE_APPLICATION_ERROR Procedure
 Is used in two different places:
 Executable section
 Exception section
 Returns error conditions to the user in a manner consistent with other Oracle Server
errors
[Link] different types of exceptions:
[Link] exceptions
[Link]-predefined exceptions
[Link]-defined exceptions
[Link] exceptions in nested blocks and call applications
17.