Description

Duration

5 Days

30 CPD hours

This course is intended for

This course is intended for individuals who are Application designers and database developers, database administrators and web server administrators.

Overview

Upon successful completion of this course, students will be able to work with Oracle database programming using the PL/SQL programming language. They will learn the syntax, structure and features of the language.

In this course, students will learn the foundation for the programming series and the use of database-resident stored program units for Oracle 12c.

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 wth 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

Beyond the Basics: Nested BlocksBeyond the Basics: Declared Subprograms

USING DECLARED SUBPROGRAMS

DECLARED PROCEDURE

DECLARED FUNCTION

Introducing Databse-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 LITS & ACCESSIBLE BY

PERSISTENT GLOBAL OBJECTS

DEFINING INITIALIZATION LOGIC

OBJECT ORIENTATION SUPPORT

Advanced Cursor Techniques

USING CUSROS VARIABLES

USING SYS_REFCURSOR

USING CURSOR EXPRESSIONS

Using System-Supplied Package

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?

DEFININGTHE SCOPE

AVAILABLE SYSTEM EVENTS

SYSTEM EVENT ATTRIBUTES

Additional course details:

Nexus Humans Oracle 12c PL/SQL Fundamentals training program is a workshop that presents an invigorating mix of sessions, lessons, and masterclasses meticulously crafted to propel your learning expedition forward.

This immersive bootcamp-style experience boasts interactive lectures, hands-on labs, and collaborative hackathons, all strategically designed to fortify fundamental concepts.

Guided by seasoned coaches, each session offers priceless insights and practical skills crucial for honing your expertise. Whether you're stepping into the realm of professional skills or a seasoned professional, this comprehensive course ensures you're equipped with the knowledge and prowess necessary for success.

While we feel this is the best course for the Oracle 12c PL/SQL Fundamentals course and one of our Top 10 we encourage you to read the course outline to make sure it is the right content for you.

Additionally, private sessions, closed classes or dedicated events are available both live online and at our training centres in Dublin and London, as well as at your offices anywhere in the UK, Ireland or across EMEA.

Course Images

Oracle 12c PL/SQL Fundamentals

By Nexus Human

Booking options

Highlights

Description

About The Provider

Tags

Reviews