Oracle Database: PL/SQL Fundamentals

• kurz Oracle
• délka kurzu: 2 dny
• cena: 23000 CZK
• kategorie: Database

Nejbližší termíny:

Kurz není v nejbližší době naplánován. Kontaktujte nás a pokusíme se Vám vyjít vstříc.

Podrobnosti:

This Oracle Database: PL/SQL Fundamentals training introduces you to PL/SQL and explains the benefits of this programming language. You'll learn how to create PL/SQL blocks of application code that can be shared by multiple forms, reports and data management applications. Learn To:Conditionally control code flow (loops, control structures).Use explicit cursors.Handle runtime errors.Describe stored procedures and functions.Work with composite data types.Declare PL/SQL variables.Create anonymous PL/SQL blocks, stored procedures and functions.Declare identifiers and trap exceptions.Benefits to YouEnrolling in this course will teach you how to extend the functionality of the SQL language with PL/SQL language to write application code. Your organization can then realize the full benefit of utilizing Oracle best practices.Participate in Hands-On ExercisesDemonstrations and hands-on exercises reinforce the fundamental concepts you'll learn in this course, which is designed for a 10g and 11g audience (there are minor changes between 10g and 11g features). You'll use Oracle SQL Developer to develop these program units. SQL*Plus and JDeveloper are available as optional tools.

Audience:

• Forms Developer
• PL/SQL Developer
• Portal Developer
• System Analysts
• Technical Consultant

Prerequisites:

• Oracle Database: Introduction to SQL (combination of Oracle Database: SQL Fundamentals I and Oracle Database: SQL Fundamentals II)

Objectives:

• Handle runtime errors
• Describe stored procedures and functions
• Use cursors to process rows
• Design PL/SQL anonymous block that execute efficiently
• Describe the features and syntax of PL/SQL
• Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)

Topics:

Introduction

• Course Objectives
• Course Agenda
• Human Resources (HR) Schema
• Introduction to SQL Developer

Introduction to PL/SQL

• PL/SQL Overview
• Benefits of PL/SQL Subprograms
• Overview of the Types of PL/SQL blocks
• Create a Simple Anonymous Block
• Generate Output from a PL/SQL Block

PL/SQL Identifiers

• List the different Types of Identifiers in a PL/SQL subprogram
• Usage of the Declarative Section to define Identifiers
• Use variables to store data
• Identify Scalar Data Types
• The %TYPE Attribute
• What are Bind Variables?
• Sequences in PL/SQL Expressions

Write Executable Statements

• Describe Basic PL/SQL Block Syntax Guidelines
• Comment Code
• Deployment of SQL Functions in PL/SQL
• How to convert Data Types?
• Nested Blocks
• Identify the Operators in PL/SQL

Interaction with the Oracle Server

• Invoke SELECT Statements in PL/SQL to Retrieve data
• Data Manipulation in the Server Using PL/SQL
• SQL Cursor concept
• Usage of SQL Cursor Attributes to Obtain Feedback on DML
• Save and Discard Transactions

Control Structures

• Conditional processing Using IF Statements
• Conditional processing Using CASE Statements
• Use simple Loop Statement
• Use While Loop Statement
• Use For Loop Statement
• Describe the Continue Statement

Composite Data Types

• Use PL/SQL Records
• The %ROWTYPE Attribute
• Insert and Update with PL/SQL Records
• Associative Arrays (INDEX BY Tables)
• Examine INDEX BY Table Methods
• Use INDEX BY Table of Records

Explicit Cursors

• What are Explicit Cursors?
• Declare the Cursor
• Open the Cursor
• Fetch data from the Cursor
• Close the Cursor
• Cursor FOR loop
• Explicit Cursor Attributes
• FOR UPDATE Clause and WHERE CURRENT Clause

Exception Handling

• Understand Exceptions
• Handle Exceptions with PL/SQL
• Trap Predefined Oracle Server Errors
• Trap Non-Predefined Oracle Server Errors
• Trap User-Defined Exceptions
• Propagate Exceptions
• RAISE_APPLICATION_ERROR Procedure

Stored Procedures and Functions

• Understand Stored Procedures and Functions
• Differentiate between anonymous blocks and subprograms
• Create a Simple Procedure
• Create a Simple Procedure with IN parameter
• Create a Simple Function
• Execute a Simple Procedure
• Execute a Simple Function