小步慢跑

使用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) aa');
  5: INSERT INTO alarm (name) 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 閱讀(787) 評論(0)  編輯 收藏 引用 所屬分類: mysql