Course Description
This Oracle 11g PL/SQL programming Part 1 online training course introduces you to PL/SQL and helps you understand the benefits of Oracle’s proprietary database language. You will learn to create PL/SQL blocks of application code that can be shared by multiple users, forms, reports, and data management applications. In addition you will create anonymous PL/SQL blocks as well as named stored procedures, functions, packages and triggers. This Oracle course also covers learning to develop, execute, and manage PL\SQL stored program units, subprograms, triggers, declaring identifiers and both user-defined and pre-defined exceptions. You will be introduced to the utilization of some of the more useful Oracle-supplied packages.
Once you have completed Oracle 11g PL/SQL Programming Part 1 the next course in this series is Oracle 11g PL/SQL Programming Part 2. The Oracle 11g PL/SQL Programming Part 2 course will continue to guide you through more intermediate PL/SQL topics. You will learn how to handle runtime errors, cursor processing, stored procedures and functions, and design and use PL/SQL packages. Also, you will learn how to manage dependencies between stored units, manage security, and create automatic triggers to solve business requirement. Click here to learn more about Oracle 11g PL/SQL Programming Part 2.
In This Course You Will Learn:
- Describe the features and syntax of PL/SQL
- Declare and use both scalar and complex datatypes
- Use PL/SQL constants and Boolean values
- Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
- Use CASE statement and expression
- Simple loop, While loop, For loop
- CONTINUE statement
- Handle runtime errors
- Use both pre-defined and user-defined exceptions
- Use the PRAGMA EXCEPTION_INIT clause
- Design and execute PL/SQL anonymous block
- Use the Oracle supplied PL/SQL packages to generate screen output
Prerequisites
You need to have equivalent experience or have taken the following GogoTraining courses:
Outline
Module 00: Oracle 11g PL/SQL programming Part 1 online training - Course Introduction
Module 01: PL/SQL Overview Part 1
- Define PL/SQL
- Identify Advantages of Using PL/SQL
- Basic PL/SQL Commands
- Discuss where PL/SQL is used
- Exercise
Module 02: PL/SQL Overview Part 2
- Understand PL/SQL Block Structure
- List the Four Main Parts of a PL/SQL Block
- State the Purpose of each Part
- Write Anonymous Blocks
- Nested Blocks
- Use DBMS_OUTPUT.PUT_LINE Procedure
- Exercise
Module 03: I/O Within PL/SQL and Overview of Sample Environment
- I/O Within PL/SQL
- Overview of Sample Environment
- Exercise: PL/SQL Overview
Module 04: Datatypes and Scalar Variables
- Datatypes
- Declare Scalar Variables
Module 05: Composite Variables
- Create Composite Variables
- Assign and update Values for Variables
- Reference PL/SQL Table Variables
Module 06: Datatypes and Scalar Variables Exercise and Exercise Review
Module 07: SQL Statements in PL/SQL
- Select Data from the Database into PL/SQL Variables
- The INTO Clause
- Using SQL in PL/SQL: SELECT
- Exercise Review
Module 08: DML Commands and Transaction Processing
- DML Commands
- Using RETURNING Clause
- Transaction Processing Statements: COMMIT, ROLLBACK, and SAVEPOINT
Module 09: DML Commands and Transaction Processing Exercise and Exercise Review
Module 10: IF and CASE Statements
- Use IF..THEN..ELSE Logic in PL/SQL
- Use the CASE Statement
- Exercise: IF and CASE
- Exercise Review
Module 11: Loops and Labels
- Labels and GOTO Statement
- Looping Statements
- Simple Loops, Numeric FOR Loops, WHILE Loops
- Exercise: Loops
- Exercise Review
- What Should I Know about Conditional Logic and Loops?
Module 12: Exception Handling
- Exception Handling
- Define and Code Exceptions
- The OTHERS Exception
Module 13: Pre-Defined Exceptions
- Pre-Defined Exceptions
- Exercise: Pre-Defined Exceptions
- Exercise Review
Module 14: Using SQL code, SQLERRM and User-defined Exceptions
- SQL code
- SQLERRM
- User-defined exceptions
Module 15: User-Defined Exceptions Exercise and Exercise Review
- What Should I Know about Exceptions?
o Identify and code exceptions
o Use pre-defined exceptions
o Declare exceptions
o Write exception handlers
o Implement exception handler for OTHERS exception
o Create and handle user-defined exceptions
o Report error messages - both user and system
Module 16: Exception Propagation and Displaying Errors
- Exception Propagation
- Using Nested Blocks
- Using RAISE_APPLICATION_ERROR
- Display errors with DBMS_UTILITY
Module 17: Exception Propagation and Displaying Errors Exercise and Exercise Review
Certification
Oracle 10g/11g PL/SQL programming Part 1 is part of a two-course online training series that helps you to prepare for one of the following exams: 1Z0-147 Program with PL/SQL or 1Z0-144 Oracle Database 11g: Program with PL/SQL. When you pass one of these exams, you have completed a portion of the requirements towards the Oracle PL/SQL Developer Certified Associate certification.
For more information about certification, requirements and fees, click on the link found in Related Links.