mysql存储过程

1.      mysql存储过程

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

存储过程的优点：

(1).增强SQL语言的功能和灵活性。

(2).标准组件式编程。

(3).较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

(4).减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。

(5).作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

不同数据库系统的存储过程语法会类似但并不相同。

2.      存储过程创

2.1.    创建/管理

CREATE [DEFINER = { user | CURRENT_USER }] 　PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name 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 [begin_label:] BEGIN 　　[statement_list] 　　　　…… END [end_label]

创建存储过程
create procedure sp_name()
begin
.........
end

调用存储过程：call sp_name()
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

删除存储过程：drop procedure sp_name//

注意：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

其他常用命令

1.show procedure status
显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

2.show create procedure sp_name
显示某一个MySQL存储过程的详细信息

分隔符：MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。

DELIMITER $$ OR DELIMITER //

参数：存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:

区块：存储过程开始和结束符号为

BEGIN .... END   

也可以给区块起别名，如：
lable:begin
...........
end lable;
可以用leave lable;跳出区块，执行区块以后的代码

循环语句

(1).while循环

[label:] WHILE expression DO

 statements

END WHILE [label] ;

(2).loop循环

[label:] LOOP

 statements

END LOOP [label];

(3).repeat until循环

[label:] REPEAT

 statements

UNTIL expression

END REPEAT [label] ;

变量声明/赋值:

DECLARE l_int int unsigned default 4000000;

SET @p_in=1

set b = 5;

2.2.    参数

IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT:该值可在存储过程内部被改变，并可返回

INOUT:调用时指定，并且可被改变和返回

IN输入参数的例子

DELIMITER //
CREATE PROCEDURE `proc_IN` (IN var1 INT)
BEGIN
    SELECT var1 + 2 AS result;
END//

输出OUT参数例子如下：

DELIMITER //
CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))
BEGIN
　　SET var1 = ‘This is a test‘;
END //

IN-OUT的例子：

DELIMITER //
CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)
BEGIN
　　SET var1 = var1 * 2;
END //

因为 MySQL 存储过程参数没有默认值，所以在调用 MySQL 存储过程时候，不能省略参数。可以用 null 来替代

2.3.    变量

MySQL存储过程常见的变量有局部变量、用户变量、系统变量……

2.3.1.   局部变量

定义：

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

declare v  int default 56;

DECLARE用来声明局部变量，且DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前；可以被用在嵌套的块中，除了那些用相同名字声明变量的块。

如果要给变量提供一个默认值，使用DEFAULT子句(值可以是常数，也可以指定为一个表达式)；如果没有DEFAULT子句，初始值为NULL。

局部变量的作用域：

也就是变量能正常使用而不出错的程序块的范围。

在嵌套块的情况下，

　　在外部块中声明的变量可以在内部块中直接使用；

　　在内部块中声明的变量只能在内部块中使用。

2.3.2.   用户变量

用户变量与数据库连接有关：在当前连接中声明的变量，在连接断开的时候，就会消失；在此连接中声明的变量无法在另一连接中使用。

前缀@表示的就是用户变量。

用户变量定义(set、select)：

1. set语句为用户变量赋值：

可以使用“=”或“:=”作为分配符；

分配给每个变量的expr可以为整数、实数、字符串或者NULL值；

可以使用“=”或“:=”作为分配符；

分配给每个变量的expr可以为整数、实数、字符串或者NULL值；

mysql> set @zjc:=999;

mysql> select @zjc;

+------+

| @zjc |

+------+

|  999 |

+------+

2.select语句为用户变量赋值：

分配符必须为“:=”而不能用“=”，因为在非SET语句中=被视为一个比较操作符；

mysql> select @abc:=123;

+-----------+

| @abc:=123 |

+-----------+

|       123 |

+-----------+

mysql> select @abc;

+------+

| @abc |

+------+

|  123 |

+------+

注意：

1. 用户变量随处可以定义，随处可以使用；不定义可以直接使用(值默认为null)。
2. 用户变量的变量名的形式：@var_name，要有@符号。
3. 滥用用户变量会导致程序难以理解及管理。

与局部变量区别：

局部变量只有变量名字，没有@符号；用户变量名前有@符号。

都是先定义，再使用；未定义的变量，select值为空。

局部变量只在存储过程内部使用，在过程体外是没有意义的，当begin-end块处理完后，局部变量就消失；而用户变量可以用在存储过程的内部和外部。

在存储过程内部，尽量使用局部变量，不要使用用户变量。

2.3.3.   用户变量与系统变量

用户变量如上所述@var_name（一个@符号）

用户变量和数据库连接有关，连接后声明变量，连接断开后，自动消失；

select一个没有赋值的用户变量，返回NULL，也就是没有值；

Mysql的变量类似于动态语言，变量的值随所要赋的值的类型而改变。

系统变量：根据系统变量的作用域分为：全局变量与会话变量（两个@符号）

全局变量(@@global.)

在MySQL启动的时候由服务器自动将全局变量初始化为默认值；

全局变量的默认值可以通过更改MySQL配置文件(my.ini、my.cnf)来更改。

会话变量(@@session.)

在每次建立一个新的连接的时候，由MySQL来初始化；

MYSQL会将当前所有全局变量的值复制一份来做为会话变量（也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的）。

#全局变量与会话变量的区别：对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话。

2.4.    其它语法

注释

# --都可以，注意注释符后面要跟一个空格

3.      mysql操作命令

mysql -h localhost -u root -p

create database db_store;

use db_store;

创建表

create table if not exists employer (

id smallint auto_increment primary key,

name varchar(80) NOT NULL,

sex varchar(2) not null,

id_num varchar(20) not null,

gjj_account varchar(20),

gzsj date,

jiguan char(10),

stamp timestamp

) engine=InnoDB default charset=utf8;

复制表结构

create table em1 like employer;

复制表结构及数据

直接复制

create table em1 select * from employer;

先复制结构再插入数据

insert into em1 select * from employer;

show procedure status;

4. 问题

4.1.  使用pymysql时报错

sql = ‘insert into em (%s,%s) values(%s,%s)’

data = db.execute(sql,st)

报错：pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘vlaues(‘。。。。。。 ‘)‘ at line 1")

错误提示为插入语句格式不对，插入语句格式不对的原因是pymysql在替换时会在变量左右自动加入单引号符，而mysql插入语句在表名和列名处是不允许有单引号符的。

下面三种写法都是可以的。

    sql = "insert into {} ({},{},{},{},{},{}) values(%s,%s,%s,%s,%s,%s)".format(‘employer‘, *colnum)

    sql_ = "insert into employer ({},{},{},{},{},{}) values(‘{}‘,‘{}‘,‘{}‘,‘{}‘,‘{}‘,‘{}‘)".format(*colnum,*_)

    #sql = "insert into yy (y,e) values(%s,%s)"

4.2.    sql脚本

注释

单行(双短横)--

多行/*……*/

mysql存储过程

原文：https://www.cnblogs.com/wodeboke-y/p/10676298.html