Développer en PL/SQL Training

Introduction and refresher

• Transactional processing in Oracle.
• Objects and dictionary of an Oracle database.
• SQL and PL/SQL. Overview of PL/SQL.
• Tools used. Oracle documentation.

Language elements

• Host environments. Introduction to syntax Declaring PL/SQL variables and constants.
• Types of data and conversion. Predefined functions.
• Control instructions: IF- ELSE, WHILE loops, FOR loops.
• Composite types: records, indexed tables.
• Object types. Defining subprograms.
• Configuring subprograms.

Exercise
Declaring variables. Changing the date. Typing attributes Using collections and their methods.

Access to the database

• The database and the notion of a cursor.
• Access via implicit cursor: update, delete, insert, select. Using RETURNING and BULK COLLECT clauses.
• Collections and FORALL.
• Cursor FOR loops. Update cursors.
• Explicit cursors. Cursor variables.

Exercise
Using explicit cursors and BULK COLLECT.

Stored procedures

• Managing stored subprograms, stored libraries and packages
• Managing stored procedures: Access rights, dictionary views, and dependency.
• Using packages.

Exercise
Creating procedures, stored functions, and packages.

Error management

• Handling an exception.
• Using RAISE_APPLICATION_ERROR.
• Triggering an exception via RAISE.
• Propagation and transaction management.

Exercise
Using Raise and RAISE_APPLICATION_ERROR.

Triggers

• Definition and types of Event/Action triggers. Order triggers.
• Lines (using: NEW and: OLD), E/C/A (Event/Condition/Action).
• “INSTEAD OF“ on views, on system events.
• Using autonomous transactions.

Exercise
Creating order triggers and line triggers.

Complements

• Dynamic SQL. Uusing dbms_application_info and WRAP to hide the source code.