mysql条件循环语句_MySQL---存储过程 及 条件语句、循环语句

存储过程

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

1、创建存储过程

1 -- 创建存储过程

2

3 delimiter //

4 create procedure p1()

5 begin

6 select * from t1;

7 end//

8 delimiter ;

9

10

11

12 -- 执行存储过程

13

14 call p1()

无参数存储过程

1 注：

2 delimiter // 设置mysql的截止符为“//”

3

4 delimiter ;

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

in          仅用于传入参数用

out        仅用于返回值用

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

1 -- 创建存储过程

2 delimiter \\

3 create procedure p1(

4 in i1 int,

5 in i2 int,

6 inout i3 int,

7 out r1 int

8 )

9 begin

10 declare temp1 int;

11 declare temp2 int default 0;

12

13 set temp1 = 1;

14

15 set r1 = i1 + i2 + temp1 + temp2;

16

17 set i3 = i3 + 100;

18

19 end\\

20 delimiter ;

21

22 -- 执行存储过程

23 set @t1 =4;

24 set @t2 = 0;

25 call p1 (1, 2 ,@t1, @t2);

26 select @t1,@t2;

有参数的存储过程

1 delimiter //

2 create procedure p1()

3 begin

4 select * from v1;

5 end //

6 delimiter ;

1. 结果集

1 delimiter //

2 create procedure p2(

3 in n1 int,

4 inout n3 int,

5 out n2 int,

6 )

7 begin

8 declare temp1 int ;

9 declare temp2 int default 0;

10

11 select * from v1;

12 set n2 = n1 + 100;

13 set n3 = n3 + n1 + 100;

14 end //

15 delimiter ;

2. 结果集+out值

1 delimiter \\

2 create procedure p1(

3 out p_return_code tinyint

4 )

5 begin

6 declare exit handler for sqlexception

7 begin

8 -- error

9 set p_return_code = 1;

10 rollback;

11 end;

12

13 declare exit handler for sqlwarning

14 begin

15 -- warning

16 set p_return_code = 2;

17 rollback;

18 end;

19

20 start transaction;

21 delete from tb1;

22 insert into tb2(name)values('seven');

23 commit;

24

25 -- success

26 set p_return_code = 0;

27

28 end\\

29 delimiter ;

3. 事务

1 delimiter //

2 create procedure p3()

3 begin

4 declare ssid int; -- 自定义变量1

5 declare ssname varchar(50); -- 自定义变量2

6 declare done int default false;

7

8

9 declare my_cursor cursor for select sid,sname from student;

10 declare continue handler for not found set done = true;

11

12 open my_cursor;

13 xxoo: loop

14 fetch my_cursor into ssid,ssname;

15 if done then

16 leave xxoo;

17 end if;

18 insert into teacher(tname) values(ssname);

19 end loop xxoo;

20 close my_cursor;

21 end //

22 delimter ;

4. 游标

1 delimiter \\

2 drop procedure if exists proc_sql \\

3 create procedure proc_sql ()

4 begin

5 declare p1 int;

6 set p1 = 11;

7 set @p1 = p1;

8

9 prepare prod from 'select * from tb2 where nid > ?';

10 execute prod using @p1;

11 deallocate prepare prod;

12

13 end\\

14 delimiter ;

5. 动态执行sql

2、删除存储过程

1 drop procedure proc_name;

3、执行存储过程

1 -- 无参数

2 call proc_name()

3

4 -- 有参数，全in

5 call proc_name(1,2)

6

7 -- 有参数，有in，out，inout

8 set @t1=0;

9 set @t2=3;

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

执行存储过程

1 #!/usr/bin/env python

2 # -*- coding:utf-8 -*-

3 import pymysql

4

5 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')

6 cursor = conn.cursor(cursor=pymysql.cursors.dictcursor)

7 # 执行存储过程

8 cursor.callproc('p1', args=(1, 22, 3, 4))

9 # 获取执行完存储的参数

10 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")

11 result = cursor.fetchall()

12

13 conn.commit()

14 cursor.close()

15 conn.close()

16

17

18 print(result)

pymysql执行存储过程

条件语句

1 delimiter \\

2 create procedure proc_if ()

3 begin

4

5 declare i int default 0;

6 if i = 1 then

7 select 1;

8 elseif i = 2 then

9 select 2;

10 else

11 select 7;

12 end if;

13

14 end\\

15 delimiter ;

if条件语句

循环语句

1 delimiter \\

2 create procedure proc_while ()

3 begin

4

5 declare num int ;

6 set num = 0 ;

7 while num < 10 do

8 select

9 num ;

10 set num = num + 1 ;

11 end while ;

12

13 end\\

14 delimiter ;

while循环

1 delimiter \\

2 create procedure proc_repeat ()

3 begin

4

5 declare i int ;

6 set i = 0 ;

7 repeat

8 select i;

9 set i = i + 1;

10 until i >= 5

11 end repeat;

12

13 end\\

14 delimiter ;

repeat循环

1 begin

2

3 declare i int default 0;

4 loop_label: loop

5

6 set i=i+1;

7 if i<8 then

8 iterate loop_label;

9 end if;

10 if i>=10 then

11 leave loop_label;

12 end if;

13 select i;

14 end loop loop_label;

15

16 end

loop

如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！