SQL Advanced

Audience

Prerequisites

Programmers and analysts who need to get a full understanding of PL/SQL.

Experience with Windows operating system. One year of experience with programming and knowledge of Relational Database technology. Six months experience with SQL on an Oracle database.

Description

Advanced topics include the study of Oracle’s database architecture, including schemas, objects, and the Data Dictionary. Students will demonstrate building and executing Stored Procedures and Functions while utilizing Oracle’s PL/SQL. Students will also learn about and practice creation of packages and triggers.  There are several hands-on labs in this course that will provide the student with the opportunity to practice and perfect the knowledge of Oracle that they will gain during these 2 days.

Goals/Objectives

        List the components of ADS/0

        List the tasks required to design an ADS/0 application.

        Determine if an application is a good candidate for ADS/0

        Define data

        Write a basic procedure

        Design, code and debug basic dialog.

        Code a procedure

        Understand the data security & integrity issues involved with using ADS/0

Outline/Table of Contents

SQL Review

        Introduction         

        Using SQL*PLUS

        Working with DUAL

        Dates

        Outer Joins

        Query Optimization

Introduction to PL/SQL

        Introduction

        Stored Procedures

        Advantages of Stored Procedures

        Procedure Structure

        Function  Structure          

        Trigger Structure 

        Procedure Parameters       

        Packages  

        Tools for Creating Stored Procedures      

o          EXERCISE -Create Simple Stored Procedure

        Error Handling in PL/SQL

        Summary

Creating Subprograms with PL/SQL

        PL/SQL Data Types

        Declaring Variables and Constants

        Scope of Variables

        Statements and Assignments

        Using SELECT INTO

        EXERCISE - Customer Read Procedure

        Using INSERT, UPDATE, and DELETE

        Enhancing Your Error Messages

        EXERCISE - Create INSERT, UPDATE, and DELETE

        Stored Procedures for the Sales Customer Table

        Control Structures

        Decision Flow Control

        Loops

        GOTO and Labels

        EXERCISE - Create Customer Maintenance Procedure

        Creating and Testing Functions

        Removing Procedures and Functions

        Stored Subprograms and the Data Dictionary