Oracle Database 11g: Advanced PL/SQL

Pershkrimi i kursit

Ky kurs eshte i destinuar per te gjithe ata persona qe programojne ne bazat e te dhenave Oracle me gjuhen PL/SQL.

Njohuri paraprake

Kursi kerkon qe pjesemarrresit te kene njohuri te mira te gjuhes SQL dhe te jene te familjarizuar me bazat e te dhenave Oracle.

Rekomandohet qe te paraprihet nga kursi 1ZO-051 Oracle 11g SQL Fundamentals.

Ju do te mesoni

• Describe the features and syntax of PL/SQL.

• Design a PL/SQL anonymous block that executes efficiently.

• Write PL/SQL code to interface with the database.

• Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors

• Handle runtime errors.

• Describe stored procedures and functions.

• Apply declaring identifiers and trapping exceptions.

• Create PL/SQL blocks of application code that can be shared by multiple forms, reports and data management applications.

Kohezgjatja e kursit

Kursi eshte i organizuar ne 24 ore mesimore dhe zhvillohet ne nje periudhe 1-2 Muaj.

Zhvillohet dy deri ne tre here ne jave,me nga dy ore, kryesisht ne oraret e pasdites.

Organizimi i kursit

Kursi zhvillohet ne laborator dhe eshte i orientuar nga praktika.

Zhillohet me nje numer te kufizuar pjesemarresish, ku secili punon ne nje kompjuter te vetem.

Leksionet e trajnimit jane edhe on-line.

Certifikimi

• ICT Education

Provim nr

• 1Z0- 044

Programi i Kursit

Introduction to PL/SQL

• Understanding the benefits and structure of PL/SQL

• Examining PL/SQL Blocks

• Generating output messages in PL/SQL

Declaring PL/SQL Variables

• Identify valid and invalid identifiers

• Declare and initialize variables

• List and describe various data types

• Identify the benefits of using the %TYPE attribute

• Declare, use, and print bind variables

Writing Executable Statements

• List Lexical Units in a PL/SQL Block

• Use SQL Functions in PL/SQL

• Use Data Type Conversion

• Use Nested Blocks as Statements

• Reference an Identifier Value in a Nested Block

• Qualify an Identifier with a Label

• Use Operators in PL/SQL

• Using Sequences in PL/SQL Expressions

Using SQL Statements within a PL/SQL Block

• Identify and use the SQL Statements in PL/SQL

• Retrieve Data in PL/SQL with the SELECT statement

• Avoid Errors by Using Naming Conventions When Using Retrieval and DML Statements

• Manipulate Data in the Server Using PL/SQL

• The SQL Cursor concept

• Use SQL Cursor Attributes to Obtain Feedback on DML

• Save and Discard Transactions

Writing Control Structures

• Control PL/SQL Flow of Execution

• Conditional processing Using IF and CASE Statements

• Handle Nulls to Avoid Common Mistakes

• Build Boolean Conditions with Logical Operators

• Use Iterative Control with Looping Statements

Working with Composite Data Types

• Learn the Composite Data Types of PL/SQL Records and Tables

• Use PL/SQL Records to Hold Multiple Values of Different Type

• Inserting and Updating with PL/SQL Records

• Use INDEX BY Tables to Hold Multiple Values of the Same Data Type

Using Explicit Cursors

• Define Cursors

• Explain Explicit Cursor Operations

• Controlling Explicit Cursors

• Use Explicit Cursors to Process Rows

• Cursors and Records

• Cursor FOR Loops Using Subqueries

• Explicit Cursor Attributes

• The %NOTFOUND and %ROWCOUNT Attributes

Handling Exceptions

• Define Exception

• Handling Exceptions with PL/SQL

• Predefined Exceptions

• Trapping Predefined and Non-predefined Oracle Server Errors

• Functions that Return Information on Encountered Exceptions

• Trapping User-Defined Exceptions

• Propagate Exceptions

• Basic RAISE Statement

Creating Stored Procedures and Functions

• Overview of Stored Procedures and Functions

• Differentiate between anonymous blocks and subprograms

• Show the CREATE OR REPLACE PROCEDURE | FUNCTION

• Understand the Header Area of a Stored Procedure and Function

• Create Simple Procedures and Functions

• Create a Simple Procedure with an IN Parameter

• Execute a Procedure and a Function