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.
Course info
Rating
(159)
Level
Intermediate
Updated
Jun 14, 2014
Duration
5h 10m
Table of contents
Overview
Procedures
Functions
Parameters in Procedures & Functions
Local Subprograms
Package Specification
Package Body
Calling Functions from SQL
Roles & Privileges With Subprograms
Description
Course info
Rating
(159)
Level
Intermediate
Updated
Jun 14, 2014
Duration
5h 10m
Description

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.

About the author
About the author

Experienced technologist, with expertise in various aspects of software development lifecycle, architecting software solutions and software development.

More from the author
Oracle REST Data Services
Beginner
5h 31m
Feb 13, 2018
Getting Productive with SQL Developer
Beginner
3h 53m
Apr 20, 2017
More courses by Pankaj Jain
Section Introduction Transcripts
Section Introduction Transcripts

Parameters in Procedures & Functions
Hi, welcome to Pluralsight. My name is Pankaj Jain, and welcome to this module on Parameters. We are all familiar with the concept of customization. We like to customize our house, our cars, or coffee, our sandwich, you name it, to meet our specific tastes and requirements. Typically it involves starting with a prebuilt product and replacing or moving a few things around to meet our needs. Oracle provides us with the ability to pass parameters to procedures and functions, to customize their behavior to meet our business needs. Parameters allow us to extend the reusability of procedures and functions by not having to duplicate a majority of code or logic, which stays constant, for the small portion with changes. It also prevents us from hard coding values in our code. Oracle provides us with a lot of flexibility and control when passing parameters, which greatly enhances the power of stored program units. In this module we will talk about how to pass parameters, their modes, constraints on parameters, positional versus named notations, and how to make parameter passing a little faster using the NOCOPY hint. There is a lot of exciting stuff to talk about, so let's get started.

Package Specification
Hi. Welcome to Pluralsight. My name is Pankaj Jain, and welcome to this module on Package Specifications. Having the ability to group things based on functionality or a common theme is not only good from an organization perspective, but also allows better manageability and retrieval of items. When you walk in the library or the grocery store, imagine the amount of time it might take you to find something if things were not organized based on sections or categories. Similarly, in the database, when you have a large number of compiled PLSQL units, you need to organize them so that you can easily find them when needed, and reuse them. Oracle provides us this functionality in the form of packages, which allow logical grouping of related items, along with mechanisms to maintain session state and global variables. In this module, we will take a look at the structure of a package, see how to define and execute a package specification, as well as understand how package specifications provide global variables and session state. We will further continue the discussion about packages in the next module, where we will talk about package body, overloading concepts, packet state, etc. Let us jump right in and start looking at this very important programming construct.

Package Body
Hi. Welcome to Pluralsight. My name is Pankaj Jain, and welcome to this module on Package Bodies. In the last module we started our discussion about packages. We talked about the differences between package specification and package body. In this module, we will take an in depth look at package body, where we will provide implementation details for the subprograms defined in the package specification. Along with talking about how to define, compile, and execute package bodies, we will also talk about important concepts related to stateful and stateless packages, and overloading subprograms. There is a lot of exciting stuff to cover, so let's get started.