Oracle PL/SQL Fundamentals - Part 2 (2014)

MP4 | AVC 155kbps | English | 1024x768 | 15fps | 5h 10mins | AAC stereo 106kbps | 646 MB

Genre: Video Training

In this course, we will take an in-depth look at Procedures, Functions, and Packages. These named program units are powerful programming constructs which can greatly enhance and optimize your code. In this course, we will learn how to create, compile, and execute Procedures, Functions, and Packages which are named program units that are pre-compiled and stored in the database. These are powerful programming constructs which help with increasing the re-usability of code along with making it more efficient. We will also take a look at how to pass parameters to these subprograms, parameter modes, and how to pass parameters by reference and by value. We will discuss how roles and privileges come into play when compiling and executing subprograms along with the authid clause.

We will also talk about local subprograms and calling functions from SQL statements. All of these are important concepts and will enable you to write efficient and highly performing PL/SQL code.

01. Overview 8:17

Overview 2:38

Benefits of Named Program Units 2:16

Prerequisites 0:52

Audience 1:05

Tools 1:25

02. Procedures 39:54

What is a Procedure? 3:00

Defining Procedures 4:39

Compiling Procedures & Native Compilation 3:29

PLSQL_OPTIMIZE_LEVEL & Compile for Debug 4:36

Errors and Warnings 6:45

Demo: Creating & Compiling Procedures, Errors and Warnings 9:42

Executing Dropping & Terminating Procedures 2:30

Demo: Executing Dropping & Terminating Procedures 3:48

Summary 1:21

03. Functions 33:11

What is a Function? 2:15

Oracle Provided Functions 3:31

Defining Functions 5:23

Compiling & Executing Functions 8:42

Dropping & Terminating Functions 3:23

Demo 8:55

Summary 0:58

04. Parameters in Procedures & Functions 49:34

Introduction 1:28

Formal vs. Actual Parameters 5:57

Parameter Modes 9:12

Passing by Reference & Value 4:50

NOCOPY hint 3:32

Demo:Parameter Modes & NOCOPY 6:13

Positional, Named & Mixed Notation 4:33

Default Values & Constraints on Formal Parameters 8:02

Demo: Default Values, Positional & Named Notations 4:11

Summary 1:32

05. Local Subprograms 18:26

Introduction 3:44

Defining Local Subprograms 2:36

Demo: Local Subprograms 2:12

Exceptions, Scope & Visibility of Variables 4:44

Demo: Exceptions, Scope & Visibility of Variables 4:22

Summary 0:46

06. Package Specification 28:42

Introduction 1:32

Why Use Package Specifications? 4:30

Package Structure & Contents 2:47

Defining Package Specification 4:37

Compiling Package Specification 2:42

Executing & Dropping Package Specification 3:21

Global Variables & Session State 1:09

Demo 6:57

Summary 1:02

07. Package Body 48:33

Introduction 0:49

Defining Package Body 8:12

Package Initialization 2:52

Compiling & Executing Package Body 3:06

Order of Subprograms & Forward Declaration 1:46

Stateful & Stateless Packages 3:08

Overloading Subprograms 8:27

Overloading Considerations 4:35

Demo: Compiling & Executing Packages 9:26

Demo: Stateful & Stateless Packages 4:50

Summary 1:16

08. Calling Functions from SQL 31:14

Where Can They Be Called? 4:15

Restrictions 5:03

Demo: Functions in SQL Statements 5:54

Deterministic 5:32

Parallel Enable 3:19

Demo: Deterministic Functions 2:55

Pragma Restrict References 2:45

Summary 1:30

09. Roles & Privileges With Subprograms 52:28

Subprogram Resolution 5:15

Demo: Subprogram Resolution 3:32

AUTHID DEFINER 7:02

AUTHID CURRENT_USER 7:32

Execution Flow Between Invoker & Executor 5:01

Demo: AUTHID Clause & Execution Flow 9:45

Direct Grants vs. Roles 7:35

Demo: Direct Grants vs. Roles 5:37

Summary 1:07