mysql 存储过程和自定义函数

2019-08-27 鲁鲁槟 收藏

一、存储过程

1.1、定义

  • 存储过程是用户定义的一系列sql语句的集合,完成一系列的数据操作,从而节省网络传输所需要的时间。

1.2、优点

  • 增强SQL语句的功能和灵活性实现较快的执行速度减少网络流量

1.3、语法

①、创建

CREATE
[ DEFINER={ user I CURRENT_USER}]
PROCEDURE sp_name([ proc_parameterL..….J])
[ characteristic..1 routine_body 

proc_parameter:
[ INIOUTIINOUT] param_name type

②、参数

  • IN,表赤该参数的值必须在调用存储过程时指定

  • OUT,表示该参数的值可以被存储过程改变,并且可以返回

  • INOUT,表示该参数的调用时指定,并且可以被改变和返回

③、特性

  • COMMENT:注释

  • CONTAINS SQL:包含SQL语句,但不包含读或写数据的语句

  • NO SQL:不包含SQL语句

  • READS SQL DATA:包含读数据的语句

  • MODIFIES SQL DATA:包含写数据的语句

  • SQL SECURITY{DEFINER |INVOKER}指明谁有权限来执行

④、过程体

  • 过程体由合法的SQL语句构成;

  • 过程体可以是任意SQL语句;

  • 过程体如果为复合结构则使用BEGIN...END语句;

  • 复合结构可以包含声明,循环,控制结构;

⑤、创建不带参数的存储过程

CREATE _PROCEDURE Sp1() SELECT UERSION();

调用存储过程

CALL sp_name([ parameterL...J])
CALL sp_name[0]
CALL sp1;
CALL sp1();

⑥、创建带有 IN 类型参数的存储过程

DELIMITER//
CREATE PROCEDURE remoueUserById(IN id INT UNSIGNED)
BEGIN 
DELETE FROM test WHERE id=id;
END
//
DELIMITER;
CALL remoueUserById(3);

注:参数的名字不能和记录表中的名字相同

数据库执行 id=id 认为是两个字段。

命令中有一个 id=id,数据库区分不开你这些 ID 中的具体含义,它默认为该数据表的 id 字段。所以数据表中记录全部被删除

create procedure remoueUserById(IN p_id INT UNSIGNED)
BEGIN 
DELETE FROM test WHERE id=p_id; 
END
//
remoueUserById(24);

⑦、创建带有 IN 和 OUT 类型参数的存储过程

CREATE PROCEDURE remoueUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT use rname INT UNSIGNED)
DELETE FROM test WHERE id=p_id;
SELECT count(id)FROM test INTO username;
;
//
CALL remoueUserAndReturnUserNums(25,@nums);
select @nums;

注:以 @ 符号开头的字符序列是指用户变量,也就是用户在 MySQL 客户端定义的变量,一般用在 begin end 块中,对存储过程传入的参数进行声明、定义等创建带有多个 OUT 类型参数的存储过程。

⑧、创建带有多个 OUT 类型参数的存储过程

CREATE PROCEDURE remoueUserByAgeAndReturnInfos(IN p_age SHALLINT UNSIGNED
,OUT delUsers INT UNSIGNED,OUT userCounts SHALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE age=p_age;
SELECT ROW_COUNT()INTO delUsers;返回删除的用户数和剩余的用户数
SELECT COUNT(id)FROM test INTO userCounts;
END
//
CALL remoueUserByAgeAndReturnInfos(41,@a,@b);

⑨、修改存储过程

只能修改简单的特性,不能修改过程体。如果想修改过程体,只能删除存储过程再创建。

ALTER PROCEDURE sp_name [ characteristic...…J 
COMMENT 'string'
I{ CONTAINS SQL I NO SQL I READS SQL DATA I MODIFIES SQL DAT 
I SQL SECURITY { DEFINER IINVOKER}

⑩、删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name

1.4、通过创建“事件”定时执行“存储过程”

①、创建表

create table test( 
    id int auto_increment not null, 
    endtime datetime, 
    primary key(id) 
);

②、插入数据

insert into test values("1","2016-6-7 14:20:52");

③、创建存储过程

CREATE PROCEDURE p_test () 
BEGIN 
update examinfo SET endtime = now() WHERE id = 1; 
END;

④、 创建事件 e_test

CREATE EVENT if not exists e_test 
on schedule every 30 second 
on completion preserve 
do call p_test();

⑤、开始事件

将事件计划开启: set global event_scheduler=1;
查看event是否开启: show variables like "%sche%"; 
关闭事件任务: alter event e_test ON COMPLETION PRESERVE DISABLE; 
开户事件任务: alter event e_test ON COMPLETION PRESERVE ENABLE;

⑥、运行查询结果即可出现想要的结果

二、自定义函数

2.1、定义

  • 函数在语句中调用,一般用来完成一些特定的数据库本身没有的函数的功能。

  • 用户自定义函数(user-defined function,UDF)是一种对 MySQL 扩展的途径,其用法与内置函数相同。

  • 自定义函数的两个必要条件:(1)参数参数最多1024个(2)返回值

  • 函数可以返回任意类型的值,同样可以接收这些类型的参数

2.2、语法

①、创建

CREATE FUNCTION function_name 
RETURNS 
{STRINGIINTEGERIREALIDECIMAL} 
routine_body

函数体:

  • (1)函数体由合法的SQL语句构成;

  • (2)函数体可以是简单的SELECT或INSERT语句;

  • (3)函数体如果为复合结构则使用BEGIN..END语句;

  • (4)复合结构可以包含声明,循环,控制结构

创建不带参数的自定义函数

CREATE FUNCTION f1()RETURNS UARCHAR(30)
RETURN DATE_FORMAT(NO(),'年%m月2d日H点:i分%s秒');

SELECT f1();

创建带有参数的自定义函数

CREATE FUNCTION F2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2)UNSIGNED
RETURN(num1+num2)/2;

SELECT f2(10,25);

创建具有复合结函数的自定义函数

DELIMITER //
SELECT UERSION();
//

delimiter修改结束符号为://
CREATE FUNCTION adduser(username UARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT test(username)UALUES(username);
RETURN LAST_INSERT_ID();
END

有两条语句需要执行,需要添加begin end

SELECT adduser('Rose)//

②、删除

DROP FUNCTION [IF EXISTS] function_name

三、存储过程和函数的区别

  • 存储过程实现的功能要复杂一些;而函数的针对性更强

  • 存储过程可以返回多个值;函数只能有一个返回值

  • 存储过程一般独立的来执行;而函数可以作为其他SQL语句的组成部分来出现。

3.1、参数和返回值

  • 函数必须指定返回值,且参数默认为IN类型。

  • 存储过程没返回值,参数可以是 IN,OUT,IN OUT类型,有的人可能会理解成OUT 也算是返回值。

3.2、调用方式

  • 函数 select my_fun();

  • 过程 call my_pro( ) ;

3.3、DEMO

DELIMITER $$
DROP FUNCTION IF EXISTS my_fun$$
CREATE
    FUNCTION my_fun(a INT(2),b INT(2))
    RETURNS INT(4)
    BEGIN
    DECLARE sum_ INT(2) DEFAULT 0;
    SET sum_ = a + b;
    RETURN sum_;
    END$$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS my_pro$$
CREATE
    PROCEDURE my_pro(IN a INT(2),IN b INT(2) ,OUT c INT(2))
    BEGIN
    SET c = a + b;
    END$$
DELIMITER ;

调用

mysql> call my_pro(1,2,@c);
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @c;
+------+
| @c   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)
 
mysql> select my_fun(1,2);
+-------------+
| my_fun(1,2) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

暂时还没有评论,快来抢沙发吧~

发表评论

您需要登录后才可以评论。登录 | 立即注册
阅读 110