当前位置: 萬仟网 > IT编程>数据库>Mysql > 涉及存储过程的问题

涉及存储过程的问题

2019年12月01日 15:13  | 萬仟网IT编程  | 我要评论

存储过程(特定功能的sql语句集)

  一组为了完成特定功能的sql语句集,存储在数据库中,经过第一次编译后再次调用不需要编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中一个重要对象。

1.创建存储过程:

  create procedure 存储过程名称 ([存储过程参数列表]) [存储过程特性] sql代码内容

1 例:delimiter  //
2         create procedure proc()
3         begin
4         select * from userinfo;
5       end  //
6       delimiter;

2.创建存储函数:

  create function 存储函数名称 ([存储过程参数列表]) returns type [存储函数特性] sql代码内容

1 例:delimiter //
2         create function namebyzip()
3         returns char(50)
4         return (select username from userinfo where userage='23');
5       //
6       delimiter;

3.变量的使用

  1)定义变量:declare 局部变量名称,...数据类型 [default value];

    例:declare myparam int default 100;

  2)为变量赋值:set 局部变量名称=expr,var_name=expr,...;

    例:declare var1,var2 int;

      set var1=10,var2=10;

4.定义条件和处理程序

  1)定义条件:declare 条件名称 condition for  sqlstate 'xxxx';

          declare 条件名称 condition for xxxx;

  2)定义处理程序:捕获 sqlstate_value: delcare continue handler for sqlstate 'xxxx' set @info='no_such_table';

            捕获 mysql_error_code: declare continue handler for xxxx set @info='no_such_table';

5.光标的使用

  1)声明光标:declare 光标名称 cursor for sql语句;

  2)打开光标:open 光标名称;

  3)使用光标:fetch 光标名称 into 字段名,...;

  4)关闭光标:close 光标名称;

6.流程控制语句

  if语句:if val is null then select 'val is null';  

      else select 'val is not null';  

      end if;

  case语句:case val

        when 1 then select 'val is 1';

        when 2 then select 'val is 2';

        else select 'val is not 1 or 2';

       end case;

  loop语句:declare id int default 0;

       add_loop:loop;

       set id = id + 1;  

        if id>=10 then leave add_loop;

        end if;

       end loop add_loop;

  leave语句:退出循环语句

  iterate语句:重新执行循环

  repeat语句:declare id int default 0;

        repeat

        set id = id + 1;

        until id >= 10;  ##先执行后判断

        end repeat;

  while语句:declare id int default 0;

       while id >=10 do  ##先判断后执行

       set id = id+1;

       end while;

7.调用存储过程和调用存储函数

  call 存储过程名称(存储过程定义的参数)

  select 存储函数名称(存储过程定义的参数)

8.查看存储过程和函数

  show {procedure | function} status like 'x%' 语句查看存储过程和函数的状态

  show create {procedure | function} 存储函数名称 语句查看存储过程和函数的定义

9.修改存储过程和函数

  alter {procedure | function}存储函数名称 (存储过程特性)

10.删除存储过程和函数

  drop {procedure | function} [if exists] 存储函数名称

存储过程优缺点:

  优点:

    1)存储过程因为sql语句已经预编译过了,因此运行的速度比较快;

    2)存储过程在服务器端运行,减少了客户端的压力。

    3)允许模块化程序设计,就是说只需要创建一次过程,以后在程序中就可以调用该过程任意次,类似方法的复用;

    4)减少网络流量,客户端调用存储过程只需要传存储过程名和相关参数即可,与传输sql语句相比自然数据量少了很多;

    5)增强了使用的安全性,充分利用系统管理员可以对执行的某一个存储过程进行权限限制,从而能够实现对某些数据访问的限制,避免非授权用户对数据的访问,保证数据的安全。程序员直接调用存储过程根本不知道表结构是什么,有什么字段,没有直接暴露表名以及字段名给程序员。

  缺点:

    调试麻烦(至少没有像开发程序那样容易),可移植性不灵活(因为存储过程是依赖于具体的数据库)。

存储过程优化思路:

  1)尽量利用一些sql语句来代替一些小循环,例如聚合函数、求平均函数等。

  2)中间结果存放于临时表,加索引;

  3)少使用游标。sql是一个集合语言,对于集合运算具有较高的性能。而cursors是过程运算,比如对一个100万行数据进行查询,游标需要读表100万次,而不是使用游标则只需要少量几次读取。

  4)事务越短越好,sql server支持并发操作,如果事务过多过长或隔离级别过高,都会造成并发阻塞、死锁。导致查询极慢,cpu占用率极低。

  5)使用try...catch处理异常;

  6)查询语句尽量不要放在循环内;

 

如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复

相关文章:

◎已有 0 人评论

Copyright © 2019  萬仟网 保留所有权利. 粤ICP备17035492号-1
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com