HBFITES Data Center, Collaboration, Security, Storage, Wireless, Telepresence Training +91-9886770046 info@hbfites.com

Oracle 11g Introduction to PL/SQL

Course Details

Get a hands-on introduction to the Oracle11g PL/SQL procedural language for SQL in this course where you will learn 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.

Course Prerequisites

Oracle 11g Foundation: SQL Basics & SQL*PLUS or equivalent experience

Course Agenda

1. PL/SQL Basics

  • SQL and PL/SQL
  • PL/SQL anonymous block structure and datatypes
  • Variable declarations
  • Object naming rules
  • Executable statements
  • Operators in PL/SQL
  • 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

2. Working with Database Data

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

3. 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

4. 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, and package bodies
  • Introduction to database triggers
  • Viewing and recompiling stored code