MySQL存储过程名词解释_MySQL存储过程详解

最新推荐文章于 2024-06-22 17:27:40 发布

KimmyLeo

最新推荐文章于 2024-06-22 17:27:40 发布

阅读量325

点赞数

文章标签： MySQL存储过程名词解释

本文链接：https://blog.csdn.net/weixin_36011746/article/details/114345572

版权

一、使用场景

1.执行时简单：名字+参数

2.存储过程中可以有一个、多个sql语句。

问：为啥要用存储过程？

答：

①将重复性很高的一些操作，封装到一个存储过程中。简化了对这些SQL的调用。

②批量处理sql+循环

③统一接口

注意：mysql中，存储功能相对较弱。使用较少。

二、基本建立语法

例1：

DELIMITER $$

CREATE PROCEDURE delete_matches(IN p playerno INTEGER)

BEGIN

DELETE FROM MATCHES

WHERE playerno=p_playerno;

END$$

DELIMITER;

call delete_matches(6);

例2：

delimiter $$

CREATE PROCEDURE dorepeat(p1 INT)

BEGIN

SET @x = 0; --》用set赋值， repeat循环

REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;

END$$

delimiter ;

CALL dorepeat(1000);

SELECT @x;

执行后：@x的值为1001

@变量名字的作用：

①存储过程中定义，存储过程中进行处理，加减乘除。

②存储过程外面可以访问这个@变量，这个变量保存了存储过程中对这个变量处理后的值。

@x：用户变量

存储过程外面可以访问，一直被保存。

直接使用set @x=0；就可以生成这么一个变量，不需要数据类型。

三、存储过程参数

三类：in(输入参数)、out(输出参数)、inout(输入和输出参数)

存储过程需要是活的–》输入不同的参数，执行不同的操作，处理不同的行等等。

IN参数

DELIMITER $$

CREATE PROCEDURE delete matches(IN p playerno INTEGER)

BEGIN

DELETE FROM MATCHES

WHERE playerno=p_playerno;

END$$

DELIMITER;

call delete_matches(6);

IN参数可以是：数值100、赋值了的变量 set @a=100；

OUT参数

delimiter $$

CREATE PROCEDURE simpleproc (OUT param1 INT)

BEGIN

SELECT COUNT(*) INTO param1 FROM PLAYERS;

END$$

delimiter ;

CALL simpleproc(@a);

SELECT @a;

OUT参数：变量就可以，可以没有赋值。 @a

INOUT参数

delimiter $$

CREATE PROCEDURE simpleproc1(INOUT param1 INT)

BEGIN

SELECT COUNT(*) INTO param1 FROM PLAYERS where PLAYERNO<=param1;

END$$

delimiter ;

set @a=10;

CALL simpleproc1(@a); --》调用存储过程时，只要引用变量就行！

SELECT @a;

INOUT参数：只能是赋了值得变量。 set @a=100；

一个存储过程如果想有返回值：

1.out参数

2.inout参数

3.在存储过程内部定义用户变量

@变量名字

建议：返回值使用out参数；输入值使用in参数。

四、begin…end语法

为什么需要begin…end？

答：

①因为begin…end语句之间的n条语句属于一条语句，一个整体。

②成对出现。

BEGIN

sql语句

END$$

五、变量

1.用户变量@

随处可定义，随处可使用；

不定义可直接使用；

随处可见；

内部定义，外部可见。

2.局部变量，只有名字，没有@符号

先定义，再使用；

只在存储过程内部可见；只能定义在begin后面！

注意：在存储过程内部，使用局部变量，不要使用用户变量。

用户变量和局部变量的区别：

①局部变量前面没有@符号

②当begin…end块处理完后，局部变量就消失了，而用户变量存在于整个会话之中。

定义局部变量：

DECLARE 变量名字类型 [default] –》而且declare必须紧接着begin！

例：

DECLARE var_name [, var_name] ... type [DEFAULT value]

delimiter $$

CREATE PROCEDURE sp1 (x VARCHAR(10))

BEGIN

DECLARE newname VARCHAR(10);

DECLARE xid INT;

SELECT xname, id INTO newname, xid

FROM table1 WHERE xname = x;

SELECT newname;

END$$

delimiter ;

call sp1('a');

初始化局部变量：

delimiter $$

CREATE PROCEDURE test1(OUT num1 INTEGER)

BEGIN

DECLARE num2 INTEGER DEFAULT (SELECT count(*) FROM PLAYERS);

SET num1 = num2;

END$$

delimiter ;

begin…end的作用域：

外层看不到内层；

内层可以看到外层；

平行互相看不见。

SET

可以给用户变量和局部变量赋值

用户变量内外都可以使用

局部变量，只有begin end之间可以使用，而且需要declare定义

六、IF…ELSE

DELIMITER $$ --》定义$$为分界符

CREATE PROCEDURE difference(

IN p1 INTEGER, --》整数

IN p2 INTEGER,

OUT p3 INTEGER)

BEGIN

IF p1 > p2 THEN

SET p3 = 1;

ELSEIF p1= p2 THEN

SET p3 = 2;

ELSE

SET p3 = 3;

END IF;

END$$

DELIMITER ;

mysql> call difference(1,10,@p);

Query OK, 0 rows affected (0.00 sec)

mysql> select @p;

+------+

| @p |

+------+

| 3 |

+------+

1 row in set (0.01 sec)

七、函数

1.返回值

两个return：一个标志返回什么类型；一个实际的返回值。

2.调用函数：函数需要出现在=的右边

例子：

DELIMITER $$

CREATE FUNCTION SimpleCompare(n INT, m INT)

RETURNS VARCHAR(20)

BEGIN

DECLARE s VARCHAR(20);

IF n > m THEN SET s = '>';

ELSEIF n = m THEN SET s = '=';

ELSE SET s = '

END IF;

SET s = CONCAT(n, ' ', s, ' ', m);

RETURN s;

END$$

DELIMITER ;

mysql> select SimpleCompare(1,2) ;

+--------------------+

| SimpleCompare(1,2) |

+--------------------+

| 1 < 2 |

+--------------------+

1 row in set (0.01 sec)

mysql> set @a=SimpleCompare(1,2) ;

Query OK, 0 rows affected (0.01 sec)

mysql> select @a;

+-------+

| @a |

+-------+

| 1 < 2 |

+-------+

1 row in set (0.00 sec)

八、case…end case

例1：

DELIMITER $$

CREATE PROCEDURE difference1(

IN p1 INTEGER,

IN p2 INTEGER,

OUT p3 INTEGER)

BEGIN

CASE

WHEN p1 > p2 THEN

SET p3 = 1;

WHEN p1= p2 THEN

SET p3 = 2;

ELSE

SET p3 = 3;

END CASE;

END$$

DELIMITER ;

mysql> call difference1(1,100,@a);

Query OK, 0 rows affected (0.01 sec)

mysql> select @a;

+------+

| @a |

+------+

| 3 |

+------+

1 row in set (0.00 sec)

例2：

DELIMITER $$

CREATE PROCEDURE p(x INT)

BEGIN

DECLARE v INT DEFAULT 1;

set v=x;

CASE v

WHEN 2 THEN SELECT v;

WHEN 3 THEN SELECT 0;

ELSE

BEGIN

END;

END CASE;

END$$

DELIMITER ;

mysql> call p(2);

+------+

| v |

+------+

| 2 |

+------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> call p(3);

+---+

| 0 |

+---+

| 0 |

+---+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

九、while

例题：

DELIMITER $$

CREATE PROCEDURE dowhile(x int)

BEGIN

DECLARE v1 INT DEFAULT 5;

set v1=x;

WHILE v1 > 0 DO

select v1;

SET v1 = v1 - 1;

END WHILE;

END$$

DELIMITER ;

mysql> call dowhile(10);

+------+

| v1 |

+------+

| 10 |

+------+

1 row in set (0.01 sec)

+------+

| v1 |

+------+

| 9 |

+------+

1 row in set (0.01 sec)

。。。一直到1

十、repeat…until

先执行，再判断。

例题：

delimiter //

CREATE PROCEDURE dorepeat1(p1 INT)

BEGIN

SET @x = 0;

REPEAT

SET @x = @x + 1;

UNTIL @x > p1

END REPEAT;

END//

delimiter ;

mysql> call dorepeat1(10);

Query OK, 0 rows affected (0.01 sec)

mysql> select @x;

+------+

| @x |

+------+

| 11 |

+------+

十一、loop配合leave

leave：跳出语句块

DELIMITER $$

CREATE PROCEDURE small_exit(OUT p1 INTEGER,OUT p2 INTEGER)

BEGIN

SET p1 = 1;

SET p2 = 1;

block1: BEGIN

LEAVE block1; --》就不再执行下面那个p2=3了！！

SET p2 = 3;

END block1;

SET p1 = 4;

END$$

DELIMITER ;

mysql> call small_exit(@a,@b);

Query OK, 0 rows affected (0.00 sec)

mysql> select @a,@b;

+------+------+

| @a | @b |

+------+------+

| 4 | 1 |

+------+------+

1 row in set (0.00 sec)

loop

DELIMITER $$

CREATE PROCEDURE wait_n(IN wait_seconds INTEGER)

BEGIN ---》PS：interval 间隔

DECLARE end_time datetime DEFAULT now() + INTERVAL wait_seconds SECOND;

wait_loop:LOOP

IF now() > end_time THEN

LEAVE wait_loop;

END IF;

END LOOP wait_loop;

END$$

DELIMITER ;

mysql> call wait_n(10);

Query OK, 0 rows affected (10.55 sec)

while：先判断再执行

repeat…until：先执行再判断

loop：判断可以放在loop…end loop之间的任意位置

iterate：跳出本次循环

十二、ITERATE

DELIMITER $$

CREATE PROCEDURE doiterate3(p1 INT,p2 INT)

BEGIN

label1: LOOP

SET p1 = p1 + 1;

SET p2 = p2 + 1;

IF p1 < 10 THEN

select p1;

ITERATE label1; --》跳出本次label1循环！继续下次label1循环。

select p2; --》一直没执行

END IF;

LEAVE label1;

END LOOP label1;

SET @x = p1;

END$$

DELIMITER ;

mysql> call doiterate3(10,10); --》直接就执行leave label1

Query OK, 0 rows affected (0.01 sec)

mysql> call doiterate3(1,1); ---》显示p1=2,3,4...9

+------+

| p1 |

+------+

| 2 |

+------+

1 row in set (0.00 sec)

+------+

| p1 |

+------+

| 3 |

+------+

1 row in set (0.00 sec)

十三、select info经典用法

DELIMITER $$

CREATE PROCEDURE total_penalties_player(

IN p_playerno INTEGER,

OUT total_penalties DECIMAL(8,2))

BEGIN

SELECT sum(amount) ---》去掉into的select只能返回一行数据！

INTO total_penalties --》把select 的结果into给(多个)变量！！

FROM PENALTIES

WHERE playerno = p_playerno;

END$$

DELIMITER ;

mysql> call total_penalties_player(44,@a); ---》计算44号人的罚款总额，结果赋给@a

Query OK, 1 row affected (0.01 sec)

mysql> select @a;

+--------+

| @a |

+--------+

| 130.00 |

+--------+

1 row in set (0.00 sec)

小结：

1.经典的方式：将select返回的多列单行数据赋值给相应的变量(一个列对应一个变量)

2.这些变量经常是out参数

3.也就是变相的将select的结果传给了存储过程，让外面的程序可见。

例2：into给多个参数：

DELIMITER $$

CREATE PROCEDURE get_address(

IN p_playerno SMALLINT,

OUT p_street VARCHAR(30),

OUT p_houseno VARCHAR(4),

OUT p_town VARCHAR(30),

OUT p_postcode VARCHAR(6))

BEGIN

SELECT street, houseno, town, postcode

INTO p_street, p_houseno, p_town, p_postcode

FROM PLAYERS

WHERE playerno = p_playerno;

END$$

DELIMITER ;

mysql> call get_address(44,@a,@b,@c,@d);

Query OK, 1 row affected (0.01 sec)

mysql> select @a,@b,@c,@d;

+--------------+------+-----------+--------+

| @a | @b | @c | @d |

+--------------+------+-----------+--------+

| Lewis Street | 23 | Inglewood | 4444LJ |

+--------------+------+-----------+--------+

1 row in set (0.00 sec)

KimmyLeo

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫