细谈Mysql的存储过程和存储函数

1 存储过程

1.1 什么是存储过程

存储过程是一组为了完成某项特定功能的sql语句集,其实质上就是一段存储在数据库中的代码,他可以由声明式的sql语句(如CREATE,UPDATE,SELECT等语句)和过程式sql语句(如IF...THEN...ELSE控制结构语句)组成。存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。

1.2 存储过程的优缺点

优点:

1.可增强sql语言的功能和灵活性
存储过程可以用流程控制语言编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。

2.良好的封装性
存储过程被创建后,可以在程序中被多次调用,而不必担心重写编写该存储过程的sql语句。

3.高性能
存储过程执行一次后,其执行规划就驻留在高速缓冲存储器中,以后的操作中只需要从高速缓冲器中调用已编译好的二进制代码执行即可,从而提高了系统性能。

缺点:

存储过程,往往定制化于特定的数据库上,因为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重写原有的存储过程。

1.3 创建存储过程

1.3.1 DELIMITER定界符

在sql中服务器处理sql语句默认是以分号作为语句的结束标志,然而在创建存储过程时,存储过程体中可能包含多条sql语句,这些sql语句如果仍以分号作为语句结束符,那么服务器在处理时会以第一条sql语句处的分号作为整个程序的结束符,而不再去处理后面的sql。
为解决这个问题,通常使用DELIMITER命令,将sql语句的结束符临时修改为其他符号。

DELIMITER语法格式:

DELIMITER $$

$$是用户定义的结束符,通常这个符号可以是一些特殊的符号。另外应避免使用反斜杠,因为他是转义字符。
若希望换回默认的分号作为结束标记,只需再在命令行输入下面的sql语句即可。

DELIMITER ;

1.3.2 存储过程创建

在Mysql中,使用CREATE PROCEDURE语句来创建存储过程。

CREATE PROCEDURE p_name([proc_parameter[,...]])
routine_body

其中,语法项“proc_parameter”的语法格式是:

[IN|OUT|INOUT]parame_name type

1."p_name"用于指定存储过程的名称。

2."proc_parameter"用于指定存储过程中的参数列表。其中,语法项"parame_name"为参数名,"type"为参数的类型(类型可以是Mysql中任意的有效数据类型)。Mysql的存储过程支持三种类型的参数,即输入参数IN,输出参数OUT,输入输出参数INOUT。输入参数是使数据可以传递给一个存储过程;输出参数是用于存储过程需要返回的一个操作结果;输入输出参数既可以充当输入参数也可以充当输出结果。
参数的取名不要和表中的列名相同,否则尽管不会返回出错信息,但储存过程中的sql语句会将参数名当做列名,从而引发不可预知的错误。

3.语法项"rountine_body"表示存储过程的主体部分,也成为存储过程体,其包含了需要执行的sql。过程体以关键字BEGIN开始,以关键字END结束。若只有一条sql可以忽略BEGIN....END标志。

1.3.3 局部变量

在存储过程体中可以声明局部变量,用来存储过程体中的临时结果。在Mysql中使用DECLARE语句来声明局部变量。

DECLARE var_name type [DEFAULT value]

"var_name"用于指定局部变量的名称;"type"用来声明变量的类型;"DEFAULT"用来指定默认值,如果没有指定则为NULL。

注意:局部变量只能在存储过程体的BEGIN...END语句块中;局部变量必须在存储过程体的开头处声明;局部变量的作用范围仅限于声明它的BEGIN...END语句块,其他语句块中的语句不可以使用它。

1.3.4 用户变量

用户变量一般以@开头。

注意:滥用用户变量会导致程序难以理解及管理。

1.3.5 SET语句

在Mysql中通过SET语句对局部变量赋值,其格式是:

SET var_name = expr[,var_name2 = expr]....

1.3.6 SELECT....INTO语句

在Mysql中,可以使用SELECT...INTO语句把选定的列的值存储到局部变量中。格式是:

SELECT col_name[,..] INTO var_name[,....] table_expr

其中"col_name"用于指定列名;"var_name"用于指定要赋值的变量名;"table_expr"表示SELECT语句中FROM后面的部分。

注意:SELECT...INTO语句返回的结果集只能有一行数据。

1.3.7 流程控制语句

条件判断语句

if-then-else 语句:

mysql > DELIMITER &&
mysql > CREATE PROCEDURE proc2(IN parameter int)
 -> begin
 -> declare var int;
 -> set var=parameter+1;
 -> if var=0 then
 -> insert into t values(17);
 -> end if;
 -> if parameter=0 then
 -> update t set s1=s1+1;
 -> else
 -> update t set s1=s1+2;
 -> end if;
 -> end;
 -> &&
mysql > DELIMITER ;

case语句:

mysql > DELIMITER &&
mysql > CREATE PROCEDURE proc3 (in parameter int)
 -> begin
 -> declare var int;
 -> set var=parameter+1;
 -> case var
 -> when 0 then
 -> insert into t values(17);
 -> when 1 then
 -> insert into t values(18);
 -> else
 -> insert into t values(19);
 -> end case;
 -> end;
 -> &&
mysql > DELIMITER ; 

循环语句
while ···· end while:

mysql > DELIMITER &&
mysql > CREATE PROCEDURE proc4()
 -> begin
 -> declare var int;
 -> set var=0;
 -> while var<6 do
 -> insert into t values(var);
 -> set var=var+1;
 -> end while;
 -> end;
 -> &&
mysql > DELIMITER ;

repeat···· end repea:

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

mysql > DELIMITER &&
mysql > CREATE PROCEDURE proc5 ()
 -> begin
 -> declare v int;
 -> set v=0;
 -> repeat
 -> insert into t values(v);
 -> set v=v+1;
 -> until v>=5
 -> end repeat;
 -> end;
 -> &&
mysql > DELIMITER ;
repeat
 --循环体
 until 循环条件
end repeat;

loop ·····endloop:

loop 循环不需要初始条件,这点和 while 循环相似,同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。

mysql > DELIMITER &&
mysql > CREATE PROCEDURE proc6 ()
 -> begin
 -> declare v int;
 -> set v=0;
 -> LOOP_LABLE:loop
 -> insert into t values(v);
 -> set v=v+1;
 -> if v >=5 then
 -> leave LOOP_LABLE;
 -> end if;
 -> end loop;
 -> end;
 -> &&
mysql > DELIMITER ;

ITERATE迭代:

mysql > DELIMITER &&
mysql > CREATE PROCEDURE proc10 ()
 -> begin
 -> declare v int;
 -> set v=0;
 -> LOOP_LABLE:loop
 -> if v=3 then
 -> set v=v+1;
 -> ITERATE LOOP_LABLE;
 -> end if;
 -> insert into t values(v);
 -> set v=v+1;
 -> if v>=5 then
 -> leave LOOP_LABLE;
 -> end if;
 -> end loop;
 -> end;
 -> &&
mysql > DELIMITER ;

1.3.8 游标

MySQL中的游标可以理解成一个可迭代对象(类比Python中的列表、字典等可迭代对象),它可以用来存储select 语句查询到的结果集,这个结果集可以包含多行数据,从而使我们可以使用迭代的方法从游标中依次取出每行数据。

MySQL游标的特点:
1.只读:无法通过光标更新基础表中的数据。
2.不可滚动:只能按照select语句确定的顺序获取行。不能以相反的顺序获取行。 此外,不能跳过行或跳转到结果集中的特定行。
3.敏感:有两种游标:敏感游标和不敏感游标。敏感游标指向实际数据,不敏感游标使用数据的临时副本。敏感游标比一个不敏感的游标执行得更快,因为它不需要临时拷贝数据。MySQL游标是敏感的。

1.声明游标

游标声明必须在变量声明之后。如果在变量声明之前声明游标,MySQL将会发出一个错误。游标必须始终与select语句相关联。

declare cursor_name cursor for select_statement;

2.打开游标

使用open语句打开游标,只有先打开游标才能读取数据。

open cursor_name;

3.读取游标

使用fetch语句来检索游标指向的一行数据,并将游标移动到结果集中的下一行。

fetch cursor_name into var_name;

4.关闭游标

使用close语句关闭游标。

close cursor_name;

当游标不再使用时,应该关闭它。   当使用MySQL游标时,还必须声明一个notfound处理程序来处理当游标找不到任何行时的情况。 因为每次调用fetch语句时,游标会尝试依次读取结果集中的每一行数据。 当游标到达结果集的末尾时,它将无法获得数据,并且会产生一个条件。 处理程序用于处理这种情况。

declare continue handler for not found set type = 1;

type是一个变量,示游标到达结果集的结尾。

delimiter $$
create PROCEDURE phoneDeal()
BEGIN
 DECLARE id varchar(64); -- id
 DECLARE phone1 varchar(16); -- phone
 DECLARE password1 varchar(32); -- 密码
 DECLARE name1 varchar(64); -- id
 -- 遍历数据结束标志
 DECLARE done INT DEFAULT FALSE;
 -- 游标
 DECLARE cur_account CURSOR FOR select phone,password,name from account_temp;
 -- 将结束标志绑定到游标
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 -- 打开游标
 OPEN cur_account;
 -- 遍历
 read_loop: LOOP
 -- 取值 取多个字段
 FETCH NEXT from cur_account INTO phone1,password1,name1;
 IF done THEN
 LEAVE read_loop;
 END IF;

 -- 你自己想做的操作
 insert into account(id,phone,password,name) value(UUID(),phone1,password1,CONCAT(name1,'的家长'));
 END LOOP;

 -- 关闭游标
 CLOSE cur_account;
END $$

1.3.7 调用存储过程

使用call语句调用存储过程

call sp_name[(传参)];

1.3.8 删除存储过程

使用drop语句删除存储过程

DROP PROCEDURE sp_name

2 存储函数

2.1 什么是存储函数

存储函数和存储过程一样,都是sql和语句组成的代码块。
存储函数不能有输入参数,并且可以直接调用,不需要call语句,且必须有一条包含RETURN语句。

2.2 创建存储函数

在Mysql中使用CREATE FUNCTION语句创建:

CREATE FUNCTION fun_name (par_name type[,...])
RETURNS type
[characteristics]
fun_body

其中,fun_name为函数名,并且名字唯一,不能与存储过程重名。par_name是指定的参数,type为参数类型;RETURNS字句用来声明返回值和返回值类型。fun_body是函数体,所有存储过程中的sql在存储函数中同样可以使用。但是存储函数体中必须包含一个RETURN 语句。
characteristics指定存储过程的特性,有以下取值:

  • LANGUAGE SQL:说明routine_body部分是由SQL语句组成的,当前系统支持的语言为SQL,SQL是LANGUAGE特性的唯一值。
  • [NOT] DETERMINISTIC:指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的,每次执行存储过程时,相同的输入会得到相同的输出,NOT DETERMINISTIC表示结果是不确定的,相同的输入可能得到不同的输出,如果没有指定任意一个值,默认为NOT DETERMINISTIC。
  • [CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA]:指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句,但不包含读写数据语句;NO SQL表明子程序不包含SQL语句;READS SQL DATA说明子程序包含读数据的语句;MODIFIES SQL DATA表名子程序包含写数据的语句。默认情况下,系统会指定为CONTAINS SQL。
  • SQL SECURITY[DEFINER|INVOKER]:指明谁有权限来执行。DEFINER表示只有定义着才能执行。INVOKER表示用友权限的调用者可以执行。默认情况下,系统指定为DEFINER。
  • COMMENT 'string':注释信息,用来描述存储过程或函数。
delimiter $$
create function getAnimalName(animalId int) RETURNS VARCHAR(50)
DETERMINISTIC
begin
 declare name VARCHAR(50);
 set name=(select name from animal where id=animalId);
 return (name);
end$$
delimiter;
-- 调用
select getAnimalName(4)

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我们。

(0)

相关推荐

  • mysql创建存储过程实现往数据表中新增字段的方法分析

    本文实例讲述了mysql创建存储过程实现往数据表中新增字段的方法.分享给大家供大家参考,具体如下: 需求: 往某数据库的某个表中新增一个字段(若该字段已存在,则不做操作:若该字段不存在,则新增) 百度了n久,没有符合要求的例子,只有参考加自己琢磨,最终终于给弄出来了,以下是几个版本的更迭 第一版: DELIMITER $$ CREATE PROCEDURE insert_column() BEGIN IF NOT EXISTS(SELECT 1 FROM information_schema.c

  • MYSQL的存储过程和函数简单写法

    什么是存储过程 简单的说,就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法: ps:存储过程跟触发器有点类似,都是一组SQL集,但是存储过程是主动调用的,且功能比触发器更加强大,触发器是某件事触发后自动调用: 有哪些特性 有输入输出参数,可以声明变量,有if/else, case,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能: 函数的普遍特性:模块化,封装,代码复用: 速度快,只有首次执行需经过编译和优化步骤,后续被调用可以直接执行,省

  • 详解MySql存储过程参数的入门使用

    存储过程 in参数 的使用 IN参数只用来向过程传递信息,为默认值. -- 存储过程中 in 参数的 使用 DELIMITER ;; CREATE PROCEDURE name_in(IN `time` VARCHAR(50)) BEGIN SELECT NOW() ,`time`; END ;; CALL name_in('现在时间'); -- in 是一个形参 你可以 定义 数据类型 调用存储过程的时候 可以给 实参 DELIMITER ;; CREATE PROCEDURE name_in

  • MySQL自定义函数和存储过程示例详解

    前言 本文主要给大家介绍的是关于MySQL自定义函数和存储过程的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧 1.前置条件 MySQL数据库中存在表user_info,其结构和数据如下: mysql> desc user_info; +-----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--

  • 细谈Mysql的存储过程和存储函数

    1 存储过程 1.1 什么是存储过程 存储过程是一组为了完成某项特定功能的sql语句集,其实质上就是一段存储在数据库中的代码,他可以由声明式的sql语句(如CREATE,UPDATE,SELECT等语句)和过程式sql语句(如IF...THEN...ELSE控制结构语句)组成.存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用. 1.2 存储过程的优缺点 优点: 1.可增强sql语言的功能和灵活性 存储过程可以用流程控制语言编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算.

  • Oracle存储过程和存储函数创建方法(详解)

    select * from emp; -----------------存储过程------------------------ --定义 create[or replace] procedure 存储过程名称(参数名 [in]/out 数据类型)    is/as    begin --逻辑表达式  end [存储过程名称]; --定义存储过程计算年薪,并答应输出 create or replace procedure proc_salyears(v_no in number)    is  

  • Oracle中 关于数据库存储过程和存储函数的使用

    存储过程和存储函数指存储在数据库中供所有用户程序调用的子程序叫存储过程.存储函数.存储过程没有返回值.存储函数有返回值 创建存储过程      用CREATE PROCEDURE命令建立存储过程和存储函数. 语法:create [or replace] PROCEDURE过程名(参数列表) AS         PLSQL子程序体: 存储过程示例:为指定的职工在原工资的基础上长10%的工资 /*为指定的职工在原工资的基础上长10%的工资,并打印工资前和工资后的工资*/SQL> create or

  • MyBatis如何调用存储过程与存储函数

    目录 1.MyBatis调用存储过程 2.MyBatis调用存储函数 1.MyBatis调用存储过程 MyBatis支持使用存储过程的配置.当使用存储过程时,需要设置一个参数"mode",其值有IN(输入参数).OUT(输出参数)和INOUT(输入/输出参数). MyBatis定义存储过程如下: <!-- 存储过程 --> <select id="selectSomeThing" statementType="CALLABLE"

  • MySQL的存储函数与存储过程相关概念与具体实例详解

    目录 MySQL存储过程与存储函数的相关概念 存储过程 存储函数 存储函数与存储过程的对比 存储过程和函数的查看修改删除 MySQL存储过程与存储函数的相关概念 存储函数和存储过程的主要区别: 存储函数一定会有返回值的 存储过程不一定有返回值 存储过程和函数能后将复杂的SQL逻辑封装在一起,应用程序无需关注存储过程和函数内部复杂的SQL逻辑,而只需要简单地调用存储过程和函数即可 存储过程 一组预先编译的SQL语句的封装 执行过程:执行过程预先存储在MySQL服务器上,需要执行的时候,客户端只需要

  • oracle中存储函数与存储过程的区别介绍

    在oracle中,函数和存储过程是经常使用到的,他们的语法中有很多相似的地方,可是也有它们的不同之处,这段时间刚学完函数与存储过程,来给自己做一个总结: 一:存储过程:简单来说就是有名字的pl/sql块. 语法结构: create or replace 存储过程名(参数列表) is --定义变量 begin --pl/sql end; 案例: create or replace procedure add_(a int,b int) is c int; begin c:=a+b; dbms_ou

  • Oracle存储过程和自定义函数详解

    概述 PL/SQL中的过程和函数(通常称为子程序)是PL/SQL块的一种特殊的类型,这种类型的子程序可以以编译的形式存放在数据库中,并为后续的程序块调用. 相同点: 完成特定功能的程序 不同点:是否用return语句返回值. 举个例子: create or replace procedure PrintStudents(p_staffName in xgj_test.username%type) as cursor c_testData is select t.sal, t.comm from

  • MySQL系列之五 视图、存储函数、存储过程、触发器

    目录 一.视图 1.视图的创建 2.查看视图定义 3.删除视图 二.存储函数 1.系统函数 2.自定义函数(user-defined function:UDF) 三.存储过程 四.触发器 总结 一.视图 视图:VIEW,虚表,保存有实表的查询结果,实际数据不保存在磁盘 物化视图:实际数据在磁盘中有保存,加快访问,MySQL不支持物化视图 基表:视图依赖的表 视图中的数据事实上存储于"基表"中,因此,其修改操作也会针对基表实现.其修改操作受基表限制. 注意:修改视图时是修改的原表 1.视

  • MySQL的存储函数与存储过程的区别解析

    MySQL存储函数(自定义函数),函数一般用于计算和返回一个值,可以将经常需要使用的计算或功能写成一个函数. 存储函数和存储过程一样,都是在数据库中定义一些 SQL 语句的集合. 存储函数与存储过程的区别 1.存储函数有且只有一个返回值,而存储过程可以有多个返回值,也可以没有返回值. 2.存储函数只能有输入参数,而且不能带in, 而存储过程可以有多个in,out,inout参数. 3.存储过程中的语句功能更强大,存储过程可以实现很复杂的业务逻辑,而函数有很多限制,如不能在函数中使用insert,

随机推荐