Oracle PL/SQL Fundamentals - Part 2
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.
What you'll learn
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.
Table of contents
- What is a Procedure? 3m
- Defining Procedures 5m
- Compiling Procedures & Native Compilation 3m
- PLSQL_OPTIMIZE_LEVEL & Compile for Debug 5m
- Errors and Warnings 7m
- Demo: Creating & Compiling Procedures, Errors and Warnings 10m
- Executing Dropping & Terminating Procedures 3m
- Demo: Executing Dropping & Terminating Procedures 4m
- Summary 1m
- Introduction 1m
- Formal vs. Actual Parameters 6m
- Parameter Modes 9m
- Passing by Reference & Value 5m
- NOCOPY hint 4m
- Demo:Parameter Modes & NOCOPY 6m
- Positional, Named & Mixed Notation 5m
- Default Values & Constraints on Formal Parameters 8m
- Demo: Default Values, Positional & Named Notations 4m
- Summary 2m
- Introduction 1m
- Defining Package Body 8m
- Package Initialization 3m
- Compiling & Executing Package Body 3m
- Order of Subprograms & Forward Declaration 2m
- Stateful & Stateless Packages 3m
- Overloading Subprograms 8m
- Overloading Considerations 5m
- Demo: Compiling & Executing Packages 9m
- Demo: Stateful & Stateless Packages 5m
- Summary 1m
About the author
Pankaj Jain is an experienced technologist, with expertise in various aspects of software development lifecycle, architecting software solutions and software development. He has worked on an array of technologies, with focus on Oracle at the database layer and Java at the web tier. He is a certified Oracle 11g DBA. He lives in Seattle with his wife Simita and boys Ronit and Rohak. When he is not playing with technology, he likes spending time with his kids, travelling and doing photography.