are creating and dealing on saved process incessantly, just because the syntax
just isn’t a one-liner. You must keep in mind the precise syntax if you’re utilizing the MySQL
database from the command line. What assist, on this case, is, fast examples. Within the final
couple of MySQL tutorial we’ve got seen How
to seek out the second highest wage and How
to hitch 3 tables in a single question; On this MySQL tutorial we are going to see a few
examples of creating saved process and calling saved process utilizing IN and
OUT parameters. A
ll these MySQL examples are easy and show you how to to grasp
syntax of making a saved process with parameters in MySQL. These examples
are examined in MySQL 5.5 database. We can even use the next worker desk to
create and check these saved procedures :
mysql> choose * from worker;
+——–+———-+———+——–+
| emp_id | emp_name | dept_id | wage |
+——–+———-+———+——–+
| 103 | Jack |
1 | 1400 |
| 104 | John |
2 | 1450 |
| 108 | Alan |
3 | 1150 |
| 107 | Ram |
NULL | 600 |
+——–+———-+———+——–+
4 rows in set (0.22 sec)
Btw, I count on that you’re acquainted with SQL and know completely different clauses and their which means in a SQL question.
Create and Name MySQL saved process with IN Parameters
parameter, right here we’re getting the full variety of worker by the division, dept_id is a overseas
key from the division desk.
mysql> DELIMITER //
mysql> create process
usp_totalEmployeeByDeparment(IN id INT)
-> start
-> choose depend(*) as complete from worker the place dept_id = id;
-> finish//
Question OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
We’ve got first modified delimiter as // to mark finish
of saved process, after which reverted it again to earlier delimiter. Additionally
utilizing “usp” as prefix for person outlined saved process is without doubt one of the SQL finest
practices to separate system and person saved procedures.
Now you’ll be able to name this saved
process from MySQL command immediate as :
mysql> name
usp_totalEmployeeByDeparment(2);
+——-+
| complete |
+——-+
| 1 |
+——-+
1 row in set (0.06 sec)
Creating and Calling MySQL saved process with IN and OUT parameters
On this MySQL instance, we’ve got created a saved process usp_GetEmployeeName which
takes one IN and one OUT parameter. Whereas calling this saved process, you
must cross two parameters, id, and identify. One could be the enter parameter id and different would-be output parameters to retailer the outcome.
mysql> DELIMITER //
mysql> create process
usp_GetEmployeeName(IN id INT, OUT identify VARCHAR(20))
-> start
-> choose emp_name into identify from worker the place emp_id = id;
-> finish//
Question OK, 0 rows affected (0.52 sec)
mysql> DELIMITER ;
mysql> name
usp_GetEmployeeName(103, @identify);
Question OK, 1 row affected (0.05 sec)
Calling a saved process from the MySQL command line:
mysql> choose @identify;
+——-+
| @identify |
+——-+
| Jack |
+——-+
1 row in set (0.00 sec)
That is all on Tips on how to create and name MySQL saved process from the command line. On this MySQL tutorial, we’ve got seen examples of making a saved process utilizing IN and OUT parameters. These are among the best methods to
keep in mind and recall the syntax of saved process in MySQL database.
