Oracle Database: PL/SQL Fundamentals

SELECTION & SETUP OF THE DATABASE INTERFACE • CONSIDERING AVAILABLE TOOLS • SELECTING THE APPROPRIATE TOOL • ORACLE NET DATABASE CONNECTIONS • ORACLE PAAS DATABASE CONNECTIONS • SETUP SQL DEVELOPER • SETUP SQL*PLUS • SETUP JDEVELOPER ABOUT BIND & SUBSTITUTION VARIABLES • USING SQL DEVELOPER • USING SQL*PLUS CHOOSING A DATABASE PROGRAMMING LANGUAGE • WHAT IS DATABASE PROGRAMMING? • PL/SQL PERFORMANCE ADVANTAGES • INTEGRATION WITH OTHER LANGUAGES PL/SQL LANGUAGE FUNDAMENTALS • PL/SQL PROGRAM STRUCTURE • LANGUAGE SYNTAX RULES • EMBEDDING SQL • WRITING READABLE CODE • GENERATING DATABASE OUTPUT • SQL*PLUS INPUT OF A PROGRAM BLOCK DECLARE SECTION • ABOUT THE DECLARE SECTION • DECLARE PRIMITIVE TYPES • DECLARATION OPTIONS • NOT NULL • CONSTANT • DATA DICTIONARY INTEGRATION • %TYPE • DECLARE SIMPLE USER-DEFINED TYPES • TYPE ... TABLE • TYPE ... RECORD • EXTENDED USER-DEFINED TYPES BEGIN SECTION • ABOUT THE BEGIN SECTION • MANIPULATING PROGRAM DATA • LOGIC CONTROL & BRANCHING • GOTO • LOOP • IF-THEN-ELSE • CASE EXCEPTION SECTION • ABOUT THE EXCEPTION SECTION • ISOLATING THE SPECIFIC EXCEPTION • PRAGMA EXCEPTION_INIT • SQLCODE & SQLERRM Example • SQL%ROWCOUNT & SELECT...INTO BEYOND THE BASICS: EXPLICIT CURSORS • ABOUT EXPLICIT CURSORS • EXTENDED CURSOR TECHNIQUES • FOR UPDATE OF Clause • WHERE CURRENT OF Clause • Using FOR…LOOP Cursors INTRODUCING DATABASE-RESIDENT PROGRAM UNITS • ABOUT DATABASE-RESIDENT PROGRAMS • PHYSICAL STORAGE & EXECUTION • TYPES OF STORED PROGRAM UNITS • STORED PROGRAM UNIT ADVANTAGES • MODULAR DESIGN PRINCIPLES CREATING STORED PROCEDURES & FUNCTIONS • STORED PROCEDURES & FUNCTIONS • CREATE PROCEDURE / CREATE FUNCTION • CREATING PROCEDURES & FUNCTIONS • RAISE_SALARY() Procedure • SALARY_VALID() Function • THE PARAMETER SPECIFICATION • DEFAULT Clause • SYSTEM & OBJECT PRIVILEGES • USING THE DEVELOPMENT TOOLS EXECUTING STORED PROCEDURES & FUNCTIONS • CALLING PROCEDURES & FUNCTIONS • UNIT TESTING WITH EXECUTE • ANONYMOUS BLOCK UNIT TESTING • SPECIFYING A PARAMETER NOTATION • SQL WORKSHEET UNIT TESTING • CALLING FUNCTIONS FROM SQL MAINTAINING STORED PROGRAM UNITS • RECOMPILING PROGRAMS • Mass Recompilation Using UTL_RECOMP() • DROPPING PROCEDURES & FUNCTIONS • DROP PROCEDURE / FUNCTION • DATA DICTIONARY METADATA • Using USER_OBJECTS • Using USER_SOURCE • Using USER_ERRORS • Using USER_OBJECT_SIZE • Using USER_DEPENDENCIES MANAGING DEPENDENCIES • DEPENDENCY INTERNALS • TRACKING DEPENDENCIES • THE DEPENDENCY TRACKING UTILITY • SQL DEVELOPER DEPENDENCY INFO • DEPENDENCY STRATEGY CHECKLISTS CREATING & MAINTAINING PACKAGES • ABOUT PACKAGES • CREATING PACKAGES • MAINTAINING PACKAGES • PERFORMANCE CONSIDERATIONS ADVANCED PACKAGE CAPABILITIES • DEFINER & INVOKER RIGHTS • WHITE LISTS & ACCESSIBLE BY • PERSISTENT GLOBAL OBJECTS • DEFINING INITIALIZATION LOGIC • OBJECT ORIENTATION SUPPORT ADVANCED CURSOR TECHNIQUES • USING CURSOR VARIABLES • Using SYS_REFCURSOR • USING CURSOR EXPRESSIONS USING SYSTEM-SUPPLIED PACKAGES • DBMS_OUTPUT() • UTL_FILE() • FOPEN() Example DATABASE TRIGGER CONCEPTS • ABOUT DATABASE TRIGGERS • DML EVENT TRIGGER SUB-TYPES • DATABASE TRIGGER SCENARIO • TRIGGER EXECUTION MECHANISMS • TRIGGERS WITHIN SQL WORKSHEET CREATING DATABASE TRIGGERS • STATEMENT-LEVEL TRIGGERS • Using RAISE_APPLICATION_ERROR() • ROW-LEVEL TRIGGERS • EXAMPLES OF TRIGGERS • EMPLOYEE_SALARY_CHECK Example • EMPLOYEE_JOURNAL Example • BUDGET_EVENT Example • INSTEAD OF TRIGGERS • TRIGGERS WITHIN AN APPLICATION MAINTAINING DATABASE TRIGGERS • CALL SYNTAX • TRIGGER MAINTENANCE TASKS • SHOW ERRORS TRIGGER • DROP TRIGGER • ALTER TRIGGER • MULTIPLE TRIGGERS FOR A TABLE • HANDLING MUTATING TABLE ISSUES IMPLEMENTING SYSTEM EVENT TRIGGERS • WHAT ARE SYSTEM EVENT TRIGGERS? • DEFINING THE SCOPE • AVAILABLE SYSTEM EVENTS • SYSTEM EVENT ATTRIBUTES RECENT ENHANCEMENTS • PLSCOPE_SETTINGS • DEPRECATE pragma • Conditional Compilation Using $IF, $ELSE, $END, and $ERROR • NOCOPY, PARALLEL_ENABLE Hints, and DETERMINISTIC Clause • INDEX_BY tables • BULK COLLECT • PLSQL_CODE_TYPE and PLSCOPE_SETTINGS • RETURNING clause is used with INSERT, UPDATE, and DELETE statements • Code-Based Access Control (CBAC) • Handling Specific Warnings in PL/SQL using PLSQL_WARNINGS • ACCESSIBLE BY clause