دوره آموزش Oracle PLSQL Program Units

Creating Stored Procedures

o Describe PL/SQL blocks and subprograms

o Describe the uses and benefits of procedures

o Create, call, and remove procedures

o Use formal and actual parameters

o Identify the available parameter-passing modes

o Pass parameters using the positional, named, or combination techniques

o Handle exceptions in procedures

o View the procedure information

Creating Functions and Debugging Subprograms

o Creating Stored Functions

o The Difference Between Procedures and Functions

o Developing Functions

o Creating and Executing and Removing Functions

o Identifying the Advantages of Using Stored Functions in SQL Statements

o Using User-Defined Functions in SQL Statements

o Using a PL/SQL Function in the SQL WITH Clause

o Restrictions When Calling Functions from SQL statements

Creating Packages

o Using PL/SQL Packages

o The Components of a PL/SQL Package

o The Visibility of a Package’s Components

o Developing a PL/SQL Package

o Creating the Package Specification and Package Body

o Invoking the Package Constructs

o Creating and Using Bodiless Packages

o Removing a Package

Working with Packages

o Overloading Subprograms

o Using Forward Declarations to Solve Illegal Procedure Reference

o Initializing Packages

o Using Package Functions in SQL and Restrictions

o Controlling Side Effects of PL/SQL Subprograms

o Persistent State of Packages

o Persistent State of Package Variables and Cursors

o Using PL/SQL Tables of Records in Packages

Using Oracle-Supplied Packages in Application Development

o Using Oracle-Supplied Packages

o Examples of Some of the Oracle-Supplied Packages

o How Does the DBMS_OUTPUT Package Work?

o Using the UTL_FILE Package to Interact With Operating System Files

o Using the UTL_MAIL Package

Using Dynamic SQL

o The Execution Flow of SQL

o Working With Dynamic SQL

o When Do You Need Dynamic SQL?

o Using Native Dynamic SQL (NDS)

o Declaring Cursor Variables

o Executing a PL/SQL Block Dynamically

o Using Native Dynamic SQL to Compile PL/SQL Code

Design Considerations for PL/SQL Code

o Standardize constants with a constant package

o Standardize exceptions with an exception package

o Write PL/SQL code that uses local subprograms

o Grant Roles to PL/SQL Packages and Standalone Stored Subprograms

o Use the NOCOPY compiler hint to pass parameters by reference

o Use the PARALLEL ENABLE hint for optimization

o Use the AUTONOMOUS TRANSACTION pragma to run independent transactions within a single transaction

o Describe the differences between invoker rights and definer rights

Creating Compound, DDL, and Event Database Triggers

o Describe compound triggers

o Describe mutating tables

o Create triggers on DDL statements

o Create triggers on system events

o Display information about triggers

Oracle Index Optimization

o Oracle Index types

o Index cardinality

o multi-column indexes

o bitmap indexes

o Index unique vs. index normal

o Finding missing indexes

o Partition Index and Local Indexes

Partitioning

o Getting optimal pruning

o Partition exchange loading

o Partitioning and unusable indexes

o Partitioning method

o Partitioning key

o Partition granularity

o Partitioning Indexes

o Local Prefixed Indexes

o Local Non-Prefixed Indexes

o Global Prefixed Indexes

o Global Non-Prefixed Indexes

o Efficient statistics management

Materialized view

o Explain the benefits of materialized views

o Increase application performance using Materialized Views

o Learn how to create and use Materialized

o Different types of materialized views

o Views and Materialized View logs

o Set Materialized Views to refresh in a variety of ways

o Create materialized views

o Create materialized view logs

o Refreshing materialized views

o Altering Materialized Views

o Dropping Materialized views

o Understand Materialized View Groups

o Exploit Oracle’s Query Rewrite capability

Advanced Features For Query and Query Performance Tuning Tips

o Oracle SQL Analytics

o SQL Aggregate Functions

o Oracle Hierarchies

o Using With Clause