41、mysql数据库(存储过程)

时间:2022-01-07 10:09:04

0、创建表:

(1)建表:

CREATE TABLE blog (

id INT PRIMARY KEY auto_increment,

name CHAR(64),

sub_time datetime

);

(2)查看数据(为空):

41、mysql数据库(存储过程)

1、什么是存储过程:

(1)存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,通过调用它的名字

可以执行其内部的一堆sql。

(2)使用存储过程的优点:

1)用于替代程序写的SQL语句,实现程序与sql解耦。

2)基于网络传输,传别名的数据量小,而直接传sql数据量大。

(3)使用存储过程的缺点:

1)程序员扩展功能不方便。

(4)补充:程序与数据库结合使用的三种方式:

1)方式一:

MySQL:存储过程

程序:调用存储过程

2)方式二:

MySQL:

程序:纯SQL语句

3)方式三:

MySQL:

程序:类和对象,即ORM(本质还是纯SQL语句)

2、创建简单存储过程(无参):

(1)代码:

delimiter //

create procedure p1()

BEGIN

select * from blog;

INSERT into blog(name,sub_time) values("xxx",now());

END //

delimiter ;

(2)在mysql中调用:

1)调用存储过程:

call p1();

2)查看数据(数据已经被插入):

41、mysql数据库(存储过程)

(3)在python中基于pymysql调用:

cursor.callproc('p1')

print(cursor.fetchall())

3、创建存储过程(有参):

(1)参数说明:

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

1)in #仅用于传入参数用

2)out #仅用于返回值用

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

(2)in:传入参数:

1)代码:

delimiter //

create procedure p2(

in n1 int,

in n2 int

/*定义输入值的类型*/

)

BEGIN

select * from blog where id > n1;

END //

delimiter ;

2)在mysql中调用存储过程:

call p2(0,2);

3)在python中基于pymysql调用:

cursor.callproc('p2',(3,2))

print(cursor.fetchall())

(3)out:返回值:

1)代码:

delimiter //

create procedure p3(

in n1 int,

out res int

)

BEGIN

select * from blog where id > n1;

set res = 1;

END //

delimiter ;

2)在mysql中调用存储过程:

--设置会话级变量:

set @res=0;

#0代表假(执行失败),1代表真(执行成功)

--查询:

41、mysql数据库(存储过程)

--调用存储过程:

call p3(0,@res);

--查询:

41、mysql数据库(存储过程)

--说明:需要设置会话级变量,out返回的结果再赋给会话级变量。

3)在python中基于pymysql调用:

cursor.callproc('p3',(3,0))

#0相当于set @res=0

print(cursor.fetchall())

#查询select的查询结果

cursor.execute('select @_p3_0,@_p3_1;')

#@p3_0代表第一个参数,@p3_1代表第二个参数,即返回值

print(cursor.fetchall())

(4)inout:既可以传入值又可以返回值:

1)代码:

delimiter //

create procedure p4(

inout n1 int

)

BEGIN

select * from blog where id > n1;

set n1 = 1;

END //

delimiter ;

2)在mysql中调用存储过程:

--设置会话级变量:

set @x=0;

--查询:

41、mysql数据库(存储过程)

--调用存储过程:

call p4(@x);

--查询:

select @x;

41、mysql数据库(存储过程)

--说明:需要设置会话级变量,inout返回的结果再赋给会话级变量。

3)在python中基于pymysql调用:

cursor.callproc('p4',(3,))

print(cursor.fetchall())

#查询select的查询结果

cursor.execute('select @_p4_0;')

print(cursor.fetchall())

4、执行存储过程:

(1)无参数:

call proc_name()

(2)有参数,全in:

call proc_name(1,2)

(3) 有参数,有in,out,inout:

set @t1=0;

set @t2=3;

call proc_name(1,2,@t1,@t2)

--说明:需要设置会话级变量,inout或out返回的结果再赋给会话级变量。

5、删除存储过程:

(1)语法:

drop procedure 存储过程名称

(2)示例:

drop procedure p1;

drop procedure p2;

drop procedure p3;

drop procedure p4;

6、存储过程之流程控制:

(1)if条件语句:

1)代码:

delimiter //

CREATE PROCEDURE proc_if (

in i int

/*定义输入值的类型*/

)

BEGIN

/*declare i int default 0;*/

/*在存储过程主体中定义变量类型及默认值*/

if i = 1 THEN

SELECT 1;

/*输出结果*/

ELSEIF i = 2 THEN

SELECT 2;

ELSE

SELECT 7;

END IF;

END //

delimiter ;

2)示例:

call proc_if(9);

41、mysql数据库(存储过程)

call proc_if(2);

41、mysql数据库(存储过程)

(2)while循环语句:

1)代码:

delimiter //

CREATE PROCEDURE proc_while ()

BEGIN

DECLARE num INT;

SET num = 0;

WHILE num < 3 DO

SELECT num;

SET num = num + 1;

END WHILE;

END //

delimiter ;

2)示例:

call proc_while();

41、mysql数据库(存储过程)

(3)repeat循环语句:

1)代码:

delimiter //

CREATE PROCEDURE proc_repeat()

BEGIN

DECLARE i INT;

SET i = 0;

repeat

select i;

set i = i + 1;

until i >= 3

end repeat;

END //

delimiter ;

2)示例:

41、mysql数据库(存储过程)