Subprograms, Stored Procedures and Store functions
Section 1
SQL Subprograms and Stored Procedures
Learning Objectives:
Describe how to create Stored Procedures with parameters, including IN, OUT, and INOUT.
Describe how to create SQL Stored Procedures.
Demonstrate how to utilize SQL Stored Procedures.
Topics of Contents
Overview of SQL Subprograms.
Overview of Stored Procedures.
Stored Procedure Parameters List.
IN / OUT / INOUT Parameters.
Listing Stored procedures.
How to Drop Stored Procedure.
Overview to Stored Functions.
Listing Stored Functions.
Overview of SQL Subprograms
SQL subprograms are named SQL blocks that can be invoked with a set of parameters. SQL provides two kinds of subprograms:
Functions − These subprograms return a single value, and are mainly used to compute and return a value.
Store Procedures − These subprograms do not return a single value directly, and are mainly used to perform an action.
Overview of Stored Procedures
A Stored Procedure is a reusable unit that encapsulates the specific business logic of the application. If you want to save any query on the database server for execution later, one way to do it is to use a Stored Procedure:
Stored Procedures are compiled and stored in the database.
Stored Procedures can be invoked by triggers, other stored procedures, and application languages such as Java, Python, PHP.
Stored Procedures run directly on the database server so that access to the data is as fast as possible.
The following illustrates the basic syntax of creating a Stored Procedure in PL/SQL:
The following shows the syntax for executing a Stored Procedure:
Syntax: Create Procedure Statement
Syntax to declare an IN parameter:
Syntax to declare an OUT parameter:
Syntax to declare an INOUT parameter:
Stored Procedure Parameters List
Most stored procedures require parameters. Parameters make stored procedures more flexible and useful. In SQL, a parameter has one of three modes: IN, OUT, or INOUT.
IN – IN is the default mode. These types of parameters are used to send values to stored procedures, and execute queries based on input value.
OUT - OUT is a type of parameter that is used to get values from the stored procedures. This is similar to a return type in methods/functions, and cannot pass values to OUT parameters in a stored procedure call.
INOUT – An INOUT parameter is simply a combination of IN and OUT parameters. This means that the calling program may pass the argument, and the stored procedure can modify the INOUT parameter and pass the new value back to the calling program. NOTE: If a parameter is not explicitly defined as a parameter type, by default, it is an IN type parameter.
Example: Stored Procedure Without Parameter
The following CREATE PROCEDURE statement creates a new stored procedure:
In the above example, we created a Stored Procedure with the name GetOfficeforUSA().
We can use the “CALL” keyword to execute the Stored Procedures.
Example: Stored Procedure IN Parameter
“countryName” is the IN parameter of the Stored Procedure.
Inside the Stored Procedure, we select all offices that are located within the country specified by the “countryName” parameter.
Explanation
Suppose we want to get all offices in the USA, and we just need to pass a value (“USA”) to the Stored Procedure as follows: Example:
Stored Procedure Example: OUT Parameter
In this example, the stored procedure returns the number of orders by order status. It has two parameters: orderStatus and total.
orderStatus: The IN parameter that is the order status, which we want for counting the orders.
total: The OUT parameter that stores the number of orders for a specific order status.
To get the number of shipped orders, we can call the CountOrderByStatus stored procedure and pass the order status as ’Shipped.’ We can also pass an argument (@total) to get the return value.
Example: Stored Procedure - INOUT Parameter
The set_counter stored procedure accepts one INOUT parameter (count) and one IN parameter (inc).
Inside the stored procedure, we increase the counter (count) by the value of the inc parameter.
Let's explore how to invoke the set_counter stored procedure, as shown below:
Listing Stored Procedures
The SHOW PROCEDURE STATUS statement shows all of the characteristics of stored procedures, including Stored Procedure names. It returns Stored Procedures that you have privilege to access.
We can use a WHERE clause in the SHOW PROCEDURE STATUS for specific databases.
How to Drop a Stored Procedure
The DROP PROCEDURE statement deletes a Stored Procedure created by the CREATE PROCEDURE statement.
Syntax of the DROP PROCEDURE statement:
Note: [ IF EXISTS ] is an option.
Example: Drop Stored Procedure
We have created three stored procedures in the previous example, as shown below. Let's drop all of them — one by one with the statements below.
Knowledge Check
What is Stored Procedures?
How do you create Stored Procedures?
Section 2
SQL Stored Function
Learning Objectives:
Create a stored function to encapsulate the common formula or business rules.
Create stored functions in SQL.
Drop stored functions in SQL.
Utilize stored functions in SQL.
Table of Contents
Overview to Stored Functions.
Syntax: Stored Function
Listing Stored Functions.
Overview to Stored Function
A function is a piece of code that we can store and use repeatedly. MySQL provides a long list of functions that help us do certain tasks. However, it may require our making our own functions. These are called stored functions.
A stored function is a special kind stored procedure, but always returns a value. We can use stored functions to encapsulate common formulas or business rules that are reusable among SQL statements or stored procedure.
Syntax: Stored Function
To create a stored function, we can use the CREATE FUNCTION statement. SQL stored functions are declared as or :
DETERMINISTIC means that a given input will always result in the same output.
NOT DETERMINISTIC means that a given input may result in various outputs.
SQL uses this information to optimize the performance of calls made to the stored function.
SQL uses the NOT DETERMINISTIC option by default.
Listing Stored Function
We can view all stored functions available in the SQL, and we can use the ‘SHOW FUNCTION STATUS’ statement, as shown below:
We can use a WHERE clause in the SHOW FUNCTION STATUS statement for any specific database: To check the script and other details of any specific function, we can use the Show Create Function statement:
Example 1: Stored Functions
In this example, we will create our own function named OrderLeadTime() with two parameters. In this function, we will calculate the difference between two dates as shown below:
Let’s utilize OrderLeadTime() function as shown below:
Example 2: Stored Function
In this example, we will create a function named WEIGHTED_AVERAGE(), which accumulates the four parameters in this function, returns the average weight, and can be used to determine an overall result for a subject.
Let’s utilize the WEIGHTED_AVERAGE() function:
Example 3: Stored Function
The following CREATE FUNCTION statement creates a function named CustomerLevel() that returns the customer level based on credit:
Let’s utilize the CustomerLevel() function as shown below:
Knowledge Check
What is Stored Function?
What is the difference between Stored Function and Stored Procedure?
Summary
In this lesson, we explored PL/SQL subprograms (PL/SQL blocks) that can be invoked with a set of parameters. PL/SQL provides two kinds of subprograms:
A Stored Procedure is a reusable unit that encapsulates the specific business logic of the application. In other words, it is a prepared SQL code that can be saved and reused over and over again. Once saved, it can be called and executed.
A Stored Function is a special kind of Stored Procedure — a defined function (also called a user function or user-defined function) that is called from within an SQL statement like a regular function, and returns a single value.
References
Questions?
Last updated