本文共 2590 字，大约阅读时间需要 8 分钟。

存储过程和函数

存储过程和函数的概念

存储过程和函数是数据库中常用的功能模块化工具，类似于Java中的方法。其主要优势在于：

存储过程的定义

存储过程是一组预先编译好的SQL语句的集合，通常被称为批处理语句。它的优势包括：

创建存储过程的语法

1. 基本语法

CREATE PROCEDURE 存储过程名(参数列表) 
BEGIN
  -- 存储过程体
END

2. 参数列表的格式

参数列表包含三部分：

• 参数模式（mode）：包括IN、OUT、INOUT
• 参数名（name）：参数的名称
• 参数类型（type）：如VARCHAR(20)

3. 注意事项

4. 示例

DELIMITER $
CREATE PROCEDURE myp1() 
BEGIN
  INSERT INTO admin(username, password) 
  VALUES('john1', '0000'), ('lily', '0000'), ('jack', '0000'), 
         ('rose', '0000'), ('tom', '0000');
END$

调用存储过程的语法

1. 调用方式

CALL 存储过程名(实参列表);

2. 空参列表示例

DELIMITER $
CREATE PROCEDURE myp1() 
BEGIN
  INSERT INTO admin(username, password) 
  VALUES('john1', '0000'), ('lily', '0000'), ('jack', '0000'), 
         ('rose', '0000'), ('tom', '0000');
END$

3. 带有IN模式参数的存储过程

CREATE PROCEDURE myp2(IN beautyName VARCHAR(20)) 
BEGIN
  SELECT bo.* 
  FROM boys bo 
  RIGHT JOIN beauty b 
  ON bo.id = b.boyfriend_id 
  WHERE b.name = beautyName;
END$

4. 带有OUT模式参数的存储过程

CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20)) 
BEGIN
  SELECT bo.boyName INTO boyName 
  FROM boys bo 
  INNER JOIN beauty b 
  ON bo.id = b.boyfriend_id 
  WHERE b.name = beautyName;
END$

5. 带有INOUT模式参数的存储过程

CREATE PROCEDURE myp8(INOUT a INT, INOUT b INT) 
BEGIN
  SET a = a * 2;
  SET b = b * 2;
END$

常见案例

1. 插入用户数据

DELIMITER $
CREATE PROCEDURE test_pro1(IN username VARCHAR(20), IN loginPwd VARCHAR(20)) 
BEGIN
  INSERT INTO admin(username, password) 
  VALUES(username, loginpwd);
END$

2. 查询用户信息

CREATE PROCEDURE test_pro2(IN id INT, OUT name VARCHAR(20), OUT phone INT) 
BEGIN
  SELECT name, phone INTO name, phone 
  FROM beauty b 
  WHERE id = b.id;
END$

3.比较生日时间

CREATE PROCEDURE test_pro3(IN birth1 DATETIME, IN birth2 DATETIME, OUT result INT) 
BEGIN
  SELECT DATEDIFF(birth1, birth2) INTO result;
END$

4. 日期格式化

CREATE PROCEDURE test_pro4(IN mydate DATETIME, OUT strDate VARCHAR(50)) 
BEGIN
  SELECT DATE_FORMAT(mydate, '%y年%m月%d日') INTO strDate;
END$

5. 查询关联数据

CREATE PROCEDURE test_pro5(IN beautyName VARCHAR(20), OUT str VARCHAR(50)) 
BEGIN
  SELECT CONCAT(beautyName, ' and ', IFNULL(boyName, 'null')) INTO str 
  FROM boys bo 
  RIGHT JOIN beauty b 
  ON bo.id = b.boyfriend_id 
  WHERE b.name = beautyName;
END$

6. 分页查询

CREATE PROCEDURE test_pro6(IN size INT, IN startIndex INT) 
BEGIN
  SELECT * FROM beauty 
  LIMIT startIndex, size;
END$

存储过程的管理

1. 删除存储过程

DROP PROCEDURE myp1;

2. 查看存储过程信息

SHOW CREATE PROCEDURE myp2;

总结

存储过程是数据库开发中的重要工具，通过模块化设计可以显著提升代码的可维护性和复用性。在实际应用中，合理设计存储过程的参数和功能，能够有效提高数据库的性能和开发效率。