Quản Trị Và Lập Trình CSDL ORACLE Với PL/SQL

PART I: DATABASE ADMINISTRATION (DBA)

Lesson 1: Introduction

• Describe the Basic Concepts of a Relational Database
• Discuss Core Database Administrator Tasks

Lesson 2: Installing Oracle Database Software

• Identify System Requirements
• Use Optimal Flexible Architecture
• Install Software with the Oracle Universal Installer

Lesson 3: Create an Oracle Database 

• Identify System Requirements
• Use Optimal Flexible Architecture
• Install Software with the Oracle Universal Installer

Lesson 4: Controlling the database

• Start and Stop the Listener
• Startup and Shutdown the Database

Lesson 5: Storage Structures 

• Define the Purpose of Tablespaces and Data Files
• Create and Manage Tablespaces
• Obtain Tablespace Information

Lesson 6: Administering Users

• Create and Manage Database User Accounts
• Manage Roles
• Grant and Revoke Privileges
• Control Resource Usage by Users
• Control Password History

Lesson 7: Managing Schema Objects

• Create and Modify Tables, Indexes, and Views
• Define Constraints
• View the Attributes and the Contents of a Table

Lesson 8: Managing Data

• Manipulating Data through SQL
• Use Import, Export, Data Pump, and SQL Loader

Lesson 9: Oracle Database Security

• Manage Default User Accounts
• Implement Standard Password Security Features
• Audit Database Activity

Lesson 10: Oracle Net Services

• Understand Oracle Net Concepts
• Use Oracle Net Manager to Create and Configure Listeners
• Configure Client and Middle-Tier Connection
• Use the Listener Control Utility to Control the Oracle Net Listener
• Secure the Listener
• Use TNSPING to Test Oracle Net Connectivity

Lesson 11: Proactive Maintenance: Objectives

• Set Warning and Critical Alert Thresholds
• Collect and Use Baseline Metrics
• Use Tuning/Diagnostic Advisors/ADDM/ADR

Lesson 12: Undo Management

• Monitor and Administer Undo
• Configure and Guarantee Retention

Lesson 13: Recovery Manager Configuration

• Parameters Affecting RMAN
• Use of a Flashback Recovery Area
• The CONFIGURE Command
• Persistent Settings
• NOCATALOG / CATALOG Mode
• Channel Allocation
• Controlfile Autobackup
• Retention Policies

Lesson 14: Recovery Manager Operations

• Using the BACKUP Command
• Creating Backup Sets / Image Copies
• Compressed Backups
• Incremental Backups
• Block Change Tracking
• RMAN Backup Monitoring
• REPORT and LIST Commands

Lesson 15: Non-Critical Loss Recovery

• Password and Network Files
• Read-Only Tablespaces
• Temporary Tablespaces
• Index Tablespaces
• Redo Log Members / Control Files

Lesson 16: Database Recovery

• Complete Recovery
• Incomplete Recovery

Lesson 17: Scheduler

• Creating Jobs and Job Classes
• Creating Schedules and Windows
• Create Scheduler Programs
• Prioritizing Jobs
• Query Scheduler Information
• Working with Calendaring tools

PART II: PL/SQL PROGRAMMING

Lesson 1: Introduction

• Course Objectives
• Course Agenda
• Describe the Human Resources (HR) Schema
• PL/SQL development environments available in this course
• Introduction to SQL Developer

Lesson 2: 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?

Lesson 3: 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

Lesson 4: 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

Lesson 5: 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

Lesson 6: 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

Lesson 7: 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

Lesson 8: 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

Lesson 9: 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

Lesson 10: 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

Lesson 11: 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?

Lesson 12: 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

Lesson 13: 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

Lesson 14: 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

Lesson 15: 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

Lesson 16: 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

Lesson 17: 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?

Lesson 18: 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

Lesson 19: 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