版本要求:mysql 5.0.13
原因:mysql5.0.13之後支援在預存程序中調用prepare
其它注意事項:
1 prepare stmt from 'select * from ?'; (錯)
mysql5.0.24,prepare尚不支援 表名做變數!
解決方案:用 contat()函數,組合字元串
2 execute stmt [using @var,@var2]
必須是@var形式的變數,傳入的參數變數,declare變數不行
3 deallocate prepare stmt; 顯式的釋放prepare,如果不釋放,mysql會釋放!
delimiter // 預存程序界符
實例1:
create procedure test11(in t_name Varchar(100))
begin
declare v_sql Varchar(500);
declare v_sql2 Varchar(500);
set v_sql2=' where user=\'user1\'';
set v_sql=concat('select * from ',t_name, v_sql2);
select v_sql;
set @v_sql=v_sql;
prepare stmt from @v_sql;
execute stmt;
deallocate prepare stmt;
end
實例2:
create procedure test4()
begin
declare s1 text default ' where pay1_order=';
set s1=concat(s1,'\'D03100000100604020080314232541\'');
set s1=concat('select * from pos_bill.pos_bill_log ', s1);
select s1;
set @s1=s1;
prepare stmt from @s1;
execute stmt;
deallocate prepare stmt;
end
實例3:本例用到了實際工作中
create procedure test6(in a text)
begin
DECLARE i INT(8) DEFAULT 0;
declare s1 text default ' where pay1_order=';
declare s2 text default null;
declare s3 text default 'pay1_order=';
REPEAT SET i = i + 1;
set s2=SUBSTRING_INDEX(SUBSTRING_INDEX(a, ',', i), ',', -1);
set s2=concat('\'',s2,'\''); 欄位值要加引號
if i=3 then
set s1=concat(s1,s2);
else
set s1=concat(s1,s2,' or ',s3);
end if;
UNTIL i >= 3 END REPEAT;
set s1=concat('select * from pos_bill.pos_bill_log ', s1);
select s1;
set @s1=s1;
prepare stmt from @s1;
execute stmt;
deallocate prepare stmt;
end
調用過程test6:
use mysql
call test6('D03100000100604020080314232541,D02100000100604020080314232304,D02100000100600120080314211752')//
全站熱搜
留言列表
