Oracle Academy: Database Programming with PL/SQL (Online)

PrerequisitesOnline£

This is a fee-based online course for Oracle: Database Programming with PL/SQL. If you are unfamiliar with the Oracle online course environment it is strongly recommended that you attend the Oracle Academy: Database Design & SQL, and Database Programming in PL/SQL Induction to the Online Environment course.

This course covers Oracle's PL/SQL, a procedural language extension to SQL. You will learn procedural logic constructs such as variables, constraints, conditional statements and iterative controls.

This online course prepares you toward the external exams:

• Oracle Database 11g: Program with PL/SQL Exam Number: 1Z0-144 Read more about this exam and its associated fee.

• Program with PL/SQL Exam Number: 1Z0-147 Read more about this exam and its associated fee.

• All current Cambridge University members (departments and colleges)
• Further details regarding eligibility criteria are available

Previous experience with at least one programming language.

• Fundamentals
  • Introduction to PL/SQL
  • Benefits of PL/SQL
  • Creating PL/SQL blocks

• Defining Variables and Data types
  • Using variables in PL/SQL
  • Recognizing PL/SQL lexical units
  • Recognizing data types
  • Using scalar data types
  • Writing PL/SQL executable statements
  • Nested blocks and variable scope
  • Good programming practices

• Using SQL in PL/SQL
  • Review of SQL DML
  • Retrieving data in PL/SQL
  • Manipulating data in PL/SQL
  • Using transaction control statements

• Program Structures to Control Execution Flow
  • Conditional control: IF statements
  • Conditional control: CASE statements
  • Iterative control: basic loops
  • Iterative control: WHILE and FOR loops
  • Iterative control: nested loops

• Using Cursors and Parameters
  • Introduction to explicit cursors
  • Using explicit cursor attributes
  • Cursor FOR loops
  • Cursors with parameters
  • Using cursors for UPDATE
  • Using multiple cursors

• Using Composite Data types
  • User-defined records
  • Indexing tables of records

• Exception Handling
  • Handling exceptions
  • Trapping Oracle server exceptions
  • Trapping user-defined exceptions
  • Recognizing the scope of exceptions

• Using and Managing Procedures
  • Creating procedures
  • Using parameters in procedures
  • Passing parameters

• Using and Managing Functions
  • Creating functions
  • Using functions in SQL statements
  • Review of the data dictionary
  • Managing procedures and functions
  • Review of object privileges
  • Using invoker's rights and autonomous transaction

• Using and Managing Packages
  • Creating packages
  • Managing package concepts
  • Advanced package concepts

• Getting the Best out of Packages
  • Persistent state of package variables
  • Using Oracle-supplied packages

• Improving PL/SQL Performance
  • Using dynamic SQL
  • Improving PL/SQL performance

• Using and Managing Triggers
  • Introduction to triggers
  • Creating DML triggers, part I
  • Creating DML triggers, part II
  • Creating DDL and database event triggers
  • Managing triggers

• Recognizing and Managing Dependencies
  • Introduction to dependencies
  • Understanding remote dependencies

• Using the PL/SQL Compiler
  • Using PL/SQL initialization parameters
  • Displaying compiler warning messages
  • Using conditional compilation
  • Hiding your source code

• Online access to the course £50 fee for University departments. Colleges and private payments from University members are charged + VAT = £60

• There is an external exam fee for those who wish to take it
• The exam can be booked and taken through a Pearson Authorised exam centre

180 hours study. The course should be completed by 31st August when Oracle expires existing student accounts.

On-going

• MySQL: Implementing a Relational Database Design