Global Vendors Training

Home > > 전체교육일정

전체교육일정

Oracle Database: Program with PL/SQL

교육일정
교육기간
5일
교육금액
2,084,775원(부가세포함)
전체일정보기 수강신청

교육개요

* 09:30 ~ 17:00 (30시간 / 5일) (점심시간:1시간30분)

본 과정에서는 다중 Form, 보고서 및 데이터 관리 응용 프로그램에서 공유할 수 있는 응용
프로그램 코드의 PL/SQL 블록을 생성하는 방법을 학습합니다.
또한 익명 PL/SQL 블록 생성 방법과 내장 프로시저 및 함수와 변수 선언, 예외 트랩 커서 선언 및 제어에 대해서도 학습합니다.



교육목표

- Conditionally control code flow (loops, control structures)
- Design and use PL/SQL packages to group and contain related constructs.
- Create triggers to solve business challenges.
- Use some of the Oracle supplied PL/SQL packages to generate screen output and file output.
- Create anonymous PL/SQL blocks of code.



수강대상

- 데이타베이스 관리자
- 애플리케이션 개발자
- 기술 지원 전문가
- 시스템 관리자



선수과목

Oracle Database: Introduction to SQL
Previous programming experience



강의내용

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

2. Working with Oracle Cloud Exadata Express Cloud Service
- Introduction to Oracle Database Exadata Express Cloud Service
- Accessing Cloud Database using SQL Workshop
- Connecting to Exadata Express using Database Clients

3. 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?

4. Declare PL/SQL Variables
- 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

5. Write Anonymous PL/SQL Blocks
- 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

6. SQL Statements in a PL/SQL block
- 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

7. 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

8. 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

9. 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

10. 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

11. 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

12. Stored Functions
- 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

13. Debugging Subprograms
- How to debug Functions and Procedures?
- Debugging through SQL Developer

14. Packgaes
Listing the advantages of Packages
Describe Packages
What are the components of a Package?
Develop a Package
How to enable visibility of a Packages 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 Parametand 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 Objects 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



기타

교육비 부가세포함