Visão Geral

Curso Oracle Database 19c PL/SQL Fundamentals: Aprenda uma introdução abrangente ao PL/SQL, incluindo o uso do SQL Developer e do SQL Plus.

Oracle 19C PL/SQL Fundamentals fornece uma introdução completa, prática e abrangente ao PL/SQL, incluindo o uso do SQL Developer e do SQL Plus.

Objetivo

Oracle 19C PL/SQL Fundamentals fornece uma introdução completa, prática e abrangente ao PL/SQL, incluindo o uso do SQL Developer e do SQL Plus.

Publico Alvo

• Este Curso Oracle Database 19c PL/SQL Fundamentals é apropriado para qualquer pessoa que precise fazer interface com um banco de dados Oracle ou para aqueles que precisam de um conhecimento geral da funcionalidade do banco de dados Oracle. Isso incluiria usuários finais, analistas de negócios, desenvolvedores de aplicativos e administradores de banco de dados.

Pre-Requisitos

• Os participantes deverão ter conhecimentos básicos de informática. Um conhecimento básico de bancos de dados é útil, mas não obrigatório.

Conteúdo Programatico

PL/SQL PROGRAM STRUCTURE

1. PL/SQL VS. SQL
2. PL/SQL ENGINES AVAILABLE
3. ANONYMOUS PL/SQL BLOCK STRUCTURE
4. OBJECT NAMING RULES
5. VARIABLE DECLARATIONS
6. AVAILABLE DATATYPES
7. Scalar Datatypes
8. Using Extended Datatypes
9. Object Types
10. EXECUTABLE STATEMENTS
11. EXPRESSIONS
12. BLOCK LABELING
13. VARIABLE SCOPING RULES
14. COMMENTS IN PROGRAMS AND SCRIPTS
15. BASIC CODING STANDARDS

PL/SQL FLOW CONTROL

1. CONDITIONAL CONTROL
2. COMPARISON OPERATORS
3. LOGICAL OPERATORS
4. Truth Tables
5. REPETITION CONTROL
6. The Simple Loop
7. WHILE Loop
8. FOR Loop
9. CONTINUE Statements In Loops
10. Step Loops
11. THE GOTO STATEMENT
12. CASE EXPRESSIONS / STATEMENTS
13. BIND VARIABLES
14. SUBSTITUTION VARIABLES

SQL DEVELOPER AND PL/SQL

1. SQL DEVELOPER AND PL/SQL
2. CREATING AND EXECUTING SCRIPTS

SELECT INTO

1. SELECTING SINGLE ROWS OF DATA
2. ANCHORING VARIABLES TO DATATYPES
3. DML IN PL/SQL
4. RETURNING INTO
5. SEQUENCES IN PL/SQL
6. TRANSACTION CONTROL IN PL/SQL
7. AUTONOMOUS TRANSACTIONS

THE PL/SQL CURSOR

1. DECLARING EXPLICIT CURSORS
2. OPENING AND CLOSING EXPLICIT CURSORS
3. USING EXPLICIT CURSORS TO RETRIEVE VALUES
4. EXPLICIT CURSOR ATTRIBUTES
5. USING A LOOP WITH AN EXPLICIT CURSOR
6. USING PERCENT ROWTYPE WITH CURSORS
7. THE CURSOR FOR LOOP
8. DBMS OUTPUT

OPTIMIZATION

1. TIMING PL/SQL
2. FOR UPDATE / WHERE CURRENT OF

PL/SQL EXCEPTION HANDLING

1. THE EXCEPTION SECTION
2. ORACLE NAMED EXCEPTIONS
3. PRAGMA EXCEPTION_INIT
4. USER DEFINED EXCEPTIONS
5. The Scope Of User-Defined Exceptions
6. RAISING NAMED EXCEPTIONS
7. EXCEPTION PROPAGATION
8. RAISING AN EXCEPTION AGAIN
9. LIFE AFTER AN EXCEPTION
10. WHEN OTHERS
11. TAKING YOUR BALL AND GOING HOME
12. DBMS_ERRLOG

STORED PROCEDURES

1. PROCEDURES
2. BENEFITS OF STORED PROCEDURES
3. Database Security
4. Performance
5. Productivity
6. Portability
7. PARAMETERS AND STORED PROCEDURES
8. Parameter Notation
9. STORED OBJECT CREATION
10. Syntax For Creating A Procedure
11. COMPILATION ERRORS
12. VIEWING COMPILED CODE
13. DROPPING A PROCEDURE
14. THE ALTER COMMAND AND STORED PROCEDURES

CREATING FUNCTIONS IN PL/SQL

1. FUNCTIONS
2. PURITY LEVELS
3. USING WHITE LISTS
4. OPTIMIZATIONS
5. PARALLEL ENABLE
6. DETERMINISTIC FUNCTIONS
7. PL/SQL RESULT CACHE
8. NOCOPY
9. DBMS OUTPUT IN FUNCTIONS
10. USING THE WITH CLAUSE FOR FUNCTIONS
11. PRAGMA UDF
12. PRAGMA INLINE
13. USING SQL DEVELOPER WITH STORED PROCEDURES
14. DEBUGGING

PACKAGES

1. CREATING PACKAGES
2. PACKAGE BENEFITS
3. Security
4. Persistent State
5. I/O Efficiency
6. A SIMPLE PACKAGE
7. OVERLOADING
8. BODILESS PACKAGES
9. SOURCE CODE ENCRYPTION
10. CREATING PACKAGES FROM PROCEDURES AND FUNCTIONS

CREATING DML TRIGGERS

1. DML TRIGGERS
2. DML TRIGGER STRUCTURE
3. CONDITIONAL TRIGGERING PREDICATES
4. TRIGGERS FOR BUSINESS RULES ENFORCEMENT
5. MUTATING AND CONSTRAINING TABLES
6. COMPOUND TRIGGERS
7. CONTROLLING FIRING ORDER
8. DDL FOR TRIGGERS
9. VIEWING TRIGGER SOURCE
10. INSTEAD OF TRIGGERS

ADVANCED CONCEPTS

1. EMBEDDED PROCEDURES
2. THE OPTIMIZING COMPILER
3. PL/SQL COMPILER WARNINGS
4. COMPILING FOR DEBUGGING
5. CONDITIONAL COMPILATION / INQUIRY DIRECTIVES
6. Error Directives
7. Inquiry Directives
8. Using Static Constants
9. DBMS DB VERSION
10. NATIVE COMPILATION
11. Recompiling All Database Objects

FILE OPERATIONS

1. MOVING FILES BETWEEN DATABASES
2. DIRECTORY ACCESS
3. FILE MANIPULATION
4. FCLOSE Procedure
5. FCLOSE ALL Procedure
6. FCOPY Procedure
7. FFLUSH Procedure
8. FGETATTR Procedure
9. FGETPOS Function
10. FOPEN Function
11. FREMOVE Procedure
12. FRENAME Procedure
13. FSEEK Procedure
14. GET_LINE Procedure
15. GET_RAW Procedure
16. IS_OPEN Function
17. NEW_LINE Procedure
18. PUT Procedure
19. PUT_LINE Procedure
20. PUTF Procedure
21. PUT_RAW Procedure

COLLECTIONS

1. DEFINING RECORDS
2. COLLECTIONS
3. Associative Arrays
4. Nested Tables
5. VARRAYs / VARYING ARRAYs
6. Assignments
7. Comparing Collections
8. COLLECTION METHODS
9. EXISTS
10. FIRST
11. LAST
12. COUNT
13. LIMIT
14. PRIOR
15. NEXT
16. DELETE
17. TRIM
18. SET THEORY AND NESTED TABLES

BULK OPERATIONS

1. BULK BINDING
2. FORALL
3. SQL%BULK_ROWCOUNT
4. SAVE EXCEPTIONS / SQL%BULK_EXCEPTIONS
5. BULK COLLECT
6. The LIMIT Clause
7. FORALL And The INDICES OF Clause
8. FORALL And VALUES OF
9. PIPELINED TABLE FUNCTIONS
10. MULTIDIMENSIONAL COLLECTIONS