D80186GC10
Oracle Database: Program with PL/SQL
Summary
Duration Vendor Audience
5 Days Oracle Developers, Technical Consultants, Database Administrators and System Analysts
Level Technology
Professional Oracle Database 12c
Delivery Method Training Credits / Vouchers Instructor-led (Classroom) Yes
Introduction
This five-day instructor-led course consists of PL/SQL Fundamentals (2 days) and PL/SQL Program Units (3 days) and introduces participants to the fundamentals of PL/SQL. In this course participants learn about PL/SQL syntax, blocks, programming constructs and about the advantages that PL/SQL provides. This course provides the essential skills that allow developers to write and execute PL/SQL program units efficiently, develop reusable program units such as procedures and functions, package constructs and database triggers. Participants will also learn how to use a subset of Oracle-supplied packages, identify various techniques that impact PL/SQL design considerations, and how to manage PL/SQL code effectively.
This course, applicable to Oracle Database 10g and 11g users, will teach participants how to make use of Oracle SQL Developer as the main tool (with SQL*Plus optionally available) for hands-on practice to reinforce the fundamental concepts and new skills acquired.
Prerequisites
Before attending this course, students must have:
• A thorough knowledge of the SQL language
• Work with SQL *Plus and SQL Developer or similar development environments.
Oracle requires and recommends that delegates have attended the following course prior to attending Oracle Database Program with PL/SQL.
• Required Course: D80174GC10 Oracle Database Introduction to SQL At Course completion
After completing this course, students will be able to:
• Identify the programming extensions that PL/SQL provides to SQL
• Write PL/SQL code to interface with the database
• Design PL/SQL anonymous blocks that execute efficiently
• Use PL/SQL programming constructs and conditional control statements
• Handle run-time errors
• Create procedures, functions, packages and database triggers
• Manage PL/SQL subprograms and triggers
• Use a subset of Oracle-supplied packages to generate screen and file output
• Identify various techniques that impact your PL/SQL code design considerations
• Use the PL/SQL compiler, manage PL/SQL code, and manage dependencies
Course Content
PL/SQL FUNDAMENTALS Lesson 1: Introduction to PL/SQL
This lesson introduces PL/SQL and the PL/SQL programming constructs.
It also explores the benefits of PL/SQL.
Lesson Topics:
• Explain the need for PL/SQL
• Explain the benefits of PL/SQL
• Identify the different types of PL/SQL blocks
• Output messages in PL/SQL
Practice:
• 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.
Lesson Topics:
• Recognize valid and invalid identifiers
• List the uses of variables
• Declare and initialize variables
• List and describe various data types
• Identify the benefits of using the %TYPE attribute
• Declare, use, and print bind variables
Practice:
• Determining valid identifiers
• Determining valid variable declarations
• Declaring variables within an anonymous block
• Using the %TYPE attribute to declare 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.
Lesson Topics:
• Identify lexical units in a PL/SQL block
• Use built-in SQL functions in PL/SQL
• Describe when implicit conversions take place and when explicit conversions have to be dealt with
• Write nested blocks and qualify variables with labels
• Write readable code with appropriate indentation
• Use sequences in PL/SQL expressions
Practice:
• 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.
Lesson Topics:
• Determine the SQL statements that can be directly included in a PL/SQL executable block
• Manipulate data with DML statements in PL/SQL
• Use transaction control statements in PL/SQL
• Make use of the INTO clause to hold the values returned by a SQL statement
• Differentiate between implicit cursors and explicit cursors
• Use SQL cursor attributes Practice:
• Selecting data from a table
• Inserting data into a table
• Updating data in a table
• Deleting a record from a table
Lesson 5: Writing Control Structures
This lesson explains the use of programming control structures such as IF statements, CASE expressions and LOOP structures within a PL/SQL block.
Lesson Topics:
• Identify the uses and types of control structures
• Construct an IF statement
• Use CASE statements and CASE expressions
• Construct and identify loop statements
• Use guidelines when using conditional control structures
Practice:
• Performing conditional actions by using IF 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.
Lesson Topics:
• Describe PL/SQL collections and records
• Create user-defined PL/SQL records
• Create a PL/SQL record with the %ROWTYPE attribute
• Create associative arrays o INDEX BY table o INDEX BY table of
records Practice:
• Declaring associative arrays
• Processing data by using associative arrays
• Declaring a PL/SQL record
• Processing data by using a PL/SQL record
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.
Lesson Topics:
• Distinguish between implicit and explicit cursors
• Discuss the reasons for using explicit cursors
• Declare and control explicit cursors
• Use simple loops and cursor FOR loops to fetch data
• Declare and use cursors with parameters
• Lock rows with the FOR UPDATE clause
• Reference the current row with the WHERE CURRENT OF clause Practice:
• Declaring and using explicit cursors to query rows of a table
• Using a cursor FOR loop
• Applying cursor attributes to test the cursor status
• Declaring and using cursors with parameters
• Using the FOR UPDATE and WHERE CURRENT OF clauses
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.
Lesson Topics:
• Define PL/SQL exceptions
• Recognize unhandled exceptions
• List and use different types of PL/SQL exception handlers
• Trap unanticipated errors
• Describe the effect of exception propagation in nested blocks
• Customize PL/SQL exception messages Practice:
• Creating and invoking user-defined exceptions
• Handling named Oracle Server exceptions
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.
Lesson Topics:
• Differentiate between anonymous blocks and subprograms
• Create a simple procedure and invoke it from an anonymous block
• Create a simple function
• Create a simple function that accepts a parameter
• Differentiate between procedures and functions Practice:
• Converting an existing anonymous block to a procedure
• 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.
Lesson Topics:
• Identify the benefits of modularized and layered subprogram design
• Create and call procedures
• Use formal and actual parameters
• Use positional, named, or mixed notation for passing parameters
• Identify the available parameter-passing modes
• Handle exceptions in procedures
• Remove a procedure
• Display the procedures’
information Practice:
• Creating stored procedures to
o Insert new rows into a table using the supplied parameter values
o Update data in a table for rows that match the supplied parameter values o Delete rows from a
table that match the supplied parameter values
o Query a table and retrieve data based on supplied parameter values
• Handling exceptions in procedures
• Compiling and invoking procedures
Lesson 2: Creating 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.
Lesson Topics:
• Differentiate between a procedure and a function
• Describe the uses of functions
• Create stored functions
• Invoke a function
• Remove a function
• Understand the basic functionality of the SQL Developer debugger Practice:
• Creating stored functions o To query a database
table and return specific values o To be used in a SQL
statement
o To insert a new row, with specified parameter values, into a database table o 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
o Inserting breakpoints in the procedure o Compiling the
procedure and function for debug mode
o Debugging the procedure and stepping into the code
o 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.
Lesson Topics:
• Describe packages and list their components
• Create a package to group together related variables, cursors, constants,
exceptions, procedures, and functions
• Designate a package construct as either public or private
• Invoke a package construct
• Describe the use of a bodiless package Practice:
• Creating packages
• 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.
Lesson Topics:
• Overload package procedures and functions
• Use forward declarations
• Create an initialization block in a package body
• Manage persistent package data states for the life of a session
• Use associative arrays (index-by tables) and records in packages Practice:
• Using overloaded subprograms
• 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.
Lesson Topics:
• Describe how the DBMS_OUTPUT package works
• Use UTL_FILE to direct output to operating system files
• Describe the main features of UTL_MAIL Practice:
• 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.
Lesson Topics:
• Describe the execution flow of SQL statements
• Build and execute SQL statements dynamically using Native Dynamic SQL (NDS)
• Identify situations when you must use the DBMS_SQL package instead of NDS to build and execute SQL statements dynamically Practice:
• Creating a package that uses Native Dynamic SQL to create or drop a table and to populate, modify, and delete rows from a table
• 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.
Lesson Topics:
• Create standard
constants and exceptions
• Write and call local subprograms
• Control the run-time privileges of a subprogram
• Perform autonomous transactions
• Pass parameters by reference using a NOCOPY hint
• Use the PARALLEL ENABLE hint for optimization
• Use the cross-session PL/SQL function result cache
• Use the DETERMINISTIC clause with functions
• Use the RETURNING clause and bulk binding with DML
Practice:
• Creating a package that uses bulk fetch
operations
• 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.
Lesson Topics:
• Describe database triggers and their uses
• Describe the different types of triggers
• Create database triggers
• Describe database trigger-firing rules
• Remove database triggers
• Display trigger information Practice:
• Creating row triggers
• Creating a statement trigger
• 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.
Lesson Topics:
• Describe compound triggers
• Describe mutating tables
• Create triggers on DDL statements
• Create triggers on system events
• Display information about triggers Practice:
• Creating advanced triggers to manage data integrity rules
• Creating triggers that cause a mutating table exception
• Creating triggers that use package state to solve the mutating table problem
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.
Lesson Topics:
• Use the PL/SQL compiler initialization parameters
• Use the PL/SQL compile- time warnings
Practice:
• 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.
Lesson Topics:
• Describe and use conditional compilation
• Hide PL/SQL source code using dynamic
obfuscation and the Wrap utility Practice:
• 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.
Lesson Topics:
• Track procedural dependencies
• Predict the effect of changing a database object on procedures and functions
• Manage procedural dependencies Practice:
• Using DEPTREE_FILL and IDEPTREE to view dependencies
• Recompiling procedures, functions, and packages
Associated Certifications & Exam This course prepares students to write Exam(s):
1Z0-144 Oracle Database 11g Program with PL/SQL 1Z0-147 Oracle Database Program with PL/SQL Accredited to certification(s):
Oracle PL/SQL Developer Certified Associate