Oracle PL/SQL Programming - Intermediate Level

Oracle PL/SQL Programming - Intermediate Level

This course in the midway level to learn about PL/SQL programming for Oracle databases. It provides more aspects to write PL/SQL block of codes in a more powerful protocols.

Course Outline

  • Working with Composite Data Types
  • Create user-defined PL/SQL records
  • Create a record with the %ROWTYPE attribute
  • Create an INDEX BY table and INDEX BY table of records
  • Describe the differences among records, collections, and collections of records
  • Initialize collections and records

 

Using Explicit Cursors

  • Distinguish between implicit and explicit cursors and use SQL cursor attributes
  • 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 and reference the current row with the WHERE CURRENT OF clause

 

Subprograms

  • Differentiate between anonymous blocks and subprograms
  • Create a simple procedure and invoke it from an anonymous block
  • Identify benefits of subprograms

 

Creating Procedures

  • Create a procedure with parameters
  • Use named notation
  • Work with procedures (create, invoke and remove procedures)
  • Handle exceptions in procedures and display a procedure's information

 

Creating Functions

  • Differentiate between a procedure and a function
  • Describe the uses of functions
  • Work with functions (create, invoke and remove functions)

 

Creating Packages

  • Identify the benefits and the components of packages
  • Work with packages (create package specification and body, invoke package subprograms, remove a package and display package information)
  • Overload package subprograms and use forward declarations

 

Working with Packages

  • Use package types and variables
  • Use packaged constants and functions in SQL
  • Use ACCESSIBLE BY to restrict access to package subprograms

 

Using Dynamic SQL

  • Describe the execution flow of SQL statements
  • Use Native Dynamic SQL (NDS)
  • Bind PL/SQL types in SQL statements

 

Design Considerations for PL/SQL Code

  • Create standard constants and exceptions
  • Write and call local subprograms
  • Control the run-time privileges of a subprogram
  • Perform autonomous transactions
  • Use NOCOPY hint, PARALLEL ENABLE hint and DETERMINISTIC clause
  • Use bulk binding and the RETURNING clause with DML

Course Enquiry