Introduction to Oracle 11G PL/SQL Programming

Request more details:

Description:
A Relational Database Management System (RDBMS) is a software system that allows you to create and manage a relational database. Minimum requirements for such a system are defined by both ANSI and ISO. PL/SQL is Oracle’s Procedural Language for SQL. It is Oracle’s database programming language for creating stored procedures, functions, database triggers, and object methods. PL/SQL can be used for implementing business rules, computing algorithms, manipulating data, and for stand-alone programs. The Oracle 11g release has greatly enhanced the features and functionality of PL/SQL. Students will write stored procedures, functions, packages, and triggers, and implement complex business rules in Oracle. Students will learn programming, management, and security issues of working with PL/SQL program units. Programming topics will include the built-in packages that come with Oracle, the creation of triggers, and stored procedure features.

View other Oracle Courses available
Prerequisites:
A good working knowledge of Oracle 11g SQL features is required. Programming experience in a high-level language, such as COBOL, Java, or Perl is also required.
Audience:
Application developers and database administrators
Triggers:
Beyond Declarative Integrity
Triggers
Types of Triggers
Trigger Sequencing
Row-Level Triggers
Trigger Predicates
Trigger Conditions
Using Sequences
Cascading Triggers and Mutating Tables
Generating an Error
Maintaining Triggers
PL/SQL Variables and Datatypes:
# Anonymous Blocks
# Declaring Variables
Datatypes
Subtypes
Character Data
Dates and Timestamps
Date Intervals
Anchored Types
Assignment and Conversions
Selecting into a Variable
Returning into a Variable
PL/SQL Syntax and Logic:
Conditional Statements – IF/THEN
Conditional Statements – CASE
Comments and Labels
Loops
WHILE and FOR Loops
SQL in PL/SQL
Local Procedures and Functions
Stored Procedures and Functions:
Stored Subprograms
Creating a Stored Procedure
Procedure Calls and Parameters
Parameter Modes
Named Parameter Notation
Default Arguments
Creating a Stored Function
Stored Functions and SQL
Invoker’s Rights
Exception Handling:
SQLCODE and SQLERRM
Exception Handlers
Nesting Blocks
Scope and Name Resolution
Declaring and Raising Named Exceptions
User-Defined Exceptions
Records, Collections, and User-Defined Types:
Record Variables
Using the %ROWTYPE Attribute
User-Defined Object Types
VARRAY and Nested TABLE Collections
Using Nested TABLEs
Using VARRAYs
Collections in Database Tables
Associative Array Collections
Collection Methods
Iterating Through Collections
Cursors:
Multi-Row Queries
Declaring and Opening Cursors
Fetching Rows
Closing Cursors
The Cursor FOR Loop
FOR UPDATE Cursors
Cursor Parameters
The Implicit (SQL) Cursor
Bulk Operations:
Bulk Binding
BULK COLLECT Clause
FORALL Statement
FORALL Variations
Bulk Returns
Bulk Fetching with Cursors
Using Packages:
Packages
Oracle-Supplied Packages
The DBMS_OUTPUT Package
The DBMS_UTILITY Package
The UTL_FILE Package
Creating Pipes with DBMS_PIPE
Writing to and Reading from a Pipe
The DBMS_METADATA Package
XML Packages
Networking Packages
Other Supplied Packages
Creating Packages:
Structure of a Package
The Package Interface and Implementation
Package Variables and Package State
Overloading Package Functions and Procedures
Forward Declarations
Strong REF CURSOR Variables
Weak REF CURSOR Variables
Working with LOBs:
Large Object Types
Oracle Directories
LOB Locators
Internal LOBs
LOB Storage and SECUREFILEs
External LOBs
Temporary LOBs
The DBMS_LOB Package
Maintaining PL/SQL Code:
Privileges for Stored Programs
Data Dictionary
PL/SQL Stored Program Compilation
Conditional Compilation
Compile-Time Warnings
The PL/SQL Execution Environment
Dependencies and Validation
Maintaining Stored Programs
Appendix A: Dynamic SQL:
Generating SQL at Runtime
Native Dynamic SQL vs. DBMS_SQL Package
The EXECUTE IMMEDIATE Statement
Using Bind Variables
Multi-row Dynamic Queries
Bulk Operations with Dynamic SQL
Using DBMS_SQL
DBMS_SQL Subprograms