Oracle 11g - Program with PL/SQL

Carga Horária: 36

Pré requisito

Para o melhor aproveitamento do curso Oracle 11g - Program with PL/SQL, é imprescindível ter
participado de cursos básicos Oracle Database: SQL I e II ou possuir conhecimentos equivalentes.

Objetivo

O curso Oracle 11g - Program with PL/SQL introduz e apresenta todos os benefícios da linguagem de
programação PL/SQL na plataforma Oracle em aulas práticas. Você vai aprender a criar blocos de código
de aplicação PL / SQL que podem ser compartilhados por vários forms, relatórios e aplicações de
gerenciamento de dados e ainda: controlar condicionalmente o fluxo de código (loops, estruturas de
controle), usar cursores explícitos, lidar com erros de execução, descrever os procedimentos armazenados
e funções, trabalhar com tipos de dados compostos e declarar as variáveis PL / SQL.

Introduction

Course [Link] [Link] the Human Resources (HR) [Link]/SQL development

environments available in this [Link] to SQL Developer.

Introduction to PL/SQL

Overview of PL/[Link] the benefits of PL/SQL [Link] of the types of PL/SQL

[Link] a Simple Anonymous [Link] to generate output from a PL/SQL Block?

Declare PL/SQL Identifiers

List the different Types of Identifiers in a PL/SQL [Link] of the Declarative Section to Define
[Link] variables to store [Link] Scalar Data [Link] %TYPE [Link] are Bind
Variables?Sequences in PL/SQL Expressions.

Write Executable Statements

Describe Basic PL/SQL Block Syntax [Link] to Comment the [Link] of SQL
Functions in PL/[Link] to convert Data Types?Describe Nested [Link] the Operators in
PL/SQL.

Interaction With the Oracle Server

Invoke SELECT Statements in PL/[Link] Data in PL/[Link] Cursor [Link] Errors by

using Naming Conventions when using Retrieval and DML [Link] Manipulation in the Server
using PL/[Link] the SQL Cursor [Link] SQL Cursor Attributes to Obtain Feedback on
[Link] and Discard Transactions.

Control Structures

Conditional processing using IF [Link] processing using CASE [Link]

simple Loop [Link] While Loop [Link] For Loop [Link] the Continue
Statement.

Composite Data Types

Use PL/SQL [Link] %ROWTYPE [Link] and Update with PL/SQL [Link] BY
[Link] INDEX BY Table [Link] INDEX BY Table of Records.

Explicit Cursors

What are Explicit Cursors?Declare the [Link] the [Link] data from the [Link] the
[Link] FOR [Link] %NOTFOUND and %ROWCOUNT [Link] the FOR UPDATE
Clause and WHERE CURRENT Clause.

Exception Handling

Understand [Link] Exceptions with PL/[Link] Predefined Oracle Server [Link]

Non-Predefined Oracle Server [Link] User-Defined [Link]
Exceptions.RAISE_APPLICATION_ERROR Procedure.

Stored Procedures
Create a Modularized and Layered Subprogram [Link] Development With PL/SQL
[Link] the PL/SQL Execution [Link] the benefits of using PL/SQL [Link]
the differences between Anonymous Blocks and [Link], Call, and Remove Stored
[Link] Procedures Parameters and Parameters [Link] Procedure Information.

Stored Functions and Debugging Subprograms

Create, Call, and Remove a Stored [Link] the advantages of using Stored [Link] the
steps to create a stored [Link] User-Defined Functions in SQL [Link] when
calling [Link] side effects when calling [Link] Functions [Link] to debug
Functions and Procedures?

Packages

Listing the advantages of [Link] [Link] are the components of a Package?Develop

a [Link] to enable visibility of a Packages Components?Create the Package Specification and Body
using the SQL CREATE Statement and SQL [Link] the Package [Link] the PL/SQL
Source Code using the Data Dictionary.

Deploying Packages

Overloading Subprograms in PL/[Link] the STANDARD [Link] Forward Declarations to solve

Illegal Procedure [Link] Package Functions in SQL and [Link] State of
[Link] State of a Package [Link] side effects of PL/SQL [Link]
PL/SQL Tables of Records in Packages.

Implement Oracle-Supplied Packages in Application Development

What are Oracle-Supplied Packages?Examples of some of the Oracle-Supplied [Link] does the
DBMS_OUTPUT Package work?Use the UTL_FILE Package to Interact with Operating System
[Link] the UTL_MAIL [Link] UTL_MAIL Subprograms.

Dynamic SQL

The Execution Flow of [Link] is Dynamic SQL?Declare Cursor [Link] Executing a

PL/SQL [Link] Native Dynamic SQL to Compile PL/SQL [Link] to invoke DBMS_SQL
Package?Implement DBMS_SQL with a Parameterized DML [Link] SQL Functional
Completeness.

Design Considerations for PL/SQL Code

Standardize Constants and [Link] Local [Link] Autonomous

[Link] the NOCOPY Compiler [Link] the PARALLEL_ENABLE [Link]
Cross-Session PL/SQL Function Result [Link] DETERMINISTIC Clause with [Link] of Bulk
Binding to Improve Performance.

Triggers

Describe [Link] the Trigger Event Types and [Link] Application Scenarios for
Implementing [Link] DML Triggers using the CREATE TRIGGER Statement and SQL
[Link] the Trigger Event Types, Body, and Firing (Timing)Differences between Statement Level
Triggers and Row Level [Link] Instead of and Disabled [Link] to Manage, Test and
Remove Triggers?

Creating Compound, DDL and Event Database Triggers

What are Compound Triggers?Identify the Timing-Point Sections of a Table Compound [Link]
the Compound Trigger Structure for Tables and [Link] a Compound Trigger to Resolve the
Mutating Table [Link] of Database Triggers to Stored [Link] Triggers on DDL
[Link] Database-Event and System-Events [Link] Privileges Required to Manage
Triggers.

PL/SQL Compiler

What is the PL/SQL Compiler?Describe the Initialization Parameters for PL/SQL [Link] the new
PL/SQL Compile Time [Link] of PL/SQL Compile Time Warnings for [Link] the
benefits of Compiler [Link] the PL/SQL Compile Time Warning Messages [Link] the
Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGS Initialization Parameter, and the
DBMS_WARNING View Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary
Views.

Manage PL/SQL Code

What Is Conditional Compilation?Implement Selection [Link] Predefined and User-Defined
Inquiry [Link] PLSQL_CCFLAGS Parameter and the Inquiry [Link] Compilation
Error Directives to Raise User-Defined [Link] DBMS_DB_VERSION [Link]
DBMS_PREPROCESSOR Procedures to Print or Retrieve Source [Link] and Wrapping PL/SQL
Code.

Manage Dependencies

Overview of Schema Object [Link] Direct Object Dependencies using the

USER_DEPENDENCIES [Link] an Objects [Link] of Dependent [Link] the
Direct and Indirect [Link]-Grained Dependency Management in Oracle Database
[Link] Remote [Link] a PL/SQL Program Unit.