开发基础系列17,存储过程

一. 概述 

 存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合,可以简化应用开发人员的很多工作,减少数据在数据库与应用服务器之间的传输,提高数据处理效率是有好处的。存储过程和函数的区别在于函数必须有返回值,存储过程的参数可以使用in,out ,inout类型,而函数参数只能是in类型。 创建需要create routine 权限, 修改删除需要alter routine权限,执行需要execute权限。

  1.1 创建存储过程,调用,删除

-- 创建
DELIMITER $$
CREATE PROCEDURE proc_city(IN city_id INT ,OUT num INT)
READS SQL DATA
BEGIN
    --  要设置初始值,不然为null 与sqlserver一样
    SET num=0;
    SELECT * FROM city WHERE city.city_id=city_id;
    SET num=num 1;
END $$
DELIMITER ;

 --  调用
CALL proc_city (2,@num); SELECT @num;

图片 1图片 2

-- 删除
DROP  PROCEDURE proc_city

  特征值介绍:
  CREATE PROCEDURE proc_name()
    { contains sql | no sql | READS SQL DATA | modifies sql data}
  这个特征值只是提供给服务器,并没有根据这些特征值来约束过程实际使用数据的情况。 contains sql 表示子程序不包含读或写数据的语句。no sql表示子程序不包含sql 语句。 READS SQL DATA:表示子程序包含数据的语句,但不包含写数据。modifies sql data 表示子程序包含写数据。默认是contains sql。

  1.2 创建函数,调用,删除

-- 创建
SET GLOBAL log_bin_trust_function_creators=TRUE;
DELIMITER $$
CREATE FUNCTION fn_city (city_count INT)
RETURNS INT 
BEGIN
DECLARE  city_count1 INT;
    --  要设置初始值,
    SET city_count1=0;
    SET  city_count1=city_count 1;
    RETURN city_count1;
END $$
DELIMITER ;

-- 调用
SELECT  fn_city (2);

图片 3

-- 删除
DROP FUNCTION fn_city

  1.3 查看信息

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='proc_city' OR  ROUTINE_NAME='fn_city'

图片 4

  1.4 变量的使用

DELIMITER $$
CREATE PROCEDURE proc_demo_declare()
READS SQL DATA
BEGIN
    -- 定义 
    DECLARE i INT ;
    -- 赋值
    SET i=10;
    SELECT i;
    -- 第二种赋值
    SELECT COUNT(1) INTO i  FROM city;
    SELECT i;
END $$
DELIMITER ;

call proc_demo_declare;

      图片 5    图片 6

1.5 定义Handler遇到问题的处理步骤

--  语法
DECLARE
{EXIT | CONTINUE}
HANDLER FOR
{error-number | SQLSTATE error-STRING | CONDITION}
SQL statement

  (1) 处理类型{EXIT | CONTINUE} continue: 当遇到执行错误时,跳过继续下面的语句。exit: 当遇到执行错误时,停止语句
  (2) 触发条件 (SQLSTATE,MYSQL ERROR,CONDITION)MYSQL错误代码。ANSI-standard SQLSTATE code。命名条件。可使用系统内置的SQLEXCEPTION,SQLWARNING和NOT FOUND。
  (3)错误触发的操作。

--   以continue为例
DELIMITER $$
CREATE PROCEDURE proc_demo_handler_continue()
BEGIN
    -- 定义条件 continue : 当遇到执行错误时,跳过继续下面的语句
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;    

    -- 这条可以插入
    INSERT INTO  city(country_id,cityname,Citycode) VALUES(2,'中国','cn4');
    SET @x=1;

    -- 这条不能插入,外键约束fk_city_country
    INSERT INTO  city(country_id,cityname,Citycode) VALUES(8,'中国','cn5');
    SET @x=2;

    -- 这条可以插入
    INSERT INTO city(country_id,cityname,Citycode) VALUES(2,'中国','cn6');
    SET @x=3;
END $$
DELIMITER ;

-- 调用 
CALL proc_demo_handler_continue; SELECT @x,@x2;

  使用了continue: 当遇到执行错误时,跳过继续下面的语句,三条语句,只插入成功二条成功

图片 7

  

--  以exit为例
DELIMITER $$
CREATE PROCEDURE proc_demo_handler_exit()
BEGIN
    -- 定义条件  : 当遇到执行错误时,停止语句  
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @x2 = 1;    

    -- 这条可以插入
    INSERT INTO  city(country_id,cityname,Citycode) VALUES(2,'中国','cn4');
    SET @x=1;

    -- 这条不能插入,外键约束fk_city_country
    INSERT INTO  city(country_id,cityname,Citycode) VALUES(8,'中国','cn5');
    SET @x=2;
    --  这条不可以插入
    INSERT INTO city(country_id,cityname,Citycode) VALUES(2,'中国','cn6');
    SET @x=3;
END $$
DELIMITER ;

-- 调用
CALL proc_demo_handler_exit;SELECT @x,@x2;

  使用了exit: 当遇到执行错误时,停止语句, 三条语句,只插入成功第一条

图片 8

 

参数说明

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语句来显示。

definer 定义者

说明存储过程的定义者,只是说明存储过程的定义者,跟执行权限关系不大

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输入输出参数把数据传入到存储过程,在存储过程中修改后再传递给调用者

SQL Security

  • sql secuirty的值决定了调用存储过程的方式,取值 :definer或者invoker
  1. definer 指定子程序该用创建子程序者的权限来执行

  2. invoker 使用调用者的权限来执行

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语句包含在一起;

DETERMINISTIC

每次调用存储过程结果是不是确定的

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表示存储过程包含插入或删除数据的语句

SQL Data Access

该声明只是提供一些信息,并不会约束实际存储过程的执行情况

  • CONTAINS SQL:未知(实验可以读写不会出问题)。

  • NO SQL:表示子程序不包含SQL语句。

  • READS SQL DATA:表示子程序包含读数据的语句,但不包含写数据的语句。

  • MODIFIES SQL DATA:表示子程序包含写数据的语句。

  • CONTAINS SQL 网上大部分的资料都说明是包含sql数据,不包含读写的语句, 感觉本身就矛盾,怎么会有不包含读写语句的存储过程,那要存储过程做什么,没有找到官网的文档,默认就用contains sql不会出问题

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)

赋值

  • set var_name = expr

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)

定义条件和处理

定义条件

declare condition_name(自定义错误类似) condition for condition_value
condition_value:

  • sqlstate [value] sqlstate_value
  • mysql_error_code

定义处理

declare handler_type handler for condition_value sp_statement;
handler_type:

  • continue
  • exit
  • undo (要查询版本是否支持)
    condition_value(即定义条件定义的名称):
  • SQLSTATE [value] sqlstate_value
  • condition_name
  • SQLWARNING(sqlstate 以01开头的部分)
  • NOT FOUND(sqlstate 以02开头的部分)
  • SQLEXCEPTION
  • mysql_error_code

光标的使用

声明光标

declare cursor_name cursor for select_statement

打开光标

open cursor_name

获取记录

fetch cursor_name into var_name,[var_name]...

循环获取光标的值

关闭光标

close cursor_name

例子

CREATE DEFINER = `cdz`@`localhost` PROCEDURE `live_db`.`<function_name>`(IN i_platform_id int)

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT '两小时统计一次作品数量'

BEGIN

    declare v_user_id  int default 0;

    declare stop int default 0;

    declare v_art_count int default 0;

    declare cur_ingkee cursor for (select distinct userId from newmedia_ingkee_art where updateTime >= date_sub(now(), interval 122 MINUTE));

    declare cur_yy cursor for (select distinct userId from newmedia_yy_art where updateTime >= date_sub(now(), interval 122 MINUTE));

    declare CONTINUE HANDLER FOR SQLSTATE '02000'SET stop = null;

    if i_platform_id=1 THEN

        open cur_ingkee;

        fetch cur_ingkee into v_user_id;

        while (stop is not null) DO

            select count(*) into v_art_count from newmedia_ingkee_art where userId = v_user_id;

            update newmedia_wanghong_users 

            set postCount = v_art_count

            where userId = v_user_id and platformId = i_platform_id;

            fetch cur_ingkee into v_user_id;

        end while;

    elseif i_platform_id=3 THEN

        open cur_yy;

        fetch cur_yy into v_user_id;

        while (stop is not null) DO

            select count(*) into v_art_count from newmedia_yy_art where userId = v_user_id;

            update newmedia_wanghong_users 

            set postCount = v_art_count

            where userId = v_user_id and platformId = i_platform_id;

            fetch cur_yy into v_user_id;

        end while;

    end if;

    commit;

end

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:开发基础系列17,存储过程

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。