Stored Procedures

Definition

A stored procedure is a set of SQL statements that are stored and executed on the database server. Instead of sending multiple SQL statements from the client to server, you encapsulate them in a stored procedure on the server and send one statement from the client to execute them.

They are stored in the database data dictionary and can be invoked from an application program or from the database command interface. Stored procedures can accept input parameters and return multiple values of output parameters. They can also include control-of-flow constructs such as loops and conditional statements.

You can write stored procedures in many different languages.

  • For example, for DB2 on cloud and DB2, you can write in SQL, PL, PL/SQL, Java, C, or other languages.
  • They can accept information as parameters, perform, create, read, update, and delete (CRUD) operations,
  • and return results to the client application.

The benefits of stored procedures include

  • reduction in network traffic because only one call is needed to execute multiple statements
  • Improvement in performance because the processing happens on the server where the data is stored with just the final result being passed back to the client
  • Reuse of code because multiple applications can use the same stored procedure for the same job
  • Increase in security because
    • you do not need to expose all of your table and column information to client-side developers,
    • and you can use server-side logic to validate data before accepting it into the system

Remember though, that SQL is not a fully fledged programming language. You should not try to write all of your business logic in your stored procedures.

Sample Procedure

Let’s look at how to create a stored procedure in SQL.

DELIMITER  $$
CREATE  PROCEDURE  UPDATE_SAL (IN empNum  CHAR(6), IN rating SMALLINT)
BEGIN
UPDATE  employees SET salary = salary * 1.10 WHERE emp_id = empNum AND rating = 1;
UPDATE  employees SET salary = salary * 1.05 WHERE emp_id = empNum AND rating <> 1;
END 
$$
DELIMITER;
  • Firstly, you use the create procedure statement
    • specifying the name of the procedure UPDATE_SAL
    • and any parameters which it will take

In this example, the update procedure will take IN an empNum and rating, which it will use to update the employee’s salary by an amount depending on their rating (as you see inside the BEGIN .. END statements).

  • Then you declare the language you are using.
  • You then enclose your procedural logic with the begin and end statements.
  • In this case, giving employees who have a rating of one, a 10% raise,
  • and all others a 5% raise
  • Notice that you can use the information passed to the procedure, the parameters, directly in your procedural logic.
  • Also, since the stored procedure is going to use multiple statements, it is prudent to change the delimiter, the character that marks the end of a statement, before we start defining the procedure.
  • Here it has been set to $$
  • Use of this delimiter dollar sign dollar sign in the code will then mark the end of the procedure commands
  • Finally, we change the delimiter back to semicolon when we are done.

You can call stored procedures from

  • your external applications or
  • from dynamic SQL statements.

Call Procedure

To call the UPDATE_SAL stored procedure that we just created, you use the call statement with the name of the stored procedure and pass the required parameters.

CALL UPDATE_SAL ('E1001', 1)

Example 1

  • We’ll be using MySQL
  • In phpMyAdmi > create db: PETS
  • We’ll use sql file: on local drive PETSALE-CREATE-v2.sql to create the tables
  • Select PETS
  • To create tables: Import Tab>Select local file

Create Stored Procedure

  • We’ll name it: RETRIEVE_ALL
  • This RETRIEVE_ALL routine will contain an SQL query to retrieve all the records from the PETSALE table, so you don’t need to write the same query over and over again. You just call the stored procedure routine to execute the query everytime.
  • To create the stored procedure routine, copy the code below and paste it to the textarea of the SQL page.
  • Click Go
DELIMITER //

CREATE PROCEDURE RETRIEVE_ALL()

BEGIN
   SELECT *  FROM PETSALE;
END
//
DELIMITER ;

Call Procedure

To call RETRIEVE_ALL:

  • Open a new SQL query tab
  • Call the procedure/routine
  • Click GO
CALL RETRIEVE_ALL

Drop Procedure

DROP PROCEDURE RETRIEVE_ALL;

CALL RETRIEVE_ALL;

Example 2

  • We’ll be modifying the table we created in Example 1
  • We’ll create a stored procedure routine named UPDATE_SALEPRICE with parameters Animal_ID and Animal_Health.

    • This UPDATE_SALEPRICE routine will contain SQL queries to update the sale price of the animals in the PETSALE table depending on their health conditions, BAD or WORSE.

    • This procedure routine will take animal ID and health conditon as parameters which will be used to update the sale price of animal in the PETSALE table by an amount depending on their health condition. Suppose that:

      • For animal with ID XX having BAD health condition, the sale price will be reduced further by 25%.

      • For animal with ID YY having WORSE health condition, the sale price will be reduced further by 50%.

      • For animal with ID ZZ having other health condition, the sale price won’t change.

Create Procedure

DELIMITER @
CREATE PROCEDURE   UPDATE_SALEPRICE (IN Animal_ID INTEGER, IN Animal_Health VARCHAR(5))
BEGIN
    IF Animal_Health = 'BAD' THEN
        UPDATE   PETSALE
        SET      SALEPRICE = SALEPRICE - (SALEPRICE * 0.25)
        WHERE    ID = Animal_ID;
    ELSEIF Animal_Health = 'WORSE' THEN
        UPDATE   PETSALE
        SET      SALEPRICE = SALEPRICE - (SALEPRICE * 0.5)
        WHERE    ID = Animal_ID;
    ELSE
        UPDATE   PETSALE
        SET      SALEPRICE = SALEPRICE
        WHERE    ID = Animal_ID;
    END IF;
END @

DELIMITER ;

Call Procedure - 1

CALL RETRIEVE_ALL;
CALL UPDATE_SALEPRICE (1,'BAD');
CALL RETRIEVE ALL;

As you can see:

  • The first call produces the table as is showing the price for the cat $450.09
  • Then you see the second procedure executed
  • Then the recall of the first procedure shows us the price has been discounted for that cat to $337.57

Call Procedure - 2

Let’s call it again with a different animal id and type

CALL RETRIEVE_ALL;
CALL UPDATE_SALEPRICE (3,'WORSE');
CALL RETRIEVE ALL;

Now you see the animal with ID=3 has been discounted again

Drop Procedure

DROP UPDATE_SALEPRICE

Summary

Stored Procedures

--#SET TERMINATOR @ CREATE PROCEDURE PROCEDURE_NAME

LANGUAGE

BEGIN

END@

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

The default terminator for a stored procedure is semicolon(;). To set a different terminator we use SET TERMINATOR clause followed by the terminator such as ‘@’.

--#SET TERMINATOR @ CREATE PROCEDURE RETRIEVE_ALL

LANGUAGE SQLREADS SQL DATA

DYNAMIC RESULT SETS 1BEGIN

DECLARE C1 CURSORWITH RETURN FOR

SELECT * FROM PETSALE;

OPEN C1;

END@

Stored Procedures in MySQL using phpMyAdmin

Stored Procedures

DELIMITER //

CREATE PROCEDURE PROCEDURE_NAME

BEGIN

END //

DELIMITER ;

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

The default terminator for a stored procedure is semicolon (;). To set a different terminator we use DELIMITER clause followed by the terminator such as $$ or //.

DELIMITER //

CREATE PROCEDURE RETRIEVE_ALL()

BEGIN

SELECT * FROM PETSALE;

END //

DELIMITER ;

Transactions with Db2

Commit command COMMIT;

A COMMIT command is used to persist the changes in the database.

The default terminator for a COMMIT command is semicolon (;).

CREATE TABLE employee(ID INT, Name VARCHAR(20), City VARCHAR(20), Salary INT, Age INT);

INSERT INTO employee( ID, Name, City, Salary, Age) VALUES( 1, ‘Priyanka pal’, ‘Nasik’, 36000, 21), (2, ‘Riya chowdary’, ‘Bangalor’, 82000, 29);

SELECT *FROM employee;COMMIT;
Rollback command ROLLBACK;

A ROLLBACK command is used to rollback the transactions which are not saved in the database.

The default terminator for a ROLLBACK command is semicolon (;).

As auto-commit is enabled by default, all transactions will be committed. We need to disable this option to see how rollback works. |

For db2, we have to disable auto-commit manually. Click the gear icon located on the right side of the SQL Assistant window. Next, select the “On Success” drop-down and choose “commit after the last statement in the script” Remember to save your changes! |

INSERT INTO employee VALUES (3, ‘Swetha Tiwari’, ‘Kanpur’, 38000, 38);

SELECT *FROM employee;ROLLBACK;SELECT *FROM employee;

Transactions with MySQL

Commit command COMMIT;

A COMMIT command is used to persist the changes in the database.

The default terminator for a COMMIT command is semicolon (;).

CREATE TABLE employee(ID INT, Name VARCHAR(20), City VARCHAR(20), Salary INT, Age INT);

START TRANSACTION;

INSERT INTO employee( ID, Name, City, Salary, Age) VALUES( 1, ‘Priyanka pal’, ‘Nasik’, 36000, 21), (2, ‘Riya chowdary’, ‘Bangalor’, 82000, 29);

SELECT *FROM employee;COMMIT;
Rollback command ROLLBACK;

A ROLLBACK command is used to rollback the transactions which are not saved in the database.

The default terminator for a ROLLBACK command is semicolon (;).

As auto-commit is enabled by default, all transactions will be committed. We need to disable this option to see how rollback works. For MySQL use the command “SET autocommit = 0;” |

INSERT INTO employee VALUES (3, ‘Swetha Tiwari’, ‘Kanpur’, 38000, 38);

SELECT *FROM employee;ROLLBACK;SELECT *FROM employee;

Db2 Transactions using Stored Procedure

Commit command

–#SET TERMINATOR @

CREATE PROCEDURE PROCEDURE_NAME

BEGIN

COMMIT;

END@

A COMMIT command is used to persist the changes in the database.

The default terminator for a COMMIT command is semicolon (;).

--#SET TERMINATOR @ CREATE PROCEDURE TRANSACTION_ROSE LANGUAGE SQL MODIFIES SQL DATA

BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0;DECLARE retcode INTEGER DEFAULT 0;DECLARE CONTINUE HANDLER FOR SQLEXCEPTIONSET retcode = SQLCODE;

UPDATE BankAccountsSET Balance = Balance-200WHERE AccountName = ‘Rose’;

UPDATE BankAccountsSET Balance = Balance-300WHERE AccountName = ‘Rose’;

IF retcode < 0 THENROLLBACK WORK;

ELSECOMMIT WORK;

END IF;

END@
Rollback command

--#SET TERMINATOR @

CREATE PROCEDURE PROCEDURE_NAME

BEGIN

ROLLBACK;

COMMIT;

END@

A ROLLBACK command is used to rollback the transactions which are not saved in the database.

The default terminator for a ROLLBACK command is semicolon (;).

--#SET TERMINATOR @ CREATE PROCEDURE TRANSACTION_ROSE LANGUAGE SQL MODIFIES SQL DATA

BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0;DECLARE retcode INTEGER DEFAULT 0;DECLARE CONTINUE HANDLER FOR SQLEXCEPTIONSET retcode = SQLCODE;

UPDATE BankAccountsSET Balance = Balance-200WHERE AccountName = ‘Rose’;

UPDATE BankAccountsSET Balance = Balance-300WHERE AccountName = ‘Rose’;

IF retcode < 0 THENROLLBACK WORK;

ELSECOMMIT WORK;

END IF;

END@

MySQL Transactions using Stored Procedure

Commit command

DELIMITER //

CREATE PROCEDURE PROCEDURE_NAME

BEGIN

COMMIT;

END //

DELIMITER ;

A COMMIT command is used to persist the changes in the database.

The default terminator for a COMMIT command is semicolon (;).

DELIMITER //

CREATE PROCEDURE TRANSACTION_ROSE()

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGINROLLBACK;RESIGNAL;END;

START TRANSACTION;UPDATE BankAccountsSET Balance = Balance-200WHERE AccountName = ‘Rose’;

UPDATE BankAccountsSET Balance = Balance-300WHERE AccountName = ‘Rose’;

COMMIT;

END //

DELIMITER ;
Rollback command

DELIMITER //

CREATE PROCEDURE PROCEDURE_NAME

BEGIN

ROLLBACK;

COMMIT;

END //

DELIMITER ;

A ROLLBACK command is used to rollback the transactions which are not saved in the database.

The default terminator for a ROLLBACK command is semicolon (;).

DELIMITER //

CREATE PROCEDURE TRANSACTION_ROSE()

BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGINROLLBACK;RESIGNAL;END;

START TRANSACTION;UPDATE BankAccountsSET Balance = Balance-200WHERE AccountName = ‘Rose’;

UPDATE BankAccountsSET Balance = Balance-300WHERE AccountName = ‘Rose’;

COMMIT;

END //

DELIMITER ;