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