Oracle 11g/12c Developer: Introduction to PL/SQL

Course:   OR11D1
Duration:   2 Days
Level:   I
On our website at:   http://www.verhoef-training.com/courses/OR11D1.html
 
Course Summary

You will receive a hands-on introduction to the Oracle11g and/or 12c PL/SQL procedural language for SQL. You will cover how to insert, update, and delete data, control data sharing and locking, debug and use error report procedures, and develop database triggers, stored procedures and functions for future reuse.

This course is the first part of our Oracle 11g/12c Developer: PL/SQL Complete course.

This course can also be presented with content specific to Oracle 10g.

Topics Covered In This Course

PL/SQL BASICS

  • SQL and PL/SQL
  • PL/SQL anonymous block structure and Datatypes
  • Variable declarations and naming conventions
  • Object naming rules
  • Executable Statements
  • Operators in PL/SQL
  • Conditional control / Repetition control
  • LOOP statements ? Basic LOOP, WHILE, FOR
  • CASE Expressions and Statements
  • Nested Blocks
  • Labels and block identification
  • Basic Coding Standards
  • SQL*PLUS development environment
  • Using SQL Developer
  • Bind variables / Substitution variables
  • Datatypes: Scalar, Collection, Record, Reference
  • Commenting code

WORKING WITH DATABASE DATA

  • SELECT statements in PL/SQL
  • Selecting single and multiple rows
  • Referencing other variables or a database column?s datatypes with %TYPE
  • Using %ROWTYPE
  • Using sequences in PL/SQL
  • Modifying database data (DML)
  • Transaction control commands
  • Selecting data into PL/SQL variables
  • Explicit cursors / Cursor attributes
  • Transaction control commands
  • Referencing other structures datatypes
  • Using the cursor FOR LOOP
  • FOR UPDATE / WHERE CURRENT OF

EXCEPTION HANDLING

  • Writing an exception handler section
  • Handling predefined and user-defined exceptions
  • Controlling exception processing
  • Preventing unhandled exceptions
  • RAISE_APPLICATION_ERROR
  • Using SQLCODE and SQLERRM

STORED PL/SQL OBJECTS

  • Introduction to procedures and functions stored in the database
  • Creating stored PL/SQL objects
  • Passing values with parameters
  • Creating procedures, functions, packages, package bodies in SQL*Plus and SQL Developer
  • Introduction to database triggers
  • Viewing and recompiling stored code
What You Can Expect

On successfully completing this course, participants can expect to be able to:

Who Should Take This Course

Anyone who needs to extract and organize information from an Oracle database, including adding, updating, and deleting data, and debug and use error report procedures, and develop database triggers, stored procedures and functions for future reuse, e.g. application designers, developers, database administrators, and end users and those seeking the OCA certification.

Recommended Prerequisites

Participants should have completed our Oracle 11g Foundations course or have equivalent experience.

Training Style

Short lecture sessions (60%) along with hands-on lab sessions (40%).

Related Courses
Code Course Title Duration Level
OR18SQL
Oracle18c SQL
5 Days
I
Details
OR18DBA1
Oracle 18c DBA I
5 Days
I
Details
OR11F
Oracle 11g and 12c Foundations: SQL and SQL*Plus
5 Days
I
Details
OR11D
Oracle 11g/12c Developer: PL/SQL Complete
5 Days
I
Details
ORAPEX
Oracle APEX Version 5.0.4
5 Days
II
Details
OR11D2
Oracle 11g/12c Developer: Intermediate PL/SQL
3 Days
II
Details
ORPLSA
Oracle 11g Advanced PL/SQL
3 Days
III
Details
ORSQLA
Oracle 11g New and Advanced SQL Features For Developers
3 Days
III
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.