MySQL数据库-MySQL存储过程
MySQL存储过程，也就是有点像MySQL函数，但是他与MySQL函数是有区别的，后面会讲到函数，所以注意区分
注意：函数与存储过程的区别
存储过程是：CREATE PROCEDURE 创建的
函数时：create function 创建的
存储过程是：CALL 执行的
函数时：SELECT 执行的
函数里：不支持SQL语句【重点】
存储过程里：支持SQL语句【重点】
1、创建存储过程(函数)无参
delimiter设置数据库语句结束符，默认是遇到;表示语句结束，delimiter可以自定义语句结束符，后面跟结束符
create procedure创建存储过程(函数)，后面跟、函数名称()
BEGIN表示SQL语句开始，后面跟SQL语句
END表示SQL语句结束
举例：有这样一张表
创建一个usr_nl_20 ()函数，功能是查询usr表nl字段等于20的数据
-- delimiter设置数据库语句结束符，默认是遇到;表示语句结束，delimiter可以自定义语句结束符，后面跟结束符
-- create procedure创建存储过程(函数)，后面跟、函数名称()
-- BEGIN表示SQL语句开始，后面跟SQL语句
-- END表示SQL语句结束 -- 格式：
-- delimiter $$ -- 将语句结束符改为$$
-- create procedure 函数名称()
-- BEGIN -- SQL语句开始
-- SQL语句
-- END $$ -- SQL语句开始
-- delimiter ; -- 将语句结束符改为默认 delimiter $$
CREATE PROCEDURE usr_nl_20 ()
BEGIN
SELECT
id,
yhm,
xb,
nl
FROM
usr
WHERE
nl = 20 ;
END $$
delimiter ;
2、执行存储过程(函数)
call执行存储过程(函数)，后面跟、函数名称()
-- call执行存储过程(函数)，后面跟、函数名称()
CALL usr_nl_20();
3、删除存储过程(函数)
DROP PROCEDURE删除存储过程(函数)，后面跟要删除的函数名称
-- DROP PROCEDURE删除存储过程(函数)，后面跟要删除的函数名称
DROP PROCEDURE usr_nl_20;
4、修改存储过程(函数)
一般函数都不用修改的办法，一般都是删除后重写
DROP PROCEDURE删除存储过程(函数)，后面跟要删除的函数名称
if EXISTS判断后面的变量或者函数是否存在，如果存在执行if EXISTS前面的语句
-- DROP PROCEDURE删除存储过程(函数)，后面跟要删除的函数名称
-- if EXISTS判断后面的变量或者函数是否存在，如果存在执行if EXISTS前面的语句 DROP PROCEDURE if EXISTS usr_nl_20; -- 判断usr_nl_20函数是否存在，如果存在删除函数，如果不存在则不删除
-- 如果存在函数删除后创建函数，不存在创建函数
delimiter $$
CREATE PROCEDURE usr_nl_20 ()
BEGIN
SELECT
id,
yhm,
xb,
nl
FROM
usr
WHERE
nl = 20 ;
END $$
delimiter ;
5、创建存储过程(函数)有参，in 仅用于传入参数用
对于存储过程，可以接收参数，其形式参数有三种用途类型：
in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值
DECLARE定义变量
DEFAULT设置变量的默认值
SET给变量赋值 要赋值的变量 值
定义变量的方式:DECLARE 变量名称 指定变量接收的数据类型 DEFAULT 设置变量的默认值
DROP PROCEDURE if EXISTS usr_nl_20; -- 判断usr_nl_20函数是否存在，如果存在删除函数，如果不存在则不删除
-- 如果存在函数删除后创建函数，不存在创建函数 -- 对于存储过程，可以接收参数，其形式参数有三种用途类型：
-- in 仅用于传入参数用
-- out 仅用于返回值用
-- inout 既可以传入又可以当作返回值
--
--
-- DECLARE定义变量
-- DEFAULT设置变量的默认值
-- SET给变量赋值 要赋值的变量 值
-- 定义变量的方式:DECLARE 变量名称 指定变量接收的数据类型 DEFAULT 设置变量的默认值 delimiter $$
CREATE PROCEDURE usr_nl_20 (
in canshu int
)
BEGIN
DECLARE d1 int;
DECLARE d2 int DEFAULT 3;
SET d1 = canshu + d2; SELECT id,yhm,xb,nl FROM usr WHERE id > d1;
END $$
delimiter ;
运行有参函数
-- 运行有参函数
CALL usr_nl_20(1);
6、创建存储过程(函数)有参，in 仅用于传入参数用，out 仅用于返回值用
delimiter $$
DROP PROCEDURE if EXISTS usr_nl_20;
CREATE PROCEDURE usr_nl_20 (
in canshu int, -- in 仅用于传入参数用
OUT canshu2 INT -- out 仅用于返回值用
)
BEGIN
DECLARE d2 INT DEFAULT 3; -- 设置变量d2等于3
IF canshu = 1 THEN -- 判断传入参数如果等于1
SET canshu2 = 100 + d2; -- 赋值返回参数等于100加d2
ELSEIF canshu = 2 THEN -- 判断传入参数等于2
SET canshu2 = 200 + d2; -- 返回参数等于200+d2
ELSE -- 否则
SET canshu2 = 1000 + d2; -- 返回参数等于1000+d2
END IF;
END $$
delimiter ;
执行函数
执行函数，传入参数，和传入引用参数，也就是返回值参数
@定义引用参数，也就是返回值参数
-- 执行函数，传入参数，和传入引用参数，也就是返回值参数
-- @定义引用参数，也就是返回值参数
CALL usr_nl_20(1, @u);
SELECT @u;
7、创建存储过程(函数)有参，in 仅用于传入参数用，out 仅用于返回值用，inout 既可以传入又可以当作返回值
delimiter $$
DROP PROCEDURE if EXISTS usr_nl_20;
CREATE PROCEDURE usr_nl_20 (
in canshu int, -- in 仅用于传入参数用
OUT canshu2 INT, -- out 仅用于返回值用
INOUT ii INT -- inout 既可以传入又可以当作返回值
)
BEGIN
DECLARE d2 INT DEFAULT 3; -- 设置变量d2等于3
SET ii = ii + 1;
IF canshu = 1 THEN -- 判断传入参数如果等于1
SET canshu2 = 100 + d2; -- 赋值返回参数等于100加d2
ELSEIF canshu = 2 THEN -- 判断传入参数等于2
SET canshu2 = 200 + d2; -- 返回参数等于200+d2
ELSE -- 否则
SET canshu2 = 1000 + d2; -- 返回参数等于1000+d2
END IF;
END $$
delimiter ;
执行函数
SET @f = 5; -- 定义inout类型变，可传入可返回变量
CALL usr_nl_20(1, @u, @f); -- 执行函数，参数in 仅用于传入参数用，参数out 仅用于返回值用，参数inout 既可以传入又可以当作返回值
SELECT @u,@f; -- 查看两个类型的返回值
8、pymysql模块，执行存储过程(函数)，获取SQL语句查询结果、和返回值
举例：创建了一个存储过程(函数)有参，函数里既有返回值、又有SQL语句查询结果，怎么通过pymysql模块既拿到返回结果，又拿到SQL语句查询结果
表
执行存储过程(函数)
delimiter $$
DROP PROCEDURE if EXISTS usr_nl_20;
CREATE PROCEDURE usr_nl_20 (
in canshu int, -- in 仅用于传入参数用
OUT canshu2 INT, -- out 仅用于返回值用
INOUT ii INT -- inout 既可以传入又可以当作返回值
)
BEGIN
DECLARE d2 INT DEFAULT 3; -- 设置变量d2等于3
SET ii = ii + 1;
SELECT * FROM usr; -- SQL查询语句
IF canshu = 1 THEN -- 判断传入参数如果等于1
SET canshu2 = 100 + d2; -- 赋值返回参数等于100加d2
ELSEIF canshu = 2 THEN -- 判断传入参数等于2
SET canshu2 = 200 + d2; -- 返回参数等于200+d2
ELSE -- 否则
SET canshu2 = 1000 + d2; -- 返回参数等于1000+d2
END IF;
END $$
delimiter ;
pymysql模块，执行存储过程(函数)，获取SQL语句查询结果、和返回值
callproc()执行存储过程(函数),两个参数
使用方式：
游标变量.callproc('存储过程(函数)名称',(函数参数多个参数,号隔开))
fetchall()获取存储过程(函数)里的SQL语句查询结果
使用方式：
游标变量.fetchall()
execute()获取存储过程(函数)的返回值,参数是要获取返回值的形式参数索引
使用方式：
游标变量.execute("select @_存储过程(函数)名称_0,@_存储过程(函数)名称_1,@_存储过程(函数)名称_2")
fetchone()拿到存储过程(函数)的返回值
使用方式：
游标变量.fetchone()
#!/usr/bin/env python
#coding:utf-8 import tornado.ioloop
import tornado.web #导入tornado模块下的web文件
import pymysql #导入数据库模块 class khdHandler(tornado.web.RequestHandler):
def get(self): #连接数据库
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='cshi',charset='utf8')
# 创建游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #执行存储过程(函数)
row = cursor.callproc('usr_nl_20',(1,2,3)) #获取存储过程(函数)里的SQL语句查询结果
chaxjieg = cursor.fetchall()
print(chaxjieg) #打印SQL语句查询结果 #获取存储过程(函数)的返回值
effect_row = cursor.execute("select @_usr_nl_20_0,@_usr_nl_20_1,@_usr_nl_20_2")
fhuizhi = cursor.fetchone()
print(fhuizhi) # 提交，不然无法保存新建或者修改的数据
# conn.commit() # 关闭游标
cursor.close()
# 关闭连接
conn.close() self.write("欢迎访问") settings = { #html文件归类配置，设置一个字典
"template_path":"views", #键为template_path固定的，值为要存放HTML的文件夹名称
"static_path":"statics", #键为static_path固定的，值为要存放js和css的文件夹名称
} #路由映射
application = tornado.web.Application([ #创建一个变量等于tornado.web下的Application方法
(r"/khd", khdHandler),
],**settings) #将html文件归类配置字典，写在路由映射的第二个参数里 if __name__ == "__main__":
#内部socket运行起来
application.listen(8002) #设置端口
tornado.ioloop.IOLoop.instance().start()
原理图
