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()
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

删除存储过程或者函数

1. 一次只能删除一个存储过程或者函数，删除存储过程或者函数需要有该过程或者函数的ALTER ROUTINE权限，具体语法如下

　　　　　　DROP {PROCEDURE|FUNCTION} [IF EXIST] sp_name

查看存储过程或者函数

1. 查看存储过程或者函数的状态

SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern']

2. 查看存储过程或者函数的定义

　　SHOW CREATE {PROCEDURE|FUNCTION} sp_name

3. 通过查看information_schema.Routines获得存储过程和函数的名称、类型、语法、创建人等信息

　　SELECT *

　　FROM routines

　　WHERE routine_name='sp_name'

分隔符：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。

局部变量的作用域：

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

在嵌套块的情况下，

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

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

变量可以直接赋值，或者通过查询赋值。直接赋值使用SET，可以赋常量或者赋表达式，具体语法如下

　　　　　　SET var_name = expr[, var_name = expr] ...

也可以通过查询将结果赋给变量，这要求查询返回的结果必须只有一行，具体语法如下

　　　　　　SELECT col_name[, ...]

　　　　　　INTO var_name[, ...]

　　　　　　table_expr

2.3.2. 用户变量

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

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

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

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 |

+------+

注意：

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

与局部变量区别：

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

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

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

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

2.3.3. 用户变量与系统变量

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

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

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

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

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

全局变量(@@global.)

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

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

会话变量(@@session.)

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

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

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

2.4. 其它语法

注释

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

2.5. store procedure function

函数与过程的区别：

函数必须有返回值，而存储过程没有

存储过程的参数可以使用IN, OUT, INOUT 类型；而函数的参数只能是IN类型的

创建存储函数的语法如下：

CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body

参数释义：

sp_name参数是存储函数的名称；

func_parameter表示存储函数的参数列表；

RETURNS type指定返回值的类型；

characteristic参数指定存储函数的特性，该参数的取值与存储过程中的取值是一样的；

routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。

func_parameter可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：param_name type

其中，param_name参数是存储函数的参数名称；type参数指定存储函数的参数类型，该类型可以是MySQL数据库的任意数据类型。

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;

3.1. 存储过程案例1

use db_store;

drop procedure if exists se_em;

drop procedure if exists se_em1;

delimiter $$

create procedure se_em1(in par int)

begin

-- show var par

select par;

# if statement

if par > 5 then

select count(id) from em1;

else

select count(id) from em1 where sex='男';

end if;

end $$

delimiter ;

注意：if是个语句，应该由;结束

3.2. 存储过程案例2

# 创建表并插入20万条随机生成的记录

use db_store;

# create table if table not exists

CREATE TABLE if not exists `tbl_user_no_part` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(255) DEFAULT NULL,

`email` varchar(20) DEFAULT NULL,

`age` tinyint(4) DEFAULT NULL,

`type` int(11) DEFAULT NULL,

`create_time` datetime DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

drop function if exists rand_string;

drop procedure if exists insert_user;

delimiter $$

create function rand_string(n int) returns varchar(255) DETERMINISTIC

begin

# 定义三个变量

declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';

declare return_str varchar(255) default '';

declare i int default 0;

while i < n do

set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1));

set i = i + 1;

end while;

return return_str;

end $$

-- 创建插入的存储过程

create procedure insert_user(in start int(10), in max_num int(10))

begin

declare i int default 0;

set autocommit = 0;

repeat

set i = i + 1;

insert into tbl_user_no_part values ((start+i) ,rand_string(8), concat(rand_string(6), '@random.com'), 1+FLOOR(RAND()*100), 3, now());

until i = max_num

end repeat;

commit;

end $$

delimiter ;

# 调用insert_user插入10万条数据

# call insert_user(100001,200000);

select count(*) from tbl_user_no_part;

3.2.1. question

create function rand_string(n int) returns varchar(255)

报错1418,

查阅相关资料，意思是说binlog启用时，创建的函数没有声明类型，因为binlog在主从复制需要知道这个函数创建语句是什么类型，否则同步数据会有不一致现象。

mysql开启了bin-log, 我们就必须指定我们的函数是否是哪种类型：

1 DETERMINISTIC 不确定的

2 NO SQL 没有SQl语句，当然也不会修改数据

3 READS SQL DATA 只是读取数据，当然也不会修改数据

4 MODIFIES SQL DATA 要修改数据

5 CONTAINS SQL 包含了SQL语句

改为create function rand_string(n int) returns varchar(255) DETERMINISTIC后正常

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脚本

注释

单行(双短横)--

多行/*……*/

posted @ 2019-04-09 13:19 木林森__𣛧阅读(331) 评论(0) 收藏举报

刷新页面返回顶部

木林森__𣛧

mysql存储过程

mysql存储过程

1. mysql存储过程

2. 存储过程创

2.1. 创建/管理

2.2. 参数

2.3. 变量

2.3.1. 局部变量

2.3.2. 用户变量

2.3.3. 用户变量与系统变量

2.4. 其它语法

2.5. store procedure function

3. mysql操作命令

3.1. 存储过程案例1

3.2. 存储过程案例2

3.2.1. question

4. 问题

4.1. 使用pymysql时报错

4.2. sql脚本

公告