视图、储存过程、函数 e3

JamSlade

已于 2022-11-14 12:54:37 修改

阅读量283

点赞数

分类专栏： DataBase 文章标签：数据库 sql mysql

于 2022-10-28 10:16:15 首次发布

本文链接：https://blog.csdn.net/JamSlade/article/details/127316304

版权

DataBase 专栏收录该内容

25 篇文章 11 订阅

订阅专栏

view

用于创建动态表的静态定义。
可以定义多个表的行列组合

一个虚拟的表，结构数据建立在对表的查询基础上。
视图中数据不需要像表、索引那样占用空间。
数据愿意索引表or其他视图

优点

保护数据安全
为不同用户分配不同视图
简化操作
隐藏表的连接操作
集中分散数据
提高数据逻辑独立性
程序建立在视图上。让程序与结构实现逻辑分离

创建视图

要针对视图create view权限，针对select语句每一列的权限
对于select语句其他地方使用的列，要有select权限

如果有or replace子句，视图要有drop权限

语法格式

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

or replace:可选，指定oe place子句
语句用于换数据库已有的同名视图，需要有DROP权限
algorithm：规定MySQL处理视图算法，默认为undefined
view_name 指定视图名称，名称唯一，不可与table or view 重名
column_list 将视图每个列指定名称，数目要等与select出来的列数，之间用逗号分隔
select_statment 指定创建视图的select 语句，给出视图定义
with check point 指定可更新视图上的修改要符合select_Statement指定的限制条件
可以确保数据修改后可以通过视图看修改后的数据，默认为cascaded，决定检查测试范围，对所有试图检查，local只对定义的视图检查

例子

create view student_view1
as select * from student；
# 创建单源表视图

create view student_view2（sname,cname,grade）
as select sname,cname,grade
from student ,course,sc
where student.sno=sc.sno and course.cno=sc.cno;
# 包含学生姓名、课程名以及课程所对应的成绩

多源，student,course,sc创建视图命名scs_view

create view scs_view（sno,sname,cname,grade,credit）
as select sno,sname,cname,grade,credit
from student,course,sc
where student.sno=sc.sno and c.cno=sc.cno;

已有视图上创建新视图

再scs_view创建新的视图

create view scs_view1
as 
select * from scs_view
where scs_view.cname = 'MySQL';

创建带表达式的图
视图可以产生派生属性列（比如ave，count产生的数据），再基本表中并不存在，称为虚拟列，带有虚拟列的图叫带表达式的视图

create view student_birthyear（sno,sname,birthyear）
as 
select sno,sname,2010-sage
from student;

含分组统计信息的视图

create vie student_Ave (sno, avggrade)
as
select sno, avg(grade) from sc
group by sno;

注意事项

创建视图需要用户有create view的权限
若有[or replace] 需要有drop view权限
select 语句不包含from子句中的子查询(5.7版本前
select不能引用系统、用户变量
select不可以引用预处理语句参数
存储子程序，定义不能引用子程序参数或局部变量
表or视图必须存在，创建视图后，可以舍弃定义引用的表或视图
定义不能引用临时表，不能创建临时视图
视图定义中命名表必须存在
不能将触发程序关联视图
视图定义允许order by

查看视图

describe view_name;

show table status like 'view_name';

show create view 'view_name';

select * from information_schema.views where 
table_name='view_name';

修改视图

基本表元素改变，可以改视图让视图和基本表保持一致

alter [alorithm = {undefined | merge | temptable}]
	view view_name [(column_list)]
	as select_statement
	[with [casaded | local] check option];

各个参数等价于create view

删除视图

drop view view_name;

使用视图更新数据

insert \ update \ delete数据

可以对基于两个以上基表 or 视图的视图进行修改，但不可以同时影响多个基表，一次只能修改一个基表
不可以修改通过计算得到的列
创建的时候有with check option 使用视图修改数据的时候要满足限制条件

缺点
数据库视图查询数据可能会慢
将根据基础表创建一个视图，每次更改关联表结构的时候要更改视图

process of storage and function

储存过程与函数是经过编译储存在数据库中的SQL语句

优点

MySQL储存过程按需编译，有助于减少APP和数据库服务器之间的流量。
储存程序对任何APP可重用、透明。
储存过程是安全的
有助于提高应用程序性能

缺点

使用大量储存过程，会导致连接的内存使用量骤增
储存让复杂业务逻辑储存更加困难。MySQL不提供调试储存过程的功能
开发维护不易，移植困难

储存过程or函数的基本语法

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

-- proc_parameter: [ IN | OUT | INOUT ] param_name type
-- func_parameter: param_name type(Any valid MySQL data type)

--characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

--routine_body:
Valid SQL procedure statement or statements

说明

存储子程序的权限问题。
¤ 创建存储子程序需要CREATE ROUTINE权限。
¤ 修改或移除存储子程序需要ALTER ROUTINE权限。这个权限自动授予子程序的创建者。
¤ 执行子程序需要EXECUTE权限。然而，这个权限自动授予子程序的创建者

RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

子程序与当前数据库关联。要明确地把子程序与给定数据库关联起来，在创建子程序时指定其名字为db_name.sp_name。

¤ 当一个子程序被调用时，一个隐含的USE db_name 被执行（当子程序终止时停止执行）。存储子程序内的USE语句是不允许的。

¤ 可以使用数据库名限定子程序名。这可以被用来引用一个不在当前数据库中的子程序。比如，要引用一个与test数据库关联的存储程序p或函数f，可以CALL test.p()或test.f()。

¤ 数据库移除的时候，与它关联的所有存储子程序也都被移除。

参数说明
¤ 由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列( )。

¤ 每个参数默认都是一个IN参数。要指定为其它参数，可在参数名之前使用关键词 OUT或INOUT

¤ 指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。

n IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
n OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
n INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

¤ FUNCTION参数总是被认为是IN参数

Characteristic特性说明

¤ CONTAINS SQL表示子程序包含SQL进行读或写数据的语句。NO SQL表示子程序不包含SQL语句。READS SQL DATA表示子程序仅包含读数据的语句，但不包含写数据的语句。MODIFIES SQL DATA表示子程序仅包含写数据的语句。如果这些特征没有明确给定，默认的是CONTAINS SQL。

¤ SQL SECURITY特征可以用来指定子程序该用创建子程序者的许可来执行，还是使用调用者的许可来执行。默认值是DEFINER。

¤ COMMENT子句是一个MySQL的扩展，它可以被用来描述存储程序。这个信息被SHOW CREATE ROCEDURE和 SHOW CREATE FUNCTION语句来显示。

过程体说明

¤ MySQL允许子程序包含DDL语句，如CREATE和DROP。MySQL也允许存储过程（但不是函数）包含SQL 交互select语句

¤ 函数不可以包含那些做明确的和绝对的提交或者做回滚的语句

¤ 存储过程不能使用LOAD DATA INFILE。

¤ 返回结果包的语句不能被用在存储函数中

¤ 其它语句：块语句、选择、循环等等

存储过程或函数说明

¨ BEGIN … END复合语句块：

[begin_label:] BEGIN
	[statement_list]
	END [end_label]

¤ 存储子程序可以使用BEGIN … END来包含多个语句。

¤ statement_list代表一个或多个语句的列表，每个语句都必须用分号（；）来结尾。

¤ 复合语句可以被标记。除非begin_label存在，否则end_label不能被给出，并且如果二者都存在，他们必须是同样的。

一些函数

--修改语法
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL 
DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string

说明：如果要重新完整的定义已有的存储过程，建议采用先删除该存储过程后，然后再进行创建。

--删除语法
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

IF EXISTS子句是一个MySQL的扩展。如果程序或函数不存储，它防止发生错误。

--查看存储过程或者函数
¨SHOW CREATE {PROCEDURE | FUNCTION} sp_name
SHOW CREATE PROCEDURE getrecord;

¨SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
SHOW PROCEDURE status like ‘getrecord’;

--¨查看系统表information_schema.Routines
SELECT * FORM Routines where routine_name=‘getrecord’;

使用变量

DECLARE var_name[,...] type [DEFAULT value]

DECLARE仅被用在BEGIN … END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。

DEFAULT子句指定默认值（常量或表达式），不指定则初始值为NULL。

SET var_name = expr [, var_name = expr] ...

SET语句可以同时给多个变量赋值`

SELECT col_name[,...] INTO var_name[,...] table_expr

把选定的多个字段直接存储到变量。
只有一条记录的字段可以被取回。

控制流

IF语句

if expression then
	statements;
elseif ei-expression then
	ei-statements;
else 
	e-statements;
end if;

CASE语句

case epression
	when expression1 then commands
	when expression2 then command
	...
	else command
end case;

¨ 当将单个表达式与唯一值的范围进行比较时，简单CASE语句比IF语句更易读。另外，简单CASE语句比IF语句更有效率
¨ 当根据多个值检查复杂表达式时，IF语句更容易理解¨
如果选择使用CASE语句，则必须确保至少有一个CASE条件匹配。否则，需要定义一个错误处理程序来捕获错误。IF语句则不需要处理错误

v REPEAT语句

repeat
	statements;
until expression
end repeat

v WHILE语句

while expression do
	statements
end while;

v LOOP语句
v LEAVE语句
v ITERATE语句
¨ LEAVE语句用于立即退出循环，而无需等待检查条件。LEAVE语句的工作原理就类似PHP、C/C++、Java等其他语言的break语句一样
¨ ITERATE语句允许跳过剩下的整个代码并开始新的迭代。ITERATE语句类似于PHP、C/C++、Java等中的continue语句
¨ MySQL还有一个LOOP语句，它可以反复执行一个代码块，可以使用循环标签达到灵活性

定义条件和处理

能够事先定义程序执行过程中有可能遇到的问题，并采用一定的机制解决相关问题，如继续、退出执行，并发出错误信息

--DECLARE条件的定义
DECLARE condition_name CONDITION FOR condition_value

--  condition_name：条件的名称
-- condition_value：可以取sqlstate_value或mysql_error_code，都表示MYSQL的错误代码。

--条件的处理
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type: CONTINUE | EXIT | UNDO(未支持)
-- CONTINUE：继续执行封闭代码块（BEGIN…END）
-- EXIT：处理程序声明封闭代码块的执行终止

condition_value: sqlstate_value | condition_name |SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code
--SQLWARNING是对所有以01开头的SQLSTATE代码的速记。
--NOT FOUND是对所有以02开头的SQLSTATE代码的速记。
--SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。

--捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1062 SET @x2 = 1;
-- 事先定义condition_name
DECLARE DuplicateKey CONDITION FOR SQLSTATE '23000';
DECLARE CONTINUE HANDLER FOR DuplicateKey SET @x2 = 1;
--捕获SQLEXCEPTION
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x2 = 1

JamSlade

关注

0
点赞
踩
1

收藏

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

能够事先定义程序执行过程中有可能遇到的问题，并采用一定的机制解决相关问题，如继续、退出执行，并发出错误信息--DECLARE条件的定义 DECLARE condition_name CONDITION FOR condition_value -- condition_name：条件的名称 -- condition_value：可以取sqlstate_value或mysql_error_code，都表示MYSQL的错误代码。
复制链接

扫一扫