存儲過程 存儲過程是一個SQL語句集合，當主動去調用存儲過程時，其中內部的SQL語句會按照邏輯執行。 1、創建存儲過程 1 -- 創建存儲過程 2 3 delimiter // 4 create procedure p1() 5 BEGIN 6 select * from t1; 7 END// 8 ...
存儲過程
存儲過程是一個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
