数据库之--存储过程、存储函数

存储过程与函数

存储过程和存储函数的区别：存储过程是没有返回值的，存储函数是有返回值的

1.定义：存储过程（stored procedure），是一组经过***预先编译***的SQL语句的封装

2.好处：

​ （1）简化操作，起高了sql语句的重用性，减少了开发程序员的压力

​ （2）减少操作过程中的失误，提高了效率

​ （3）减少网络传输量

​ （4）减少sql语句暴露网络上的风险

3.视图和函数的对比

视图是虚拟表，通常不对底层数据表直接操作，而且存储过程是程序化的sql，可以直接操作底层数据，相比于面向集合的操作方式，能够实现一些更复杂的数据处理

4.视图的操作

#0.准备工作
CREATE DATABASE dbtest1;

USE dbtest1;

#复制一份数据
CREATE TABLE employees
AS
SELECT * FROM atguigudb.employees;

CREATE TABLE departments
AS
SELECT * FROM atguigudb.departments;

#查看
SELECT * FROM employees;

SELECT * FROM departments;


#1.创建存储过程
#类型1，无参数、无返回值
DELIMITER $

CREATE PROCEDURE select_all_data()
BEGIN
			SELECT * FROM employees;
END $

DELIMITER;

#2.存储过程的调用
CALL select_all_data();

#举例1:创建存储过程avg_employee_salary()，返回所有员工的
#平均工资

DELIMITER $
CREATE PROCEDURE avg_employee_salary()
BEGIN 
			SELECT AVG(salary)
			FROM employees;
END $
DELIMITER;
	
			
#调用存储过程
CALL avg_employee_salary();

#举例3:创建存储过程show_max_salary()，用来查看"employee"表的最高薪资值。
DELIMITER //

CREATE PROCEDURE show_max_salary()
BEGIN
			SELECT MAX(salary) FROM employees;
END //

DELIMITER;

CALL show_max_salary();


#类型2，带out
#举例：创建存储过程show_min_salary(),查看employees表最低的薪资，并将最低薪资通过out参数ms输出
DESC employees;

DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
			SELECT MIN(salary) INTO ms
			FROM employees;
END //

DELIMITER;		
#调用
CALL show_min_salary(@ms);
#查看值
SELECT @ms;


#举例5:创建存储过程show_someone_salary()，查看"employees"表的某个员工的薪资，并用IN参数empname输入员工姓名。
DELIMITER //

CREATE PROCEDURE show_someone_salary(IN ename VARCHAR(20))
BEGIN
			SELECT salary FROM employees
			WHERE last_name = ename;
END //

DELIMITER;
#调用方式一
CALL show_someone_salary('Abel');
#调用方式二
SET @ename = 'Abel';
CALL show_someone_salary(@ename);


#举例6:创建存储过程show_someone_salary2()，查看"employees"表的某个员工的薪资，
#并用IN参数empname输入员工姓名，用ouT参数empsalary输出员工薪资
desc employees;

DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN ename VARCHAR(25),OUT esalary DOUBLE)
BEGIN
		SELECT salary INTO esalary
		FROM employees
		WHERE last_name=ename;
END //
DELIMITER;

#调用
SET @ename='Abel';
CALL show_someone_salary2(@ename,@esalary);

SELECT @esalary;


#4.存储过程、函数的修改
ALTER PROCEDURE show_max_salary
SQL SECURITY INVOKER
COMMENT'查询最高工资';

#5．存储过程、函数的删除
DROP FUNCTION IFEXISTs count_by_id;
DROP PROCEDURE IFEXISTs show_min_salary;

CREATE DATABASE test2;

USE test2;

#1．创建存储过程insert_user()，实现传入用户名和密码，插入到admin表中
CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(15) NOT NULL,
pwd VARCHAR(25) NOT NULL
);


DELIMITER $
CREATE PROCEDURE insert_user(IN uname VARCHAR(15),IN pwd VARCHAR(25))
BEGIN
			INSERT INTO admin(user_name,pwd)
			VALUES(uname,pwd);
END $
DELIMITER;

SELECT * FROM admin;

CALL insert_user('tom','pwd');

#2．创建存储过程get_phone(),实现传入女神编号，返回女神姓名和女神电话
CREATE TABLE beauty(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(15) NOT NULL,
phone VARCHAR(15) UNIQUE,
birth DATE
);

INSERT INTO beauty(`name`,phone,birth)
VALUES 
('liping','123456','1998-02-14'),
('li','1234567','1998-02-14'),
('lipingping','12345','1998-02-14'),
('ping','1234566','1998-02-14');

SELECT * FROM beauty;

DELIMITER $
CREATE PROCEDURE get_phone(IN id INT ,OUT name VARCHAR(15),OUT phone varchar(15))
BEGIN
		SELECT b.`name`,b.phone into `name`,phone
		FROM beauty b
		WHERE b.id = id;
END $
DELIMITER;

CALL get_phone(16,@name,@phone);
SELECT @name,@phone;

#3．创建存储过程format_date(),实现传入一个日期，格式化成xx年xx月xx日并返回
DELIMITER  $
CREATE PROCEDURE format_date(IN my_date DATE,OUT str_date VARCHAR(25))
BEGIN
			SELECT DATE_FORMAT(my_date,'%y年%m月%d日') into str_date;
END $
DELIMITER;
CALL format_date(CURDATE(),@str);
SELECT @str;


#4．创建存储过程date_diff( )，实现传入两个女神生日，返回日期间隔大小

DELIMITER $
CREATE PROCEDURE date_diff(IN birth1 DATE,IN birth2 DATE,OUT sum_date INT)
BEGIN
		SELECT DATEDIFF(birth1,birth2) INTO sum_date;
END $
DELIMITER;

SET @birth1='1998-02-14';
SET @birth2='1999-10-30';
CALL date_diff(@birth1,@birth2,@sum_date);
SELECT @sum_date;

5存储函数

#创建存储函数，名称为email_by_name(),参数定义为空
#该函数查询Abel的email,并返回，数据类型为字符串型

DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
				DETERMINISTIC
				CONTAINS SQL
				READS SQL DATA

BEGIN
			RETURN (SELECT email FROM employees 
							WHERE last_name='Abel');
END //

DELIMITER;

SELECT email_by_name();

#无参有返回
#1．创建函数get_count (),返回公司的员工个数
DELIMITER $
CREATE FUNCTION get_count()
RETURNS INT
BEGIN

		RETURN(SELECT COUNT(*) FROM employees);
END $
DELIMITER;

SELECT get_count();


#举例2:创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型万整型。

DELIMITER //

CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT

BEGIN
		RETURN(SELECT COUNT(*) FROM employees WHERE department_id=dept_id);
END //
DELIMITER;

SET @dept_id:=30;
SELECT count_by_id(@dept_id);

SET GLOBAL log_bin_trust_function_creators = 1;
#有参有返回
#2创建函数ename_salary(),根据员工姓名，返回它的工资
DELIMITER $
CREATE FUNCTION ename_salary(emp_name VARCHAR(25))
RETURNS DOUBLE
BEGIN
			RETURN (SELECT salary
			        FROM employees 
			        WHERE last_name = emp_name
							);
END $
DELIMITER;

SELECT ename_salary('Abel');

#创建函数dept_sal(),根据部门名，返回该部门的平均工资

DELIMITER //
CREATE FUNCTION dept_sal(dept_name VARCHAR(15))
RETURNS DOUBLE

BEGIN
		
		RETURN(SELECT AVG(salary) FROM employees e 
					JOIN departments d 
					ON e.department_id = d.department_id 
					WHERE d.department_name = dept_name
					);
END //

DELIMITER;

SELECT dept_sal('Marketing');

#4．创建函数add_float ()，实现传入两个float，返回二者之和
DELIMITER //
CREATE FUNCTION add_float(num1 FLOAT,num2 FLOAT)
RETURNS FLOAT
BEGIN
			RETURN(SELECT num1+num2);
END //
DELIMITER;


SET @num1 := 12;
SET @num2 := 13;

SELECT add_float(@num1,@num2);

6.优点
(1)、存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译，这就提升了SQL的执行效率。
(2)、可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。
(3)、存储过程的安全性强。我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。

7.缺点

（1）、可移植性差。存储过程不能跨数据库移植，比如在MySQL、Oracle和sQL Server里编写的存储过程，在换成其他数据库时都需要重新编写。
（2）、调试困难。只有少数DBMS支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。|
（3）、存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
（4）、它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。