-
Learning by doing
-
Trainers with practical experience
-
Classroom training
-
Detailed course material
-
Clear content description
-
Tailormade content possible
-
Training that proceeds
-
Small groups
In the course PL-SQL Programming participants learn to program in PL-SQL, the procedural programming language from Oracle. PL-SQL fits in with the query language SQL, but extends the capabilities of SQL and improves performance.
First the position of PL-SQL amongst the different Oracle tools is discussed. Next attention is paid to the syntax of PL-SQL, the structure of PL SQL programs and statements, declarations and expressions.
Also control flow in PL-SQL with if else and case statements and the various loop constructs are discussed. Next cursors are discussed and how they are used in accessing the database. Also advanced issues like the creation and calling of local and stored procedures and the use of packages are part of the subject matter.
Finally, attention is paid to triggers. This course uses the newest Oracle version, but where necessary, Oracle 9i, 10g and 11g features are specifically indicated.
The course Oracle PL-SQL Programming is designed for developers who want to use PL-SQL in applications that access an Oracle database.
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.
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. The course uses the newest version of Oracle but is also suitable for other Oracle versions. The course times are from 9.30 up and to 16.30.
Participants receive an official certificate Oracle PL-SQL Programming after successful completion of the course.
Module 1 : PL-SQL Intro |
Module 2 : PL-SQL Syntax |
Module 3 : Control Flow |
What is PL-SQL Strenght of PL/SQL Basic Concepts Procedural possibilities Enhanced performance Oracle Suppport tools Oracle Specifics SQL Developer |
PL-SQL Structure Statements Declarations Assignments %TYPE and %ROWTYPE Expressions Comments NULL |
IF-THEN-ELSE statement CASE statements GOTO WHILE loops FOR loops with index FOR loops with cursor Infinite loops Nested blocks |
Module 4 : Cursors |
Module 5 : Transactions |
Module 6 : Error Handling |
Implicit cursor Query with SELECT INTO Query with cursor OPEN-FETCH-CLOSE FOR loops with cursor Cursor attributes |
Transaction in PL-SQL Queries for Changes Autonomous Transactions Locking Read Consistency Self Defined Records |
Exceptions RAISE EXCEPTION_INIT SQLCODE SQLERRM Parameters |
Module 7 : Procedures and functions |
Module 8 : Packages |
Module 9 : Triggers |
Local versus stored procedures Parameters DEFAULT values Removing procedures Local and stored functions Functions in SQL Dependencies Recompilation Remote dependencies |
Benefits of Packages Package specification Package body Calling a function in SQL RAISE_APPLICATION_ERROR DESCRIBE Removing Packages NDS Packages Standard packages for SQL |
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 |