mysql存储过程与函数

   存储过程

  存储过程是一段代码,由存储在一个数据库的目录中、声明式的和过程式的sql语句组成,可以从一个程序、触发器或者另一个存储过程调用它从而激活它。

  每个存储过程包含至少3部分:一个参数列表、一个存储过程、一个名字。

  一个数据库中的存储过程的名字必须是唯一的,就像表的名字一样。

  一个参数列表可以有0个、1个或多个参数,通过这些参数,过程就可以和外界联系。

  存储过程支持3中参数类型:

  1、输入参数IN:数据可以传递到存储过程;

  2、输出参数OUT:数据可以由存储过程传到外界;

  3、输入输出参数INOUT:既可以充当输入参数,也可以充当输出参数。

  就像c语言函数一样,即使没有参数,过程名后面还是需要跟一对括号。

  存储过程以begin开始end结束,且之间还可以嵌套begin-end块。

  局部变量:

  declare 变量列表 变量类型 [default 默认值]

  存储过程不仅可以使用局部变量,还可以使用全局变量。

  默认值不仅限于直接量,还可以是符合表达式,也可以是标量子查询。

?

1
2
3
4
5
6
7
8

mysql> delimiter //
mysql> create procedure test (in a integer)
    -> begin
    -> declare b integer default
    -> (select count(*) from student );
    -> end
    -> //
Query OK, 0 rows affected (0.42 sec)

  set语句

  set用于给一个变量赋值。如:

?

1
2
3

set a = 1;
set a := 1;
set a = 1,b := a;

  leave语句

  离开一个块(循环块或者语句块),类似于break;

  如下,进入begin后立即离开。

?

1
2
3
4

mysql> create procedure test (in a integer)
    -> block : begin
    -> leave block;
    -> end//

  iterate语句

  进入一个循环。

  call语句

  调用存储过程。

  if-esle语句

  格式:

  if 条件 then 语句 ;

  elseif 条件 then 语句;

  esle 语句;

  end if

?

1
2
3
4
5
6
7
8
9

mysql> create procedure test (in a integer)
    -> begin
    -> declare b integer;
    -> if a < 60 then set b = -1;
    -> elseif a >60 then set b = 1;
    -> else set b = 0;
    -> end if;
    -> end
    -> //

  case语句

  格式:

  case

  when 条件 then 语句;

  when 条件 then 语句;

  else 语句;

  end case;

  while 语句

  格式:

  while 条件 do

  语句;

  end while;

?

1
2
3
4
5
6
7

mysql> create procedure test (in a integer)
    -> begin
    -> declare b integer default 1;
    -> while b < a do
    -> set b = b + 1;
    -> end while;
    -> end//

  repeat语句

  格式:

  repeat

  语句;

  until 条件 end repeat;

?

1
2
3
4
5
6
7

mysql> create procedure test (in a integer)
    -> begin
    -> declare b integer default 1;
    -> repeat
    -> set b = b + 1;
    -> until b > a end repeat;
    -> end//

  loop语句

  格式:

  loop

  if或case条件 leave loop;

  语句;

  end loop;

?

1
2
3
4
5
6
7
8
9

mysql> create procedure test (in a integer)
    -> begin
    -> declare b integer default 1;
    -> loop_block: loop
    -> if b > a then leave loop_block;
    -> end if;
    -> set b = b + 1;
    -> end loop;
    -> end//

  select into 语句

  用于将select的查询结果赋值给过程内的变量。

?

1
2
3
4

mysql> create procedure test (out b integer)
    -> begin
    -> select count(*) into b from student;
    -> end//

  现在student内有4条数据,调用test如下:

?

1
2
3
4
5
6
7
8

mysql> set @b = 0//
mysql> call test(@b)//
mysql> select @b//
+------+
| @b   |
+------+
|    4 |
+------+

  如果select语句查询的结果包含有多行,直接使用into赋值时不可行的。比如:

?

1
2
3
4

mysql> create procedure test (out b integer)
    -> begin
    -> select stu_id into b from student;
    -> end//

  虽然语法正确,但是在调用时报错:

?

1
2

mysql> call test(@b)//
ERROR 1172 (42000): Result consisted of more than one row

  怎么办?

  使用游标访问多行数据

  使用游标涉及到4个语句:

  declare cursor(声明游标)、open cursor、fetch cursor(获取一行数据)、close cursor。

  格式:

  declare 游标名字 cursor for 表查询语句

  如下:统计student表有多少行数据。

?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> create procedure test (out a integer)
    -> begin
    -> declare found boolean default true;--found 用于 判断是否到最后一行
    -> declare b integer;
    -> declare num cursor for select stu_id from student; --定义游标
    -> declare continue handler for not found set found = false;
    -> set a = 0;
    -> open num;--打开游标
    -> fetch num into b;--读取一行
    -> while found do
    -> set a = a+1;
    -> fetch num into b;
    -> end while;
    -> close num;--关闭游标
    -> end//

  declare handler语句

  存储过程在执行时可能会出现错误,declare handler语句涌来解决当出现错误时应该怎么做。

  格式:

  declare handler for <条件> 处理办法

  包括:continue、exit、undo

  <条件>包括:、sqlwarning、not found、sqlexception

  所以上面的“declare continue handler for not found set found = false;”就是当游标到达行尾是继续执行过程并且set found = false。

  drop 语句

  删除存储过程;

  drop procedure [if exists] 过程名

  存储函数

  存储函数与存储过程很相似:都是由sql语句和过程式语句所组成的代码片段,可以从应用程序和sql语句调用。

  区别:

  1。存储函数可以拥有输入参数,但是不能拥有输出参数。存储函数本身就是输出参数。

  2.存储函数的调用和调用熟悉的表两函数一样,不能使用一个call语句调用存储函数。

  3.存储函数必须包含一个return语句。

  格式:

  create function 函数名(<参数列表>) return 返回类型

  begin

  函数体;

  end

?

1
2
3
4
5
6
7
8
9
10
11
12

mysql> create function dd(ss char(20))
    -> returns date
    -> begin 
    -> return (date(ss));
    -> end//
mysql> select dd('2012-12-12 12:12:12')//
+---------------------------+
| dd('2012-12-12 12:12:12') |
+---------------------------+
| 2012-12-12                |
+---------------------------+
1 row in set (0.00 sec)

?

1
2
3
4
5
6
7

mysql> select * from student//
+------+---------+------+-------+
| name | address | sid  | score |
+------+---------+------+-------+
| zh   | beijing |    1 |    70 |
+------+---------+------+-------+
1 row in set (0.00 sec)

?

1
2
3
4
5

mysql> create function dd(id int)
    -> returns int
    -> begin  return (select score from student where id=id);
    -> end//
Query OK, 0 rows affected (0.00 sec)

?

1
2
3
4
5
6
7

mysql> select dd(1)//
+-------+
| dd(1) |
+-------+
|    70 |
+-------+
1 row in set (0.00 sec)

时间: 2024-10-06 03:27:07

mysql存储过程与函数的相关文章

MySql存储过程与函数详解

存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句.存储过程和函数可以避免开发人员重复的编写相同的SQL语句.而且,存储过程和函数是在MySQL服务器中存储和执行的,可以减少客户端和服务器端的数据传输.一.存储过程1.1.基本语法 CREATE PROCEDURE sp_name ([proc_parameter[,...]])    [characteristic ...] routine_body   Sp_name:存储过程的名称

理解MySQL存储过程和函数_Mysql

一.概述  一提到存储过程可能就会引出另一个话题就是存储过程的优缺点,这里也不做讨论,一般别人问我我就这样回答你觉得它好你就用它.因为mysql中存储过程和函数的语法非常接近所以就放在一起,主要区别就是函数必须有返回值(return),并且函数的参数只有IN类型而存储过程有IN.OUT.INOUT这三种类型. 二.语法   创建存储过程和函数语法 CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] rout

php调用mysql存储过程和函数的方法

mysql|存储过程|函数 存储过程和函数是MySql5.0刚刚引入的.关于这方面的操作在PHP里面没有直接的支持.但是由于Mysql PHP API的设计,使得我们可以在以前的PHP版本中的mysql php api中支持存储过程和函数的调用. 在php中调用存储过程和函数.     1.调用存储过程的方法.       a.如果存储过程有 IN/INOUT参数,声明一个变量,输入参数给存储过程,该变量是一对,       一个php变量(也可以不必,只是没有php变量时,没有办法进行动态输入

MySQL存储过程、函数、触发器和视图的权限检查

当存储过程.函数.触发器和视图创建后,不单单创建者要执行,其它用户也可能需要执行,换句话说,执行者有可能不是创建者本身,那么在执行存储过程时,MySQL是如何做权限检查的? 在默认情况下,MySQL将检查创建者的权限.假设用户A创建了存储过程p()访问表T,并把execute的权限赋给了B,即使用户B没有访问表T的权限,也能够通过执行存储过程p()访问表T. 下面看一个例子: 首先,我们创建一个表test.t和两个用户a,b,并把权限赋予用户a root@(none) 05:39:45>crea

MySQL 存储过程和函数(转)

概述 一提到存储过程可能就会引出另一个话题就是存储过程的优缺点,这里也不做讨论,一般别人问我我就这样回答你觉得它好你就用它.因为mysql中存储过程和函数的语法非常接近所以就放在一起,主要区别就是函数必须有返回值(return),并且函数的参数只有IN类型而存储过程有IN.OUT.INOUT这三种类型. 语法 创建存储过程和函数语法 CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_bod

MySQL存储过程和函数的操作(十二)_Mysql

数据库对象表时存储和操作数据的逻辑结构,而数据库对象存储过程和函数,则是用来实现将一组关于表操作的sql语句当作一个整体来执行.在数据库系统中,当调用存储过程和函数时,则会执行这些对象中所设置的sql语句组,从而实现相应功能. 1. 为什么使用存储过程和函数的操作     有时针对表的一个完整操作往往不是单条sql语句就可以实现的,而是需要一组sql语句来实现.在具体应用当中,一个完整的操作会包含多条sql语句,在执行过程中需要根据前面sql语句的执行结果有选择地执行后面sql语句.     存

MySQL 存储过程的函数与基本用法

基本用法 MySQL 存储过程是从 MySQL 5.0 开始逐渐增加新的功能.存储过程在实际应用中也是优点大于缺点.不过最主要的还是执行效率和SQL 代码封装.特别是 SQL 代码封装功能,如果没有存储过程. 在外部程序访问数据库时(例如 PHP),要组织很多 SQL 语句. 特别是业务逻辑复杂的时候,一大堆的 SQL 和条件夹杂在 PHP 代码中,让人不寒而栗.现在有了 MySQL 存储过程,业务逻辑可以封装存储过程中,这样不仅容易维护,而且执行效率也高. 一.MySQL 创建存储过程 "pr

mysql 存储过程和函数

1.什么是存储过程与函数 mysql教程存储过程和函数是事先经过,编辑并存储在数据库教程中的一段sql语的集合,调用存储过程和函数可以简化应用开发人员的工作,减少数据在数据库和应用中的传输,从而提高mysql数据库的处理能力. 2.存储过程相关函数与存储过程的创建,修改存储过程和函数.   创建存储过程与创建函数   create procedure sp_name()    create function sp_name() 调用存储过程的实例  call sp_name(); 在mysql存

Mysql 存储过程、函数、触发器和视图的权限检查

当存储过程.函数.触发器和视图创建后,不单单创建者要执行,其它用户也可能需要执行,换句话说,执行者有可能不是创建者本身,那么在执行存储过程时,MySQL是如何做权限检查的? 在默认情况下,MySQL将检查创建者的权限.假设用户A创建了存储过程p()访问表T,并把execute的权限赋给了B,即使用户B没有访问表T的权限,也能够通过执行存储过程p()访问表T. 下面看一个例子: 首先,我们创建一个表test.t和两个用户a,b,并把权限赋予用户a   [sql] view plaincopypri

Mysql存储过程和函数区别介绍

存储过程是用户定义的一系列sql语句的集合,涉及特定表或其它对象的任务,用户可以调用存储过程,而函数通常是数据库已定义的方法,它接收参数并返回某种类型的值并且不涉及特定用户表. 存储过程和函数存在以下几个区别: 1)一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强.存储过程,功能强大,可以执行包括修改表等一系列数据库操作:用户定义函数不能用于执行一组修改全局数据库状态的操作. 2)对于存储过程来说可以返回参数,如记录集,而函数只能返回值或者表对象.函数只能返回一个变量:而存