Oracle® 11g, Basics of PL/SQL Language

The course is an introduction to the Oracle PL/SQL procedural language. The language version used in DBMS 11g is considered in this training.

  • duration 24 hours
  • Language English
  • format Online
duration
24 hours
location
Online
Language
English
Code
DB-018
price
€ 650 *

Available sessions

To be determined



Training for 7-8 or more people?
Customize trainings for your specific needs

Description

The course focuses on the basics of the PL/SQL language and its structures, such as anonymous blocks, procedures and functions, as well as on specifics of their operation on the Oracle® 11g R2 database server. The training talks about various types of data (including composite types and collections), control statements, as well as the mechanism of exceptions triggering and exceptions handling in EXCEPTION block. Oracle® SQL Developer is used for developing.

After completing the course, a certificate is issued on the Luxoft Training form

Objectives

  • Reading and analyzing PL/SQL code
  • Creating anonymous blocks and stored executable PL/SQL code blocks
  • Using the basic algorithmic constructions of the language

Target Audience

  • Database administrators
  • System administrators
  • Application developers
  • Testers

Prerequisites

  • Knowledge of the principles of algorithmization.
  • Knowledge of the principles of operation of the relational database management system and basics of SQL language

Roadmap

Introduction

  • Description of PL/SQL
  • Capabilities of PL/SQL
  • Advantages of using PL/SQL

Declaring Variables

  • Anonymous PL/SQL block and its sections
  • Types of PL/SQL programming constructs
  • Defining and using PL/SQL variables
  • Declaring PL/SQL variables and constants
  • Executing PL/SQL block

Writing executable commands

  • The executable section of the block
  • Rules for nested blocks
  • Executing and testing PL/SQL block
  • Using coding conventions

Interaction with Oracle® Server

  • Features of SELECT-statement in PL/SQL block
  • Dynamic declaration of data types
  • Writing data manipulation statements (DML) in PL/SQL block
  • Transaction management in PL/SQL
  • Defining the output of DML statements

The use of control structures

  • Types of control structures
  • Using the IF statement
  • Expressions and CASE statement
  • Various types of loops
  • Controlling handling using nested loops and labels
  • Using logical tables

Working with composite data types

  • Creating user-defined PL/SQL records
  • Adding and updating tables using PL/SQL records. Associative arrays

Using Cursors

  • Differences between implicit and explicit cursor
  • Statements for handling explicit cursor
  • Explicit cursor attributes
  • FOR loop for the cursor
  • Cursors with parameters
  • Using FOR UPDATE in the definition of the cursor
  • Cursors with subqueries

Exception handling

  • Defining PL/SQL exceptions
  • Propagation of unhandled exceptions
  • Types of PL/SQL exception handlers
  • Interception of non-predefined exceptions
  • Exception propagation in nested blocks
  • Generation of custom PL/SQL error messages

Creating stored procedures and functions

  • Creating and calling a procedure
  • Getting information about procedures and deleting procedures
  • Creating and calling functions
  • Getting information about the functions and deleting functions

Working with packages

  • Package structure
  • Package design: header and body
  • Creating packages
  • Advantages of using packages

Using Triggers

  • The principle of operation
  • Types of triggers
  • Creating table level triggers


Related courses

You may also be interested in

Discover more about professional growth and skills development

contact us