mysql 存储过程

存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。

一、创建存储过程

delimiter $$
create procedure p1()
BEGIN
    select * from t1;
END $$
delimiter ;

这里创建存储过程与创建视图要进行区别，创建视图 create view, 创建存储过程 create procedure；而且创建存储过程的时候在存储过程名字后面要加上括号。

对于存储过程，可以接收参数，其参数有三类：

in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值

在navicat中创建的存储过程：

二、使用存储过程

call p1()

注意：
使用的关键字 call。

无参数存储过程

无参数的存储国过程，直接使用 call 存储过程名();

有参数存储过程

1、in 仅用于传入参数用

delimiter $$
drop procedure if exists p1;
create procedure p1(
	in i1 int
)
BEGIN
declare d1 int;
declare d2 int default 3;
set d1 = i1 + d2;

select * from part where nid > d1;
end $$
delimiter ;

使用：

call p1(1);

说明：
1、in：定义的参数只能由执行函数的时候传入，然后便可在函数中使用，同时要注意，当我们在创建的时候定义参数的类型，那么在传递参数的时候，类型必须一直，不然会报错。上面列在，定义的参数是 int，所以我们在执行方法的时候我们只能传int类型（整型）的数据。
2、declare：在方法内部定义变量使用的关键字(和js中var区别理解)
3、set：对变量进行设置值的时候，必须使用的。
4、delimiter：重新定义方法内部的结束符号，在sql中一般一个完成sql执行完之后都会使用分号“;”；进行结束，但是在定义存储过程的时候，内部定义变量、设置值、内部sql语句，都会形成一个完成的语句，而结尾就应该使用分号“;”结束，但是如果直接用分号结束那么会导致整个sql语句的结束，这样会导致sql语句执行不完整，因此的在函数内部定义一个符号。让其在函数内部表示一个语句的执行完成。而不会导致整个sql语句的结束。
代码中，开始定义了内部的执行结束的表示符号为 “$$”。而在结尾必须将其变会 “;”，不然会影响后面语句的执行。
5、drop procedure if exists p1; 这句表示先检测是否存在 p1存储过程，如果存在则删除。注意：其中 if exists 是判断的关键。

2、out 仅用于返回值用

delimiter $$
DROP PROCEDURE
IF EXISTS p1$$

CREATE PROCEDURE p1 (IN i1 INT, OUT i2 INT)
BEGIN

DECLARE d2 INT DEFAULT 3 ;
IF i1 = 1 THEN

SET i2 = 100 + d2 ;
ELSEIF i1 = 2 THEN

SET i2 = 200 + d2 ;
ELSE

SET i2 = 1000 + d2 ;
END
IF ; END$$
delimiter ;

在代码中现在有2个参数，第一个 in 是必传的，但是 out 并不是必传的，从它的定义上来说，它只是接收方法返回值。

问题：既然是接收返回值，那怎么接收那？

call p1(1, @u);

这里要涉及一个回话变量，回话变量：相当于我们在方法外面定义一个变量，然后将变量的引用传入方法。

既然是在外部定义的，那么就可以直接访问这个变量。

call p1(1, @u);
select @u;

结果：

说明：
sql中查看某个变量要使用 select。格式：select 变量名

3、inout 既可以传入又可以当作返回值

delimiter $$
DROP PROCEDURE IF EXISTS p1$$

CREATE PROCEDURE p1 (
	IN i1 INT,
	INOUT i3 INT,
	OUT i2 INT
)
BEGIN

	DECLARE d2 INT DEFAULT 3 ;
	SET i3 = i3 + 1 ;
	IF i1 = 1 THEN

	SET i2 = 100 + d2 ;
	ELSEIF i1 = 2 THEN

	SET i2 = 200 + d2 ;
	ELSE

	SET i2 = 1000 + d2 ;
	END
	IF ; 
END$$
delimiter ;

inout是必传字段。执行代码是要注意，inout既然是能传值也能获取值，就不能单单的传一个对应类型的值进去就行了的。

set @x = 5;
call p1(1, @x, @u);
select @x,@u;

结果：

说明：
1、in 是必传字段。
2、out 可以不用传。但是如果想获取返回值的话，那就必须传。
3、inout：也是必传字段。

三、修改存储过程

修改存储过程很麻烦，因此一般不修改存储过程，而是直接删除，重新创建一个新的存储过程。

四、删除存储过程

drop procedure proc_name;

五、pymysql执行存储过程

创建存储过程：

delimiter $$
drop PROCEDURE if EXISTS p1;
create PROCEDURE p1(
	in i1 int,
	inout i2 int,
	out i3 int
)
BEGIN
	DECLARE d1 int default 3;
	set i2 = i2 + 1;
	select * from part WHERE nid > i2;
	if i1 = 1 then
		set i2 = 100 + d1;
	ELSEIF i1 = 2 THEN
		set i2 = 200 + d1;
	else
		set i2 = 1000 + d1;
	end if;
end $$
delimiter ;

存储过程

问题：在python中怎样获取方法返回的东西？

1、获取方法中sql语句操作的结果。

# !usr/bin/evn python

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='waijian')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 执行存储过程
row = cursor.callproc('p1', args = (1,2,3))
# 获取存储过程中sql语句操作的结果
ret = cursor.fetchall()

print(ret)

# 获取存储过程的返回值,括号中的三个参数对应执行存储过程中接收的三个参数
effect_row = cursor.execute("select @_p1_0, @_p1_1, @_p1_2")

# 取返回值
r = cursor.fetchone()
print(r)

conn.commit()
cursor.close()
conn.close()

pymysql

执行结果：

说明：
1、pymysql执行语句中，传入参数为3个，前面分析，out不是必传字段。因此按上面这样操作，即使传了值在程序中，第三个值也会自动被忽略。
2、存储方法中的sql语句返回的结果是存在与 cursor（游标）中的。因此获取时只能通过游标获取，代码中只有一条查询语句，如果有多条的话，fetchall() 一次性获取所有的，fetchone() 一条条的获取。
3、sql语句中的 if判断语句，主要要和python中的区别记忆。
4、接收方法中返回参数， cursor.execute("select @_p1_0, @_p1_1, @_p1_2")；代码是这句，注意括号中接收参数的写法，写法不对获取结果都为 none。
之前我使用的是 @_proc_p1_0,@_proc_p1_1,@_proc_p1_2；结果获取的都为None，后面改了名字就可以了。

python 获取存储过程中的信息，要分2步，获取查询是一步，获取返回值是一步，注意区分。

六、扩展

当在存储过程中，执行多条sql语句，并且某些sql语句执行的条件，是基于别的sql语句执行的结果。

当然可以通过连表操作获取对应的信息。
但是这里讨论的是在存储过程中实现。

注意：
上面代码中 into 是关键。

posted on 2017-09-25 00:04 jayafs 阅读(364) 评论(0) 编辑收藏举报

刷新页面返回顶部

手里有糖