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:

CREATE PROCEDURE procedure_name (parameter_list)
BEGIN
    --statements
END;

• The following shows the syntax for executing a Stored Procedure:

CALL procedure_name( arguments);

Syntax: Create Procedure Statement

Syntax to declare an IN parameter:

CREATE PROCEDURE procedure_name (IN param_name1 datatype, param_name2 IN datatype
... )

Syntax to declare an OUT parameter:

CREATE PROCEDURE procedure_name (OUT param_name datatype)

Syntax to declare an INOUT parameter:

CREATE PROCEDURE procedure_name (INOUT param_name datatype)

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:

DELIMITER $$
CREATE PROCEDURE GetOfficeforUSA()
BEGIN
     SELECT  * FROM offices WHERE country = 'USA';
END $$
DELIMITER ;

• In the above example, we created a Stored Procedure with the name GetOfficeforUSA().

• We can use the “CALL” keyword to execute the Stored Procedures.

CALL GetOfficeforUSA;

Example: Stored Procedure IN Parameter

DELIMITER $$
CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255)) BEGIN
    SELECT  * FROM offices WHERE country = countryName;
END $$
DELIMITER;

• “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:

CALL GetOfficeByCountry('USA');

Stored Procedure Example: OUT Parameter

DELIMITER $$
CREATE PROCEDURE CountOrderByStatus(IN orderStatus VARCHAR(25), OUT total INT)
BEGIN
    SELECT count(orderNumber) INTO total FROM orders WHERE status = orderStatus;
END$$ DELIMITER ;

• 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

DELIMITER $$
CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))
BEGIN
     SET count = count + inc;
END$$
DELIMITER ;

• 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:

SET @counter = 1;
CALL set_counter(@counter,1);
CALL set_counter(@counter,1);
CALL set_counter(@counter,5);
SELECT @counter;

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.

SHOW PROCEDURE STATUS;

We can use a WHERE clause in the SHOW PROCEDURE STATUS for specific databases.

SHOW PROCEDURE STATUS WHERE db = 'classicmodels';

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:

DROP PROCEDURE [IF EXISTS] stored_procedure_name;

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.

DROP PROCEDURE GetOfficeByCountry; -- GetOfficeByCountry will delete
DROP PROCEDURE GetOfficeforUSA; -- GetOfficeforUSA will delete
DROP PROCEDURE set_counter; -- set_counter will delete

Knowledge Check

1. What is Stored Procedures?

2. 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 :

DELIMITER $$
CREATE FUNCTION function_name( param1, param2,... )
RETURNS datatype
[NOT] DETERMINISTIC
BEGIN
 -- statements
END $$
DELIMITER ;

• 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:

SHOW FUNCTION STATUS;
SHOW FUNCTION STATUS WHERE db = 'classicmodels';

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:

Show create function functionName;

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:

DELIMITER $$
CREATE FUNCTION OrderLeadTime (orderDate DATE, requiredDate DATE)
     RETURNS INT DETERMINISTIC
BEGIN
     RETURN requiredDate - orderDate;
END; $$
DELIMITER ;

Let’s utilize OrderLeadTime() function as shown below:

Example 1: SELECT OrderLeadTime( '2019-02-13', CURRENT_DATE());
Example 2: SELECT OrderLeadTime(orderDate, requiredDate) FROM orders;

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.

DELIMITER &&
CREATE FUNCTION WEIGHTED_AVERAGE (n1 INT, n2 INT, n3 INT, n4 INT)
  RETURNS INT
   DETERMINISTIC
    BEGIN
     DECLARE avg INT;
     SET avg = (n1+n2+n3*2+n4*4)/8;
     RETURN avg;
END &&

Let’s utilize the WEIGHTED_AVERAGE() function:

SELECT WEIGHTED_AVERAGE(70,65,65,60);

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:

SELECT customerName,
CustomerLevel(creditLimit) as 'Client status'
FROM  customers ORDER BY customerName;

DELIMITER $$
CREATE FUNCTION CustomerLevel(credit DECIMAL(10,2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
  DECLARE customerLevel VARCHAR(20);
      SET customerLevel = 'PLATINUM';
  IF credit >=10000 AND credit <= 50000 THEN
     SET customerLevel = 'GOLD';
  ELSEIF credit < 10000 THEN
     SET customerLevel = 'SILVER';
  END IF;
  -- return the customer level
  RETURN (customerLevel);
END$$
DELIMITER ;

Knowledge Check

1. What is Stored Function?

2. 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

• https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm

• https://www.ibm.com/docs/en/db2-for-zos/11?topic=programs-creating-user-defined-function

Questions?