Stored Procedures and Functions

Stored Routines (Procedures and Functions) are supported in version MySQL 5.0. Stored Procedure is a set of statements, which allow ease and flexibility for a programmer because stored procedure is easy to execute than reissuing the number of individual SQL statements. Stored procedure can call another stored procedure also. Stored Procedure can very useful where multiple client applications are written in different languages or it can be work on different platforms but they need to perform the same database operations.

Store procedure can improve the performance because by using the stored procedure less information needs to be sent between the server and the client. It increase the load on the database server because less work is done on the client side and much work is done on the server side.

CREATE PROCEDURE Syntax

The general syntax of Creating a Stored Procedure is :
        CREATE PROCEDURE proc_name ([proc_parameter[......]]) routine_body

proc_name : procedure name
proc_parameter : [ IN | OUT | INOUT ] param_name type
routine_body : Valid SQL procedure statement

The parameter list is available with in the parentheses. Parameter can be declared to use any valid data type, except that the COLLATE attribute cannot be used. By default each parameter is an IN parameter. For specifying other type of parameter used the OUT or INOUT keyword before the parameter name.

An IN parameter is used to pass the value into a procedure. The procedure can be change the value but when the procedure return the value then modification is not visible to the caller. An OUT parameter is used to pass the value from the procedure to the caller but its visible to the caller. An INOUT parameter is initialized by the caller and it can be modified by the procedure, and any change made by the procedure is visible to the caller.

For each OUT or INOUT parameter you have to pass a user –defined variable because then the procedure returns the value then only you can obtain it values. But if you invoking the procedure from the other procedure then you can also pass a routine parameter or variable as an IN or INOUT parameter.

The routine_body contains the valid SQL procedure statement that can be a simple statement like SELECT or INSERT or they can be a compound statement written using BEGIN and END. Compound statement can consists declarations, loops or other control structure.

Now we are describing you a example of a simple stored procedure which uses an OUT parameter. It uses the mysql client delimiter command for changing the statement delimiter from ; to // till the procedure is being defined. Example :

mysql> delimiter //
mysql> CREATE PROCEDURE Sproc(OUT p1 INT)
    -> SELECT COUNT(*) INTO p1 FROM Emp;
    -> //
Query OK, 0 rows affected (0.21 sec)

mysql> delimiter ;
mysql> CALL Sproc(@a);
Query OK, 0 rows affected (0.12 sec)

mysql> select @a;
+------+
| @a   |
+------+
| 5    |
+------+
1 row in set (0.00 sec)

CREATE FUNCTION Syntax

The general syntax of Creating a Function is :
        CREATE FUNCTION func_name ([func_parameter[,...]]) RETURNS type routine_body

func_name : Function name
func_parameter : param_name type
type : Any valid MySQL datatype
routine_body : Valid SQL procedure statement

The RETURN clause is mandatory for FUNCTION. It used to indicate the return type of function.

Now we are describing you a simple example a function. This function take a parameter and it is used to perform an operation by using an SQL function and return the result. In this example there is no need to use delimiter because it contains no internal ; statement delimiters. Example :

mysql> CREATE FUNCTION func(str CHAR(20))
    -> RETURNS CHAR(50)
    -> RETURN CONCAT('WELCOME TO, ',str,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT func('RoseIndia');
+------------------------+
| func('RoseIndia')      |
+------------------------+
| WELCOME TO, RoseIndia! |
+------------------------+
1 row in set (0.00 sec)

ALTER PROCEDURE and ALTER FUNCTION Syntax

For creating the procedure or function we used the CREATE PROCEDURE | FUNCTION statement and for altering the procedure we used the ALTER PROCEDURE | FUNCTION statement. Alter Procedure statement is used to change access permissions that preserves by the procedure. And ALTER PROCEDURE needs the use of the same encryption and recompile option as the original CREATE PROCEDURE command. ALTER PROCEDURE | FUNCTION statement can be used for renaming the stored procedure or function and for changing it characteristics also. We can specify the more than one changes also in an ALTER PROCEDURE | FUNCTION statement. But for this you required the ALTER ROUTINE privilege.

        ALTER {PROCEDURE | FUNCTION} {proc_name | func_name} [characteristic ...] characteristic: SQL SECURITY {DEFINER | INVOKER}| COMMENT 'string'
Example : 

mysql> ALTER PROCEDURE Sproc SQL SECURITY DEFINER;
Query OK, 0 rows affected (2.00 sec)

With the ALTER PROCEDURE Statement you can change only the characteristics and if you want to change in statement list then you have to DROP the procedure and CREATE again.

DROP PROCEDURE and DROP FUNCTION Syntax

DROP PROCEDURE | FUNCTION Statement is used to drop a Procedure or Function. But for dropping them you must have the ALTER ROUTINE privilege. If IF NOT EXISTS clause is available then its prevents you from occurring an error when the procedure or function does not exist its produced only a warning.
        DROP {PROCEDURE | FUNCTION} [IF EXISTS] {proc_name | func_name};

The following example shows you a syntax of Dropping procedure and function if it exists : Examples :

mysql> DROP FUNCTION IF EXISTS func;
Query OK, 0 rows affected (0.30 sec)

mysql> DROP PROCEDURE IF EXISTS Sproc;
Query OK, 0 rows affected (0.00 sec)

But when you want to drop the procedure and function that does not exists it shows you only a warning. Examples :

mysql> DROP FUNCTION IF EXISTS func;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> DROP PROCEDURE IF EXISTS Sproc;
Query OK, 0 rows affected, 1 warning (0.00 sec)

CALL Statement Syntax

The CALL statement is used to call a procedure, which has been defined previously. CALL can return the values to its caller through its parameters that are declared as OUT or INOUT parameters. This statement is also used to returns the number of rows affected that a client program can obtain at the SQL level by calling the ROW_COUNT(). The general syntax of CALL Statement is :
        CALL p_name([parameter[,...]])

The following example shows you the use of CALL statement. Example :

mysql> delimiter //
mysql> CREATE PROCEDURE Sp1(OUT p VARCHAR(20),OUT p1 VARCHAR(20),IN p2 INT)
    -> SELECT Ename,City INTO p,p1 FROM Emp WHERE Eid=p2;
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> CALL Sp1(@Name,@City,1);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @Name,@City;
+-------+-------+
| @Name | @City |
+-------+-------+
| Rahul | Delhi |
+-------+-------+
1 row in set (0.00 sec)

BEGIN.....END Compound Statement Syntax

Stored Procedure or Functions can contain multiple statement by using the BEGIN…..END compound statement. The general syntax of BEGIN....END compound statement is :
        BEGIN [statement_list] END

statement_list means a list of one or more statements but each statements must be terminated by a semicolon. statement_list is optional means compound statement can be empty.

In the following example firstly we are inserting the record and then we are selecting the record. Example :

mysql> SELECT * FROM Emp;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 10300  | 853   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10300  | 853   |
| 3   | Chandan | Banglore | Team Leader       | 15450  | 999   |
| 5   | Tapan   | Pune     | Developer         | 20600  | 1111  |
| 6   | Amar    | Chennai  | Developer         | 16000  | 1124  |
| 7   | Santosh | Delhi    | Designer          | 10000  | 865   |
+-----+---------+----------+-------------------+--------+-------+
6 rows in set (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE Proc(OUT p VARCHAR(20), OUT p1 VARCHAR(20),IN p2 INT)
    -> BEGIN
    -> INSERT INTO Emp VALUES(p2,'Suman','Pune','Web Designer',20000,965);
    -> SELECT Ename,City INTO p,p1 FROM Emp WHERE Eid=p2;
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> CALL Proc(@Name,@City,8);
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT @Name,@City;
+-------+-------+
| @Name | @City |
+-------+-------+
| Suman | Pune  |
+-------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM Emp;
+-----+---------+----------+-------------------+--------+-------+
| Eid | Ename   | City     | Designation       | Salary | Perks |
+-----+---------+----------+-------------------+--------+-------+
| 1   | Rahul   | Delhi    | Manager           | 10300  | 853   |
| 2   | Gaurav  | Mumbai   | Assistant Manager | 10300  | 853   |
| 3   | Chandan | Banglore | Team Leader       | 15450  | 999   |
| 5   | Tapan   | Pune     | Developer         | 20600  | 1111  |
| 6   | Amar    | Chennai  | Developer         | 16000  | 1124  |
| 7   | Santosh | Delhi    | Designer          | 10000  | 865   |
| 8   | Suman   | Pune     | Web Designer      | 20000  | 965   |
+-----+---------+----------+-------------------+--------+-------+
7 rows in set (0.00 sec)

DECLARE Statement Syntax

The DECLARE statement is used to specify the various items. Its allowed only inside the BEGIN….END compound statements. Declarations must follow the order like Cursor must be declare before declaring handlers and the variables and conditions must be declare before declaring either handler or cursors

DECLARE Local Variable

The general syntax of declaring local variable is :
        DECLARE var_name[,...] type [DEFAULT value]

DECLARE statement is used for declaring the local variables. DEFAULT clause is used for providing a default value to the variable but if this clause is missing then the initial value is NULL. Local variable scope is limited within the BEGIN….END compound block. 

Variable SET Statement

The general syntax of Variable SET statement is:
        SET var_name = expr [, var_name = expr] ...

In stored procedure SET statement is extended version of general SET statement and its implements as part of the preexisting SET Syntax. It allows an extended syntax of j=a , k=b….. where different variables types (like local variables, global variables etc) can be mixed.

SELECT......INTO Statement Syntax

The general syntax of SELECT....INTO Statement is:
        SELECT column_name1,column_name2[...] INTO var_name1,var_name2[....] table_expr

This SELECT statement is used to store selected columns into variables. But by this we can retrieve only single row. The number of columns and the number of variable name must be same in this statement.

In the following example we are demonstrating you the use of all above three statement. Example :

mysql>  delimiter //
mysql> CREATE PROCEDURE Sproced(OUT p VARCHAR(20),OUT p1 VARCHAR(20),IN p2 INT)
    -> BEGIN
    -> DECLARE a VARCHAR(20);
    -> DECLARE b INT;
    -> SET b=p2;
    -> SET a='Dev%';
    -> SELECT * FROM Emp WHERE Designation LIKE a;
    -> SELECT Ename,City INTO p,p1 FROM Emp WHERE Eid=b;
    -> END
    -> //
Query OK, 0 rows affected (0.07 sec)

mysql> delimiter ;
mysql> CALL Sproced(@Name,@City,5);
+-----+-------+---------+-------------+--------+-------+
| Eid | Ename | City    | Designation | Salary | Perks |
+-----+-------+---------+-------------+--------+-------+
| 5   | Tapan | Pune    | Developer   | 20600  | 1111  |
| 6   | Amar  | Chennai | Developer   | 16000  | 1124  |
+-----+-------+---------+-------------+--------+-------+
2 rows in set (0.05 sec)

Query OK, 0 rows affected (0.10 sec)

mysql> SELECT @Name,@City;
+-------+-------+
| @Name | @City |
+-------+-------+
| Tapan | Pune  |
+-------+-------+
1 row in set (0.01 sec)

Conditions and Handlers

Some conditions needs specific handling and these conditions can be related to errors or may be general flow control inside a routine. Handlers are the methods of handling conditions that need to be dealt with. Before describing conditions and handlers lets try to produce some errors. Example :

mysql> INSERT INTO Emp VALUES(1,'AAA','Delhi','Manager',20000,583);
ERROR 1062 (23000): Duplicate entry '1' for key 1

In the above example MySQL produced an error, ERROR 1062. And the number between the brackets (23000) is the SQLSTATE that can be the same for a number of errors. Another example :

mysql> INSERT INTO Emp VALUES(11,NULL,'Delhi','Manager',20000,583);
ERROR 1048 (23000): Column 'Ename' cannot be null

But in this example we get a different error number 1048 but the same SQLSTATE. If these errors occur in our functions and procedures then they will terminate out programs. To deal with these conditions we have to create a handler. The general syntax of Handler is as follows:
        DECLARE handler_type HANDLER FOR condition_value[,...] statement 

Firstly we have to use DECLARE for creating a handler, handler_type can be of the following like CONTINUE, EXIT or UNDO. If we are using CONTINUE the the program will carry on the process after the handler has been called. When we are using EXIT then the program will end immediately. The UNDO is used on transactional tables to rollback work carried out up to that point. HANDLER FOR tells the compiler, we are declaring a handler. Condition_value is used so that the handler fires when a define conditions met. The statement is section of code that we want to execute when the handler is fired.

Now we are creating a simple procedure and in this we are trying to deal with duplicate entry. In this procedure we are not handling the error. Example :

mysql> delimiter //
mysql> CREATE PROCEDURE hproc(OUT p VARCHAR(30))
    -> BEGIN
    -> INSERT INTO Emp VALUES(1,'aaa','Delhi','Manager',20000,535);
    -> SET p='Can not Insert';
    -> END
    -> //
Query OK, 0 rows affected (0.19 sec)
mysql> delimiter ;
mysql> CALL hproc(@b);
ERROR 1062 (23000): Duplicate entry '1' for key 1
mysql> SELECT @b;
+------+
| @b   |
+------+
|      |
+------+
1 row in set (0.02 sec)

In the above example we got the error message but our parameter is empty because error stopped the procedure. But if we wouldn’t want that then we must use handler. In the following example lets include a handler to the procedure. Example :

mysql> delimiter //
mysql> CREATE PROCEDURE hproc(OUT p VARCHAR(35))
    -> BEGIN
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @b=' With Errors';
    -> INSERT INTO Emp VALUES(1,'AAA','Delhi','Manager',20000,698);
    -> SET p=CONCAT('Can not Insert ',@b);
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> CALL hproc(@c);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @c;
+---------------------------+
| @c                        |
+---------------------------+
| Can not Insert With Errors|
+---------------------------+
1 row in set (0.00 sec)

Now in this example we didn’t get the error message and our parameter also passed out the value because when the error occurred then handler deal with the problem and continue the procedure processing.

In the above example we are using a handler to deal with SQLSTATE but the handler can deal with a set of different errors. Now in the following example we are taking the different error numbers but they had the same SQLSTATE that situation we looked at earlier. Example :

mysql> delimiter //
mysql> CREATE PROCEDURE hproc(OUT p VARCHAR(35))
    -> BEGIN
    -> DECLARE CONTINUE HANDLER FOR 1062 SET @b=' With Error 1062';
    -> DECLARE CONTINUE HANDLER FOR 1048 SET @b=' With Error 1048';
    -> INSERT INTO Emp VALUES(1,'AAA','Delhi','Manager',20000,698);
    -> SET p=CONCAT('Can not Insert',@b);
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> CALL hproc(@c);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @c;
+--------------------------------+
| @c                             |
+--------------------------------+
| Can not Insert With Error 1062 |
+--------------------------------+
1 row in set (0.01 sec)

mysql> DROP PROCEDURE hproc;
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE hproc(OUT p VARCHAR(35))
    -> BEGIN
    -> DECLARE CONTINUE HANDLER FOR 1062 SET @b=' With Error 1062';
    -> DECLARE CONTINUE HANDLER FOR 1048 SET @b=' With Error 1048';
    -> INSERT INTO Emp VALUES(11,NULL,'Delhi','Manager',20000,698);
    -> SET p=CONCAT('Can not Insert',@b);
    -> END
    -> //
Query OK, 0 rows affected (0.09 sec)

mysql> delimiter ;
mysql> CALL hproc(@c);
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT @c;
+--------------------------------+
| @c                             |
+--------------------------------+
| Can not Insert With Error 1048 |
+--------------------------------+
1 row in set (0.03 sec)

In the above section we have seen how we can handle various conditions. Additionally MySQL allows us to define our own named conditions. But these conditions only be linked to SQLSTATE values or mysql_error_code. Syntax for creating a conditions is as follows :
        DECLARE condition_name CONDITION FOR condition_value 

condition_value can be the SQLSTATE [value] or mysql_error_code. In the following example we are describing how we can create a condition and how we can use it within a handler. Example :

mysql> delimiter //
mysql> CREATE PROCEDURE condproc(OUT p VARCHAR(35))
    -> BEGIN
    -> DECLARE duplicate CONDITION FOR SQLSTATE '23000';
    -> DECLARE CONTINUE HANDLER FOR duplicate SET @b=' With Duplicate Error';
    -> INSERT INTO Emp VALUES(1,'AAA','Delhi','Manager',20000,568);
    -> SET p=CONCAT('Can not Insert',@b);
    -> END
    -> //
Query OK, 0 rows affected (0.12 sec)

mysql> CALL condproc(@c);
    -> //
Query OK, 0 rows affected (0.14 sec)

mysql> delimiter ;
mysql> CALL condproc(@c);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @c;
+-------------------------------------+
| @c                                  |
+-------------------------------------+
| Can not Insert With Duplicate Error |
+-------------------------------------+
1 row in set (0.00 sec)

In the following example we are using error code. Example :

mysql> delimiter //
mysql> CREATE PROCEDURE condproc(OUT p VARCHAR(35))
    -> BEGIN
    -> DECLARE not_null CONDITION FOR 1048;
    -> DECLARE CONTINUE HANDLER FOR not_null SET @b=' With Not Null Error';
    -> INSERT INTO Emp VALUES(11,NULL,'Delhi','Manager',20000,698);
    -> SET p=CONCAT('Can not Insert',@b);
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> CALL condproc(@c);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @c;
+------------------------------------+
| @c                                 |
+------------------------------------+
| Can not Insert With Not Null Error |
+------------------------------------+
1 row in set (0.00 sec)