Oracle PL/SQL Programming: Fundamentals to Advanced

Oracle PL/SQL Programming: Fundamentals to Advanced

Released August 2015
Publisher(s): Pearson
ISBN: 0134291484

Watch it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Video description

Overview

Oracle PL/SQL Programming: Fundamentals to Advanced LiveLessons begins with a fundamentals module that covers the basics of Oracle's language extension for SQL and the Oracle relational database. The focus is to give students with a working knowledge of SQL the ability to develop Oracle procedures, functions, and packages using the PL/SQL language.

The advanced module teaches developers with PL/SQL experience the new and advanced features of the PL/SQL language along with performance tuning techniques.

Description

In the fundamentals module, Oracle ACE Director Dan Hotka teaches developers how to work with existing code and create new programs. Along the way, students will learn both conditional and looping syntax and techniques, error handling and error processing, plus how to work with script files, procedures, functions and packages (a collection of procedures and functions).

In the advanced module, Dan demonstrations how to take advantage of better options for performance, gain knowledge about the newer features of the language, as well as actually work with these same features in hands-on lab exercises. The students will learn the latest features of PL/SQL collections, triggers, and a variety of compiler features such as conditional compilation, result_cache/deterministic features for functions, autonomous transactions, optimizing compiler features, and more. Dan covers additional exception processing useful for collections. The advanced lessons include useful extensions to the error processing part with a clever use of database triggers. This advanced lessons also covers a variety of PL/SQL performance tips and the tuning and debugging tools for the PL/SQL programming environment.

This LiveLessons course contains hands-on lab exercises throughout, and three common tools are used for demonstration, including SQL*Plus, Toad, and SQL*Developer. Students can use the Oracle PL/SQL development tool of their choice to do the hands-on labs with these lessons. All examples work with Oracle10, Oracle11, and Oracle12 databases, but the concepts and most of the tips apply to any Oracle database.

Instructor Resources

Download the source code repository for the PL/SQL Fundamentals LiveLesson from InformIT.com.

Download the source code repository for the Advanced PL/SQL LiveLesson from InformIT.com.

About the Instructor

Dan Hotka is a training specialist and an Oracle ACE director who has more than 37 years in the computer industry and more than 31 years of experience with Oracle products. His experience with the Oracle RDBMS dates back to the Oracle V4.0 days. Dan enjoys sharing his knowledge of the Oracle RDBMS. Dan is well published with 12 Oracle books and well over 200 published articles. He is also the video author for Oracle SQL LiveLessons and Oracle SQL Performance Tuning for Developers LiveLessons. He is frequently published in Oracle trade journals, regularly blogs, and speaks at Oracle conferences and user groups around the world. Visit his website at www.DanHotka.com.

Skill Level

  • Beginner for the fundamentals module
  • Intermediate for the advanced module

Learn How To

Fundamentals module:

  • Use the PL/SQL programming language
  • Understand both conditional and looping syntax and techniques
  • Understand error handling and error processing
  • Work with script files, procedures, functions and packages (a collection of procedures and functions)
  • Use two useful tools: PL/SQL debugger and the PL/SQL profiler
  • Understand additional PL/SQL performance tips

Advanced module:

  • Learn and review the newer and more advanced features of PL/SQL
  • Utilize the advanced compiler features of PL/SQL
  • Use the available database trigger code, complete with advanced topics and with useful real-life examples
  • Understand advanced error handling and error processing
  • Work with collections, the gas pedal for row processing
  • Use dynamic SQL and prevent SQL injection attacks
  • Understand useful PL/SQL tools: PL/SQL Debugger and the PL/SQL profilers
  • Understand PL/SQL performance tips, including PL/SQL table functions

Who Should Take This Course

  • Oracle programmers
  • Project Managers who need to know more about PL/SQL
  • Business Analysts who need to know more about PL/SQL
  • For the advanced module, Oracle programmers who have not attended PL/SQL training in a while (this course could easily be named "New Features for PL/SQL Developers”)

Course Requirements

  • Working knowledge of the SQL query language
  • For the advanced module, working knowledge of PL/SQL programming fundamentals

About LiveLessons Video Training

The LiveLessons Video Training series publishes hundreds of hands-on, expert-led video tutorials covering a wide selection of technology topics designed to teach you the skills you need to succeed. This professional and personal technology video series features world-leading author instructors published by your trusted technology brands: Addison-Wesley, Cisco Press, IBM Press, Pearson IT Certification, Prentice Hall, Sams, and Que. Topics include: IT Certification, Programming, Web Development, Mobile Development, Home and Office Technologies, Business and Management, and more. View all LiveLessons on InformIT at: http://www.informit.com/livelessons.

Table of contents

  1. Introduction
    1. Oracle PL/SQL Programming Fundamentals LiveLessons: Introduction
  2. Lesson 1: PL/SQL Course Overview
    1. Learning Objectives
    2. 1.1 Understand the course pre-requisites
    3. 1.2 Learn the Oracle database objects to be used in this course
    4. 1.3 Review the command-line SQL*Plus tool
    5. 1.4 Review SQL Developer tool
    6. 1.5 Review TOAD tool
    7. Put it all together
  3. Lesson 2: Oracle PL/SQL Overview
    1. Learning Objectives
    2. 2.1 Review Oracle architecture
    3. 2.2 Review PL/SQL overview
    4. Put it all together
  4. Lesson 3: PL/SQL Basics and Variables
    1. Learning Objectives
    2. 3.1 Explore PL/SQL basics
    3. 3.2 Use PL/SQL variables
    4. Put it all together
  5. Lesson 4: Logic Flow
    1. Learning Objectives
    2. 4.1 Understand IF/THEN/ELSE logic
    3. 4.2 Explore the CASE statement
    4. 4.3 Utilize looping syntax
    5. Put it all together
  6. Lesson 5: PL/SQL Cursors
    1. Learning Objectives
    2. 5.1 Introduce PL/SQL cursors and the implicit cursor
    3. 5.2 Explore explicit cursors
    4. Put it all together
  7. Lesson 6: PL/SQL Exception Handling
    1. Learning Objectives
    2. 6.1 Introduce PL/SQL exceptions
    3. 6.2 Explore PL/SQL exception basic syntax
    4. 6.3 Discover implicit vs explicit exceptions
    5. 6.4 Work with SQLCODE and SQLERRM features
    6. 6.5 Understand user-defined exceptions
    7. 6.6 Use application-defined exceptions
    8. Put it all together
  8. Lesson 7: PL/SQL Procedures and Functions
    1. Learning Objectives
    2. 7.1 Introduce PL/SQL procedures and functions
    3. 7.2 Explore PL/SQL procedures
    4. 7.3 Understand PL/SQL functions
    5. 7.4 Work with parameter passing
    6. Put it all together
  9. Lesson 8: PL/SQL Packages
    1. Learning Objectives
    2. 8.1 Introduce PL/SQL packages
    3. 8.2 Explore the package syntax
    4. 8.3 Review executing code in packages
    5. Put it all together
  10. Lesson 9: PL/SQL Triggers
    1. Learning Objectives
    2. 9.1 Introduce PL/SQL triggers
    3. 9.2 Explore the trigger syntax
    4. 9.3 Review the order of trigger processing and other restrictions
    5. 9.4 Work with useful examples
    6. Put it all together
  11. Lesson 10: PL/SQL New Features
    1. Learning Objectives
    2. 10.1 Introduce PL/SQL new features
    3. 10.2 Explore the new features with tips and techniques
    4. Put it all together
  12. Lesson 11: PL/SQL Collections
    1. Learning Objectives
    2. 11.1 Introduce PL/SQL collections
    3. 11.2 Explore the collection syntax
    4. 11.3 Review code examples
    5. 11.4 Explore bulk binding techniques
    6. Put it all together
  13. Lesson 12: PL/SQL Debugger, PL/SQL Profiler, PL/SQL Tuning Tips
    1. Learning Objectives
    2. 12.1 Introduce PL/SQL debugging, PL/SQL profiling, PL/SQL coding tips
    3. 12.2 Explore the TOAD debugger
    4. 12.3 Explore the SQL developer debugger
    5. 12.4 Review the PL/SQL profiler
    6. 12.5 Use the PL/SQL profiler with SQL*Plus
    7. 12.6 Use the TOAD PL/SQL profiler
    8. 12.7 Review PL/SQL coding tips
    9. Put it all together
  14. Oracle PL/SQL Programming Fundamentals LiveLessons: Summary
    1. Summary
  15. Introduction
    1. Oracle PL/SQL Advanced Programming LiveLessons: Introduction
  16. Lesson 1: Oracle PL/SQL Advanced Course Overview
    1. Learning objectives
    2. 1.1 Review LiveLessons course objectives and pre-requisites
    3. 1.2 Understand the course database objects and the course download
    4. 1.3 Introduce the course tools
    5. 1.4 Review the Oracle Architecture
    6. 1.5 Explore the PL/SQL Architecture
    7. Put it all together
  17. Lesson 2: PL/SQL Compiler Options: Part I
    1. Learning objectives
    2. 2.1 Introduce function/procedure/package advanced syntax
    3. 2.2 Review Oracle11 result cache
    4. 2.3 Explore PL/SQL limits including Oracle12 new limits
    5. 2.4 Hands-on Lab
    6. 2.5 Work with identity columns
    7. 2.6 Utilize sequences
    8. 2.7 Understand date fields
    9. 2.8 Utilize definer rights
    10. 2.9 Discover Oracle12 select lists
    11. 2.10 Explore Oracle12 new privilege features
    12. 2.11 Work with Autonomous Transactions
    13. 2.12 Explore compiling with Purity features
    14. 2.13 Hands-on Lab
    15. Put it all together
  18. Lesson 3: PL/SQL Compiler Options: Part II
    1. Learning objectives
    2. 3.1 Invoke the PL/SQL compiler
    3. 3.2 Utilize compiler warnings
    4. 3.3 Work with inlining
    5. 3.4 Use the conditional compilation
    6. 3.5 Review Oracle12 compiler directives
    7. 3.6 Introduce native compilation
    8. 3.7 Hands-on Lab
    9. Put it all together
  19. Lesson 4: PL/SQL Compiler Options and Packages
    1. Learning objectives
    2. 4.1 Review PL/SQL packages syntax
    3. 4.2 Work with PL/SQL packages Pragma declarations
    4. 4.3 Introduce PL/SQL packages optional block
    5. 4.4 Discover PL/SQL packages forward declaration
    6. 4.5 Hands-on Lab
    7. Put it all together
  20. Lesson 5: PL/SQL Coding Style and New Features
    1. Learning objectives
    2. 5.1 View PL/SQL source code
    3. 5.2 Work with data dictionary information
    4. 5.3 Use source code encryption
    5. 5.4 Work with overloading
    6. 5.5 Review bodiless package
    7. 5.6 Discover code dependencies
    8. 5.7 Explore new PL/SQL syntax
    9. 5.8 Hands-on Lab
    10. Put it all together
  21. Lesson 6: Oracle Database Triggers
    1. Learning objectives
    2. 6.1 Introduce Oracle triggers
    3. 6.2 Explore trigger types and syntax
    4. 6.3 Review trigger restrictions
    5. 6.4 Work with trigger management
    6. 6.5 Understand trigger new features: updateable views
    7. 6.6 Hands-on Lab
    8. 6.7 Review triggers that audit
    9. 6.8 Explore event triggers
    10. 6.9 Hands-on Lab
    11. Put it all together
  22. Lesson 7: Cursors
    1. Learning objectives
    2. 7.1 Introduce reference cursors (ref cursors)
    3. 7.2 Explore ref cursor syntax
    4. 7.3 Work with implementation of ref cursors
    5. 7.4 Review working examples
    6. 7.5 Discover cursor sharing
    7. 7.6 Utilize cursor parameters
    8. 7.7 Hands-on Lab
    9. Put it all together

Product information

  • Title: Oracle PL/SQL Programming: Fundamentals to Advanced
  • Author(s):
  • Release date: August 2015
  • Publisher(s): Pearson
  • ISBN: 0134291484