数据库实验七 存储过程和函数

实验目的：
了解存储过程和函数的概念和功能，掌握存储过程和函数的创建方法和步骤，掌握存储过程和函数的使用方法。

实验报告要求：
针对各种查询需求，按照SQL程序设计规范设计存储过程和函数，并以截图的形式显示操作结果。

实验内容及完成情况：
1.创建一个存储过程CountEmp，其作用是获取employee表中记录的条数。

CREATE PROCEDURE CountEmp ()
BEGIN
	SELECT
		COUNT(*)
	FROM
		employee;
END;

2.创建一个存储过程AvgSal，其作用是获取所有员工的平均工资。

CREATE PROCEDURE CountCom1 ()
BEGIN
	SELECT
		AVG(salary)
	FROM
		works;
END;

3.创建一个存储过程CountCom1，输入变量为公司的名字（company_name），输出为该公司中员工的个数。

CREATE PROCEDURE CountCom1 (
	IN company_name VARCHAR (30),
	OUT count INT
)
BEGIN
SET count = (
	SELECT
		count(employee_ID)
	FROM
		works AS w
	WHERE
		w.company_name = company_name
);
END;

4.调用存储过程CountCom1，其输入参数为‘The People Bank’。

CALL CountCom1('The People Bank',@count);
SELECT @count;

5.创建一个函数CityByName, 其作用是返回姓名为‘Shelby’的员工所居住的城市city。

CREATE FUNCTION CityByName () RETURNS VARCHAR (30)
BEGIN
DECLARE city VARCHAR (30);
SET city = (
	SELECT
		employee.city
	FROM
		employee
	WHERE
		employee_name = 'Shelby'
);
RETURN city;
END;

SELECT
	CityByName ();

如果运行出现了err[1418]的报错则输入下列语句。报错原因是设置了函数必须有参数。

set global log_bin_trust_function_creators=TRUE;

6.创建一个函数CountCom2，输入变量为公司的名字（company_name），输出为该公司中员工的个数。

CREATE FUNCTION CountCom2 (company_name VARCHAR(30)) RETURNS INT
BEGIN
DECLARE count INT;
SET count = (
	SELECT
		COUNT(employee_ID)
	FROM
		works AS w
	WHERE
		w.company_name = company_name
);
RETURN count;
END;

SELECT
	CountCom2 ('Baidu');

7.调用函数CountCom2，其输入参数为‘The People Bank’。

SELECT CountCom2('The People Bank')

8.分别查看存储过程CountCom1的状态和定义。

SHOW PROCEDURE STATUS LIKE 'CountCom1';
SHOW CREATE PROCEDURE CountCom1;

9.分别查看函数CountCom2的状态和定义。

SHOW FUNCTION STATUS LIKE 'CountCom2';
SHOW CREATE FUNCTION CountCom2;

10.删除存储过程CountEmp。

DROP PROCEDURE CountEmp

思考题：
（1）存储过程和函数有什么区别？

1. 存储过程是SQL语句和可选控制流语句的预编译集合，而函数是由一个或多个 SQL语句组成的子程序；
2. 存储过程可以在单个存储过程中执行一系列SQL语句，而自定义函数有诸多限制；
3. 执行方式不同

（2）存储过程中可以调用其他存储过程吗？

可以。

DROP PROCEDURE IF EXISTS test1;
CREATE PROCEDURE test1 ()
BEGIN
	CALL AvgSal ();
	CALL CountCom1 ('Baidu',@count);
	SELECT @count;
END;
CALL test1();