MySQL-5.7存储过程及函数
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:
htmlfunction函数的用法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输⼊输出参数把数据传⼊到存储过程，在存储过程中修改后再传递给调⽤者
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;
w3cschool mysql
+-----+--------+--------+---------+
| 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语句，通过d将多个SQL语句包含在⼀起；
4.测试definer
[root@localhost ~]# mysql -uabc2 -p
Enter password:
mysql> show databases;
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_SC +----------------+--------------+--------------+--------------+---------------
----------------------------------------------------------------------------------------------------------------+---------| ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | ROUTINE_BODY | ROUTINE_DEFINITION                                                                                        +----------------+--------------+--------------+--------------+-------------------------------------------------------------------------------------------------------------------------------+---------| 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;
mysql查看所有存储过程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表⽰存储过程包含插⼊或删除数据的语句
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)
6.查看存储过程及函数
mysql> show create procedure simpleproc;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------| Procedure  | sql_mode                                                                                                                                  | Create Procedure                                                        +------------+-------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------| simpleproc | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CRE 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                                                            +------------+-------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------| simplefunc | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CRE 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)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。