A small example of MySQL stored procedure with parameters: bitsCN.com
Example of a MySQL stored procedure with parameters
The stored procedure P_GET_CLASS_NAME is used to determine the class name based on the input class number.
The stored procedure P_INSERT_STUDENT is used to receive input student information and insert the information into the student table.
[SQL]
Drop procedure if exists 'p _ GET_CLASS_NAME ';
Create procedure P_GET_CLASS_NAME (in id int, out name varchar (50 ))
BEGIN
IF (ID = 1) THEN
Set name = 'class1 ';
End if;
IF (ID = 2) THEN
Set name = 'second class ';
End if;
END;
Drop procedure if exists 'p _ INSERT_STUDENT ';
Create procedure P_INSERT_STUDENT (in id int, in name varchar (10), in classno int, in birth datetime)
BEGIN
SET @ ID = ID;
SET @ NAME = NAME;
SET @ CLASSNO = CLASSNO;
SET @ BIRTH = BIRTH;
SET @ CLASSNAME = NULL;
CALL P_GET_CLASS_NAME (@ CLASSNO, @ CLASSNAME );
SET @ insertSql = CONCAT ('Insert INTO TBL_STUDENT VALUES (?,?,?,?) ');
PREPARE stmtinsert FROM @ insertSql;
EXECUTE stmtinsert USING @ ID, @ NAME, @ CLASSNAME, @ BIRTH;
Deallocate prepare stmtinsert;
END;
CALL P_INSERT_STUDENT (1, 'Xu Yue ', 1, '2017-10-01 10:20:01 ');
In the second stored procedure
① Declared parameters using SET and called the first stored procedure
② The NAME parameter in the first stored procedure is the output parameter, so @ CLASSNAME is assigned a value after the first procedure is called.
③ Use CONCAT to splice SQL statements and input parameters to execute SQL statements.
CALL P_INSERT_STUDENT (1, 'Xu Yue ', 1, '2017-10-01 10:20:01'); CALL the stored procedure
BitsCN.com