Oracle PL/SQL

Oracle PL/SQL Courseware (ORA200)

OracleDataSQL

This Oracle PL/SQL course is designed for people who are already familiar with Oracle SQL and want to learn the fundamentals of PL/SQL programming in Oracle Database. The course covers topics such as PL/SQL basics, subprograms, conditional processing, exceptions, cursors, packages, and triggers. Through practical exercises and real-world examples, students will gain a strong foundation in PL/SQL and learn how to create efficient, maintainable, and secure applications in Oracle databases.

Publisher: Webucator

Benefits

  1. Foundational Knowledge: The course provides a comprehensive introduction to Oracle PL/SQL, ensuring students have a solid understanding of PL/SQL programming and Oracle database application development.
  2. Hands-on Learning: Practical exercises and real-world examples help students develop their skills and confidence in working with PL/SQL in Oracle databases.
  3. Efficient and Secure Applications: Students will learn how to create efficient, maintainable, and secure applications in Oracle databases, improving their productivity and problem-solving abilities.
  4. Experienced Authors: The course is created by expert instructors with extensive knowledge and experience in Oracle PL/SQL and database programming, ensuring that students receive high-quality and up-to-date content.
  5. Real-World Applications: After completing the course, students will be able to apply their Oracle PL/SQL skills to develop and optimize data-driven applications, helping them make a tangible impact in their organizations and industries.

PowerPoint Presentation

This course includes a PowerPoint presentation that maps to the manual and to the labs:

Full Lab Environment Add-On

Enhance and simplify your classes by providing an unparalleled learning platform that requires no setup. Your trainers and students can dive straight into a fully-prepared lab environment with just a click. This seamless integration means no time wasted on installations or configurations, allowing trainers and students to focus solely on the task at hand. The lab comes pre-loaded with all the necessary tools and resources, ensuring a smooth, hassle-free learning experience.

Skillable Lab

Outline

  1. PL/SQL Basics
    1. The HR Schema
    2. What is PL/SQL?
    3. Blocks
    4. Outputting Information
    5. Variables and Constants
    6. Constants
    7. Data Types
    8. Naming Variables and Other Elements
    9. Embedding SQL in PL/SQL
    10. SELECT…INTO and RETURNING…INTO
    11. Using Variables (Exercise)
    12. PL/SQL Features
  2. Subprograms
    1. Introduction to Subprograms
    2. Procedures
    3. Variable Declarations
    4. Parameters
    5. Parameters with Default Values
    6. Parameter Modes
    7. IN Mode
    8. OUT Mode
    9. IN OUT Mode
    10. Named Notation
    11. Using SQL in a Subprogram
    12. %TYPE
    13. Creating a Procedure (Exercise)
    14. Functions
    15. Creating a Function (Exercise)
    16. Using PL/SQL Functions in SQL Queries
    17. Dropping a Subprogram
  3. Conditional Processing
    1. Conditions and Booleans
    2. IF-ELSIF-ELSE Conditions
    3. Creating a get_age() Function (Exercise)
    4. ELSIF
    5. Creating a check_rights() Procedure (Exercise)
    6. Creating an is_manager() Function (Exercise)
    7. BOOLEAN Values and Standard SQL
    8. The CASE Statement
    9. CASE Expressions
    10. Replacing the Head Honcho (Exercise)
  4. Exceptions
    1. Introduction to Exceptions
    2. Predefined Exceptions
    3. The EXCEPTION Part of the Block
    4. Catching NO_DATA_FOUND Exception (Exercise)
    5. User-defined Exceptions
    6. User-defined Exceptions in Subprograms
    7. Re-raising Exceptions
    8. Replacing the Head Honcho (revisited) (Exercise)
    9. Adding Exceptions to update_employee_manager() (Exercise)
    10. Naming Unnamed Predefined Exceptions
    11. WHILE Loops
    12. When to Use Exceptions
  5. Cursors
    1. Implicit Cursors
    2. Using Implicit Cursor Attributes (Exercise)
    3. Explicit Cursors
    4. %ROWTYPE
    5. Explicit Cursor Use Case
    6. Cursor FOR LOOP
    7. Using an Explicit Cursor (Exercise)
    8. Cursor Parameters
  6. Packages
    1. Package Basics
    2. The Package Specification
    3. The Package Body
    4. Modifying the Package (Exercise)
    5. Building an Employee Package
    6. Adding a get_manager() Function (Exercise)
    7. Overloading Subprograms
    8. Adding Overloaded Functions to the Package (Exercise)
    9. Auditing
    10. Validation Procedures
    11. Adding a Validation Procedure (Exercise)
    12. Package Cursors
    13. Adding a Cursor to the Package (Exercise)
    14. Benefits of Packages
  7. Triggers
    1. What are Triggers?
    2. Trigger Parts
    3. Validation Triggers
    4. Creating a Trigger on the jobs Table (Exercise)
    5. The WHEN Clause
    6. Using the WHEN Clause (Exercise)
    7. Audit Triggers
    8. Statement-level Triggers
    9. Compound Triggers
    10. Trigger Warning

Required Prerequisites

  • SQL
License

Length: 5 days | $200.00 per copy
Labs: Add-on available | $74.33 per lab
View Lab Details

LicenseRequest More InformationDownload Sample CopyRequest Trainer Evaluation Copy
What is Included?
  • Student Manual
  • Student Class Files
  • PowerPoint Presentation
  • Labs (optional add-on)
    Courseware Tracks

    This course is included in the following tracks: