Location

Community Services & Continuing Education - Alexandria

Objectives

• • Use conditional compilation to customize the functionality in a PL/SQL application without removing any source code• Design PL/SQL packages to group related constructs • Create overloaded package subprograms for more flexibility • Design PL/SQL anonymous blocks that execute efficiently • Use the Oracle supplied PL/SQL packages to generate screen output, file output and mail output • Write dynamic SQL for more coding flexibility• Describe the features and syntax of PL/SQL• Create and debug stored procedures and functions • Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)• Manage dependencies between PL/SQL subprograms Handle run time errors • Create triggers to solve business challenges

Outcomes

Upon successful completion students should be able to:• Enhance the knowledge and understanding of Database analysis and design.• Enhance the knowledge of the processes of Database Development and Administration using SQL and PL/SQL.• Enhance Programming and Software Engineering skills and techniques using SQL and PL/SQL.• Preparation of background materials and documentation needed for Technical Support using SQL and PL/SQL.• Use the Relational model and how it is supported by SQL and PL/SQL.• Use the PL/SQL code constructs of IF-THEN-ELSE and LOOP types as well as syntax and command functions.• Solve Database problems using Oracle 9i SQL and PL/SQL. This will include the use of Procedures, Functions, Packages, and Triggers.

Course Contents

-Writing Basic Select Statement-Using Update -Delete – Merge Statements-Creating and Managing Tables and Views-Joining Tables (ANSI Joins)-Using Sub-queries (Static - Correlated)-Correlated update-delete-exists-Multiple inserts -Copying rows-Controlling user Access-Writing PL/SQL Block and Declaring Variables-Writing executable statements-Using Control structures if statements and Loops -Using Cursors and Composite Data types-Handling Exceptions-Creating Procedures and Functions -Creating and Managing Packages -Creating and Managing Triggers