ISQLGlobal Global Logo
Contact UsCertificationPlacementsCoursesCorporateHome

    Oracle Database 12c: PL/SQL Fundamentals

    The Oracle Database: PL/SQL Fundamentals Ed 2 course introduces students to the PL/SQL programming language. In the class, students learn to create PL/SQL blocks of application code.

    Learn To:

    • Use various features of PL/SQL.
    • Write anonymous blocks of codein PL/SQL.
    • Use various PL/SQL conditionalconstructs and loops in program blocks.
    • Interface the PL/SQL code withthe database.
    • Use stored procedures andfunctions in their program blocks.
    • Handle Exceptions in PL/SQLcode.

    Benefits To You

    Students will benefit from this course as it enables programmers with the skill of using this programming language. In the class students will learn to create anonymous PL/SQL blocks, to create procedures, functions and triggers. Students will learn to use explicit cursors in their program blocks. Demonstrations and hands-on practice reinforce the fundamental concepts. Students use Oracle SQL Developer to develop these program units. SQL*Plus is introduced as optional tools.

    Audience

    • Analyst
    • Developer
    • Implementer
    Course Objectives
      • Design PL/SQL anonymous blockthat execute efficiently
      • Use PL/SQL programmingconstructs and conditionally control code flow (loops, controlstructures, and explicit cursors)
      • Handle exceptions in theprogram units
      • Create and execute simplestored procedures and functions
      • Write PL/SQL code to interfacewith the database
      • Describe the features andsyntax of PL/SQL

    Course Topics

    Introduction

    • Course Objectives, Course Agendaand Class Account Information
    • The Human Resources (HR) Schema
    • Appendices Used in this Course
    • PL/SQL Development Environments
    Introduction to PL/SQL
    • Understanding the benefits andstructure of PL/SQL
    • Understanding PL/SQL Blocks
    • Generating output messages inPL/SQL
    Declaring PL/SQL Variables
    • Identifying valid and invalididentifiers
    • Declaring and initializingvariables
    • Various data types
    • Identifying the benefits ofusing the %TYPE attribute
    • Using bind variables
    Writing Anonymous PL/SQL blocks

    • Lexical Units in a PL/SQL Block
    • Using SQL Functions in PL/SQL
    • Data Type Conversion
    • Using Nested Blocks asStatements
    • Referencing an Identifier Valuein a Nested Block
    • Qualifying an Identifier with aLabel
    • Operators in PL/SQL
    • Using Sequences in PL/SQLExpressions
    Using SQL Statements within a PL/SQL Block

    • Using SQL Statements in PL/SQL
    • Retrieving Data in PL/SQL withthe SELECT statement
    • Using Naming Conventions in DMLStatements and data retrieval
    • Manipulating Data on the ServerUsing PL/SQL
    • SQL Cursors
    • Using SQL Cursor Attributes toObtain Feedback on DML
    • Saving and Discarding Transactions
    Writing Control Structures
    • Controlling PL/SQL Flow ofExecution
    • Using IF and CASE Statements forconditional processing
    • Handling Nulls
    • Building Boolean Conditions withLogical Operators
    • Using Iterative Control withLoop Statements
    Working with Composite Data Types
    • Composite Data Types of PL/SQLRecords and Tables
    • Using PL/SQL Records
    • Inserting and Updating PL/SQLRecords
    • Using INDEX BY Tables
    • Using Associative arrays
    • Declaring and Using VArrays
    Using Explicit Cursors
    • Cursors
    • Explicit Cursor Operations
    • Controlling Explicit Cursors
    • Using Explicit Cursors toProcess Rows
    • Cursors and Records
    • Cursor FOR Loops Using Subqueries
    • Explicit Cursor Attributes
    • %NOTFOUND and %ROWCOUNTAttributes
    Handling Exceptions
    • Concept of Exception
    • Handling Exceptions with PL/SQL
    • Predefined Exceptions
    • Trapping Predefined andNon-predefined Oracle Server Errors
    • Functions that ReturnInformation on Exceptions
    • Usage of PRAGMA keyword
    • Trapping User-Define d Exceptions
    • Propagating Exceptions
    Creating Stored Procedures and Functions
    • Overview of Stored Proceduresand Functions
    • Differentiating betweenanonymous blocks and subprograms
    • CREATE OR REPLACE PROCEDURE |FUNCTION
    • Understanding the Header Area of a Stored Procedure and Function
    • Creating Simple Procedures andFunctions
    • Creating a Simple Procedure withan IN Parameter
    • Executing a Procedure and aFunction

All rights reserved. ©1999 - 2024, ISQL Global.

Terms of Service | Privacy Policy