mysql存储过程、函数和触发器的创建

最新推荐文章于 2020-04-01 01:01:25 发布

ckgjx9555

最新推荐文章于 2020-04-01 01:01:25 发布

阅读量141

点赞数

文章标签：数据库

今天花了半天时间来研究mysql的存储过程函数和触发器的创建，觉得和oracle的大同小异，只是语法上更艰涩点，可能是先入为主吧，经过多次失败，终于创建成功，需要注意创建前要先执行“delimiter //”，创建后以“//”结束，还有就是怎么没有像plsql dev这样的工具来调试程序呢。和oracle的在语法上主要不同点有如下几方面：

1.declare放到了begin体里面

2.给变量复制是set xxx = 1

3. 在事务开始时要start transaction;

4.函数的返回是returns（oracle的是return），返回类型也需要给varchar等类型定义长度

还有就是在网上翻了个遍，手册也看了，就是没有找到存储过程的自定义异常，不知道到底有没有。

以下举几个例子：

没有create or replace语句，只要先删除再创建

delimiter$$

create function f_a(ai_n int)
returns varchar(64)
begin
declare vs_res varchar(64);
declare vi_count int;
select max(n),count(1) into vs_res ,vi_count from d where i = ai_n;
if vs_res is null then
set vs_res = '';
end if;
return vs_res;
end$$
DELIMITER ;

如果出现如下错误，需要设置set global log_bin_trust_function_creators=TRUE。
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

mysql> select f_a(1);
+--------+
| f_a(1) |
+--------+
| 直播 |
+--------+
1 row in set (0.05 sec)

存储过程：
delimiter$$

create procedure p_a(in ai_num int)
begin
declare vi_loop int default 0;
declare vi_maxid,vi_count int;
select max(id),count(1) into vi_maxid,vi_count from a;
if vi_count = 0 then
  set vi_maxid = 0;
end if;
start transaction;
set vi_loop = 1;
while vi_loop < ai_num do
         set vi_maxid = vi_maxid + 1;
  insert into u values(vi_maxid,vi_maxid);
  set vi_loop = vi_loop + 1;
end while;
commit;
end$$

delimiter$$

delimiter $$

create procedure curdemo()
begin
declare done int default 0;
declare a char(16);
declare b,c,d int;
declare cur1 cursor for select id,data from test.t1;
declare cur2 cursor for select i from test.t2;
declare continue handler for sqlstate '02000' set done = 1;
open cur1;
open cur2;
repeat
    fetch cur1 into a, b;
    fetch cur2 into c;
    if not done then
       set d = sign(b - c);
       case d
       when 0 then
              insert into test.t3 values (a,0);
        when 1 then
              insert into test.t3 values (a,b);
        when -1 then
              insert into test.t3 values (a,c);
       end case;
    end if;
until done end repeat;
close cur1;
close cur2;
end$$
delimiter;
触发器：
和oracle差不多，就是需要开头delimiter //，结尾用//，new前的：去掉即可。
mysql> drop trigger trg_a
mysql> delimiter //
mysql> create trigger trg_a before insert on a
    -> for each row

    -> insert into b set id=new.id,i=111;
    -> delete from c where a=new.id;
    -> end;
    -> //
Query OK, 0 rows affected (0.02 sec)

运行过程
msql>call p_a(1000);
通过show create procedure/function name可以查看过程和函数的结果
如mysql>show create procedure p_a;

####以下是项目中的例子

drop FUNCTION `manage`.`f_getarea`;
DELIMITER $$

CREATE FUNCTION `manage`.`f_getarea`(as_areaid varchar(32), ai_i   int)
#ai_i层数
#select f_getarea('1.22.333.55',2) can get '1.22.'
RETURNS varchar(32)
BEGIN
declare vi_temp,vi_i,vi_instr tinyint(2);
#不可能超过5层
if ai_i > 5 or ai_i < 1 then
    return '';
end if;
set vi_temp = 0,vi_i = 0;
while 1 = 1 do
        set vi_instr = vi_temp;
set vi_temp = locate('.',as_areaid,vi_temp+1);
        if vi_temp = 0 then
  return substr(as_areaid,1,vi_instr);
end if;
        if vi_i = ai_i then
  return substr(as_areaid,1,vi_instr);
        end if;
set vi_i = vi_i + 1;
end while;
return '';
END$$