mysql创建存储过程
DROP PROCEDURE IF EXISTS getCreateTimes /*前面要写DELIMITER $$ 或DELIMITER // */ DELIMITER $$ CREATE PROCEDURE `getCreateTimes`() BEGIN SELECT userCreateTime FROM users; END;
mysql 存储过程有参数输入拼接
DROP PROCEDURE IF EXISTS getTest01; DELIMITER $$ CREATE PROCEDURE `getTest01`( uname VARCHAR(50), upass VARCHAR(50) ) BEGIN SET @sql= 'SELECT * FROM users where 1=1'; IF uname IS NOT NULL THEN SET @sql=CONCAT(@sql,' AND userName=',"'",uname,"'"); END IF; IF upass IS NOT NULL THEN SET @sql=CONCAT(@sql,' AND password=',"'",upass,"'"); END IF; PREPARE stmt FROM @sql; EXECUTE stmt; END; CALL getTest01(NULL,"123");
mysql存储过程含输入参数的拼接的分页
DROP PROCEDURE IF EXISTS getTest01; DELIMITER $$ CREATE PROCEDURE `getTest01`( startPage INT,/*第startPage页,从0开始算*/ pageSize INT,/*每页显示的记录数*/ uname VARCHAR(50), upass VARCHAR(50) ) BEGIN SET @sql= 'SELECT * FROM users where 1=1'; IF uname IS NOT NULL THEN SET @sql=CONCAT(@sql,' AND userName=',"'",uname,"'"); END IF; IF upass IS NOT NULL THEN SET @sql=CONCAT(@sql,' AND password=',"'",upass,"'"); END IF; SET @sql=CONCAT(@sql,' LIMIT ',startPage*pageSize,",",pageSize); PREPARE stmt FROM @sql; EXECUTE stmt; END; CALL getTest01(4,2,NULL,NULL);