逍遥阁

mysql存储过程使用笔记

关键词: mysql procedure

mysql存储过程使用笔记

\-- 显示某数据库的存储过程
show procedure status where Db='temp';

\-- 创建一个存储过程
create procedure test() select * from menu;

\-- 调用存储过程
call test();

\-- 删除存储过程
drop procedure if exists test;

\-- 相加
create procedure test_add(a int,b int)
begin
   declare total int;
   set total=a+b;
   select total;
end;

\-- 调用
set @a=10;
set @b=90;
call test_add(@a,@b);

\-- 语法
create procedure 存储过程名字([in|out|inout] 参数名 类型)
begin
   SQL语句;
end;

\-- 例子
drop procedure if exists test;
create procedure test()
begin
  declare string varchar(500);
  set string='select * from menu';
 set @sql=string;
 prepare s1 from @sql;
 execute s1;
 deallocate prepare s1;
end;

\-- 例子
drop procedure if exists xxfaxy_test;
create procedure xxfaxy_test(keyword varchar(4))
begin
     declare the_id integer;
     declare the_name varchar(4);
     declare the_grade integer;
     declare is_over integer default 0;
     declare rs cursor for select `id`,`name`,`grade` from `temp_table`;
     declare continue handler for not found set is_over=1;
     set @execute_sql=concat('create temporary table `temp_table`(select * from `menu`',' ','where `name` like \'%',keyword,'%\')');
     prepare stmt from @execute_sql;
     execute stmt;
     deallocate prepare stmt;
     open rs;
     repeat
           fetch rs into the_id,the_name,the_grade;
              update `menu` set `dataSort`=the_id where `id`=the_id;
     until is_over end repeat;
     close rs;
     drop temporary table `temp_table`;
end;
call xxfaxy_test('a');

\-- 字符串参数
call test("'keyword'");
call test('keyword');

admin
2016-09-03 22:12:34
mysql

文章分类

文章标签

文章归档

友情链接

mysql存储过程使用笔记