The Oracle PL/SQL language provides for programming logic features not contained within SQL. The grammar, syntax and benefits of using the PL/SQL language within an Oracle database environment are considered.
The primary objective of this course is to provide an introduction to the PL/SQL database programming language. Major subject areas to be explored are:
- Declaring variables and complex datatypes.
- Developing logic within PL/SQL program blocks.
- Fetching data from the database into program variables.
- Returning program output to users.
- Handling program exceptions.
- Using explicit and implicit database cursors.
- Using the advanced features of nested blocks and subprograms.
LANGUAGE FEATURES WHY USE PL/SQL? Where PL/SQL Program Units May Be Deployed The Java Alternative To PL/SQL STRUCTURE OF A PL/SQL PROGRAM BLOCK PL/SQL FROM INTERACTIVE TOOLS GENERATING OUTPUT
DECLARE CLAUSE DATATYPES DECLARE Example SPECIAL DATATYPES %TYPE VARIABLE %ROWTYPE RECORD TYPE TABLE
BEGIN CLAUSE DATA MANIPULATION CHAR and VARCHAR Numeric Date Boolean Comparison Operators Functions Date Calculations SELECTINTO LOGIC CONTROL & BRANCHING GOTO LOOP IF-THEN-ELSE CASE
EXCEPTION HANDLING EXCEPTION SPECIAL FEATURES SYSTEM DEFINED EXCEPTIONS USING SQLCODE & SQLERRM IMPLICIT CURSORS USER-DEFINED EX_INED PRAGMA EXCEPTION_INIT
EXPLICIT CURSORS UNDERSTANDING CURSORS EXPLAINING THE CURSOR EXAMPLE DECLARE Cursor E OPEN Cursor Example FETCH Cursor Example CLOSE Cursor Example INCLUDING CURSOR PARAMETERS PERFORMING UPDATES FROM CURSORS USING FORLOOP CURSORS
ADVANCED USING NESTED BLOCKS USING DECLARED SUBPROGRAMS DISPLAY_MESSAGE() Procedure Example GET_SPOUSE_NAME() Function Example
