• Presencial

Oracle Database 12c: Program with PL/SQL


Este curso dá uma introdução à linguagem PL/SQL e explorar os benefícios desta linguagem de programação. Este curso ajuda a desenvolver e criar procedimentos, funções e pacotes.

Cursos relacionados

Destinatários

Para programadores de objectos de base de dados.

Pré-requisitos

Conhecimentos básicos de programação.

Objetivos

  • Criar fluxos de controlo condicionais
  • Usar pacotes para agrupar e criar objectos
  • Criar trigger para ajudar na lógica de negócio
  • Criar variáveis

Programa

  • Introduction
  • Introduction to PL/SQL
  • Declare PL/SQL Identifiers
  • Write Executable Statements
  • Interaction with the Oracle Server
  • Control Structures
  • Composite Data Types
  • Explicit Cursors
  • Exception Handling
  • Stored Procedures
  • Stored Functions and Debugging Subprograms
  • Packages
  • Deploying Packages
  • Implement Oracle-Supplied Packages in Application Development
  • Dynamic SQL
  • Design Considerations for PL/SQL Code
  • Triggers
  • Creating Compound, DDL, and Event Database Triggers
  • PL/SQL Compiler
  • Manage Dependencies
    • Introduction
        • Course Objectives
        • Course Agenda
        • Describe the Human Resources (HR) Schema
        • PL/SQL development environments available in this course
        • Introduction to SQL Developer
    •  Introduction to PL/SQL
        • Overview of PL/SQL
        • Identify the benefits of PL/SQL Subprograms
        • Overview of the types of PL/SQL blocks
        • Create a Simple Anonymous Block
        • How to generate output from a PL/SQL Block?
    • Declare 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
        • Learn to Comment the Code
        • Deployment of SQL Functions in PL/SQL
        • How to convert Data Types?
        • Describe Nested Blocks
        • Identify the Operators in PL/SQL
    • Interaction with the Oracle Server
        • Invoke SELECT Statements in PL/SQL
        • Retrieve Data in PL/SQL
        • SQL Cursor concept
        • Avoid Errors by using Naming Conventions when using Retrieval and DML Statements
        • Data Manipulation in the Server using PL/SQL
        • Understand the SQL Cursor concept
        • Use SQL Cursor Attributes to Obtain Feedback on DML
        • Save and Discard Transactions
    • Control Structures
        • Conditional processing using IF Statements
        • Conditional processing using CASE Statements
        • Describe simple Loop Statement
        • Describe While Loop Statement
        • Describe For Loop Statement
        • Use the Continue Statement
    • Composite Data Types
        • Use PL/SQL Records
        • The %ROWTYPE Attribute
        • Insert and Update with PL/SQL Records
        • 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
        • The %NOTFOUND and %ROWCOUNT Attributes
        • Describe the 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
        • Create a Modularized and Layered Subprogram Design
        • Modularize Development With PL/SQL Blocks
        • Understand the PL/SQL Execution Environment
        • List the benefits of using PL/SQL Subprograms
        • List the differences between Anonymous Blocks and Subprograms
        • Create, Call, and Remove Stored Procedures
        • Implement Procedures Parameters and Parameters Modes
        • View Procedure Information
    • Stored Functions and Debugging Subprograms
        • Create, Call, and Remove a Stored Function
        • Identify the advantages of using Stored Functions
        • Identify the steps to create a stored function
        • Invoke User-Defined Functions in SQL Statements
        • Restrictions when calling Functions
        • Control side effects when calling Functions
        • View Functions Information
        • How to debug Functions and Procedures?
    • Packages
        • Listing the advantages of Packages
        • Describe Packages
        • What are the components of a Package?
        • Develop a Package
        • How to enable visibility of a Package’s Components?
        • Create the Package Specification and Body using the SQL CREATE Statement and SQL Developer
        • Invoke the Package Constructs
        • View the PL/SQL Source Code using the Data Dictionary
    • Deploying Packages
        • Overloading Subprograms in PL/SQL
        • Use the STANDARD Package
        • Use Forward Declarations to solve Illegal Procedure Reference
        • Implement Package Functions in SQL and Restrictions
        • Persistent State of Packages
        • Persistent State of a Package Cursor
        • Control side effects of PL/SQL Subprograms
        • Invoke 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 Packages
        • How does the DBMS_OUTPUT Package work?
        • Use the UTL_FILE Package to Interact with Operating System Files
        • Invoke the UTL_MAIL Package
        • Write UTL_MAIL Subprograms
    • Dynamic SQL
        • The Execution Flow of SQL
        • What is Dynamic SQL?
        • Declare Cursor Variables
        • Dynamically Executing a PL/SQL Block
        • Configure Native Dynamic SQL to Compile PL/SQL Code
        • How to invoke DBMS_SQL Package?
        • Implement DBMS_SQL with a Parameterized DML Statement
        • Dynamic SQL Functional Completeness
    • Design Considerations for PL/SQL Code
        • Standardize Constants and Exceptions
        • Understand Local Subprograms
        • Write Autonomous Transactions
        • Implement the NOCOPY Compiler Hint
        • Invoke the PARALLEL_ENABLE Hint
        • The Cross-Session PL/SQL Function Result Cache
        • The DETERMINISTIC Clause with Functions
        • Usage of Bulk Binding to Improve Performance
    • Triggers
        • Describe Triggers
        • Identify the Trigger Event Types and Body
        • Business Application Scenarios for Implementing Triggers
        • Create DML Triggers using the CREATE TRIGGER Statement and SQL Developer
        • Identify the Trigger Event Types, Body, and Firing (Timing)
        • Differences between Statement Level Triggers and Row Level Triggers
        • Create Instead of and Disabled Triggers
        • How 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 Trigger
        • Understand the Compound Trigger Structure for Tables and Views
        • Implement a Compound Trigger to Resolve the Mutating Table Error
        • Comparison of Database Triggers to Stored Procedures
        • Create Triggers on DDL Statements
        • Create Database-Event and System-Events Triggers
        • System Privileges Required to Manage Triggers
    • PL/SQL Compiler
        • What is the PL/SQL Compiler?
        • Describe the Initialization Parameters for PL/SQL Compilation
        • List the new PL/SQL Compile Time Warnings
        • Overview of PL/SQL Compile Time Warnings for Subprograms
        • List the benefits of Compiler Warnings
        • List the PL/SQL Compile Time Warning Messages Categories
        • Setting the Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGS Initialization Parameter, and the DBMS_WARNING Package Subprograms
        • View Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary Views
    • Manage Dependencies
        • Overview of Schema Object Dependencies
        • Query Direct Object Dependencies using the USER_DEPENDENCIES View
        • Query an Object’s Status
        • Invalidation of Dependent Objects
        • Display the Direct and Indirect Dependencies
        • Fine-Grained Dependency Management in Oracle Database 12c
        • Understand Remote Dependencies
        • Recompile a PL/SQL Program Unit

Outras Edições

Oracle Database 12c: Program with PL/SQL

Área

Dados

Como chegou até nós

Os seus dados pessoais são recolhidos em conformidade com o Regulamento Geral de Proteção de Dados (RGPD).
Consente que os seus dados sejam utilizados, nos termos da nossa Politica de Privacidade, para o contacto/envio de:

Ações de informação, de marketing de produtos e serviços, como campanhas e eventos?

Para mais informações, consulte a Política de Privacidade do Grupo Rumos.
pode retirar o seu consentimento a qualquer momento através do botão Cancelar subscrição ou Unsubscribe que estão presentes em cada comunicação enviada, bem como exercer os direitos descritos na politica de privacidade

Para a subscrição da newsletter deverá desativar o bloqueio de pop-ups do seu browser.