Description

The PL/SQL course aims to master the PL/SQL language and the use of stored processing and triggers in applications. The concepts covered are systematically put into practice.

Who is this training for ?

For whom ?

• Analysts and programmer analysts.
• Database administrators, operators.

Prerequisites

Training objectives

• Know and use the PL/SQL procedural language and the objects stored in the core: procedure, function, package, trigger.
• Know how to create PL/SQL blocks that can be shared by several applications, forms or reports.
• Have practical indications for developing more efficient and above all more relevant PL/SQL code

Training program

• Introduction and reminders
  • Transactional processing in Oracle.
  • Objects and dictionary of an Oracle database.
  • SQL and PL/SQL.
  • Overview of PL/SQL.
  • Tools used.
  • Oracle documentation.
• Elements of language
  • Host environments.
  • Syntactic introduction.
  • Declarations of PL/SQL variables and constants.
  • Data types and conversion.
  • Predefined functions.
  • Control statements: IF-ELSE, LOOP loop, FOR loop.
  • Composite types: records, indexed tables.
  • Object types.
  • Defining subroutines.
  • Configuring subroutines.
  • Exercise: Declaring variables.
  • Date manipulation.
  • Typing attributes.
  • Using collections and their methods.
• Access to the database
  • The basis and the notion of cursor.
  • Access via implicit cursor: update, delete, insert, select.
  • Use the RETURNING and BULK COLLECT clauses.
  • Collections and FORALL.
  • Cursor FOR loops.
  • Updating cursors.
  • Explicit cursors.
  • Cursor variables.
  • Exercise: Using explicit cursor and BULK COLLECT.
• Stored treatments
  • Management of stored subroutines, stored libraries and packages.
  • Management of stored processes: access rights, dictionary views and dependencies.
  • 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 transactional management.
  • Exercise: Using Raise and RAISE_APPLICATION_ERROR.
• Triggers
  • Definition and types of Event/Action triggers.
  • Triggers Order.
  • Lines (use of: NEW and: OLD), E/C/ A (Event/Condition/Action).
  • " INSTEAD OF " on views, on system events.
  • Using autonomous transactions.
  • Exercise: Creation of order triggers and line triggers.
• Supplements
  • Dynamic SQL.
  • Using dbms_application_info and WRAP to hide source code.