Summer-Ready Savings: Find the Training Course You Need at a Price You'll Love

closeClose

Oracle Database 12c: PL/SQL Fundamentals (Training On Demand)

  • Tuition USD $1,360 Save $68
  • Reviews star_rate star_rate star_rate star_rate star_half 347 Ratings
  • Course Code D100745GC11
  • Duration 2 days
  • Available Formats Self Paced

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 code in PL/SQL.
  • Use various PL/SQL conditional constructs and loops in program blocks.
  • Interface the PL/SQL code with the database.
  • Use stored procedures and functions in their program blocks.
  • Handle Exceptions in PL/SQL code.

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.

Skills Gained

  • Design PL/SQL anonymous block that execute efficiently
  • Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
  • Handle exceptions in the program units
  • Create and execute simple stored procedures and functions
  • Write PL/SQL code to interface with the database
  • Describe the features and syntax of PL/SQL

Who Can Benefit

  • Analyst
  • Developer
  • Implementer

Course Details

Topics

  • Introduction
    • Course Objectives, Course Agenda and Class Account Information
    • The Human Resources (HR) Schema
    • Appendices Used in this Course
    • PL/SQL Development Environments
  • Introduction to PL/SQL
    • Understanding the benefits and structure of PL/SQL
    • Understanding PL/SQL Blocks
    • Generating output messages in PL/SQL
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • Course Objectives, Course Agenda and Class Account Information
  • Describe the Human Resources (HR) Schema
  • Identify the Appendices Used in this Course
  • Introduction to PL/SQL Development Environments
  • Oracle Database 12c SQL Documentation and Additional Resources

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

How do I enroll?

A comprehensive listing of ExitCertified courses can be found here. You can register directly for the required course/location when you select "register". If you have any questions or prefer to speak with an ExitCertified education consultant directly, please submit your query here. A representative will contact you shortly.

How do I pay for a class?

You can pay at the time of registration using credit card (Mastercard/Visa/American Express) cheque or PO.

What if I have training credits?

ExitCertified honors all savings programs from the partners we work with. ExitCertified also offers training credits across multiple partners through our FLEX Account.

When does class start/end?

Classes begin promptly at 9:00 am, and typically end at 5:00 pm.

Lunchtime?

Lunch is normally an hour long and begins at noon. Coffee, tea, hot chocolate and juice are available all day in the kitchen. Fruit, muffins and bagels are served each morning. There are numerous restaurants near each of our centers, and some popular ones are indicated on the Area Map in the Student Welcome Handbooks - these can be picked up in the lobby or requested from one of our ExitCertified staff.

How can someone reach me during class?

If someone should need to contact you while you are in class, please have them call the center telephone number and leave a message with the receptionist.

What languages are used to deliver training?

Most courses are conducted in English, unless otherwise specified. Some courses will have the word "FRENCH" marked in red beside the scheduled date(s) indicating the language of instruction.

I really enjoyed the course. It was well designed given that it was online. I would recommend that the higher level courses be taken on-site, but this course was very thorough for those looking to get started with an AWS Certification.

Just finished up a course taught by Sean Mohseni. Excellent instructor. If you can find a course he's teaching, its well worth it.

Great personnel and facility. I just should have been told i was the only person physically in thr class, and that there was an option to attend the class remotely.

Very clean, great cafeteria and well sorted, very kind staff. The bathrooms have to be expanded as they might get crowded sometimes

The course is perfectly designed with material and labs to get hands on experience on AWS, nicely done!

Contact Us 1-800-803-3948
Contact Us Live Chat
FAQ Get immediate answers to our most frequently asked qestions. View FAQs arrow_forward