创建存储

#举例1:创建存储过程select_all_date(),查看 emploees表的所有数据(空参数)

create procedure select_all_data()
begin
		SELECT * FROM employees;
end;

CALL select_all_data();
#
CREATE procedure avg_employee_salary1()
BEGIN
		SELECT AVG(salary)
		FROM employees	;	
end;

CALL avg_employee_salary1();

带OUT

#举例:创建存储过程show_min_salary(), 查看'emp'表的最低薪资, 并将最低薪资通过OUT返回

CREATE PROCEDURE show_min_salary1(OUT ms DOUBLE)
BEGIN
			SELECT MIN(salary) INTO ms
			FROM employees;
end;
#调用:
CALL show_min_salary1(@ms)
#查看变量:
SELECT @ms;
#IN
#创建存储过程show_someone_salary(),查看'emps'表的某个员工的薪资,并用in参数empname输入员工姓名
DESC employees
CREATE procedure show_someone_salary(IN empname VARCHAR(25))
BEGIN
			SELECT salary
			FROM employees
			where last_name = empname;
end;
#调用:
CALL show_someone_salary('Abel')

带 in 和 out

#创建存储过程show_someone_salary2(),查看'emps'表的某个员工的薪资,并用IN参数empname输入员工姓名,用OUT参数empsalary输出员工薪资.

CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DECIMAL(10,2))
BEGIN
				SELECT salary INTO empsalary
				FROM employees
				WHERE last_name = empname;
end;
#调用
SET @empname = 'Abel'
CALL show_someone_salary2(@empname,@empsalary);
#显示:
SELECT @empsalary;

带 INOUT

#创建存储过程show_mgr_name(),查询某个员工领导的姓名,并用inout参数'empname'输入员工姓名输出领导的姓名.
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN
			SELECT last_name
			FROM employees
			WHERE employee_id = (
					SELECT manager_id
					FROM employees
					WHERE last_name = empname
			);
			
			
end;
#调用
SET @empname = 'Abel';
CALL show_mgr_name(@empname);
#输出
SELECT @empname;

存储函数

#举例:创建一个存储函数,名称为:email_by_name,参数定义为空 #该函数查询Abel的email,并返回,数据类型为字符串型.

 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;
 #调用
 SELECT email_by_name()