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.
What You’ll Learn
- The PL/SQL environment and program structure
- Update, insert, and delete statements
- Error report functions
- Debugging procedures
- Logical comparisons
- Define, store, and execute procedures
- Define, store, and execute Functions
- Create package specifications and package bodies
- Create composite datatypes
- Work with implicit and explicit cursors
Who Needs to Attend
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. I.E. Application designers, developers, database administrators, and End Users
Prerequisites
Oracle 11g Foundation: SQL Basics & SQL*PLUS or equivalent experience
Follow-On Courses
There are no follow-ons for this course.
Course Outline
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