#第15章_储存过程与存储函数
#1创建存储过程
#举例1:
CREATE DATABASE dbtest15;
USE dbtest15;
CREATE TABLE employess
AS
SELECT *
FROM atguigudb.`employees`;
CREATE TABLE departments
AS
SELECT *
FROM atguigudb.`departments`;
SELECT * FROM employess;
SELECT * FROM departments;
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
SELECT * FROM employess;
END $
DELIMITER ;
#2.存储过程的调用:
CALL select_all_data();
DELIMITER $
CREATE PROCEDURE show_max_salary()
BEGIN
SELECT MAX(salary)
FROM employess;
END $
DELIMITER ;
CALL show_max_salary();
#;类型2:带OUT
DELIMITER $
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
SELECT MIN(salary) INTO ms
FROM employess;
END$
DELIMITER ;
CALL show_min_salary(@ms);
SELECT @ms;
#in
DELIMITER $
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
SELECT salary
FROM employess
WHERE last_name = empname;
END$
DELIMITER ;
#调用方式1:
CALL show_someone_salary('Abel');
#调用方式2:
SET @empname = 'Abel';
#in 和 out
DELIMITER $
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DECIMAL(10,2))
BEGIN
SELECT salary INTO empsalary
FROM employess
WHERE last_name = empname;
END$
DELIMITER ;
#调用
SET @empname = 'Abel';
CALL show_someone_salary2(@empname,@empsalary);
SELECT @empsalary;
#inout
DELIMITER $
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN
SELECT e2.last_name INTO empname
FROM employess e1 JOIN employess e2
ON e1.manager_id = e2.employee_id
WHERE e1.last_name = empname;
END$
DELIMITER ;
SET @empname = 'Abel';
CALL show_mgr_name(@empname);
SELECT @empname;
#2.存储函数:
DELIMITER $
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
READS SQL DATA
BEGIN
RETURN (SELECT email FROM employess WHERE last_name = 'Abel');
END $
DELIMITER ;
#调用:
SELECT email_by_name();
#3.存储过程的查看情况
#3.1
SHOW CREATE PROCEDURE show_mgr_name;
SHOW CREATE FUNCTION
#3.2
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS LIKE 'show_mgr_name';#模糊查询
#3.3
#4.函数的修改