DROP PROCEDURE IF EXISTS f_qr_album;
DELIMITER $$
CREATE PROCEDURE f_qr_album
(
IN _albumid VARCHAR(10),
IN _albumname VARCHAR(10),
IN _releasedate VARCHAR(10),
IN _publisher VARCHAR(10),
IN _albumdesc VARCHAR(10),
IN _recordstart INTEGER(10),
IN _recordend INTEGER(10),
IN _querytype INTEGER(10),
OUT _recordnum INTEGER(10),
OUT _recordsum INTEGER(10),
OUT _ret INTEGER(10)
)
BEGIN
DECLARE _sql VARCHAR(500);
DECLARE CONTINUE HANDLER FOR NOT FOUND set _ret = -1;
set @sql = ' from t_album where 1 = 1 ';
set _ret = 0;
-- 1 查询总数 2 查询结果集
-- IF ISNULL(_albumid) THEN
IF _albumid IS NOT NULL THEN
SET @sql = CONCAT(' and albumid = ', _albumid);
ELSE
IF _albumname IS NOT NULL THEN
SET @sql = CONCAT(@sql, ' and albumname like ', _albumname);
END IF;
IF _releasedate IS NOT NULL THEN
SET @sql = CONCAT(@sql, ' and releasedate like ', DATE(_releasedate));
END IF;
IF _publisher IS NOT NULL THEN
SET @sql = CONCAT(@sql, ' and publisher like ', _publisher);
END IF;
IF _albumdesc IS NOT NULL THEN
SET @sql = CONCAT(@sql, ' and albumdesc like ', _albumdesc);
END IF;
END IF;
IF _querytype = 1 THEN
SET @sql = CONCAT('select count(*) ', @sql);
ELSE
SET @sql = CONCAT('select * ', @sql);
END IF;
PREPARE _sql from @sql;
EXECUTE _sql;
DEALLOCATE PREPARE _sql;
END
$$
DELIMITER ;
mysql存储过程2
最新推荐文章于 2021-04-22 15:08:10 发布