关键字:procedure
drop procedure if exists pp;/*如果存在存储过程pp则删除pp*/
delimiter // /**设置以//为sql语句结尾*/
1:不带参数的存储过程
create procedure pp()
begin
select count(*) from t_com_dim_date;
end//
delimiter ;
call pp();
drop procedure if exists pp;
delimiter //
2:带参数的存储过程(参数类型分为in、out、inout三种,在此未具体分析)
create procedure pp(in dt_name varchar(100),in number int)
begin
select * from t_com_dim_date where week_name=dt_name limit 0,number;
end//
delimiter ;
call pp('2011年4月-第1周',5);
测试时候本想把表名设置成参数,未成功,不知道为什么不成功
还有另外一种预编译的
create procedure pp(in tb_name varchar(50),in num int)
begin
set @tbname=tb_name;
set @numn=num;
set @insersql=concat('select date_id,date from t_com_dim_date where week_name=? limit 0,?');
prepare stmt from @insersql;
execute stmt using @tbname,@numn;
deallocate prepare stmt;
end//
begin
set @tbname=tb_name;
set @numn=num;
set @insersql=concat('select date_id,date from t_com_dim_date where week_name=? limit 0,?');
prepare stmt from @insersql;
execute stmt using @tbname,@numn;
deallocate prepare stmt;
end//