Oracle Database 10g: PL/SQL Fundamentals

Posted by admin - 27/01/10 at 12:01 am

What you will learn

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.Students learn to create anonymous PL/SQL blocks and are introduced to stored procedures and functions. They learn about declaring variables, trapping exceptions and they also learn to declare and control cursors.Learn To:Declaring PL/SQL VariablesWorking with Composite Data Types Creating the Executable SectionUsing Explicit CursorsWriting Control StructuresIncluding Exception Handling

Audience

• Application Developers
• Database Administrators
• Database Designers
• Forms Developer
• PL/SQL Developer
• Technical Consultant

Prerequisites

• Required Prerequisites
• Oracle Database 10g: Introduction to SQL

Course Objectives

• Write PL/SQL code to interface with the database
• Design PL/SQL program units that execute efficiently
• Use PL/SQL programming constructs and conditional control statements
• Handle run-time errors
• Describe stored procedures and functions

Course Topics

• Introduction to PL/SQL
• What is PL/SQL
• PL/SQL Environment
• Benefits of PL/SQL
• Overview of the Types of PL/SQL blocks
• Create and Execute a Simple Anonymous Block
• Generate Output from a PL/SQL Block
• iSQL*Plus as PL/SQL Programming Environment

Declaring PL/SQL Identifiers

• Identify the Different Types of Identifiers in a PL/SQL subprogram
• Use the Declarative Section to Define Identifiers
• List the Uses for Variables
• Store Data in Variables
• Declare PL/SQL Variables

Writing Executable Statements

• Describe Basic Block Syntax Guidelines
• Use Literals in PL/SQL
• Customize Identifier Assignments with SQL Functions
• Use Nested Blocks as Statements
• Reference an Identifier Value in a Nested Block
• Qualify an Identifier with a Label
• Use Operators in PL/SQL
• Use Proper PL/SQL Block Syntax and Guidelines

Interacting with the Oracle Server

• Identify the SQL Statements You Can Use in PL/SQL
• Include SELECT Statements in PL/SQL
• Retrieve Data in PL/SQL with the SELECT Statement
• Avoid Errors by Using Naming Conventions When Using Retrieval and DML Statements
• Manipulate Data in the Server Using PL/SQL
• The SQL Cursor concept
• Use SQL Cursor Attributes to Obtain Feedback on DML
• Save and Discard Transactions

Writing Control Structures

• Control PL/SQL Flow of Execution
• Conditional processing Using IF Statements
• Conditional Processing CASE Statements
• Handle Nulls to Avoid Common Mistakes
• Build Boolean Conditions with Logical Operators
• Use Iterative Control with Looping Statements

Working with Composite Data Types

• Learn the Composite Data Types of PL/SQL Records and Tables
• Use PL/SQL Records to Hold Multiple Values of Different Types
• Inserting and Updating with PL/SQL Records
• Use INDEX BY Tables to Hold Multiple Values of the Same Data Type

Using Explicit Cursors

• Cursor FOR Loops Using Sub-queries
• Increase the Flexibility of Cursors By Using Parameters
• Use the FOR UPDATE Clause to Lock Rows
• Use the WHERE CURRENT Clause to Reference the Current Row
• Use Explicit Cursors to Process Rows
• Explicit Cursor Attributes
• Cursors and Records

Handling Exceptions

• Handling Exceptions with PL/SQL
• Predefined Exceptions
• Trapping Non-predefined Oracle Server Errors
• Functions that Return Information on Encountered Exceptions
• Trapping User-Defined Exceptions
• Propagate Exceptions
• Use The RAISE_APPLICATION_ERROR Procedure To Report Errors To Applications

Creating Stored Procedures and Functions

• Differences Between Anonymous Blocks and Subprograms
• Creating and invoking Procedure
• Creating and invoking Functions
• Passing Parameter to the Function

This entry was posted on Wednesday, January 27th, 2010 at 12:11 am and is filed under Oracle Administration. The entry is tagged under 10g class, 10g pl/sql fundamentals, oracle pl/sql fundamentals, pl/sql fundamentals. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.