存储过程

基本操作

创建

# 查看emplyoees表中所有数据
CREATE PROCEDURE select_all_employees ()
BEGINSELECT*FROMemployees;
END;

调用

CALL select_all_employees ();

再看一个例子，返回所有员工平均工资：

CREATE PROCEDURE avg_employees_salary ()
BEGINSELECTAVG(salary)FROMemployees;
END;CALL avg_employees_salary ();

出参

带出参的存储过程，定义格式：out 变量名 变量类型，然后通过into把结果传给返回变量：

CREATE PROCEDURE min_salary (OUT min_salary DOUBLE)
BEGINSELECTMIN(salary) INTO min_salaryFROMemployees;
END;

调用方式：

# 调用，通过@变量名定义变量，再select该变量即可
CALL min_salary (@min_salary);SELECT@min_salary;

入参

带入参的存储过程，在存储过程语句中直接用该变量即可：

CREATE PROCEDURE show_single_salary (IN empname VARCHAR(20))
BEGINSELECTsalaryFROMemployeesWHEREemployees.last_name = empname;
END;

调用方式，直接传参：

CALL show_single_salary ('Abel');

也可以通过变量传参：

SET @empname := 'Abel';CALL show_single_salary (@empname);

带入参和出参的存储过程

CREATE PROCEDURE show_single_salary_v2 (IN empname VARCHAR (20),OUT empsalary DOUBLE
)
BEGINSELECTsalary INTO empsalaryFROMemployeesWHEREemployees.last_name = empname;END;

调用，并传参：

CALL show_single_salary_v2 ('Abel', @min_salary);

输出结果

SELECT@min_salary;

带inout参数的存储过程

CREATE PROCEDURE show_mgr_name (INOUT empname VARCHAR(20))
BEGINSELECTemployees.last_name INTO empname # 此时为outFROMemployees,(SELECTmanager_idFROMemployeesWHEREemployees.last_name = empname # 此时为in) managerWHEREemployees.employee_id = manager.manager_id;
END;

通过变量调用inout存储过程，并查看结果：

SET @empname = 'Abel';CALL show_mgr_name (@empname);SELECT@empname;

存储函数

存储函数，即自定义函数。先设置信任创建自定义函数，然后才能创建自定义函数：

SET GLOBAL log_bin_trust_function_creators = 1;

创建

创建存储函数时，需要指定函数名、参数列表(可有可无)、返回值类型、约束条件(可有可无)

CREATE FUNCTION email_by_name () RETURNS VARCHAR (25)
BEGINRETURN ( # 直接返回结果SELECTemailFROMemployeesWHERElast_name = 'Abel');END;

调用存储函数，通过select语句：

SELECTemail_by_name ();

带参数的存储函数

CREATE FUNCTION email_by_id (emp_id INT) RETURNS VARCHAR (25)
BEGINRETURN (SELECTemailFROMemployeesWHEREemployees.employee_id = emp_id);
END;

调用：

SELECTemail_by_id (101);

再看一个例子：查看某部门的员工数：

CREATE FUNCTION count_by_id (dept_id INT) RETURNS INT
BEGINRETURN (SELECTcount(*)FROMemployeesWHEREemployees.department_id = dept_id);END;

调用：

SELECTcount_by_id (30);

存储函数和存储过程的对比

• 存储函数可以放在查询语句中使用，存储过程不行；
• 存储过程中可以执行对表的操作(创建、删除等)和事务操作，但存储函数不行。

查看

show create语句

用于查看创建信息：

SHOW CREATE PROCEDURE show_mgr_name;SHOW CREATE FUNCTION count_by_id;

show status语句

用于查看状态信息：

SHOW PROCEDURE STATUS;

显示所有：

SHOW PROCEDURE STATUS LIKE 'show_mgr_name';

显示单个，可以模糊查询:

SHOW FUNCTION STATUS LIKE 'count_by_id';

从information_schema.ROUTINES表中查看

一般用于查看详细信息：

SELECT*
FROMinformation_schema.ROUTINES
WHEREROUTINE_NAME IN ('show_mgr_name','count_by_id');

修改

我们可以修改存储过程和存储函数，不过只能修改相关特性(charateristic)：

ALTER PROCEDURE show_single_salary SQL SECURITY INVOKER COMMENT '查询最高工资';

修改存储函数就是ALTER FUNCTION：

SHOW PROCEDURE STATUS LIKE 'show_single_salary';

删除

DROP FUNCTION # 或PROCEDURE
IF EXISTS count_by_id;

优缺点

存储过程或函数的优点：

• 一次编译多次使用；
• 减少开发工作量；
• 安全性强，可以设置使用权限；
• 减少网络传输量；
• 良好的封装性。

缺点：

• 可移植性差；
• 调试困难；
• 存储过程的版本管理困难；
• 不适合高并发。