Szkolenia Oracle

Cel szkolenia dlearning

This Oracle Database: Develop PL/SQL Program Units course is designed for developers with basic PL/SQL and SQL language skills. You will learn to develop, execute and manage PL/SQL stored program units, which include: procedures, functions, packages and database triggers.

Plan szkolenia Rozwiń listę

  1. Introduction
    • Course Objectives, Course Agenda and Appendixes Used in this Course
    • Describe the full Human Resources (HR) Schema
    • Review the online Oracle Database 12c SQL and PL/SQL documentation and the additional available resources
    • List the PL/SQL development environments Available in this course
    • Use the SQL Worksheet
    • Execute SQL Statements
    • Work With Script Files
    • Create and Execute Anonymous Blocks
  2. Creating Stored Procedures
    • Describe PL/SQL blocks and subprograms
    • Describe the uses and benefits of procedures
    • Create, call, and remove procedures
    • Use formal and actual parameters
    • Identify the available parameter-passing modes
    • Pass parameters using the positional, named, or combination techniques
    • Handle exceptions in procedures
    • View the procedure information
  3. Creating Functions and Debugging Subprograms
    • Creating Stored Functions
    • The Difference Between Procedures and Functions
    • Developing Functions
    • Creating and Executing and Removing Functions
    • Identifying the Advantages of Using Stored Functions in SQL Statements
    • Using User-Defined Functions in SQL Statements
    • Using a PL/SQL Function in the SQL WITH Clause
    • Restrictions When Calling Functions from SQL statements
  4. Creating Packages
    • Using PL/SQL Packages
    • The Components of a PL/SQL Package
    • The Visibility of a Package’s Components
    • Developing a PL/SQL Package
    • Creating the Package Specification and Package Body
    • Invoking the Package Constructs
    • Creating and Using Bodiless Packages
    • Removing a Package
  5. Working With Packages
    • Overloading Subprograms
    • Using Forward Declarations to Solve Illegal Procedure Reference
    • Initializing Packages
    • Using Package Functions in SQL and Restrictions
    • Controlling Side Effects of PL/SQL Subprograms
    • Persistent State of Packages
    • Persistent State of Package Variables and Cursors
    • Using PL/SQL Tables of Records in Packages
  6. Using Oracle-Supplied Packages in Application Development
    • Using Oracle-Supplied Packages
    • Examples of Some of the Oracle-Supplied Packages
    • How Does the DBMS_OUTPUT Package Work?
    • Using the UTL_FILE Package to Interact With Operating System Files
    • Using the UTL_MAIL Package
  7. Using Dynamic SQL
    • The Execution Flow of SQL
    • Working With Dynamic SQL
    • When Do You Need Dynamic SQL?
    • Using Native Dynamic SQL (NDS)
    • Declaring Cursor Variables
    • Executing a PL/SQL Block Dynamically
    • Using Native Dynamic SQL to Compile PL/SQL Code
  8. Design Considerations for PL/SQL Code
    • Standardize constants with a constant package
    • Standardize exceptions with an exception package
    • Write PL/SQL code that uses local subprograms
    • Grant Roles to PL/SQL Packages and Standalone Stored Subprograms
    • Use the NOCOPY compiler hint to pass parameters by reference
    • Use the PARALLEL ENABLE hint for optimization
    • Use the AUTONOMOUS TRANSACTION pragma to run independent transactions within a single transactio
    • Describe the differences between invoker rights and definer rights
  9. Creating Triggers
    • Describe different types of triggers
    • Describe database triggers and their use
    • Create database triggers
    • Describe database trigger firing rules
    • Remove database triggers
  10. Creating Compound, DDL, and Event Database Triggers
    • Describe compound triggers
    • Describe mutating tables
    • Create triggers on DDL statements
    • Create triggers on system events
    • Display information about triggers
  11. Using PL/SQL compiler
    • Using the PL/SQL CompilerUsing the Initialization Parameters for PL/SQL Compilation
    • Using the PL/SQL Compile Time Warnings
    • Viewing the Current Setting of PLSQL_WARNINGS
    • Viewing the Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary Views
    • Guidelines for Using PLSQL_WARNINGS
  12. Managing Dependencies
    • Describe dependent and referenced objects
    • Track procedural dependencies with dictionary views
    • Predict the effect of changing a database object upon stored procedures and functions
    • Manage local and remote procedural dependencies
Pobierz konspekt szkolenia w formacie PDF

Dodatkowe informacje


Wymagane prerekwizyty:

  • Basic Knowledge of PL/SQL
  • Familiarity with programming languages
  • Oracle Database 12c: Introduction to SQL Ed 1.1
  • Oracle Database: PL/SQL Fundamentals

Sugerowane prerekwizyty:

  • Oracle SQL Tuning for Developers Workshop
Poziom trudności
Czas trwania 3 dni
Zobacz więcej szkoleń Oracle

Prosimy o wypełnienie poniższego formularza, jeśli chcą Państwo uzyskać więcej informacji o powyższym szkoleniu.

Cena szkolenia

  • Forma szkolenia: stacjonarne

  • Forma szkolenia: dlearning


Czytaj więcej o ctab

Czas trwania

3 dni

Dostępne terminy

Brak ustalonych terminów dla tego szkolenia.

Zaproponuj własny termin