涉及存储过程的问题

database

存储过程" title="存储过程">存储过程(特定功能的SQL语句集)

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

1.创建存储过程:

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

1 例:delimiter  //

2createprocedureproc()

3begin

4select*from userinfo;

5end//

6 delimiter;

2.创建存储函数:

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

1 例:delimiter //

2createfunction NameByZip()

3returnschar(50)

4return (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)查询语句尽量不要放在循环内;

 

以上是 涉及存储过程的问题 的全部内容, 来源链接: utcz.com/z/531637.html

回到顶部