MySQL - SPLessons

MySQL Stored Procedures

Chapter 17

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

MySQL Stored Procedures

MySQL Stored Procedures

shape Description

MySQL Stored Procedures and functions are bolstered in version MySQL 5.0. MySQL Stored Procedures is an accumulation of Statements, which permits adaptability and consistence for a software engineer in light of the fact that put away strategy are anything but difficult to execute in the Databases operations.

MySQL Stored Procedures are useful where different number of client applications are composed in various languages or it can work on different stages. However they have to perform the same database operations. A store procedure can order another store procedure. In MySQL Stored Procedures, less information will be sent between the user and the server which enhances the execution in the database server.

Create Procedures and Functions

Create Procedure

shape Description

Create procedure is utilized to create a procedure with the procedure name followed by parameters within the parentheses. Parameters can be announced for accessing the Datatypes except few attributes. The procedure can adjust the values however when the procedure gives back the values then alterations is not noticeable to the client.

shape Syntax

Create Procedure <proc_name> ([proc_parameter[……]]) proc_body

proc_name : Name of the procedure
proc_parameter : [ IN | OUT|INOUT] param_name type
proc_body : statement in the syntax

shape Conceptual
figure

  • Client application – Used to perform a task based on system administration.
  • Stored procedure – Set of sql statements with an assigned name.

shape Examples

By using the below example, the concept of create procedure can be easily understood.

mysql> select * from employee;
+--------+----------+-----------+-------------------+--------+-------+
| emp_id | emp_name | city      | designation       | salary | perks |
+--------+----------+-----------+-------------------+--------+-------+
|      1 | david    | delhi     | manager           |  12000 |   855 |
|      2 | shaha    | mumbai    | assistant manager |  13000 |   853 |
|      3 | sha      | puna      | scales manager    |  11000 |   850 |
|      4 | jack     | bangalore | designer          |  14000 |   854 |
|      5 | james    | mangalore | web designer      |  15000 |  1124 |
|      6 | mike     | chennai   | develpoer         |  15500 |   840 |
+--------+----------+-----------+-------------------+--------+-------+
6 rows in set (0.00 sec)
mysql> delimiter $$
mysql> create procedure proc3(out p1 int)
    -> select count(*) into p1 from employee;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc3(@a);
Query OK, 1 row affected (0.00 sec)

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

In the above example, the procedure proc3 will count all the employees in the employee table from the IN parameter and pass the values to OUT parameter.(i.e, proc3 will count all the emp_id from the table employee and display the output).

Create Function

 

shape Description

Functions is a accumulation of statements, which contains some particular storage in the database server. The function takes a parameter and is utilized to perform an operation by using MySQL and return the outcome set. The function can call a function at any time, once the code is stored in the database.

shape Syntax

Create Function <func_name>([func_parameter]) returns type function_body

Func_name : Name of the Function
Func_parameter : param_name type
Type : Any datatype
Function_body :statement in mysql 

shape Examples

By using the below example, the concept of create function can be easily understood.

mysql> CREATE FUNCTION func1(str CHAR(20))
    -> RETURNS CHAR(50)
    -> RETURN CONCAT('WELCOME TO, ',str,'!');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT func('ITOOLSINFO.COM');
+-----------------------------+
| func('ITOOLSINFO.COM')      |
+-----------------------------+
| WELCOME TO, ITOOLSINFO.COM! |
+-----------------------------+
1 row in set (0.00 sec)

In the above example, the function will read the string character and display the output as welcome to ITOOLSINFO.COM.

Drop procedures and functions

Drop procedure

shape Description

Drop procedure is utilized to drop a procedure. For dropping them privileges are required. IF EXISTS clause is accessible, then it keeps them from causing an error and when the procedure does not exist its produces a notice.

shape Syntax


Drop {Procedure} [If Exits] {proc_name};

proc_name:name of the procedure.

shape Examples

By using the below example, the concept of drop procedure can be easily understood.

mysql> Drop procedure if exits proc3;
Query OK, 0 rows affected (0.30 sec)

In the above example, drop procedure will drop the procedure name. (i.e., drop procedure will drop the proc3 name from the database).

Drop function

shape Description

Drop function is utilized to drop a function. For dropping them, privileges are required. IF EXISTS statement is accessible, then it keeps from causing a mistake and when the function does not exist it produces a notice.

shape Syntax

Drop {Function} [If Exits] {func_name};

Func_name:name of the function.

shape Examples

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

In the above example, drop function will drop the function name.(i.e., drop function will drop the function name func1 from the database.)

Summary

shape Key Points

  • MySQL Stored Procedures are the objects created on the database server.
  • Function are objects that reside on the database server.
  • Create procedures/functions are used to create procedures and functions.
  • Drop procedures/functions are used to drop procedures and functions.