Example of input and output parameters of mysql Stored Procedure
drop PROCEDURE if exists my_procedure; create PROCEDURE my_procedure(in my_id int,out my_name char) BEGIN select list_name into my_name from aa_list where list_id=my_id; end; call my_procedure(24,@a); select @a;
Can I enter multiple parameters for a mysql stored procedure? For example
Mysql> DELIMITER //
Mysql> create procedure HelloWorld2 (
-> IN vUserName VARCHAR (10 ),
-> OUT vOutValue VARCHAR (10 ),
-> INOUT vInOutValue VARCHAR (10 ))
-> BEGIN
-> Select concat ('hello', vUserName );
-> SET vOutValue = 'a ';
-> SET vInOutValue = 'B ';
-> END //
Query OK, 0 rows affected (0.00 sec)
Mysql> call HelloWorld2 ('edward ', @ a, @ B )//
+ ----------------------------- +
| CONCAT ('hello', vUserName) |
+ ----------------------------- +
| Hello Edward |
+ ----------------------------- +
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Mysql> select @ //
+ ------ +
| @ A |
+ ------ +
| A |
+ ------ +
1 row in set (0.00 sec)
Mysql> select @ B //
+ ------ +
| @ B |
+ ------ +
| B |
+ ------ +
1 row in set (0.00 sec)
Reference: hi.baidu.com/...1.html
Mysql stored procedure parameters
Table names cannot directly use variables. You can check PREPARE usage more.
Eg:
Drop procedure if exists 'table ';
DELIMITER //
Create procedure 'table' (IN tname varchar (64 ))
BEGIN
SET @ sqlcmd = CONCAT ('select count (1) from', tname );
PREPARE stmt FROM @ sqlcmd;
EXECUTE stmt;
Deallocate prepare stmt;
END //
DELIMITER;
Call table ('test ');