Opleiding: Oracle PL-SQL Programming

PL-SQL Intro

The course Oracle PL/SQL Programming starts with a discussion of the presence
of PL-SQL in the various Oracle tools. The advantages of using PL-SQL are explained.

PL-SQL Syntax

Attention is then paid to the syntax of PL-SQL, the structure of PL-SQL programs and to statements, declarations and expressions.

Control Flow

Control flow in PL-SQL with if else and case statements and the various loop constructions are also covered.

Cursors

Then the course proceeds with explaining what cursors are and how they are used when accessing the database. Implicit cursors and cursor attributes are then treated.

Transactions

Attention is also paid to PL-SQL transactions. This involves executing or reversing an atomic set of SQL data manipulation statements in their entirety using commit or rollback.

Error Handling

Furthermore error and exception handling in PL-SQL are covered. Allowing exceptions and error parameters to occur are part of the course.

Procedures and Functions

Some more advanced topics such as the creation and calling of local and stored procedures are discussed as well.

Packages

The use of packages that group related PL-SQL types, variables and subprograms is also on the program of the course Oracle PL/SQL Programming.

Triggers

Finally attention is paid to triggers. Triggers are programs that are automatically fired when certain events occur.

Audience Oracle PL-SQL Programming Course

The course Oracle PL-SQL Programming is designed for developers who want to use PL-SQL in applications that access an Oracle database.

Prerequisites Course Oracle PL-SQL Programming

In order to participate in the course Oracle PL-SQL Programming knowledge of and experience with the SQL query language is required for this course. Experience with programming in a procedural programming language is beneficial to the understanding.

Realization Training Oracle PL-SQL Programming

The theory is treated on the basis of presentation slides. Demos are used to explain the theory. There is ample opportunity to practice and theory and exercises are alternated.

Certificate Oracle PL-SQL Programming

Attendants receive a certificate of participation in Oracle PL-SQL Programming after successful completion of the course.

Modules

Module 1 : PL-SQL Intro

• What is PL-SQL
• Strenght of PL/SQL
• Basic Concepts
• Procedural possibilities
• Enhanced performance
• Oracle Suppport tools
• Oracle Specifics
• SQL Developer

Module 2 : PL-SQL Syntax

• PL-SQL Structure
• Statements
• Declarations
• Assignments
• %TYPE and %ROWTYPE
• Expressions
• Comments
• NULL

Module 3 : Control Flow

• IF-THEN-ELSE statement
• CASE statements
• GOTO
• WHILE loops
• FOR loops with index
• FOR loops with cursor
• Infinite loops
• Nested blocks

Module 4 : Cursors

• Implicit cursor
• Query with SELECT INTO
• Query with cursor
• OPEN-FETCH-CLOSE
• FOR loops with cursor
• Cursor attributes

Module 5 : Transactions

• Transaction in PL-SQL
• Queries for Changes
• Autonomous Transactions
• Locking
• Read Consistency
• Self Defined Records

Module 6 : Error Handling

• Exceptions
• RAISE
• EXCEPTION_INIT
• SQLCODE
• SQLERRM
• Parameters

Module 7 : Procedures and functions

• Local versus stored procedures
• Parameters
• DEFAULT values
• Removing procedures
• Local and stored functions
• Functions in SQL
• Dependencies
• Recompilation
• Remote dependencies

Module 8 : Packages

• Benefits of Packages
• Package specification
• Package body
• Calling a function in SQL
• RAISE_APPLICATION_ERROR
• DESCRIBE
• Removing Packages
• NDS Packages
• Standard packages for SQL

Module 9 : Triggers

• DML-triggers
• Statement triggers
• Row triggers
• Trigger Limitations
• Data integrity
• Cascading triggers
• System Event en DDL-triggers
• Triggers on user logon and logoff
• Shutdown and startup triggers