【MySQL期末复习】2-2 视图（view）、存储过程（procedure）、函数（function）

一、视图

视图是一种虚拟的表，本身不存储数据，占用空间极少。在使用sql操作视图时，所有数据都是从其他表中查出来的。

创建

语法为：CREATE VIEW <视图名> AS <SELECT语句>;

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;-- condition 就是条件的意思hhh

使用

使用方法与表的使用方法无差异

SELECT empno, ename FROM view_test;
SELECT * FROM view_test;
SELECT * FROM view_test WHERE ename like 'M%';

修改

view（视图）支持修改操作。

• 方式一

CREATE OR REPLACE VIEW v_student AS SELECT SId,Sname,Sage from student;

•  方式二

ALTER VIEW v_student AS SELECT SId,Sname,Sage from student;

删除

DROP VIEW view_name;

注意：在创建视图中包含聚合函数、DISTINCT 去重、GROUP BY 分组、HAVING 筛选、UNION ALL 查询结果合并的时候，视图不可进行更新。

二、存储过程（procedure）

创建

简易模板如下：

delimiter ## -- 自定义一个分隔符，在过程中临时使用

create procedure Yukino()
begin
	
    -- 一些语句，比如……
    select * from emp;
	select * from salary;

end ## -- 结束存储过程

delimiter ; -- 把分隔符改回来

 调用

call Yukino();

删除

drop procedure if exists Yokino; 
drop procedure Yokino; 

变量

举例如下：

变量分为用户变量和局部变量；

局部变量的定义与输出

局部变量是只在 begin 和 end 之间起作用的变量，所有有时也被叫做过程变量。

delimiter ##

	create procedure Hachima()
	begin
		declare a int default 0; 
        -- 设置int类型的变量a的默认值为0（如果不给默认值，则为null）

        set a = 1; -- 对a进行赋值
        select a; -- 输出a
	end ##

	delimiter ;

 

用户变量

用户变量根据其可生效的客户端，分为全局变量和会话变量。全局变量对所有客户端生效，会话变量仅对当前会话生效。

会话变量：

SET @@global.autocommit=0; --设置全局的自动提交变量为0 也即关闭自动提交
SET GLOBAL autocommit = 1; --同上

SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

全局变量：  

SET @myvar = 'hello world';-- 定义会话变量
SELECT @myvar; -- 输出该变量

删除会话变量

关闭会话后，所有会话变量会被清除。(●'◡'●) 

 

in/out 的使用

drop procedure if exists in_out_test;
delimiter ##
create procedure in_out_test(in a int,in b int,out c int)
begin 
	set c = a + b;
end ##

delimiter ;

set @res = 0;
call in_out_test(1,2,@res);
select @res;
drop procedure if exists in_out_test;

inout的使用（是 inout 不是 in/out ！） 

drop procedure if exists inout_test;
delimiter ##
create procedure inout_test(in a int,inout ans int)
begin 
	set ans = a + ans;
end ##

delimiter ;

set @ans = 2;
call inout_test(1,@ans);
select @ans;
drop procedure if exists inout_test;

inout 可把变量传入再带回，而 in 或 out 后面跟的变量只能完成其对应的一项功能。

三、函数（function）

用法示例如下：

CREATE FUNCTION annual_salary(salary INT) 
RETURNS INT
BEGIN
    RETURN salary * 12;
END;

drop function if exists sum_ab;
delimiter ##

create function sum_ab(a int,b int)
returns int -- 返回值的类型
deterministic
begin 
	declare ans int;
	set ans = a + b;
    return ans;
end ##

delimiter ;

select sum_ab(2,3);
drop function if exists sum_ab;

deterministic关键字

deterministic 关键字用于指示函数是确定性的，即对于给定的输入参数，它总是返回相同的结果。这允许 MySQL 对于具有相同输入参数的函数调用进行缓存，并在下一次调用时直接返回缓存中的结果，从而提高查询效率。如果不指定 deterministic 关键字，MySQL 将默认将函数标记为非确定性的，这可能会导致性能下降。

需要注意的是，在使用 deterministic 关键字时，应确保函数的执行结果仅由其输入参数决定，并且在函数的执行过程中不会访问任何外部资源，例如数据库表格、文件系统等。否则，即使将函数标记为确定性的，MySQL 也可能会重新计算函数的结果，而不是使用缓存中的值。

——ChatGPT

如何将function或procedure的结果插入到表格当中？

假设我们有

• 一个名为 employees 的表格，其中包含了员工的姓名（name）和工资（salary）；
• 一个根据employees的salary计算年薪的函数 annualSalary()；
• 一个目标表格 employee_salary；

则操作如下：

INSERT INTO employee_salary(name, annualSalary)
SELECT name, annualSalary(salary)
FROM employees;