MySQL Stored Procedure Prepared Statement (Dynamic SQL) Parameterized

类似于SQL Server中的:sp_executesql

sql server script:

--- 涂聚文 20160906
IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'proc_Select_DuDeptUserCount')
DROP PROCEDURE proc_Select_DuDeptUserCount
GO
CREATE PROCEDURE proc_Select_DuDeptUserCount
(
 @where NVARCHAR(1000)
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql='select count(*) as H from DuDeptUser '
IF @where<>''
 SET @sql=@sql+@where
EXEC(@sql)
GO

MySql script:

# 建表 塗聚文 20160907
drop table attendrecord;

create table attendrecord
(
    seq  INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    emp_no varchar(20) null,
    rdate datetime not null,
    rtime time not null,
    rdescription varchar(100),
    rdes_reasnon varchar(100),
    branch varchar(50)
);

# 添加
DELIMITER $$
DROP PROCEDURE  IF EXISTS  `attend`.`proc_Insert_Attendrecord` $$
CREATE PROCEDURE `attend`.`proc_Insert_Attendrecord`
(
	IN param1emp_no VarChar(20),
	IN param1rdate Datetime ,
	IN param1rtime Time,
	IN param1rdescription VarChar(100),
	IN param1rdes_reasnon VarChar(100),
	IN param1branch VarChar(50)
)
BEGIN
INSERT INTO attendrecord
(
	emp_no ,
	rdate ,
	rtime ,
	rdescription ,
	rdes_reasnon ,
	branch
)
	VALUES
(
	param1emp_no ,
	param1rdate ,
	param1rtime ,
	param1rdescription ,
	param1rdes_reasnon ,
	param1branch
); END $$
DELIMITER ;

-- 添加
DELIMITER $$
DROP PROCEDURE  IF EXISTS  proc_Insert_Attendrecord $$
CREATE PROCEDURE proc_Insert_Attendrecord
(
	IN param1emp_no VarChar(20),
	IN param1rdate Datetime ,
	IN param1rtime Time,
	IN param1rdescription VarChar(100),
	IN param1rdes_reasnon VarChar(100),
	IN param1branch VarChar(50)
)
BEGIN
INSERT INTO attendrecord
(
	emp_no ,
	rdate ,
	rtime ,
	rdescription ,
	rdes_reasnon ,
	branch
)
	VALUES
(
	param1emp_no ,
	param1rdate ,
	param1rtime ,
	param1rdescription ,
	param1rdes_reasnon ,
	param1branch
); END $$
DELIMITER ;

#添加
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_Insert_AttendrecordOutput $$
CREATE PROCEDURE proc_Insert_AttendrecordOutput
(
	IN param1emp_no VarChar(20),
	IN param1rdate Datetime,
	IN param1rtime Time,
	IN param1rdescription VarChar(100),
	IN param1rdes_reasnon VarChar(100),
	IN param1branch VarChar(50),
 	out param1seq int
)
BEGIN
INSERT INTO attendrecord
(
	emp_no ,
	rdate ,
	rtime ,
	rdescription ,
	rdes_reasnon ,
	branch
)
	VALUES
(
	param1emp_no ,
	param1rdate ,
	param1rtime ,
	param1rdescription ,
	param1rdes_reasnon ,
	param1branch
);
SELECT LAST_INSERT_ID() into param1seq;
END $$
DELIMITER ;

#更新 塗聚文註 2016-09-07
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_Update_Attendrecord $$
CREATE PROCEDURE proc_Update_Attendrecord
(
	IN param1seq Int(8),
	IN param1emp_no VarChar(20),
	IN param1rdate Datetime,
	IN param1rtime Time,
	IN param1rdescription VarChar(100),
	IN param1rdes_reasnon VarChar(100),
	IN param1branch VarChar(50)
)
BEGIN
UPDATE attendrecord
	SET
		emp_no=param1emp_no ,
		rdate=param1rdate ,
		rtime=param1rtime ,
		rdescription=param1rdescription ,
		rdes_reasnon=param1rdes_reasnon ,
		branch=param1branch
	where
		seq=param1seq;

END $$
DELIMITER ;

#修改某一列数据
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_Update_AttendrecordField $$
CREATE PROCEDURE proc_Update_AttendrecordField
(
	IN param1seq Int(8),
	IN FieldValue VarChar(200)
)
BEGIN
DECLARE sqlstr varchar(2000);
DECLARE wherestr varchar(1000);
SET sqlstr=CONCAT('update  attendrecord set ',FieldValue);
SET wherestr=CONCAT(' WHERE seq =',cast(param1seq as char(20)));
SET sqlstr=CONCAT(sqlstr,wherestr);
SET @Sql=sqlstr;
PREPARE stmt FROM @Sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

# 測試
call proc_Update_AttendrecordField(1,'rdes_reasnon=''geovin''');



-- 刪除
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_Delete_Attendrecord $$
CREATE PROCEDURE proc_Delete_Attendrecord
(
	IN param1seq Int
)
BEGIN
DELETE from	attendrecord	WHERE		seq = param1seq;
END $$
DELIMITER ;

#删除多条记录
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_Delete_AttendrecordId $$
CREATE PROCEDURE proc_Delete_AttendrecordId
(
	IN param1seq varchar(1000)
)
BEGIN
DELETE from	attendrecord	WHERE		seq IN(param1seq);
END $$
DELIMITER ;





#查詢
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_Select_Attendrecord $$
CREATE PROCEDURE proc_Select_Attendrecord
(
	IN param1seq Int
)
BEGIN
SELECT * FROM attendrecord WHERE seq = param1seq;
END $$
DELIMITER ;

#是否在存在
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_Select_AttendrecordExists $$
CREATE PROCEDURE proc_Select_AttendrecordExists
(
	IN param1seq Int
)
BEGIN
SELECT count(1) as H FROM attendrecord WHERE seq = param1seq;
END $$
DELIMITER ;

-- 测试
call proc_Select_AttendrecordExists(1);


#表有多少条记录 Geovin Du
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_Select_AttendrecordCount $$
CREATE PROCEDURE proc_Select_AttendrecordCount
(
	IN wherestr varchar(1000)
)
BEGIN
DECLARE sqlstr varchar(2000);
SET sqlstr='SELECT count(1) as H FROM attendrecord';
if wherestr='' then
SET sqlstr=sqlstr;
else
SET sqlstr=sqlstr+wherestr;
end if;
SET @sqlstr=sqlstr;
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
# 测试
call proc_Select_AttendrecordCount('');

# http://stackoverflow.com/questions/23545525/mysql-stored-procedure-prepared-statement-dynamic-sql-parameterized
# https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html
  -- 测试
  set @sql='SELECT count(1) as H FROM attendrecord';
  set @where='WHERE seq =1';
  set @sql=@sql+@where;
  select @sql;
  
  
# test
set @sql='SELECT * FROM attendrecord';
set @where=' WHERE seq <>1';
set @sql=CONCAT(@sql,@where);
select @sql;  #显示字符串
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;  


-- 测试

--
SET @sql = "select * from attendrecord";
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 测试
SET @skip=1; 
SET @numrows=5;
SET @Sql='SELECT * FROM tbl attendrecord  limit ?,?';
PREPARE STMT FROM @Sql;
-- EXECUTE STMT;
EXECUTE STMT USING @skip, @numrows;
DEALLOCATE PREPARE STMT;

#视图有多少条记录
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_Select_AttendrecordCountView $$
CREATE PROCEDURE proc_Select_AttendrecordCountView
(
  IN wherestr varchar(1000)
)
BEGIN
declare sqlstr varchar(2000);
set sqlstr='SELECT count(1) as H FROM View_attendrecord';
if wherestr='' then
set sqlstr=sqlstr;
else
set sqlstr=sqlstr+wherestr;
end if;
set @sqlstr=sqlstr;
-- call(sqlstr);
PREPARE stmt FROM @sqlstr;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;


-- 5.1 up
#查询某记录的字段
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_Select_AttendrecordTitle $$
CREATE PROCEDURE proc_Select_AttendrecordTitle
(

  IN FieldName varchar(1000),
  IN param1id int
)
BEGIN
declare sqlstr varchar(2000);
declare wherestr varchar(1000);
set sqlstr=CONCAT('select ',FieldName);
set wherestr=CONCAT(' from attendrecord WHERE seq =',cast(param1id as char(20)));
set sqlstr=CONCAT(sqlstr,wherestr);
set @sqlstr=sqlstr;
PREPARE stmt FROM @sqlstr;  
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;


call proc_Select_AttendrecordTitle('rdescription',1);

select concat(2);

select cast(2 as char(20));
#模糊查询
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_Select_AttendrecordFuzzySearch $$
CREATE PROCEDURE proc_Select_AttendrecordFuzzySearch
(

  IN FieldList varchar(1000),
  IN wherestr varchar(2000) 
)
BEGIN
declare sqlstr varchar(2000);
declare ifwherestr varchar(1000);
declare iflist varchar(1000);
set ifwherestr='';
if FieldList='' then
   set iflist=' * ';
else
   set iflist=FieldList;
end if;
if wherestr<>'' then
   set ifwherestr=CONCAT(' WHERE ',wherestr);
end if;
set sqlstr=CONCAT('select ',iflist);
set sqlstr=CONCAT(sqlstr,'  from attendrecord ');
set sqlstr=CONCAT(sqlstr,ifwherestr);
set @sqlstr=sqlstr;
-- select @sqlstr;
-- call(sqlstr);
 PREPARE stmt FROM @sqlstr;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

-- seq =1
call proc_Select_AttendrecordFuzzySearch('rdescription','seq=1');


#查詢所有
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_Select_AttendrecordAll $$
CREATE PROCEDURE proc_Select_AttendrecordAll
()
BEGIN
SELECT * FROM attendrecord;
END $$
DELIMITER ;