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