ORACLE10G SQL/PLSQL
 
ORACLE10g PL/SQL
 

Course duration(in hrs): 28

This course introduces students to PL/SQL and helps them understand the benefits of this powerful programming language. In the class, students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications.

Course Objectives:

  1. Describe the features and syntax of PL/SQL
  2. Conditionally control code flow (loops, control structures, and explicit cursors)
  3. Compile time warnings
  4. Handle runtime errors
  5. Manage program constructs
  6. Create, execute, and maintain procedures, functions, packages, and triggers
  7. Manage dependencies
  8. Manipulate large objects
  9. Describe Oracle-supplied packages
 
Prerequisites:
  • Database Fundamentals
  • Working knowledge of SQL

 
Duration: 3 Days
 
Break-up
Day 1

Session 1

 
Introduction to PL/SQL
  Session 2

PL/SQL Basics
  Session 3 Using SQL with PL/SQL
Day 2 Session 4 Records
  Session 5
Collections
  Session 6 Error Handling
  Session 7 Creating Procedures
Day 3 Session 8

Database Triggers

  Session 9

Dynamic SQL
  Session 10

Large Objects
  Session 11 Scheduling Tasks
 
 
Course Topics
 
Session 1
Introduction to PL/SQL

  1. Introduction to Programming Languages

  2. Structured Query Language (SQL)


  3. PL/SQL vs. SQL

  4. PL/SQL History and Features


  5. Language Fundamentals

  6. Anonymous Blocks

Session 2
PL/SQL Basics

  1. The PL/SQL Block
  2. The Basic Structure
  3. Named Blocks
  4. Nested Blocks
  5. Language Rules and Conventions
  6. Lexical Units
  7. PL/SQL Data Types
  8. Composite
  9. Reference
  10. LOB
  11. %TYPE  %ROWTYPE
  12. Variable Scope
  13. Bind Variables
  14. Assignment Operator
  15. Controlling Program Flow
  16. Conditional Evaluation
  17. Circular Execution
  18. Quoting mechanism for string literals

Session 3

Using SQL with PL/SQL


  1. Transaction Processing
  2. Autonomous Transactions
  3. Set Transaction
  4. Retrieving Data
  5. SQL SELECT Statement
  6. Cursors
  7. Explicit Cursors
  8. Implicit Cursors
  9. Cursor Subqueries
  10. Open Cursors
  11. Introduction to Dynamic SQL      
Session4

Records

  1. Writing a Successful SELECT Statement in PL/SQL

  2. Declaring the Data type and Size of a PL/SQL Variable Dynamically

  3. Writing Data Manipulation Language (DML) Statements in PL/SQL
Writing Control Structures

  1. Identifying the Uses and Types of Control Structures

  2. Constructing an IF Statement

  3. Constructing and Identifying Different Loop Statements

  4. Controlling Block Flow Using Nested Loops and Labels

  5. Using Logic Tables
Working with Composite Data types

  1. Creating User-Defined PL/SQL Records

  2. Creating a PL/SQL Table

  3. Creating a PL/SQL Table of Records

  4. Differentiating Among Records, Tables, and Tables of Records
 
 
 
 
 
© 2009, qsoftindia.com, All Rights Reserved
`