> 文档中心 > MySQL存储过程,让存储过程不再“高级”难懂

MySQL存储过程,让存储过程不再“高级”难懂

目录

一:存储过程简介

二:MySQL存储过程变量及赋值

1. 局部变量

2. 用户变量

3. 会话变量

4. 全局变量

三:MySQL存储过程的创建

1. 语法格式

2. 使用navicat创建一个存储过程

3. 存储过程中的参数:IN OUT INOUT

四:MySQL存储过程的管理

1. MySQL存储过程的查询

2. MySQL存储过程的修改 

3. MySQL存储过程的删除

五:MySQL存储过程的控制语句 

1. 变量作用域

2. 条件语句

2.1 if-then-else语句

2.2 case语句

3. 循环语句

3.1 while...end while

3.2 repeat...end repeat

3.3 loop...end loop

六:游标


一:存储过程简介

        我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。
        一个存储过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。

        存储过程是数据库存储的一个重要的功能,但是MySQL在5.0以前并不支持存储过程,
这使得MySQL在应用上大打折扣。好在MySQL 5.0 终于开始已经支持存储过程,这样即
可以大大提高数据库的处理速度,同时也可以提高数据库编程的灵活性。

存储过程优点:
     (1)存储过程增强了SQL语言的功能和灵活性存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
     (2)存储过程允许标准组件是编程。存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。
     (3)存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。

     (4)存储过程能过减少网络流量。针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织程存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大增加了网络流量并降低了网络负载。
    (5)存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制,能够实现为相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。

存储过程缺点:
     
 (1)过程化编程,复杂业务处理的维护成本高。
     
(2)调试不便。
   
  (3)不同数据库之间可移植性差。--不同数据库语法不一致!

二:MySQL存储过程变量及赋值

1. 局部变量

(1)用户自定义,在begin...end块中有效。

(2)变量声明语法:        

declare var_ name type [default var_value]; -- 变量声明declare nicknane varchar (32); -- 例子

(3)使用set赋值:

        一般用来对于变量的赋值;

        语法格式:set 变量名 = 变量值;

create procedure sp_01()begin -- 声明变量name,并赋初始值为zldeclare name varchar(25) default 'zl';-- 利用set对变量进行赋值set name = 'lisi';-- 查询(相当于打印)select name; -- lisiend;-- 调用存储过程(相当于调用方法)call sp_01();

(4)使用into赋值

        一般用来把一个SQL语句的结果,存到存储过程的变量中

        例如:先声明一个变量:name,然后把查询的结果放到这个变量当中

        select e.ename into name from emp e where e.empno = 7902; 

create procedure sp_02()begin declare name varchar(32) default 'unknow';select e.ename into name from emp e where e.empno = 7902; select name; -- FORDend;call sp_02();select @name;  -- NULL 局部变量,出来就不起作用了

2. 用户变量

用户变量就相当于Java中的成员变量,用户自定义,当前会话(连接)有效

变量声明语法:  

@var_name -- 不需要提前声明,使用即声明
create procedure sp_03()begin -- 不需要提前用declare声明,使用即声明set @var_name = 'lisi';end; call sp_03(); select @var_name;  -- lisi

3. 会话变量

由系统提供,当前会话(连接)有效

变量声明语法:       

@@session.var_name

(1)查看会话变量

show session variables;

(2)查看某会话变量

-- auto_increment_increment是一个会话变量select @@session.auto_increment_increment

(3)修改会话变量

set @@session.auto_increment_increment = 0;

4. 全局变量

由系统提供,整个mysql服务器有效

变量声明语法:

@@global.var_name;

(1)查看全局变量中变量名有 "char" 的记录

show global variables like '%char%';

(2)查看全局变量 character_set_client的值

select @@global.character_set_client;

三:MySQL存储过程的创建

1. 语法格式

(1)delimiter //  声明结束符为// 。因为MySQL默认使用" ;"作为结束符,而在存储过程中,会使用" ;"作为一段语句的结束,导致" ;"使用冲突

(2)MySQL存储过程创建的格式:
         create procedure  过程名 ([过程参数[,...]]) [特性 ...] 过程体

(3)MySQL存储过程调用的格式:

         call 过程名 ([过程参数[,...]])
(4)这里先举个例子:

delimiter // --声明分隔符为 //create procedure hello_procedure()begin    select 'hello procedure';end;// --结束符call hello_procedure(); --调用存储过程(就相当于Java中调用方法)// --结束符delimiter ; --恢复分隔符为分号;

 注意:

(1)这里需要注意的是 delimiter// 和 delimiter; 两句,delimiter是分割符的意思,MySQL默认以";"为分隔符,如果我们没有声明分割符,那么编译器会把存储过程当成SQL语句进行处理,则存储过程的编译过程会报错,所以要事先用delimiter关键字申明当前段分隔符,这样MySQL才会将";"当做存储过程中的代码,不会执行这些代码,用完了之后要把分隔符还原。

 例如:

1、使用默认的分隔符分号 ;是SQL语句的结束标志             

select count(*) from emp;--程序看到 分号; 就会执行

2、修改默认的分隔符为双斜杠 // 作为SQL语句的结束标志              

delimiter // --修改分隔符为//select count(*) from emp; --不会执行// --看到双斜杠才会执行

(2)存储过程根据需要可能会有输入、输出、输入输出参数,对应着:IN、OUT、INOUT
(3)过程体的开始与结束使用begin与end进行标识。这样,我们的一个MySQL存储过程就完成了。

2. 使用navicat创建一个存储过程

(1)连接数据库 

(2)新建数据库并导入数据脚本

右击鼠标选择新建数据库,并输入数据库名bjpowernode和字符集utf-8

 再次右击选择运行SQL文件

把SQL脚本导进去,生成表

(3)选择查询右键,新建一个查询,在里面编写SQL语句;值得注意的是使用navicat工具就不需要修改分隔符了,可以直接进行存储过程的创建

全选,然后运行已选择的

(4)找到函数,右击然后刷新,就可以找到我们创建的PROC1

3. 存储过程中的参数:IN OUT INOUT

MySQL存储过程的参数用在存储过程的定义,共有三种参数类型:IN,OUT,INOUT,分别代表输入、输出、输入输出

CREATE PROCEDURE([IN | OUT | INOUT] 参数名 数据类形...])

IN输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认类型
OUT输出参数:该值可在存储过程内部被改变,并可返回
INOUT输入输出参数:调用时指定,并且可被改变和返回

MySQL 存储过程的调用:用call和过程名以及一个括号,括号里面根据需要,加入参数,参数包括输入参数、输出参数、输入输出参数。

例题1:IN参数

IN类型是把存储过程之外的数据带入给存储过程,但在存储过程中即使把IN类型参数改变了,最终结果也不会带回给调用环境的;入参!

-- P_IN参数没有声明输入输出类型,则默认的就是IN类型-- 声明存储过程CREATE PROCEDURE PROC1(P_IN INT)BEGINSELECT P_IN;  -- 10 P_IN的值能传过来SET P_IN = 3; -- 设置P_IN参数的值SELECT P_IN ; -- 3 再次查询P_IN 参数的值END;-- 开始调用存储过程SET @P_IN = 10; -- 声明一个变量P_IN,并且赋初始值,声明变量采用@XXX格式CALL PROC1(@P_IN); -- 调用存储过程并且传递P_IN参数SELECT @P_IN; -- 10 查询P_IN的结果,IN类型不会把结果带出来,最终还是10,而不是3

语句运行结果是:10  3  10 

例题2:OUT参数

OUT类型参数,不能把数据代入存储过程,但存储过程中的数据可以带回给调用环境;出参!

CREATE PROCEDURE PROC2(OUT S INT)BEGIN  SELECT S; -- NULL,OUT类型数据是不能把数据代入给存储过程的SELECT COUNT(*) INTO S FROM emp; -- 14条数据SELECT S; -- 14SET S = 1000; -- 修改数据  SELECT S; -- 1000,再次查询END;-- 开始调用存储过程SET @S = 10; -- 并没有初始化值,OUT类型数据是不能把数据代入给存储过程的CALL PROC2(@S); -- 调用存储过程SELECT @S; -- 1000,存储过程中的数据可以带回给调用环境

语句运行结果是:NULL  14 1000 1000 

例题3:IN,OUT联合使用

IN是入参,OUT是出参;利用IN入参传dept表中的dname,再利用OUT出参,把dept表中的loc显示出来

create procedure pro_in_out(IN dept_dname varchar(32), out dept_loc varchar(32))begin    select  d.loc into dept_loc from dept d where d.dname =  dept_dname;end;call pro_in_out('ACCOUNTING',@dept_loc);select @dept_loc; --  NEW YORK

例题4:INOUT参数

INOUT类型参数,既可以把数据代入存储过程,也可以把存储过程中的数据带回给调用环境;既能入也能出!

CREATE PROCEDURE PROC3(INOUT P_IN_OUT INT)BEGIN  SELECT P_IN_OUT; -- 10,可以把外界的值带入存储过程SET P_IN_OUT = 66; -- 修改数据  SELECT P_IN_OUT; -- 66,再次查询END;-- 开始调用存储过程SET @P_IN_OUT = 10; -- 初始化值,可以把外界的值带乳存储过程CALL PROC3(@P_IN_OUT); -- 调用存储过程SELECT @P_IN_OUT; -- 66,存储过程中的数据可以带回给调用环境

语句运行结果是:10 66 66

四:MySQL存储过程的管理

1. MySQL存储过程的查询

      我们想知道一个数据库下面有那些表,我们一般采用show tables; 进行查看。那么我们要查看某个数据库下面的存储过程,是否也可以采用呢? 答案是,我们可以查看某个数据库下面的存储过程,但是是令一种方式:

show procedure status where db = “数据库名”;

例如:查看bjpowernode数据库下面的存储过程

SHOW PROCEDURE STATUS WHERE DB = "bjpowernode";

    如果我们想知道,某个存储过程的详细,那我们又该怎么做呢?是不是也可以像操作表一样用 desc 表名 进行查看呢?

    答:可以查看存储过程的详细,语法SHOW CREATE PROCEDURE 数据库.存储过程名;就可以查看当前存储过程的详细。

例如:查看PROC2存储过程的详细信息

SHOW CREATE PROCEDURE bjpowernode.PROC2;

2. MySQL存储过程的修改 

目前,MySQL 还不提供对已存在的存储过程的代码修改,如果一定要修改存储过程的代码,必须,先将存储过程删除之后,在重新编写代码;或者直接创建一个新的存储过程

3. MySQL存储过程的删除

删除一个存储过程比较简单,和删除表一样;语法:
DROP PROCEDURE [IF EXISTS] 存储过程名称

例如:从MySQL的表格中删除一个或多个存储过程。

DROP PROCEDURE IF EXISTS PROC1;

运行成功界面:

删除成功结果界面: 

五:MySQL存储过程的控制语句 

1. 变量作用域

内部的变量在其作用域范围内享有更高的优先权,当执行到end时,内部变量消失,此时已经在其作用域外,变量不再可见了,因为在存储过程外再也不能找到这个声明的变量,但是我们可以通过out参数或者将其值指派给会话变量来保存其值。

CREATE PROCEDURE PROC4()BEGINDECLARE X1 VARCHAR(5) DEFAULT "OUTER" ;-- 嵌套块,在嵌套块(内部块)中也定义一个变量X1-- 在内部块中和外部块中有相同名称的X1,在内部块中优先使用内部块中定义的X1BEGIN    -- 如果下面定义的变量X1,注释掉,最终运行的结果全是OUTERDECLARE X1 VARCHAR(5) DEFAULT "INNER" ;    DECLARE X2 VARCHAR(5) DEFAULT "INNER2" ;    SELECT X1; -- 结果是INNER    END;SELECT X1; -- 结果是OUTER    SELECT X2; -- 不能访问X2,因为X2已经超出了使用范围END;CALL PROC4();

运行结果如下:INNER OUTER

2. 条件语句

2.1 if-then-else语句

语法格式:

        if 条件语句 then SQL语句 end if;     或者

        if 条件语句 then

        SQL语句 else SQL语句

        end if;

-- 创建一个表CREATE TABLE TEMP(ID INT(4));-- 创建存储过程CREATE PROCEDURE PROC5(IN P_IN INT)BEGINDECLARE VAR INT;SET VAR = P_IN + 1;--    if--------------------  -- 注意:在MySQL中判断变量是否相等没使用 =  IF VAR = 1 THENINSERT INTO TEMP VALUES(100);END IF;--    if else--------------------IF P_IN = 0 THENINSERT INTO TEMP VALUES (-1);ELSEINSERT INTO TEMP VALUES (1);END IF;END;CALL PROC5(0);SELECT *FROM TEMP;

先插入了一个100,后又插入了一个-1

2.2 case语句

语法格式:

        case  变量

        when 值  then

        SQL语句

或者:    

        case  

        when 变量和值的关系式  then

        SQL语句

-- 删除表中的数据DELETE FROM TEMP;-- 创建存储过程CREATE PROCEDURE PROC6(IN P_IN INT)BEGINDECLARE VAR INT;SET VAR = P_IN + 1;CASE VARWHEN 1 THENINSERT INTO TEMP VALUES (1);WHEN 2 THENINSERT INTO TEMP VALUES (2);ELSEINSERT INTO TEMP VALUES (3);END CASE;END;CALL PROC6(0);SELECT *FROM TEMP;

运行结果:1

3. 循环语句

3.1 while...end while

例题语法格式:

        while 变量 do

                SQL语句

                变量自增

        end while

-- 清空TEMP表中的记录DELETE FROM TEMP:-- 创建存储过程CREATE PROCEDURE PROC7 ()BEGIN-- 设置一个int类型的var变量,初始值为0DECLARE VAR INT DEFAULT 0;WHILE VAR < 10 DOINSERT INTO TEMP VALUES (VAR) ;SET VAR = VAR + 1;END WHILE;END;CALL PROC7();SELECT * FROM TEMP;

运行结果:插入10个数据到表中

3.2 repeat...end repeat

例题语法格式:     

        repeat

                SQL语句

                变量自增

                UNTIL 循环结束条件

        end repeat

repeat是在执行操作后检查结果,而while则是执行前进行检查!

-- 清空TEMP表中的记录DELETE FROM TEMP ;-- 创建存储过程CREATE PROCEDURE PROC8()BEGINDECLARE VAR INT DEFAULT 0;REPEATINSERT INTO TEMP VALUES (VAR) ;SET VAR = VAR + 1;-- 增加退出循环  -- 退出循环的语句,末尾没有分号UNTIL VAR > 10 -- until直到...才END REPEAT;END;CALL PROC8();SELECT * FROM TEMP;

运行结果:插入0-10的数据到表中

3.3 loop...end loop

loop循环不需要初始条件,这点和while循环相似,同时和repeat循环一样不需要结束条件。

loop是死循环需要手动退出循环,我们可以使用leave来退出。可以把leave看成我们java中的break;与之对应的就有iterate (继续循环),类比java的continue。

例题语法格式:

        别名:LOOP    

                SQL语句

                变量自增

                if 循环结束条件 then

                        leave 别名

                end if

        END LOOP

-- 清空TEMP表中的记录DELETE FROM TEMP ;-- 创建存储过程CREATE PROCEDURE PROC9()BEGINDECLARE VAR INT DEFAULT 0;LOOP1:LOOP -- 起一个名字INSERT INTO TEMP VALUES (VAR) ;SET VAR = VAR + 1;-- 退出循环的条件IF VAR > 6 THEN -- 终止循环的条件leave LOOP1; -- 退出loop1循环END IF; -- 上面也可以写成 IF VAR <=6 THEN     iterate LOOP1; -- 继续循环 END IF; leave LOOP1;END LOOP LOOP1;END;CALL PROC9();SELECT * FROM TEMP;

运行结果:插入0-6的数据到表中

六:游标

(1)游标:用游标得到一个结果集,逐行处理数据

(2)语法格式:

-- 声明语法declare cursor_name cursor for select_statement; -- 把查询结果集放到cursor_name-- 打开语法open cursor_name; -- 打开这个结果集-- 取值语法fetch cursor_name into var_name[,var_name...]; -- 一行一行的取值-- 关闭语法close cursor_name;

(3)在语法中,变量声明、游标声明、handle声明必须按照先后顺序书写的,否则创建存储过程出错。

(4)例:按照部门名称查询,通过select查看员工的编号、姓名、薪资

-- 删除存储过程drop procedure if exists sp_show_emp;-- 创建存储过程create procedure sp_show_emp (in dept_name varchar(32))begin      -- 变量声明,用来接收数据      declare e_no int;      declare e_name varchar(32);      declare e_sal decimal(7,2);  -- 总共7位,2位是小数      -- 先定义一个标记,后面handler句柄用  declare flag boolean default true;       -- 游标声明      declare emp_cursor cursor for       select e.empno,e.ename,e.sal       from emp e,dept d       where e.deptno = d.deptno and d.dname = dept_name;      -- handler句柄声明      -- 1329是错误码,还可以使用02000,或者NOT FOUND(02开头的都可以)          declare continue handler for 1329 set flag = false;          -- 打开游标      open emp_cursor;      -- 取值      emp_loop:loop          fetch emp_cursor into e_no,e_name,e_sal; -- 数据取完,会报错,对应有一个错误码          if flag then             select e_no,e_name,e_sal;          else               leave emp_loop;  end if;              end loop emp_loop;      set @end_flag = 'end'      -- 关闭游标      close emp_cursor;end;call sp_show_emp('ACCOUNTING');-- handler使用continue,这里就会执行到@end_flag赋值;如果使用exit就不会执行select @end_flag;