How To Practice Together With Telephone Vociferation Upward Stored Physical Care For Inwards Mysql Amongst Inwards Together With Out Parameters
It's difficult to retrieve exact syntax of, how to do stored physical care for inwards MySQL, until you lot are creating together with working on stored physical care for frequently, merely because syntax is non a i liner. You ask to retrieve exact syntax, if you lot are using MySQL database from ascendancy line. What help inwards this instance is, quick examples. In terminal twosome of MySQL tutorial nosotros possess got seen How to respect minute highest salary together with How to bring together iii tables inwards i query; In this MySQL tutorial nosotros volition come across twosome of examples of creating stored procedure together with calling stored physical care for using IN together with OUT parameters. All these MySQL examples are elementary together with help you lot to empathise syntax of creating stored physical care for amongst parameters inwards MySQL. These examples are tested inwards MySQL 5.5 database. We volition too locomote next employee tabular array to do together with examine 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 together with Call MySQL stored physical care for amongst IN Parameters
Here is the ascendancy to do a MySQL stored physical care for amongst i IN parameter, hither nosotros are getting full set out 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 possess got commencement changed delimiter equally // to grade destination of stored procedure, together with and thus reverted it dorsum to previous delimiter. Also using “usp” equally prefix for user defined stored physical care for is i of the SQL best practices to split upwards organisation together with user stored procedures. Now you lot tin telephone yell upwards this stored physical care for from MySQL ascendancy prompt equally :
mysql> call usp_totalEmployeeByDeparment(2);
+-------+
| full |
+-------+
| 1 |
+-------+
1 row in set (0.06 sec)
Creating together with Calling MySQL stored physical care for amongst IN together with OUT parameters
In this MySQL example, nosotros possess got created stored physical care for usp_GetEmployeeName which takes i IN together with i OUT parameter. While calling this stored procedure, you lot ask to locomote yesteryear ii parameters, id together with name. One would locomote input parameter id together with other would locomote output parameter to shop result.
mysql> DELIMITER //
mysql> create procedure usp_GetEmployeeName(IN id INT, OUT lift VARCHAR(20))
-> begin
-> select emp_name into lift 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 ascendancy line:
mysql> select @name;
+-------+
| @name |
+-------+
| Jack |
+-------+
1 row in set (0.00 sec)
That's all on How to do together with telephone yell upwards MySQL stored physical care for from ascendancy line. In this MySQL tutorial, nosotros possess got seen examples of creating stored physical care for using IN together with OUT parameters. These are i of the best agency to retrieve together with 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
Komentar
Posting Komentar