MariaDB/MySQL存储过程和函数分分快三全天计划网站

作者:分分快三全天计划网站

对于函数来说,除了存储函数,还有用户自定义函数(UDF,user defined function),在MySQL/MariaDB中,用户自定义函数是存储函数的扩展,它像一个小程序一样,需要编译、安装后才能运行。这和SQL Server对UDF的定义不同,倒是类似于SQL Server的CLR程序。

3.实例演示

(1)简单存储过程

mysql> delimiter //
mysql> create procedure simpleproc(in param1 int,out param2 int)
    -> begin
    -> select count(*) into param2 from students where sid > param1;
    -> end//
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> call simpleproc(1,@a);
Query OK, 1 row affected (0.00 sec)

mysql> select @a;
 ------ 
| @a   |
 ------ 
|    7 |
 ------ 
1 row in set (0.00 sec)

说明:

  • delimiter命令是改变语句的结束符,MySQL默认结束符为;号,由于存储过程和函数中的;号并不代表结束,所以要替换另外的结束符;

(2)简单的函数

mysql> create function hello(s char(20))
    -> returns char(50)
    -> return concat('Hello',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> select hello('world');
 ---------------- 
| hello('world') |
 ---------------- 
| Helloworld!    |
 ---------------- 
1 row in set (0.00 sec)

(3)复杂的示例

mysql> delimiter //
mysql> create function simplefunc(param1 int)
    -> returns int
    -> begin
    -> update students set gender=1 where sid=param1;
    -> select count(*) into @a from students where sid > param1;
    -> return @a;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select * from students where sid=1;
 ----- -------- -------- --------- 
| sid | sname  | gender | dept_id |
 ----- -------- -------- --------- 
|   1 | Andrew | 0      |       1 |
 ----- -------- -------- --------- 
1 row in set (0.00 sec)

mysql> select simplefunc(1);
 --------------- 
| simplefunc(1) |
 --------------- 
|             7 |
 --------------- 
1 row in set (0.03 sec)

mysql> select * from students where sid=1;
 ----- -------- -------- --------- 
| sid | sname  | gender | dept_id |
 ----- -------- -------- --------- 
|   1 | Andrew | 1      |       1 |
 ----- -------- -------- --------- 
1 row in set (0.01 sec)

说明:

  • MySQL存储过程和函数中也可以包含类似create和drop等DDL语句;
  • rontine_body子句可以包含一个简单的SQL语句,也可以包含多个SQL语句,通过begin...end将多个SQL语句包含在一起;

存储函数必须遵守这样一条限制:不允许对调用本函数的语句正在读或写的数据表进行修改。存储过程通常没有这个限制,但如果它们是从存储函数里被调用,就需要遵守这条限制。

3.查看存储过程、函数信息

查看存储过程和函数的信息。

show {procedure|function} status like 'pattern'; -- 查看routine的基本信息
show create {procedure|function} proc_name;      -- 查看routine的创建语句
show {procedure|function} code routine_name;     -- 查看MariaDB内部是如何操作routine中各语句的

从information_schema.routines中查看存储过程和函数的信息,这个比show status更详细一点。

select * from information_schema.routines where routine_name='xxx'G

这里说明下show code功能,这个功能必须要在支持debug的MariaDB上才能使用,可以在编译时使用选项"--with-debug"启用该功能。

例如:

DELIMITER $$
CREATE PROCEDURE p1 ()
  BEGIN
    DECLARE fanta INT DEFAULT 55;
    DROP TABLE t2;
    LOOP
      INSERT INTO t3 VALUES (fanta);
      END LOOP;
  END$$
delimiter ;
SHOW PROCEDURE CODE p1;
 ----- ---------------------------------------- 
| Pos | Instruction                            |
 ----- ---------------------------------------- 
|   0 | set fanta@0 55                         |
|   1 | stmt 9 "DROP TABLE t2"                 |
|   2 | stmt 5 "INSERT INTO t3 VALUES (fanta)" |
|   3 | jump 2                                 |
 ----- ---------------------------------------- 

其中第一列是从0开始的序列值,表示存储过程中所执行的动作先后顺序。第二列是mariadb要执行的动作,这些动作是基于存储过程中的源语句进行设置的。

5.删除语句

DROP {procedure | function} {IF EXISTS} sp_name;

if exists用来避免在删除一个本身不存在的存储过程或函数时,MySQL返回错误;

mysql> drop procedure oldboy;
ERROR 1305 (42000): PROCEDURE course.oldboy does not exist
mysql> drop procedure if exists oldboy;
Query OK, 0 rows affected, 1 warning (0.00 sec)

存储过程的参数分为3种类型。对于IN参数,调用者把一个值传递给过程,过程可以对这个值进行修改,但任何修改在过程返回后对调用者是不可见的。OUT参数刚好相反,过程把一个值赋值给OUT参数,这个值在过程返回后可以由调用者访问。INOUT参数允许调用者向过程传递一个值,然后再取回一个值。
要想明确地为参数指定类型,在参数表里把IN, OUT或INOUT写在参数名字前面即可。如果没有为参数指定类型,其默认类型将是IN。

2.修改和删除存储过程、函数

可以使用alter语句修改存储过程、函数,但alter语句只能修改characteristic部分,不支持对body部分和参数部分修改。若要修改它们,只能先删除再创建。

-- 修改存储过程和函数
ALTER {PROCEDURE | FUNCTION} proc_name [characteristic ...]
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

删除存储过程和函数的方式很简单,直接使用drop命令,但要求有alter权限。

drop {procedure|function} [if exists] sp_name

6.查看存储过程及函数

mysql> show create procedure simpleproc;
 ------------ ------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ---------------------- -------------------- 
| Procedure  | sql_mode                                                                                                                                  | Create Procedure                                                                                                                                               | character_set_client | collation_connection | Database Collation |
 ------------ ------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ---------------------- -------------------- 
| simpleproc | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleproc`(in param1 int,out param2 int)
begin
select count(*) into param2 from students where sid > param1;
end | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
 ------------ ------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ---------------------- -------------------- 

mysql> show create function simplefunc;
 ------------ ------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ---------------------- -------------------- 
| Function   | sql_mode                                                                                                                                  | Create Function                                                                                                                                                                                                   | character_set_client | collation_connection | Database Collation |
 ------------ ------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ---------------------- -------------------- 
| simplefunc | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `simplefunc`(param1 int) RETURNS int(11)
begin
update students set gender=1 where sid=param1;
select count(*) into @a from students where sid > param1;
return @a;
end | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
 ------------ ------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ---------------------- -------------------- 
1 row in set (0.01 sec)
    username  hostname  
      ()     (   INSTR(,’@’)  SUBSTRING_INDEX(, ,      
           CONCAT(,, ) ;

在MySQL/MariaDB中,存储过程(stored procedure)、存储函数(stored function)、触发器(trigger)、事件(event)统称为存储程序(stored programs)。此外,存储过程和存储函数还合称为stored routines。

1.语法

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

参数详解:
(1)DEFINER
表示创建存储过程及函数的用户,默认为当前用户;
(2)func_parameter
表示存储过程的参数。在Create Procedure 语句中,可以声明一个或多个参数。当调用该存储过程时,用户必须给出所有的参数值,除非定义了参数的缺省值。若参数的形式以 @parameter=value 出现,则参数的次序可以不同,否则用户给出的参数值必须与参数列表中参数的顺序保持一致。若某一参数以@parameter=value 形式给出,那么其它参数也必须以该形式给出。一个存储过程至多有1024 个参数。
(3)characteristic

  • LANGUAGE SQL:表示此存储过程和函数的创建语言;
  • [NOT] DETERMINISTIC:表明输入相同的参数会返回相同的结果,反之表示相同的参数不会是相同的结果,默认是not deterministic;
  • -- CONTAINS SQL :子程序不包含读或写数据的语句;
  • -- NO SQL : 子程序不包含SQL语句;
  • -- READS SQL DATA :子程序包含读数据的语句;
  • -- MODIFIES SQL DATA : 子程序包含写数据的语句;
  • -- 如果这些特征没有明确给定,默认的是CONTAINS SQL;

(4)SQL SECURITY { DEFINER | INVOKER }
子程序该用创建子程序者的许可来执行,还是使用调用者的许可来执行。默认值是DEFINER。
(5)COMMENT
是一个MySQL的扩展,它可以被用来描述存储程序。这个信息被SHOW CREATE PROCEDURE和 SHOW CREATE FUNCTION语句来显示。

在调用这个过程时,请把各个参数替换为相应的用户定义变量。这个过程将把计数值放到这些参数里,在它返回之后,那些变量将包含计数值:

虽然存储过程和存储函数在功能实现上有些区别,但在使用方法上几乎一致。

2.存储过程与函数特性

(1)存储过程与函数的区别

  • 函数调用有返回值
  • 存储过程调用用call语句,函数调用直接饮用函数名 参数

(2)Definer和sql security

  • Definder是MySQL的特殊访问控制手段,当数据库当前没有这个用户权限时,执行存储过程可能会报错;
  • sql security的值决定了调用存储过程的方式,取值:definer(默认)或invoker;
  • definer在执行时先验证definer对应的用户,如:cdq@127.0.0.1是否存在,以及是否具有执行存储过程的权限,若没有则报错;
  • invoker在执行存储过程时判断invoker,即调用该存储过程的用户是否有相应权限,若没有则报错

(3)IN,OUT,INOUT
只适用于存储过程,对函数而言所有参数默认都是输入参数

  • IN用于把数值传入到存储过程中
  • OUT用于输出参数将数值传递给调用者
  • INOUT输入输出参数把数据传入到存储过程,在存储过程中修改后再传递给调用者

IN、OUT和INOUT关键字不适用于存储函数、触发器或事件。对于存储函数,所有的参数都像IN参数。触发器和事件则根本没有任何参数。下一部分介绍:触发器和事件。

1.1 存储过程的IN、OUT和INOUT

它们表示的是参数的类型。

IN参数类型表示将调用者给定的值传递给存储过程。存储过程可能会修改这个值,但是对于调用者来说,在存储过程返回结果时,所做的修改是不可见的。

OUT参数类型表示将存储过程的返回值传递给调用者。其初始值为NULL,当存储过程返回时,这个值对调用者来说是可见的。

INOUT参数类型表示由调用者传递值给存储过程,存储过程可能会修改这个值,当存储过程返回的时候,所做的修改对调用者来说是可见的。

对于每个OUT或INOUT类型的参数,当调用者在CALL语句中调用存储过程时,所传递的每个用户变量都可以在存储过程返回的时候获取其值。

默认每个参数都是IN。要指定其他类型的参数,可以在参数名前面使用关键字OUT或INOUT。

(1).IN参数类型。

IN参数类型是指调用者将某个值传递给存储过程,存储过程借用这个值来完成某些操作。

以下是IN类型参数的示例。

create or replace table t1(a int);
insert into t1 values(1),(2),(3),(4),(5),(6);
delimiter $$
create or replace procedure proc1(min int,max int)
begin
    select * from t1 where t1.a >= min and t1.a <= max;
end$$
delimiter ;
call proc1(3,5);
 ------ 
| a    |
 ------ 
|    3 |
|    4 |
|    5 |
 ------ 

(2).OUT参数类型。

OUT参数类型是指存储过程将某个值通过该参数返回给调用者。因此调用者必须传递一个用户变量给存储过程,用来记录存储过程OUT参数的值。这个用户变量在传递给存储过程之前,可以是一个已赋值的变量,但在传递给存储过程时,将自动初始化为NULL值。

以下是OUT类型参数的示例。在此示例中,传入@a给proc(),最后将count(*)赋值给out参数cnt,cnt代表的就是传入参数@a。

delimiter $$
create or replace procedure proc2(out cnt int)
begin
    select count(*) into cnt from A;
end$$
delimiter ;
call proc1(@a);
select @a as a;
     a  
--------
       6

(3).INOUT参数类型。

INOUT参数类型指的是调用者和存储过程之间传递的内容可以互相赋值。INOUT有两个过程,一个是IN的过程,这个过程是将调用者指定的值传递给存储过程,另一个是OUT的过程,这个过程是存储过程将某个返回值返回给调用者。因此,调用者传递INOUT参数时,也必须传递一个用户变量。但与OUT不同的是,INOUT的用户变量有初始值,这个初始值会直接应用在存储过程中。而OUT的用户变量虽然也有初始值,但这个初始值会自动转换为NULL。

以下是INOUT参数类型的示例。

/* procedure INOUT */
create or replace table t1(a int);
insert into t1 values(1),(2),(3),(4),(5),(6);
delimiter $$
create or replace procedure proc3(INOUT cnt int,min int,max int)
begin
    if cnt <5 then   /* 直接用cnt这个INOUT参数来判断大小,因为它继承了传递时的值 */
        select count(*) into cnt from t1 where t1.a >=min and t1.a <=max;
    else
        select count(*) into cnt from t1;
    end if;
end$$
delimiter ;
set @a=3;
set @b=6;
call proc1(@a,3,5);
call proc1(@b,3,5);
select @a,@b;
 ------ ------ 
| @a   | @b   |
 ------ ------ 
|    3 |    6 |
 ------ ------ 

4.测试definer

[root@localhost ~]# mysql -uabc2 -p
Enter password: 

mysql> show databases;
 -------------------- 
| Database           |
 -------------------- 
| information_schema |
| course             |
 -------------------- 
2 rows in set (0.01 sec)

mysql> use course;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> delimiter //
mysql> create procedure simpleproc2(IN param1 int,out param2 int)
    -> begin
    -> select count(*) into param2 from students where sid > param1;
    -> end//
Query OK, 0 rows affected (0.00 sec)

[root@localhost ~]# mysql -p
Enter password: 

mysql> use course;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> call simpleproc2(1,@a);
Query OK, 1 row affected (0.00 sec)

mysql> drop user 'abc2'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> call simpleproc2(1,@a);
ERROR 1449 (HY000): The user specified as a definer ('abc2'@'localhost') does not exist

查看存储过程及函数的相关数据库信息(在information_schema库)
mysql> select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_BODY,ROUTINE_DEFINITION,DEFINER from ROUTINES where ROUTINE_SCHEMA = 'course';
 ---------------- -------------- -------------- -------------- ------------------------------------------------------------------------------------------------------------------------------- ---------------- 
| ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | ROUTINE_BODY | ROUTINE_DEFINITION                                                                                                            | DEFINER        |
 ---------------- -------------- -------------- -------------- ------------------------------------------------------------------------------------------------------------------------------- ---------------- 
| course         | hello        | FUNCTION     | SQL          | return concat('Hello',s,'!')                                                                                                  | root@localhost |
| course         | simplefunc   | FUNCTION     | SQL          | begin
update students set gender=1 where sid=param1;
select count(*) into @a from students where sid > param1;
return @a;
end | root@localhost |
| course         | simpleproc   | PROCEDURE    | SQL          | begin
select count(*) into param2 from students where sid > param1;
end                                                       | root@localhost |
| course         | simpleproc2  | PROCEDURE    | SQL          | begin
select count(*) into param2 from students where sid > param1;
end                                                       | abc2@localhost |
 ---------------- -------------- -------------- -------------- ------------------------------------------------------------------------------------------------------------------------------- ---------------- 

mysql> alter procedure simpleproc2 sql security invoker;
Query OK, 0 rows affected (0.00 sec)

mysql> call simpleproc2(1,@a);
Query OK, 1 row affected (0.00 sec)

说明:
相关属性短语只有咨询含义,并不是强制性约束

  • contains sql表明此存储过程或函数不包含读或者写数据的语句,这是默认属性;
  • no sql表明此存储过程或函数不包含SQL语句
  • reads sql data表示此存储过程包含诸如select的查询数据的语句,但不包含插入或删除数据的语句
  • modifies sql data表示存储过程包含插入或删除数据的语句

存储程序。泛指各种类型的存储对象(存储函数、存储过程、触发器、事件)。存储例程(stored routine ),特指存储函数和存储过程。这两种对象的定义语法很相似,所以很自然地把它们放在一起讨论。在开始讨论各种类型的存储程序之前,我们首先学习一下:复合语句。

1.创建存储过程、函数

在MySQL/MariaDB中创建存储过程、函数的语法如下:其中OR REPLACE是MariaDB 10.1.3版本中才有的,MySQL中不支持OR REPLACE

-- 创建存储过程和函数
CREATE [OR REPLACE] PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
CREATE [OR REPLACE] FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type
func_parameter:
    param_name type

type:
    Any valid MySQL data type
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
routine_body:
    Valid SQL routine statement

在MySQL/MariaDB的存储过程和函数中,允许存在DML和DDL语句。且存储过程中还允许(存储函数不允许)使用SQL事务类型的语句,例如提交commit。当然,肯定也支持嵌套其他存储过程或函数。

存储过程的参数有三种类型:IN、OUT和INOUT,下文将详细分析这三种类型参数。

在创建和修改的语法中,需要说明的就是characteristic部分,这部分基本没什么用,但可以了解下它们是干什么的。

  • language sql:表示后面的body部分使用标准SQL语句编写,这是默认的。该选项的作用是为了以后支持非SQL语句书写存储过程和函数的,例如SQL Server中就有使用.NET写的CLR存储过程、函数、触发器等。但目前,这个还没有任何意义。
  • [not] deterministic:deterministic的意思是确定的。这里的意思是函数返回值是明确的,而非具有随机性的值。例如,使用了随机数,使用了now()这样的函数等。not deterministic表示返回值是不确定的,这是系统默认值。当优化器知道函数返回值是确定值时,将选择一个更优化的执行计划。如果返回值是确定的,却定义为not deterministic,则性能会降低,如果返回值是不确定的,却定义为deterministic,则报错。
  • {contains sql|no sql|reads sql data|modifies sql data}:这些是提供给服务器的信息类子句,看上去是约束性语句,但MariaDB不会根据它们做任何检查。也就是说,这些提示符只是给人看的,没有任何作用。contains sql表示body不包含读和写数据的语句,例如SET和DO;no sql表示body不包含SQL语句;reads sql data表示body包含读数据的语句,但不包含写数据的语句,如SELECT。modifies sql data表示body包含写数据的语句,如DELETE/UPDATE;如果没有指定这些特征值,则使用默认值contains sql。
  • sql security:指明执行该程序时以谁的身份执行。definer表示执行时获取创建者的权限,invoker表示以调用者的身份执行,若调用该程序的用户对程序中涉及的对象没有对应的权限则会执行失败(如lisa用户有执行存储过程的权限,但是没有读取存储过程中涉及的表a的权限,那么执行存储过程时因为读表失败而导致执行被拒绝)。默认是definer。
  • comment:程序的注释信息。

当要调用存储过程或函数时,可以使用call命令调用存储过程,如call sp_name();;而函数则可以当作表达式一样进行调用,例如使用select命令select func();,当作表达式赋值给变量set @a=func()

需要注意的是,在MySQL/MariaDB中,因为语句的结束符是分号";",在存储过程或函数创建过程中直接使用分号会导致语句报错。所以当存储过程或存储函数中包含需要使用分号的语句时,应在创建存储过程或函数之前使用delimiter命令来暂时改变语句结束符,在创建完毕之后再改回结束符为分号";"。

例如:

delimiter $$
create or replace procedure proc()       -- procedure name
begin                                    -- procedure body
    select * from A;
end $$
delimiter ;
--调用存储过程
call proc();

在上面的语句中,首先定义了结束符为$$;然后判断了存储过程proc()是否存在,存在则删除,之后才开始创建存储过程。这个存储过程很简单,只是一个select语句。创建语句结束之后,再次使用delimiter命令将结束符改回了分号";"。最后使用call命令进行了存储过程的调用。

以下是一个函数的创建和使用示例:

delimiter $$
create or replace function func1()
    returns int
    return (select count(*) from t1);$$
delimiter ;
set @c=func1();
select func1(),@c;
 --------- ------ 
| func1() | @c   |
 --------- ------ 
|       6 |    6 |
 --------- ------ 
 count_born_in_year();

1.存储程序对象的可执行部分可以用复合语句来编写,复合语句对SQL语法进行了扩展,可以包括代码块、循环和条件语句。
2.存储程序都被保存在服务器端,定义它们所需要的代码只需在它们被创建时通过网络传递一次,而不是每次执行都要传递一次。这大大减少了开销。
3.它们可以把复杂的计算封装为程序单元,而你可以简单地通过程序单元的名字来调用它们。你甚至可以把一组存储程序打包为一个“函数库”供其他应用程序调用。
4.它们提供了一种错误处理机制。
5.它们可以提高数据库的安全性。你可以通过选择存储程序执行时所需的权限下来对敏感数据的访问情况进行限制和调控。

MySQL支持把几种对象存放在服务器端供以后使用。这几种对象有一些可以根据情况通过程序代码调用,有一些会在数据表被修改时自动执行,还有一些可以在预定时刻自动执行。它们包括以下几种:

4.存储过程的参数类型

如果服务器启用了二进制日志功能,存储函数还需要遵守一些额外的限制条件(不允许创建不确定或是会修改数据的存储函数)以保证二进制日志能够安全地完成备份和复制操作。这些限制条件如下:
1.如果log_bin_trust_function_creators系统变量没有被激活,你就必须具备SUPER权限才能创建存储函数。在此前提下,你创建的每一个函数都必须是确定的,并且不得修改数据。为了表明这一点,需要使用DETERMINISTIC、NO SQL或READS SQL DATA之一来定义存储函数。
2.如果log bin_trust_function_creators系统变量已被激活,则没有任何限制。只有当你可以相信MySQL服务器上的所有用户都不会去定义不安全的存储函数时,这种设置才是最适当的。

服务器端 供以后使用。这几种对象有一些可以根据情况通过程序代码调用,有一些会在数据表被修改时自动执行...

这里的原则是:只要在某个存储程序内部的语句里会用到分号,就应该在定义这个存储程序时临时改变mysql程序的分隔符。

  show_born_in_year(p_year 
     (birth)

在使用OUT或INOUT参数时,在调用过程时需要给出一个变量名。过程可以设置参数的值,相应的变量将在过程返回时获得那个值。如果想让某个存储过程返回多个结果值,OUT和INOUT参数类型将非常有用(存储函数只能返回一个值,不能胜任)。下面的过程演示了OUT参数的用法。它将分别统计出student数据表里的男生和女生人数并通过它的参数返回这两个计数值,让调用者可以访问它们:

存储函数和存储过程属于数据库。要想创建存储函数或存储过程,必须拥有那个数据库的CREATE ROUTINE权限。在默认的情况下,当你创建一个存储例程时,服务器将自动地把EXECUTE和ALTER ROUTINE权限授予你(如果你还没有获得这些权限),这样你才可以执行那个例程或删除它。当你删除那个例程时,服务器将自动撤销那些权限。如果你不想使用这种自动化的权限授予/撤销机制,把automatic_sp_privileges系统变量设置为0即可。

  update_expiration (p_id 
     member  expirationp_date  member_id

CALL count_students_by_sex(,  ,;

3.存储函数和存储过程的权限

1.存储函数(stored function)。返回一个计算结果,该结果可以用在表达式里。
2.存储过程(stored procedure)。,但可以用来完成一般的运算或是生成一个结果集并传递回客户。
3.触发器(trigger)。与数据表相关联,当那个数据表被工NSERT、DELETE或UPDATE语句修改时,触发器将自动执行。
4.事件(event)。根据时间表在预定时刻自动执行。

与存储函数不同,存储过程不能用在表达式里,它们只能通过CALL语句来调用。如下所示:

存储函数将向调用者返回一个计算结果,这个结果可以用在表达式里(就像COS()或HEX()这样的内建函数那样)。存储过程需要使用CALL语句来调用,是一个独立的操作,不能用在表达式里。使用存储过程的情况主要有两种:(1)只需通过运算来实现某种效果或动作而无需返回一个值,(2)运算会返回多个结果集(函数做不到这一点)。这只是些指导性建议,不是硬性规定。
比如说,如果你需要返回两个或更多的值,就不能使用函数。但你可以使用一个过程,因为过程支持的参数类型允许它们的值在过程执行期间被设置,而调用者可以在过程返回后去访问那些值。

定义一个存储过程时把mysql程序的默认分隔符临时改变为$,然后在恢复了mysql程序的默认分隔符之后执行了那个存储过程:

存储过程和存储函数很相似,但它不返回值。因此,它没有RETURNS子句或任何RETURN语句。下面这个简单的存储过程和count_born_in_year()函数很相似,它将显示一个结果集而不是把计算结果作为其返回值。

 TABLE_NAME  TABLE_SCHEMA’sampdb’   TABLE_NAME;

你无法让一个给定的函数返回多个值。你可以编写任意多个函数,然后在同一条语句里调用它们全体。另一个办法是使用一个存储过程并通过它的OUT参数“返回”多个值。存储过程负责计算那些值并把它们赋值给相应的参数,而那些参数可以在过程返回后由调用者访问。如果你定义了一个与某个MySQL内建函数同名的存储函数,在调用它时就必须用数据库的名字对该函数的名字进行限定以避免歧义。

这个函数有一条用来表明其返回值数据类型的RETURNS子句和一个用来计算那个值的函数体。函数体至少需要包含一条RETURN语句,用来向调用者返回一个值。把计算定义为函数的好处是可以方便地执行它而无须每次都写出所有的逻辑,你可以像使用内建函数那样来调用存储函数:

存储函数要用CREATE FUNCTION语句来创建,存储过程要用CREATE PROCEDURE语句来创建。下面的例子将创建一个函数,该函数有一个代表着年份的整数参数。(为了与数据表或数据列的名字有所区别,参数命名时将使用p_前缀)。

不过,存储程序并非只能包含一条简单的SQL语句。它们可以包含多条SQL语句,可以使用局部变量、条件语句、循环和嵌套语句块等多种语法构造。要使用这些构造编写存储程序,就需要用到复合语句。复合语句由BEGIN开头,END结束,在它们之间可以写出任意数量的语句,这些语句构成了一个语句块。下面的存储过程将显示一条欢迎消息,其中有你的用户名;如果你是一位匿名用户,用户名将是“earthing":

CALL show_born_in_year();

1.复合语句和语句分隔符

简单的存储程序只包含一条SQL语句,在编写时不需要特殊对待。下面的存储过程使用了一条SELECT语句来列出sampdb数据库里的数据表的名字:

与log_bin_trust_function_creators系统变量有关的限制条件同样适用于触发器的创建工作。

  count_students_by_sex(OUT p_male , OUT p_female 
     ()  student  sex   ()  student  sex  
     ,  
CALL show_times();

在使用复合语句时,必须考虑和解决这样一个问题:复合语句块里的语句必须以分号(;)彼此隔开,但因为分号同时也是mysql程序默认使用的语句分隔符,所以在使用mysql程序定义存储程序时会发生冲突。
解决这个问题的办法是使用delimiter命令把mysql程序的语句分隔符重定义为另一个字符或字符串,它必须是在存储例程的定义里没有出现过的。这样一来,mysql程序就不会把分号解释为语句终止符了,它将把整个对象定义作为一条语句传递给服务器。在定义完存储程序之后,可以把mysql程序的语句终止符重新定义为分号。

MySQL对存储函数和存储过程的支持始于5.0.0版本,对触发器和事件的支持分别始于5.0.2版本和5.1.6版本。存储程序有以下优点和能力:

前面的例子都是选取信息,但存储例程还可以用来修改数据表,如下例所示:

  count_born_in_year(p_year  
     ( ()  president  (birtb) 

本文由分分快三计划发布,转载请注明来源

关键词: 分分快三计划 mysql procedure function begin.end.de