Oracle Database 11g PL/SQL Fundamentals

Day 1

  • Introduction
  • Lesson 1 : Introduction to PL/SQL
  • Lesson 2 : Declaring PL/SQL Variables
  • Lesson 3 : Writing Executable Statements
  • Lesson 4 : Interacting with the Oracle Database Server
  • Lesson 5 : Writing Control Structures

Day 2

  • Lesson 6 : Working with Composite Data Types
  • Lesson 7 : Using Explicit Cursors
  • Lesson 8 : Handling Exceptions
  • Lesson 9 : Creating Stored Procedures and Functions

Lesson 1 : Introduction to PL/SQL

  • Explain the need for PL/SQL
  • Explain the benefits of PL/SQL
  • Identify the different types of PL/SQL blocks
  • Output messages in PL/SQL

Lesson 2 : Declaring PL/SQL Variables

  • Recognize valid and invalid identifiers
  • List the uses of variables
  • Declare and initialize variables
  • List and describe various data types
  • Identify the benefits of using the %TYPE attribute
  • Declare, use, and print bind variables

Lesson 3 : Writing Executable Statements

  • Identify lexical units in a PL/SQL block
  • Use built-in SQL functions in PL/SQL
  • Describe when implicit conversions take place and when explicit conversions have to be dealt with
  • Write nested blocks and qualify variables with labels
  • Write readable code with appropriate indentations
  • Use sequences in PL/SQL expressions

Lesson 4 : Interacting with the Oracle Server

  • Determine which SQL statements can be directly included in a PL/SQL executable block
  • Manipulate data with DML statements in PL/SQL
  • Use transaction control statements in PL/SQL
  • Make use of the INTO clause to hold the values returned by a SQL statement
  • Differentiate between implicit cursors and explicit cursors
  • Use SQL cursor attributes

Lesson 5 : Writing Control Structures

  • Identify the uses and types of control structures
  • Construct an IF statement
  • Use CASE statements and CASE expressions
  • Construct and identify different loop statements
  • Use guidelines when using conditional control structures

Lesson 6 : Working with Composite Data Types

  • Create user-defined PL/SQL records
  • Create a record with the %ROWTYPE attribute
  • Create an INDEX BY table
  • Create an INDEX BY table of records
  • Describe the differences among records, tables, and tables of records

Lesson 7 : Using Explicit Cursors

  • Distinguish between implicit and explicit cursors
  • Discuss the reasons for using explicit cursors
  • Declare and control explicit cursors
  • Use simple loops and cursor FOR loops to fetch data
  • Declare and use cursors with parameters
  • Lock rows with the FOR UPDATE clause
  • Reference the current row with the WHERE CURRENT OF clause

Lesson 8 : Handling Exceptions

  • Define PL/SQL exceptions
  • Recognize unhandled exceptions
  • List and use different types of PL/SQL exception handlers
  • Trap unanticipated errors
  • Describe the effect of exception propagation in nested blocks
  • Customize PL/SQL exception messages

Lesson 9 : Creating Stored Procedures and Functions

  • Differentiate between anonymous blocks and subprograms
  • Create a simple procedure and invoke it from an anonymous block
  • Create a simple function
  • Create a simple function that accepts a parameter
  • Differentiate between procedures and functions

In-house Price for 2 days

  • 27,000 baht(THB) : Economic Class : 1 - 5 people
  • 38,000 baht(THB) : Small Class : 6 - 10 people
  • 54,000 baht(THB) : Medium Class : 11 - 20 people
  • 70,000 baht(THB) : Large Class : 21 - 30 people
  • All prices exclude VAT 7 %
  • Public price : 9,000 baht per person

Printable Version ดาวน์โหลดหน้านี้ >>