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
