| PL/SQL Fundamentals Lesson 1: Introduction
This lesson introduces PL/SQL and the PL/SQL programming constructs. It also explores the benefits of PL/SQL.
Lessons: |
| · Explain the need for PL/SQL |
· Output messages in PL/SQL |
| · Explain the benefits of PL/SQL |
Lab: |
| · Identify the different types of PL/SQL blocks |
· Identifying different PL/SQL blocks that will execute successfully |
|
· Creating and executing a simple PL/SQL block |
| Lesson 2: Declaring PL/SQL Variables
This lesson describes what valid and invalid identifiers are. It also explores the different ways of declaring and initializing variables, the different PL/SQL data types available and the use of the %TYPE attribute.
Lessons: |
| · Recognize valid and invalid identifiers |
Lab: |
| · List the uses of variables |
· Determining valid identifiers |
| · Declare and initialize variables |
· Determining valid variable declarations |
| · List and describe various data types |
· Declaring variables within an anonymous block |
| · Identify the benefits of using the %TYPE attribute |
· Using the %TYPE attribute to declare variables |
| · Declare, use, and print bind variables |
· Declaring and printing a bind variable |
|
· Executing a PL/SQL block |
| Lesson 3: Writing Executable Statements
This lesson explores the nature of lexical units within a PL/SQL block. It also demonstrates how to write nested blocks and how this affects a variable’s scope and visibility.
Lessons: |
| · Identify lexical units in a PL/SQL block |
· Write readable code with appropriate indentation |
| · Use built-in SQL functions in PL/SQL |
· Use sequences in PL/SQL expressions |
| · Describe when implicit conversions take place and when explicit conversions have to be dealt with |
Lab: |
| · Write nested blocks and qualify variables with labels |
· Reviewing scoping and nesting rules |
|
· Writing and testing PL/SQL blocks |
| Lesson 4: Interacting with Oracle Database Server: SQL Statements in PL/SQL Programs
This lesson describes how to embed standard SQL SELECT, DML, DDL and TCL statements within PL/SQL blocks. In also describes the need for cursors, the types of cursors available and the SQL cursor attributes that can be used for implicit cursors.
Lessons: |
| · Determine the SQL statements that can be directly included in a PL/SQL executable block |
· Use SQL cursor attributes |
| · Manipulate data with DML statements in PL/SQL |
Lab: |
| · Use transaction control statements in PL/SQL |
· Selecting data from a table |
| · Make use of the INTO clause to hold the values returned by a SQL statement |
· Inserting data into a table |
| · Differentiate between implicit cursors and explicit cursors |
· Updating data in a table |
|
· Deleting a record from a table |
| Lesson 5: Writing Control Structures This lesson
This lesson explains the use of programming control structures such as IF statements, CASE expressions and LOOP structures within a PL/SQL block.
Lessons: |
| · Identify the uses and types of control structures: |
· Use guidelines when using conditional control structures |
| · Construct an IF statement |
Lab: |
| · Use CASE statements and CASE expressions |
· Performing conditional actions by using IF statements |
| · Construct and identify loop statements |
· Performing iterative steps by using LOOP structures |
| Lesson 6: Working with Composite Data Types
This lesson describes the nature and usage of composite data types within PL/SQL. Participants will learn various techniques for creating PL/SQL records and collections.
Lessons: |
| · Describe PL/SQL collections and records |
Lab: |
| · Create user-defined PL/SQL records |
· Declaring associative arrays |
| · Create a PL/SQL record with the %ROWTYPE attribute |
· Processing data by using associative arrays |
| · Create associative arrays |
· Declaring a PL/SQL record |
| · INDEX BY table |
· Processing data by using a PL/SQL record |
| · INDEX BY table of records |
|
| Lesson 7: Using Explicit Cursors
This lesson demonstrates the use of explicit cursors and their attributes within PL/SQL. Participants will also learn how to differentiate between explicit and implicit cursors as well as using cursors with parameters.
Lessons: |
| · Distinguish between implicit and explicit cursors |
Lab: |
| · Discuss the reasons for using explicit cursors |
· Declaring and using explicit cursors to query rows of a table |
| · Declare and control explicit cursors |
· Using a cursor FOR loop |
| · Use simple loops and cursor FOR loops to fetch data |
· Applying cursor attributes to test the cursor status |
| · Declare and use cursors with parameters |
· Declaring and using cursors with parameters |
| · Lock rows with the FOR UPDATE clause |
· Using the FOR UPDATE and WHERE CURRENT OF clauses |
| · Reference the current row with the WHERE CURRENT OF clause |
|
| Lesson 8: Handling Exceptions
This lesson describes the different types of errors or exceptions that can occur during the execution of a PL/SQL block, and the different ways of dealing with these exceptions.
Lessons |
| · Define PL/SQL exceptions |
· Customize PL/SQL exception messages |
| · Recognize unhandled exceptions |
Lab: |
| · List and use different types of PL/SQL exception handlers |
· Creating and invoking user-defined exceptions |
| · Trap unanticipated errors |
· Handling named Oracle Server exceptions |
| · Describe the effect of exception propagation in nested blocks |
|
| Lesson 9: Introducing Stored Procedures and Functions
This lesson introduces participants to named PL/SQL blocks, also called subprograms. It describes the differences and implementation of two types of subprograms namely procedures and functions. Lessons: |
| · Differentiate between anonymous blocks and subprograms |
· Differentiate between procedures and functions |
| · Create a simple procedure and invoke it from an anonymous block |
Lab: |
| · Create a simple function |
· Converting an existing anonymous block to a procedure |
| · Create a simple function that accepts a parameter |
· Modifying the procedure to accept a parameter |
|
· Writing an anonymous block to invoke the procedure |
| Develop PL/SQL Program Units Lesson 1: Creating Procedures
This lesson demonstrates how to create, execute and remove procedures with or without parameters.
Lessons: |
| · Identify the benefits of modularized and layered subprogram design |
Lab: |
| · Create and call procedures |
· Creating stored procedures to |
| · Use formal and actual parameters |
· Insert new rows into a table using the supplied parameter values |
| · Use positional, named, or mixed notation for passing parameters |
· Update data in a table for rows that match the supplied parameter values |
| · Identify the available parameter-passing modes |
· Delete rows from a table that match the supplied parameter values |
| · Handle exceptions in procedures |
· Query a table and retrieve data based on supplied parameter values |
| · Remove a procedure |
· Handling exceptions in procedures |
| · Display the procedures’ information |
· Compiling and invoking procedures |
| Lesson 2: Creating Procedures, Functions and Debugging Subprograms
This lesson demonstrates how to create, invoke and maintain functions. In addition it also explores the basic functionality of the SQL Developer debugger.
Lessons: |
| · Differentiate between a procedure and a function |
Lab: |
| · Describe the uses of functions |
· Creating stored functions |
| · Create stored functions |
· To query a database table and return specific values |
| · Invoke a function |
· To be used in a SQL statement |
| · Remove a function |
· To insert a new row, with specified parameter values, into a database table |
| · Understand the basic functionality of the SQL Developer debugger |
· Using default parameter values |
|
· Invoking a stored function from a SQL statement |
|
· Invoking a stored function from a stored procedure |
|
· Use the SQL Developer debugger to |
|
· Inserting breakpoints in the procedure |
|
· Compiling the procedure and function for debug mode |
|
· Debugging the procedure and stepping into the code |
|
· Displaying and modifying the subprograms’ variables |
| Lesson 3: Creating Packages
This lesson describes what a PL/SQL package is and what its components are. Participants will learn how to create and use PL/SQL packages.
Lessons: |
| · Describe packages and list their components |
· Describe the use of a bodiless package |
| · Create a package to group together related variables, cursors, constants, exceptions, procedures, and functions |
Lab: |
| · Designate a package construct as either public or private |
· Creating packages |
| · Invoke a package construct |
· Invoking package program units |
| Lesson 4: Working with Packages
This lesson introduces the more advanced features of PL/SQL packages including overloading, forward referencing, one-time only procedures, and the persistency of variables, constants, exceptions, and cursors. It also explains the effect of packaging functions that are used in SQL statements.
Lessons: |
| · Overload package procedures and functions |
· Use associative arrays (index-by tables) and records in packages |
| · Use forward declarations |
Lab: |
| · Create an initialization block in a package body |
· Using overloaded subprograms |
| · Manage persistent package data states for the life of a session |
· Creating a package initialization block |
|
· Using a forward declaration |
| Lesson 5: Using Oracle-Supplied Packages in Application Development
This lesson demonstrates the use of some of the Oracle-supplied packages and their capabilities. Lessons: |
| · Describe how the DBMS_OUTPUT package works |
· Describe the main features of UTL_MAIL |
| · Use UTL_FILE to direct output to operating system files |
Lab: |
|
· Make use of the supplied UTL_FILE package to generate a text report |
| Lesson 6: Using Dynamic SQL
This lesson explains how to construct and execute SQL statements dynamically through the implementation of Native Dynamic SQL within PL/SQL.
Lessons |
| · Describe the execution flow of SQL statements |
Lab: |
| · Build and execute SQL statements dynamically using Native Dynamic SQL (NDS) |
· Creating a package that uses Native Dynamic SQL to create or drop a table and to populate, modify, and delete rows from a table |
| · Identify situations when you must use the DBMS_SQL package instead of NDS to build and execute SQL statements dynamically |
· Creating a package that compiles the PL/SQL code in your schema |
| Lesson 7: Design Considerations for PL/SQL Code
This lesson explains how to create standard constants and exceptions, write and call local subprograms, use compiler directives to control the run-time privileges of PL/SQL code and create autonomous transactions. It also covers some performance considerations that can be applied to PL/SQL applications through the use of compiler hints.
Lessons: |
|
|
| · Create standard constants and exceptions |
· Use the cross-session PL/SQL function result cache |
| · Write and call local subprograms |
· Use the DETERMINISTIC clause with functions |
| · Control the run-time privileges of a subprogram |
· Use the RETURNING clause and bulk binding with DML |
| · Perform autonomous transactions |
Lab: |
| · Pass parameters by reference using a NOCOPY hint |
· Creating a package that uses bulk fetch operations |
| · Use the PARALLEL ENABLE hint for optimization |
· Creating a local subprogram to perform an autonomous transaction to audit a business operation |
| Lesson 8: Creating Triggers
This lesson describes Oracle database triggers and how to create and use them effectively.
Lessons: |
| · Describe database triggers and their uses |
· Display trigger information |
| · Describe the different types of triggers |
Lab: |
| · Create database triggers |
· Creating row triggers |
| · Describe database triggerfiring rules |
· Creating a statement trigger |
| · Remove database triggers |
· Calling procedures from a trigger |
| Lesson 9: Creating Compound, DDL, and Event Database Triggers
This lesson demonstrates the creation of compound triggers, triggers for DDL statements and triggers for system events. It also explores the nature of table mutation within the Oracle database.
Lessons: |
| · Describe compound triggers |
Lab: |
| · Describe mutating tables |
· Creating advanced triggers to manage data integrity rules |
| · Create triggers on DDL statements |
· Creating triggers that cause a mutating table exception |
| · Create triggers on system events |
· Creating triggers that use package state to solve the mutating table problem |
| · Display information about triggers |
|
| Lesson 10: Using the PL/SQL Compiler
This lesson explains the use and effects of PL/SQL compiler initialisation parameters to optimize code performance. It also demonstrates the use of PL/SQL compile-time warnings.
Lessons: |
| · Use the PL/SQL compiler initialization parameters |
Lab: |
| · Use the PL/SQL compiletime warnings |
· Displaying the compiler initialization parameters |
|
· Enabling native compilation for your session and compiling a procedure |
|
· Disabling the compiler warnings, and then restoring the original session-warning settings |
|
· Identifying the categories for some compiler-warning message numbers |
| Lesson 11: Managing PL/SQL Code
This lesson introduces the concept of conditional compilation of PL/SQL code. It also demonstrates how to hide and secure PL/SQL source code by using dynamic obfuscation and the Wrap utility. Lessons: |
| · Describe and use conditional compilation |
Lab: |
| · Hide PL/SQL source code using dynamic obfuscation and the Wrap utility |
· Creating a package and a procedure that uses conditional compilation |
|
· Using the appropriate package to retrieve the post-processed source text of the PL/SQL unit |
|
· Obfuscating some PL/SQL code |
| Lesson 12: Managing Dependencies
This lesson describes object dependencies and the effect it has on database and PL/SQL program performance. It also explores the nature and tasks involved in implicit and explicit recompilation of invalid objects.
Lessons: |
| · Track procedural dependencies |
Lab: |
| · Predict the effect of changing a database object on procedures and functions |
· Using DEPTREE_FILL and IDEPTREE to view dependencies |
| · Manage procedural dependencies |
· Recompiling procedures, functions, and packages |
