小步慢跑

使用PREPARE 动态执行sql语句

  1: DROP TABLE IF EXISTS alarm;
  2: CREATE TABLE alarm     ( id int(11) NOT NULL  auto_increment, name    varchar(255) default NULL,PRIMARY KEY (id));
  3:  
  4: INSERT INTO alarm (name) values('aa');
  5: INSERT INTO alarm (name) values('bb');
  6:  
  7: DROP procedure if exists statalarm;
  8: delimiter // 
  9: create procedure statalarm()
 10: begin
 11: SET @id  = '1,2';
 12: 
 13: SET @sql = 'select * from alarm where id IN (?)';
 14: PREPARE stmt FROM @sql;
 15: /* 
 16:  注意 EXECUTE 的最终语句是：select * from alarm where id IN ('1,2');
 17:  而不是 select * from alarm where id IN (1,2);
 18:  这是因为如果用户变量的值是字符串，在EXECUTE时 会自动的在变量的值前后加上引号
 19:  */
 20: EXECUTE stmt USING @id;
 21: DEALLOCATE PREPARE stmt;
 22: /*
 23:  如果想要 组成select * from alarm where id IN (1,2);可以使用下面的语句
 24: */
 25: SET @sql = concat('select * from alarm where id IN (',@id,')');
 26: PREPARE stmt FROM @sql;
 27: EXECUTE stmt ;
 28: DEALLOCATE PREPARE stmt;
 29:  
 30: end;//
 31: delimiter ;

posted on 2013-02-21 14:15 zaccheo 阅读(759) 评论(0)  编辑 收藏 引用 所属分类: mysql