English French German Italian Portuguese Russian Spanish

logo_old

  • Database Training

    databaseInterSource offers live instructor-led courses on all important database programming technologies, including Crystal Reports, Microsoft Access, MySQL, Oracle, SQL, SQL Server, SSAS, SSIS, SSRS and Xcelsius.

    These live classes are offered both on client sites, at our Geneva training center, and via a Web interface.

  • About Database

    A database is a collection of data stored and maintained for one or more uses. Most modern databases are managed by a Database Management System (DBMS), a set of computer programs that controls the creation, maintenance, and the use of the database with computer as a platform or of an organization and its end users. It allows organizations to place control of organization-wide database development in the hands of database administrators (DBAs) and other specialists.

    The proper integration of databases can dramatically increase the functionality of all types of applications, whether or not Web-enabled.


    Read More
  • Course Details Database

    Classes are offered at client sites, at our Geneva training center, and via a live web conference. For detailed course outlines and scheduled classes, please see below.

    To book training, navigate to the course you need, then:

    • For scheduled online classes, register from the choices indicated.
    • If you need an alternative dates, time or location, or if you want a live classroom course, click on “request an offer for this course,” to complete the form.

    InterSource clients are active globally. Live web courses run during Eastern Standard Time (New York) business hours and are priced in US dollars.

    We also run live web conference classes during European business hours, which can be invoiced in local currencies. To discuss your requirements please contact us on +41 (22) 958 0114.

Oracle PL/SQL Training

 PDF PrintEmail

Course duration

  • 5 Days

Course Outline

PL/SQL is Oracle's extension language for standard SQL.

In this PL/SQL training class, students who already know SQL, learn to use PL/SQL to write sophisticated queries against an Oracle database.

  • Understand the PL/SQL Development Environment.
  • Learn the basics of the PL/SQL language.
  • Learn to declare and work with variables.
  • Learn to use conditionals and loops in PL/SQL.
  • Learn to handle and create user-defined exceptions.
  • Learn to use SQL within PL/SQL.
  • Learn about nested blocks and variable scope.
  • Learn to create Subprograms, Stored Procedures and Functions.
  • Learn to work with Packages, Triggers and Cursors.
  • Learn to use Oracle Supplied Packages.
  • Oracle SQL as taught in our Introduction to Oracle SQL and Advanced Oracle SQL classes.
  1. The Environment for PL/SQL Development
    1. Prerequisites
      1. File Naming Conventions
    2. Connecting to Oracle
      1. SQL*Developer
      2. SQL*Plus
    3. Executing PL/SQL Code
    4. SQL Developer Configuration
    5. The PL/SQL Development Cycle
  2. PL/SQL Basics
    1. PL/SQL Block
    2. Anonymous Block Structure
    3. Named Block Structure
    4. Executing Blocks
    5. Calling PL/SQL Functions
    6. Executing PL/SQL Blocks and Functions
  3. Declaring Variables
    1. Variable Usage
    2. Variable Data Types
    3. Variable Naming
    4. Variable Assignment
      1. Variable Display
    5. Complex Variable Types
    6. More Information
    7. Variable Declaration, Initialization and Display
  4. Within the Block
    1. Conditional Processing
      1. The IF Statement
      2. The CASE Statement
    2. Iterative Processing
      1. The LOOP Statement
      2. The WHILE Statement
      3. The FOR Statement
    3. Salary Classification using Conditional Statements
    4. Salary Increases using Loops
  5. Handling Exceptions
    1. Overview of Exceptions
    2. Causing System Generated Exceptions
    3. Handling System Generated Exceptions
    4. Identifying System Generated Exceptions
    5. OTHERS Exception Handler
    6. User Defined Exceptions
    7. Exception Handling for Invalid Salary
  6. Use of SQL in PL/SQL
    1. Implicit Cursors
    2. %TYPE and %ROWTYPE Attributes
    3. EXECUTE IMMEDIATE statement
    4. Cursors Attributes
    5. Implicit Cursor
    6. Explicit Cursors
    7. Explicit Cursor
    8. Cursor FOR loop
  7. Nested Blocks
    1. Nesting Blocks
    2. Scope of Variables
    3. Scope of Exceptions
    4. Nested Subprograms
    5. Exception Propagation in Nested Blocks
    6. Block Variable Visibility
  8. Introducing Subprograms
    1. Anonymous blocks
    2. Types of Subprograms
    3. Finding Subprograms through SQLDeveloper
    4. Finding Subprograms in the Oracle Data Dictionary
    5. Object Dependencies
    6. Subprogram information in the Oracle Data Dictionary
  9. Stored Procedures and Functions
    1. Creating Subprograms
    2. Modifying Subprograms
    3. Removing Subprograms
    4. Application Maintenance
    5. Exercise Title
    6. Parameters
    7. Cursors as Parameters
    8. Subprogram Development Techniques
    9. Addressing Compilation Errors
    10. Directives for Debugging
    11. Issues with Booleans
    12. Integrated Development Environments
    13. PL/SQL Subprograms with Parameters
  10. Packages
    1. Structure of Packages
    2. Purpose of Packages
    3. Wrap Utility
    4. Positional vs Named Parameter Notation
    5. Subprograms Omitted from Specifications
    6. Using PL/SQL Packages
  11. Database Triggers
    1. Purpose of Triggers
    2. Invocation of Triggers
    3. Coding Triggers
    4. Validation Trigger
    5. Modifying Triggers
    6. Viewing Triggers
    7. Enabling/Disabling Triggers
    8. Trigger Errors
    9. System and User Event Triggers
    10. Logon Counter Trigger
  12. Data Retrieval Techniques
    1. Cursor Review
    2. Cursor Parameters
    3. Cursor Variables
    4. Dynamic SQL
    5. Employee Report by State/Province
  13. Using Oracle Supplied Packages
    1. Oracle Supplied Packages
    2. Identifying Available Packages
    3. Identifying Package Subprograms
    4. Selected Oracle Supplied Package Demos
    5. Packages related to Input/Output
    6. Packages related to Networking
    7. DBMS_SQL: Dynamic SQL
    8. DBMS_URL: Working with URLS
    9. The DBMS_APPLICATION_INFO
  14. Specialized Topics
    1. Application Partitioning
    2. Subprograms in other Languages
    3. Oracle Object Features
    4. Statistical Analysis
    5. Data Mining
    6. Use Oracle Object Features

InterSource Geneva, a premier Information Technology training firm, offers over 400 different courses on server, database and programming technologies, as well as end-user classes for the most popular office, graphics and design applications. We serve clients in Switzerland (Geneva, Lausanne, Bern, Basel, Zurich) and throughout Europe (France, Germany, Austria, Finland, Sweden, England, Netherlands, Spain, etc.).


InterSource offers custom, private courses at client sites, standard public courses in our Geneva classroom, and online training via live Web conference. Training is offered in English and many other languages (Francais, Deutsch, Espanol, Italiano.)


For an overall view of our offerings, please visit us at www.intersource.ch.