Oracle 12c - PL/SQL Fundamentals

Considering Available Tools

Selecting the Appropriate Tool

Oracle Net Database Connections

Oracle PAAS Database Connections

Setup SQL Developer

Setup SQL*Plus

Setup Jdeveloper

Using SQL Developer

Using SQL*Plus

What is Database Programming?

PL?SQL Performance Advantages

Integration wth Other Languages

PL/SQL Program Structure

LANGUAGE SYNTAX RULES

EMBEDDING SQL

WRITING READABLE CODE

GENERATING DATABASE OUTPUT

SQL*PLUS INPUT OF A PROGRAM BLOCK

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

About the Begin Section

Manipulating Program Data

Logic Control & Branching

GOTO

LOOP

IF-THEN-ELSE

CASE

ABOUT THE EXCEPTION SECTION

ISOLATING THE SPECIFIC EXCEPTION

PRAGMA EXCEPTION_INIT

SQLCODE &SQLERRM Example

SQL%ROWCOUNT &SELECT...INTO

ABOUT EXPLICIT CURSORS

EXTENDED CURSOR TECHNIQUES

FOR UPDATE OF Clause

WHERE CURRENT OF Clause

Using FOR?LOOP Cursors

USING DECLARED SUBPROGRAMS

DECLARED PROCEDURE

DECLARED FUNCTION

ABOUT DATABASE---RESIDENT PROGRAMS

PHYSICAL STORAGE & EXECUTION

TYPES OF STORED PROGRAM UNITS

STORED PROGRAM UNIT ADVANTAGES

MODULAR DESIGN PRINCIPLES

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

CALLING PROCEDURES & FUNCTIONS

UNIT TESTING WITH EXECUTE

ANONYMOUS BLOCK UNIT TESTING

SPECIFYING A PARAMETER NOTATION

SQL WORKSHEET UNIT TESTING

CALLING FUNCTIONS FROM SQL

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

DEPENDENCY INTERNALS

TRACKING DEPENDENCIES

THE DEPENDENCY TRACKING UTILITY

SQL DEVELOPER DEPENDENCY INFO

DEPENDENCY STRATEGY CHECKLISTS

ABOUT PACKAGES

CREATING PACKAGES

MAINTAINING PACKAGES

PERFORMANCE CONSIDERATIONS

DEFINER & INVOKER RIGHTS

WHITE LITS & ACCESSIBLE BY

PERSISTENT GLOBAL OBJECTS

DEFINING INITIALIZATION LOGIC

OBJECT ORIENTATION SUPPORT

USING CUSROS VARIABLES

USING SYS_REFCURSOR

USING CURSOR EXPRESSIONS

DBMS_OUTPUT()

UTL_FILE()

FOPEN() EXAMPLE

ABOUT DATABASE TRIGGERS

DML EVENT TRIGGER SUB---TYPES

DATABASE TRIGGER SCENARIO

TRIGGER EXECUTION MECHANISMS

TRIGGERS WITHIN SQL WORKSHEET

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

CALL SYNTAX

TRIGGER MAINTENANCE TASKS

SHOW ERRORS TRIGGER

DROP TRIGGER

ALTER TRIGGER

MULTIPLE TRIGGERS FOR A TABLE

HANDLING MUTATING TABLE ISSUES

WHAT ARE SYSTEM EVENT TRIGGERS?

DEFININGTHE SCOPE

AVAILABLE SYSTEM EVENTS

SYSTEM EVENT ATTRIBUTES