Introduction to Oracle® PL/SQL Programming
- Introduction to Oracle PL/SQL Programming Training Class Summary
-
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 10g 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.
Audience: Application developers and database administrators.
Prerequisites: Introduction to Oracle SQL Programming is required. Programming experience in a high-level language, such as COBOL, Java or Perl is also required.
Class Length: 3 days
- Introduction to Oracle PL/SQL Programming Training Class Objectives
-
- Create triggers on database tables.
- Use PL/SQL's datatypes for database and program data.
- Use program structure and control flow to design and write PL/SQL programs.
- Create PL/SQL stored procedures and functions.
- Write robust programs that handle runtime exceptions.
- Use PL/SQL's collection datatypes.
- Use cursors to work with database data.
- Use the packages supplied with Oracle.
- Design and write your own packages.
- Maintain and evolve your PL/SQL programs.
- Manage the security of your stored PL/SQL programs.
- Introduction to Oracle PL/SQL Programming Training Class Detailed Outline
-
- Course Introduction
- Course Objectives
- Overview
- Suggested References
- Triggers
- Beyond Declarative Integrity
- Triggers
- Types of Triggers
- 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
- 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
- VARRAY and Nested TABLE Collections
- Using Nested TABLEs
- Using VARRAYs
- Collection 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
- 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
- Appendix B - PL/SQL Versions, Datatypes, and Language Limits
- Appendix C – Oracle 10g Supplied Packages
- Course Introduction