视图、MySQL的存储过程

奇见疯

已于 2023-02-18 10:48:06 修改

阅读量351

点赞数

文章标签： mysql 数据库 sql

于 2023-02-18 09:54:29 首次发布

本文链接：https://blog.csdn.net/m0_75278009/article/details/129017110

版权

文章目录

视图
MySQL的存储过程

视图

介绍

视图（view）是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用。
数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。
查看视图的方法和查看表的方法相同，都是select

作用

简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用。
安全原因，如果一张表中有很多数据，很多信息不希望让所有人看到，此时可以使用视图视，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，可以对不同的用户，设定不同的视图。

视图的创建语法为

create [or replace] [algorithm = {undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded| local] check option]

参数说明：

（1）algorithm：可选项，表示视图选择的算法。

（2）view_name ：表示要创建的视图名称。

（3）column_list：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。

（4）select_statement：表示一个完整的查询语句，将查询记录导入视图中。

（5）[with [cascaded | local] check option]：可选项，表示更新视图时要保证在该视图的权限范围之内。with check option保证了通过视图进行的修改，必须也能通过该视图看到修改后的结果。（就是通过修改视图修改原表后刷新后在视图中可以看到数据被修改了）其使用可以看博主 greedy-hat 的博客
链接: 视图中with check option的作用是什么？

创建一张简单的视图

CREATE VIEW view1 AS SELECT*FROM db1.employee;

查看表和视图

show full tables;

修改视图

修改视图是指修改数据库中已存在的表的定义。
当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。
MySQL中通过CREATE OR REPLACE VIEW语句和ALTER VIEW语句来修改视图。

语法

（1）create or replace view 视图名 as select 语句
（2）alter view 视图名 as select语句

CREATE OR REPLACE VIEW view1 AS SELECT dname,ename,salary FROM db1.`employee`;
ALTER VIEW view1 AS SELECT*FROM db1.employee

更新视图

某些视图是可更新的。也就是说，可以在UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。
对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。

但如果视图包含下述结构中的任何一种，那么它就是不可更新的：

聚合函数（SUM(), MIN(), MAX(), COUNT()等）
DISTINCT
GROUP BY
HAVING
UNION或UNION ALL
位于选择列表中的子查询
JOIN
FROM子句中的不可更新视图
WHERE子句中的子查询，引用FROM子句中的表。
仅引用文字值（在该情况下，没有要更新的基本表）

视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。

重命名视图（和更新表名的方法一致）

语法
rename table 视图名 to 新视图名;

rename table view1_emp to my_view

删除视图

语法
drop view [if exists] 视图名[,视图名…];

drop view if exists view_student;

删除视图时，只会删除视图的定义，不会删除原表数据

MySQL的存储过程

概述

MySQL 5.0 版本开始支持存储过程。
简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法。
存储过就是数据库 SQL 语言层面的代码封装与重用。

语法

delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名数据类形…)
begin
sql语句
end 自定义的结束符合
delimiter ;

数据准备

CREATE TABLE dept(
	deptno INT PRIMARY KEY,
  dname VARCHAR(20),
	loc VARCHAR(20)
);
INSERT INTO dept VALUES(10, '教研部','北京'),
(20, '学工部','上海'),
(30, '销售部','广州'),
(40, '财务部','武汉');

CREATE TABLE emp(
	empno INT PRIMARY KEY,
	ename VARCHAR(20),
	job VARCHAR(20),
	mgr INT,
	hiredate DATE,
	sal NUMERIC(8,2),
	comm NUMERIC(8, 2),
	deptno INT,
-- 	FOREIGN KEY (mgr) REFERENCES emp(empno),
	FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO emp VALUES
(1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, NULL, 20),
(1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30),
(1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30),
(1004, '刘备', '经理', 1009, '2001-4-02', 29750.00, NULL, 20),
(1005, '谢逊', '销售员', 1006, '2001-9-28', 12500.00, 14000.00, 30),
(1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, NULL, 30),
(1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, NULL, 10),
(1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, NULL, 20),
(1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000.00, NULL, 10),
(1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30),
(1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, NULL, 20),
(1012, '程普', '文员', 1006, '2001-12-03', 9500.00, NULL, 30),
(1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, NULL, 20),
(1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, NULL, 10);

CREATE TABLE salgrade(
	grade INT PRIMARY KEY,
	losal INT,
	hisal INT
);
INSERT INTO salgrade VALUES
(1, 7000, 12000),
(2, 12010, 14000),
(3, 14010, 20000),
(4, 20010, 30000),
(5, 30010, 99990);

创建一个存储过程

DELIMITER $$
CREATE PROCEDURE store1()
BEGIN
SELECT*FROM emp;
END $$
DELIMITER ;

调用存储过程

语法

call 存储过程名();
或
call 存储过程名;

删除存储过程

语法

drop procedure 存储过程名;

变量定义之局部变量

局部变量：用户自定义，在begin/end块中有效

语法

声明变量
declare var_name type [default var_value];

如

declare nickname varchar(32);

给局部变量赋值

语法

set 局部变量名=值;

具体操作

DELIMITER $$
CREATE PROCEDURE proc02()
BEGIN
    DECLARE var_name01 VARCHAR(20) DEFAULT 'aaa';  -- 定义局部变量
    SET var_name01 = 'zhangsan';  -- 给局部变量var_name01赋值
    SELECT var_name01;
END $$
DELIMITER ;
-- 调用存储过程
CALL proc02();

MySQL 中还可以使用 SELECT…INTO 语句为变量赋值。其基本语法如下：

select col_name […] into var_name[,…]
from table_name wehre condition

其中：

col_name 参数表示查询的字段名称；
var_name 参数是变量的名称；
table_name 参数指表的名称；
condition 参数指查询条件。

注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列。

DELIMITER $$
CREATE PROCEDURE store3()
BEGIN
	DECLARE NAME VARCHAR(20) DEFAULT '张三';
	SELECT ename INTO NAME FROM emp WHERE empno=1001;
	SELECT NAME;
END $$
DELIMITER ;
CALL store3;

变量定义之用户变量

用户自定义，当前会话（连接）有效（类比java的成员变量）

语法（不需要提前声明，使用即声明）

@var_name

SET @name='张三';
DELIMITER $$
CREATE PROCEDURE store4()
BEGIN
SELECT @name;
END $$
DELIMITER ;
CALL store4; -- 李四  6B
select @name; -- 李四  6B

变量定义之系统变量

系统变量介绍

系统变量又分为全局变量与会话变量
全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。
会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。
也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。
全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。
有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。

系统变量——全局变量

由系统提供，在整个数据库有效。

语法

@@global.系统变量名

操作

查看所有全局变量

SHOW GLOBAL VARIABLES;

查看某全局变量

-- 查看全局变量中自增长的值
SELECT @@global.auto_increment_increment;

修改全局变量的值
语法

set global 全局变量名=值;
或
set @@global.全局变量名=值;

-- 修改全局变量中自增长的值
SET GLOBAL auto_increment_increment=2;
SET @@global.auto_increment_increment=1;

系统变量——会话变量

由系统提供，当前会话（连接）有效。

语法

@@session.系统变量名

操作

查看所有会话变量

SHOW SESSION VARIABLES;

查看某会话变量

-- 查看局部变量中自增长的值
SELECT @@session.auto_increment_increment;

修改会话变量的值

-- 修改会话变量中自增长的值
SET @@session.auto_increment_increment=2;
SET SESSION auto_increment_increment=1;

存储过程传参——in

概述

in 表示传入的参数，可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。

DELIMITER $$
CREATE PROCEDURE store1(IN in_parameter VARCHAR(20))-- 设置传入参数类型
BEGIN
SELECT*FROM emp WHERE empno=in_parameter;
END $$
DELIMITER ;
-- 传参并调用
CALL store1(1001);

我们也可以定义传入多个参数

DELIMITER $$
CREATE PROCEDURE store2(IN in_parameter1 VARCHAR(20),IN in_parameter2 DECIMAL(6,2))
BEGIN
SELECT*FROM emp WHERE empno=in_parameter1 AND sal=in_parameter2;
END $$
DELIMITER ;
CALL store2(1001,8000)

存储过程传参——out

概述

out 表示从存储过程内部传值给调用者。

注意：

输出的时候，需要在存储方法中指定，并@变量名，最后才能让这个变量接收到数值。
其次输出参数的定义的时候，也和输入参数一样的，out 参数名类型(长度)。
同理，我们也可以传入多个out 参数。

操作

DELIMITER $$
CREATE PROCEDURE store3(IN in_parameter INT,OUT out_parameter VARCHAR(20))
BEGIN
SELECT ename INTO out_parameter FROM emp WHERE empno=in_parameter;
END $$
DELIMITER ;
-- 传参并调用
CALL store3(1001,@name);
-- 查询传出的数值
SELECT @name;

存储过程传参——inout

概述

inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）。
简单来说。就是inout可以当in，也可以当out。

操作

DELIMITER $$
CREATE PROCEDURE store4(INOUT in_parameter INT,INOUT out_parameter VARCHAR(20))
BEGIN
SELECT ename INTO out_parameter FROM emp WHERE empno=in_parameter;
END $$
DELIMITER ;
CALL store3(1001,@name);
SELECT @name;

in，out，inout小总结

in 输入参数，意思说你的参数要传到存储过程的过程里面去，在存储过程中修改该参数的值不能被返回
out 输出参数:该值可在存储过程内部被改变，并向外输出
inout 输入输出参数，既能输入一个值又能传出来一个值

流程控制——判断

概述

IF语句包含多个条件判断，根据结果为TRUE、FALSE执行语句，与编程语言中的if、else if、else语法类似

语法格式如下

if 条件1 then 结果1
[elseif 条件2 then 结果2] …
[else 最终结果]
end if

DELIMITER $$
CREATE PROCEDURE store6(IN score DECIMAL(4,1))
BEGIN
IF score BETWEEN 90 AND 100 THEN SELECT '优';
ELSEIF score BETWEEN 60 AND 90 THEN SELECT '良';
ELSE SELECT '不及格';
END IF;
END $$
DELIMITER ;
-- 调用存储过程
CALL store6(59); -- 不及格

流程控制——case

语法

– 语法一（类比java的switch）：
case case_value
when when_value then statement_list
[when when_value then statement_list] …
[else statement_list]
end case
– 语法二：
case
when search_condition then statement_list
[when search_condition then statement_list] …
[else statement_list]
end case

DELIMITER $$
CREATE PROCEDURE store7(IN score DECIMAL(4,1))
BEGIN
CASE 
WHEN score BETWEEN 90 AND 100 THEN SELECT '优';
WHEN score BETWEEN 60 AND 90 THEN SELECT '良';
ELSE SELECT '不及格';
END CASE;
END $$
DELIMITER ;
CALL store7(59);

流程控制——循环

概述

循环是一段在程序中只出现一次,但可能会连续运行多次的代码。
循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环。

循环分类

while
repeat
loop

循环控制

leave 类似于 break，跳出，结束当前所在的循环
iterate类似于 continue，继续，结束本次循环，继续下一次

流程控制——循环——while

语法

【标签:】while 循环条件 do
循环体;
end while【标签】;

准备数据

-- 创建测试表
CREATE TABLE USER (
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR (50),
    PASSWORD VARCHAR (50)
);

存储过程与while循环结合

DELIMITER $$
CREATE PROCEDURE store1()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=6 DO
	INSERT INTO USER VALUES(NULL,'zhangsan','123456');
	SET i=i+1;
END WHILE;
END $$
DELIMITER ;
-- 调用
CALL store1();

存储过成与while + leave结合（此处可以看出标签的作用是方便leave跳出循环）

DELIMITER $$
CREATE PROCEDURE store2(IN insertcount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    label:WHILE i<=insertcount DO
        INSERT INTO USER VALUES(NULL,CONCAT('user-',i),'123456');
        IF i=5 THEN LEAVE label;
        END IF;
        SET i=i+1;
    END WHILE label;
END $$
DELIMITER ;
-- 调用
CALL store2(10);

流程控制——循环——repeat

语法

[标签:]repeat
循环体;
until 条件表达式
end repeat [标签];

操作

DELIMITER $$
CREATE PROCEDURE store4()
BEGIN
DECLARE i INT DEFAULT 1;
REPEAT 
INSERT INTO USER VALUES(NULL,CONCAT('user-',i),'123456');
SET i=i+1;
UNTIL i=5
END REPEAT;
END $$
DELIMITER ;
-- 调用
CALL store4();

流程控制——循环——loop

语法

[标签:] loop
循环体;
if 条件表达式 then
leave [标签];
end if;
end loop;

DELIMITER $$
CREATE PROCEDURE store1()
BEGIN
DECLARE i INT DEFAULT 1;
label: LOOP
INSERT INTO USER(uid,username,`password`) VALUES(NULL,CONCAT('user-',i),'123456');
SET i=i+1;
IF i=5 THEN LEAVE label;
END IF;
END LOOP ;
END $$
DELIMITER ;
-- 调用
CALL store1;

游标

概述

游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。
光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE。

语法

声明语法

declare cursor_name cursor for select_statement

打开语法

open cursor_name

取值语法

fetch cursor_name into var_name [, var_name] ...

关闭语法

close cursor_name

DELIMITER $$
CREATE PROCEDURE store3()
BEGIN 
DECLARE NAME VARCHAR(20);
DECLARE job VARCHAR(20);
DECLARE cursor_name CURSOR FOR
SELECT 
t1.ename,t1.Job
FROM emp t1,dept t2
WHERE t1.deptno=t2.deptno;
OPEN cursor_name;
label:LOOP
FETCH cursor_name INTO NAME,job;
SELECT NAME,job;
END LOOP label;
CLOSE cursor_name;
END $$
DELIMITER ;
-- 调用
CALL store3();

句柄

概述

MySql存储过程也提供了对异常处理的功能：通过定义HANDLER来完成异常声明的实现。
官方文档

语法

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement
 
handler_action: {
    CONTINUE
  | EXIT
  | UNDO
}
 
condition_value: {
    mysql_error_code
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION

注意：在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

需求

输入一个部门名，查询该部门员工的编号、名字、薪资，将查询的结果集添加游标

DELIMITER $$
CREATE PROCEDURE store4(IN in_dname VARCHAR(50))
BEGIN
  -- 定义局部变量
    DECLARE var_empno INT;
    DECLARE var_ename VARCHAR(50);
    DECLARE var_sal DECIMAL(7,2);
    
    DECLARE flag INT DEFAULT 1;
    
    -- 声明游标
    DECLARE my_cursor CURSOR FOR
        SELECT empno,ename,sal
        FROM dept a, emp b
        WHERE a.deptno = b.deptno AND a.dname = in_dname;
    
    -- 定义句柄，当数据未发现时将标记位设置为0
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 0;   
    -- 打开游标
    OPEN my_cursor;
    -- 通过游标获取值
    label:LOOP
        FETCH my_cursor INTO var_empno, var_ename,var_sal;
        -- 判断标志位
        IF flag = 1 THEN
            SELECT var_empno, var_ename,var_sal;
        ELSE
            LEAVE label;
        END IF;
    END LOOP label;
    
    -- 关闭游标
    CLOSE my_cursor;
END $$;
DELIMITER ;
CALL store4('销售部');

奇见疯

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
视图、MySQL的存储过程

局部变量：用户自定义，在begin/end块中有效语法声明变量如给局部变量赋值语法set 局部变量名=值;具体操作-- 定义局部变量 SET var_name01 = 'zhangsan';-- 给局部变量var_name01赋值 SELECT var_name01;-- 调用存储过程 CALL proc02();MySQL 中还可以使用 SELECT…INTO 语句为变量赋值。其基本语法如下：其中：col_name 参数表示查询的字段名称；
复制链接

扫一扫