Oracle Database: PL/SQL pamati

• Izstrādāt PL/SQL anonīmu bloku, kas darbojas efektīvi.
• Izmantot PL/SQL programmēšanas konstrukcijas un nosacīti kontrolēt koda plūsmu (ciklus, vadības struktūras un kursorus).
• Rīkoties ar izņēmumiem programmas vienībās.
• Izveidot un izpildīt vienkāršas procedūras un funkcijas.
• Uzrakstīt PL/SQL kodu saskarnei ar datu bāzi.
• Aprakstīt PL/SQL funkcijas un sintaksi.

Kurss paredzēts analītiķiem, izstrādātājiem un ieviesējiem.

• Izmantot atbilstošus MySQL rīkus.

• Pamata zināšanas par SQL
• Orientēšanas pamata datubāzes jēdzienos
• Priekšstats par programmēšanas konceptu

Oracle University vai BDA mācību materiāli.

Nav

1. Introduction

• Course Objectives, Course Agenda and Class Account Information
• The Human Resources (HR) Schema
• Appendices Used in this Course
• PL/SQL Development Environments

2. Introduction to PL/SQL

• Understanding the benefits and structure of PL/SQL
• Understanding PL/SQL Blocks
• Generating output messages in PL/SQL

3. Declaring PL/SQL Variables

• Identifying valid and invalid identifiers
• Declaring and initializing variables
• Various data types
• Identifying the benefits of using the %TYPE attribute
• Using bind variables

4. Writing Anonymous PL/SQL blocks

• Lexical Units in a PL/SQL Block
• Using SQL Functions in PL/SQL
• Data Type Conversion
• Using Nested Blocks as Statements
• Referencing an Identifier Value in a Nested Block
• Qualifying an Identifier with a Label
• Operators in PL/SQL
• Using Sequences in PL/SQL Expressions

5. Using SQL Statements within a PL/SQL Block

• Using SQL Statements in PL/SQL
• Retrieving Data in PL/SQL with the SELECT statement
• Using Naming Conventions in DML Statements and data retrieval
• Manipulating Data on the Server Using PL/SQL
• SQL Cursors
• Using SQL Cursor Attributes to Obtain Feedback on DML
• Saving and Discarding Transactions

6. Writing Control Structures

• Controlling PL/SQL Flow of Execution
• Using IF and CASE Statements for conditional processing
• Handling Nulls
• Building Boolean Conditions with Logical Operators
• Using Iterative Control with Loop Statements

7. Working with Composite Data Types

• Composite Data Types of PL/SQL Records and Tables
• Using PL/SQL Records
• Inserting and Updating PL/SQL Records
• Using INDEX BY Tables
• Using Associative arrays
• Declaring and Using VArrays

8. Using Explicit Cursors

• Cursors
• Explicit Cursor Operations
• Controlling Explicit Cursors
• Using Explicit Cursors to Process Rows
• Cursors and Records
• Cursor FOR Loops Using Subqueries
• Explicit Cursor Attributes
• %NOTFOUND and %ROWCOUNT Attributes

9. Handling Exceptions

• Concept of Exception
• Handling Exceptions with PL/SQL
• Predefined Exceptions
• Trapping Predefined and Non-predefined Oracle Server Errors
• Functions that Return Information on Exceptions
• Usage of PRAGMA keyword
• Trapping User-Defined Exceptions
• Propagating Exceptions

10. Creating Stored Procedures and Functions

• Overview of Stored Procedures and Functions
• Differentiating between anonymous blocks and subprograms
• CREATE OR REPLACE PROCEDURE | FUNCTION
• Understanding the Header Area of a Stored Procedure and Function
• Creating Simple Procedures and Functions
• Creating a Simple Procedure with an IN Parameter
• Executing a Procedure and a Function