How To Utilisation As Well As Telephone Outcry Upwardly Stored Physical Care For Inwards Mysql Alongside Inwards As Well As Out Parameters

It's difficult to shout out upward exact syntax of, how to do stored physical care for inwards MySQL, until y'all are creating as well as working on stored physical care for frequently, merely because syntax is non a ane liner. You ask to shout out upward exact syntax, if y'all are using MySQL database from ascendance line. What assistance inwards this instance is, quick examples. In terminal pair of MySQL tutorial nosotros own got seen How to respect minute highest salary as well as How to bring together three tables inwards ane query; In this MySQL tutorial nosotros volition meet pair of examples of creating stored procedure as well as calling stored physical care for using IN as well as OUT parameters. All these MySQL examples are unproblematic as well as assistance y'all to sympathise syntax of creating stored physical care for amongst parameters inwards MySQL. These examples are tested inwards MySQL 5.5 database. We volition too purpose next employee tabular array to do as well as attempt out these stored procedures :
 

mysql> select * from employee;

+--------+----------+---------+--------+

| emp_id | emp_name | dept_id | salary |

+--------+----------+---------+--------+

|    103 | Jack     |       1 |   1400 |

|    104 | John     |       2 |   1450 |

|    108 | Alan     |       3 |   1150 |

|    107 | Ram      |    NULL |    600 |

+--------+----------+---------+--------+

4 rows in set (0.22 sec)

Create as well as Call MySQL stored physical care for amongst IN Parameters

Here is the ascendance to do a MySQL stored physical care for amongst ane IN parameter, hither nosotros are getting full expose of employee yesteryear department, dept_id is a foreign key from subdivision table.

mysql> DELIMITER //

mysql> create procedure usp_totalEmployeeByDeparment(IN id INT)

    -> begin

    -> select count(*) as full from employee where dept_id = id;

    -> end//

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

We own got showtime changed delimiter equally // to grade goal of stored procedure, as well as thence reverted it dorsum to previous delimiter. Also using “usp” equally prefix for user defined stored physical care for is ane of the SQL best practices to split upward organisation as well as user stored procedures. Now y'all tin terminate telephone phone this stored physical care for from MySQL ascendance prompt equally :

mysql> call usp_totalEmployeeByDeparment(2);

+-------+

| full |

+-------+

|     1 |

+-------+

1 row in set (0.06 sec)

Creating as well as Calling MySQL stored physical care for amongst IN as well as OUT parameters

In this MySQL example, nosotros own got created stored physical care for usp_GetEmployeeName which takes ane IN as well as ane OUT parameter. While calling this stored procedure, y'all ask to locomote yesteryear 2 parameters, id as well as name. One would live on  input parameter id as well as other would live on output parameter to shop result.

mysql> DELIMITER //

mysql> create procedure usp_GetEmployeeName(IN id INT, OUT refer VARCHAR(20))

    -> begin

    -> select emp_name into refer from employee where emp_id = id;

    -> end//

Query OK, 0 rows affected (0.52 sec)

mysql> DELIMITER ;

mysql> call usp_GetEmployeeName(103, @name);

Query OK, 1 row affected (0.05 sec)

Calling stored physical care for from MySQL ascendance line:

mysql> select @name;

+-------+

| @name |

+-------+

| Jack  |

+-------+

1 row in set (0.00 sec)

That's all on How to do as well as telephone phone MySQL stored physical care for from ascendance line. In this MySQL tutorial, nosotros own got seen examples of creating stored physical care for using IN as well as OUT parameters. These are ane of the best means to shout out upward as well as recall syntax of stored physical care for inwards MySQL database.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Share this post