Programming with PL/SQL

Categories

Oracle Database

Programming with PL/SQL

Programming with PL/SQL

Students are introduced to PL/SQL and explore how it extends and automates SQL in administering the Oracle database, and create projects to design, implement, and demonstrate a database solution for a business.
180 Hours

For students: Who wish to learn the techniques and tools to design, build and extract information from a database. This foundational course is suitable for computer science majors and non-majors alike.

Educator prerequisites: High level knowledge of database design concepts and of programming with SQL

Suggested: Database Design & Programming with SQL

 

 

 

 

Lesson-by-Lesson Topics

Fundamentals

  • Introduction to PL/SQL
  • Benefits of PL/SQL
  • Creating PL/SQL Blocks

Defining Variables and Datatypes

  • Using Variables in PL/SQL
  • Recognizing PL/SQL Lexical Units
  • Recognizing Data Types
  • Using Scalar Data Types
  • Writing PL/SQL Executable Statements
  • Nested Blocks and Variable Scope
  • Good Programming Practices

Using SQL in PL/SQL

  • Review of SQL DML
  • Retrieving Data in PL/SQL
  • Manipulating Data in PL/SQL
  • Using Transaction Control Statements

Program Structures to Control Execution Flow

  • Conditional Control: IF Statements
  • Conditional Control: CASE Statements
  • Iterative Control: Basic Loops
  • Iterative Control: WHILE and FOR Loops
  • Iterative Control: Nested Loops

Using Composite Datatypes

  • User-Defined Records
  • Indexing Tables of Records

Using Cursors and Parameters

  • Introduction to Explicit Cursors
  • Using Explicit Cursor Attributes
  • Cursor FOR Loops
  • Cursors with Parameters
  • Using Cursors for UPDATE
  • Using Multiple Cursors

Exception Handling

  • Handling Exceptions
  • Trapping Oracle Server Exceptions
  • Trapping User-Defined Exceptions
  • Recognizing the Scope of Exceptions

Using and Managing Procedures

  • Creating Procedures
  • Using Parameters in Procedures
  • Passing Parameters

Using and Managing Functions

  • Creating Functions
  • Using Functions in SQL Statements
  • Review of the Data Dictionary
  • Managing Procedures and Functions
  • Review of Object Privileges
  • Using Invoker’s Rights and Autonomous Transactions

Using and Managing Packages

  • Creating Packages
  • Managing Package Concepts
  • Advanced Package Concepts

Getting the Best out of Packages

  • Persistent State of Package Variables
  • Using Oracle-Supplied Packages

Improving PL/SQL Performance

  • Using Dynamic SQL
  • Improving PL/SQL Performance

Using and Managing Triggers

  • Introduction To Triggers
  • Creating DML Triggers, Part I
  • Creating DML Triggers, Part II
  • Creating DDL and Database Event Triggers
  • Managing Triggers

Recognizing and Managing Dependencies

  • Introduction to Dependencies
  • Understanding Remote Dependencies

Using the PL/SQL Compiler

  • Using PL/SQL Initialization Parameters
  • Displaying Compiler Warning Messages
  • Using Conditional Compilation
  • Hiding Your Source Code