计算机基础

数据库原理

发布日期: 2022-11-15

1. 存储过程概述

1.1 理解

存储过程(stored procedure),它是一组经过预先编译的SQL语句的封装。

执行过程:存储过程预先存储在Mysql服务器上,需要执行的时候,客户端只需要向服务器端发出调用存储过程的命令,服务器端就可以把预先存储好的这一系列SQL语句全部执行。

一旦存储过程被创建出来,使用它就像使用函数一样简单,直接通过调用存储过程即可,相较于函数,存储过程是没有返回值的。

1.2 分类

存储过程的参数类型可以是in、out、和inout

没有参数无参数无返回
仅仅带IN,也就是有参数无返回
仅仅带out,也就是无参数有返回
inout,也就是有参数也有返回。

create procedure 存储过程名(in|out|intout 参数名 参数类型...)
[characteristics...]
BEGIN
	存储过程体
END

characteristics表示创建存储过程时指定的对存储过程的约束条件,其取值信息如下:

LANGUAGE SQL:表示存储过程执行体是由SQL语句组成的,当前系统支持的语言为SQL
`[not]`DETERMINSTIC:指明存储过程执行的结果是否确定,DETERMINSTIC表示结果是确定的,每次执行存储过程的时候,相同的输入将会得到相同的输出,NOT DETERMINSTIC表示结果是不确定的,相同的输入可能得到不同的输出,如果没有指定人一个值,则默认为`NOT DETERMINSTIC`
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}:指明子程序使用SQL语句的限制

1.3 存储过程的创建与调用

一个典型的创建存储过程的SQL代码如下:

delimiter $
create procedure select_all_data()
begin
	select * 
	from student;
end $
delimiter;

存储过程的调用

call select_all_data();//此时就可以调用存储过程了

delimiter $
create procedure select_avg_salary
begin
	select avg(salary) from employees;
end$
delimiter;

带有返回类型的存储过程编写

delimiter $
create procedure show_min_salary(out ms Double(8,2))
begin
	select min(salary) into ms#将这个值写入ms
	from employees;
end$
delimiter;
#用户定义的变量
CALL show_min_salary(@ms);
#查看变量
select @ms;

带有输入参数的存储过程编写

delimiter $
create procedure show_some_salary(IN empname varchar(20))
begin
	select salary from employee
	where last_name = empname;
end$
delimiter;
#调用过程1
CALL show_some_salary("Jack");
#调用过程2
set @empname := "Jack";#赋值符号
CALL show_some_salary(@empname);

同时带有输入输出参数的存储过程编写

delimiter
create procedure show_someone_salary2(IN empname varchar(20),OUT empname double(8,2))
begin
	select salary into empname
	where last_name = empname;
end
delimiter;
#调用的过程
set @empname := "Jack";
#传入和传出,记得顺序不能乱
CALL show_someone_salary(@empname,@empsalary)
select @empsalary;

同一个参数,既是输入又是输出

delimiter
create procedure show_mgr_name(INOUT empname varchar(25))
begin
	# 单句子查询
	select mamager_id
	from employees
	where last_name = empname;
end
delimiter;

1.4 存储函数

语法分析

create function 函数名(参数名 参数类型...)
returns 返回值类型#一定有返回值。
[characteristics]
begin
	函数体#函数体中有returns语句
end

参数列表:指定参数是IN、OUT还是INOUT,FUNCTION总是默认为IN参数

returns type语句表示函数返回数据的类型

returns子句只能对FUNCTION做指定,对函数而言是强制的,它用来指定函数的返回类型。

select email_by_name();

带有参数的存储函数定义

set global log_bin_trust_function_creators = 1;
delimiter
create function show_something(emp_id INT)#入参
returns varchar(25)
begin
	return (select * from employee where employee_id = emp_id)
end
delimiter;

存储函数可以放在查询语句中使用,存储过程则不行,存储过程的功能更加强大,它能够执行对表的操作和事务操作,这些功能是存储函数所不具备的。

1.5 查看存储过程

使用show create语句来查看存储过程和函数的创建信息

show create {(procedure)|(function)} 函数名;

使用show status查看存储过程和函数的状态信息

show procedure status;
show function status;
show procedure status like 'show_max_salary';
show function status like 'show_max_time';

使用数据字典进行查询

select * from information_schema.Routines
where ROUTINE_NAME = 'show_max_time' and ROUTINE_TYPE = 'FUNCTION';

1.6 修改特性

alter {(procedure)|(function)} 存储过程或者函数名 
[约束的情况];

1.7 存储过程的优缺点

存储过程可以一次编译而多次使用
可以减少开发的工作量
存储过程的安全性强,可以设定其使用的权限
可以减少网络的传输量,不需要传输sql而是直接调用服务器内部资源

缺点

可移植性差,不能跨数据库移植。换成其他数据库都需要重新编写

调试困难,只有少数的DBMS支持存储过程的调试

存储过程的版本管理很困难,比如说数据表的索引发生变化了,可能会导致存储过程的失效,而我们在开发软件的时候往往需要进行版本管理,而存储过程本身没有版本控制,版本迭代的时候将会很麻烦。

不适合高并发。高并发的场景需要减少数据库的压力,有时候数据库会采用分库分表的方式,而且对可扩展性要求很高,在这种情况下,存储过程会变得难以维护,增加数据库的压力。

2. 条件与处理程序

定义条件是事先定义程序执行过程中可能遇到的问题,处理程序定义了在遇到问题的时候应该采取的处理方式,并且保证存储过程或者函数在遇到警告或者错误时能够继续执行,避免程序异常停止运行。

2.1 定义条件

declare 错误名称 condition for 错误码(错误条件)
declare Field_Not_Be_Null condition for 1048;
declare Field_Not_Be_Null condition for sql state '23000';

2.2 定义处理程序

declare 处理方式 handler for 错误类型 处理语句

处理方式

continue:表示遇到错误不处理,继续执行
exit:表示遇到错误马上退出
undo:表示遇到错误后撤回之前的操作

错误类型

SQLSTATE 字符串错误码：表示长度为5的sqlstate_value类型的错误代码
MYSQL_error_code:匹配数值类型的错误代码
错误名称:表示declare ... condition定义的错误条件名称
SQLWARNING:匹配所有以01开头的SQLSTATE错误代码
NOT FOUND:匹配所有以02开头的SQLSTATE的错误代码
SQLEXCEPTION:匹配所有没有被SQLWARNING或者NOTFOUND捕获的SQLSTATE错误代码

处理语句

如果出现上述条件之一,则采用对应的处理方式,并执行指定的处理语句,语句可以是像set 变量 = 值这样的形式的语句。

# 捕获sqlstate_value
declare continue handle for 1146 set @info = 'NO_SUCH_TABLE';
declare continue handle for sqlstate '23000' set @info = 'NO_SUCH_TABLE';

2.3 分支结构IF

示例程序

delimiter
create procedure testif()
begin
	declare stu_name varchar(15);
	if stu_name is not null
		then select * from student where name = stu_name; 
	end if
	declare email varchar(25) default 'aaa';#相当于给email赋初值
	if email is not null
		then select * from student where stu_email = email
	elseif email between '20000' and '30000'
		then select * from student;
	elseif email between '30001' and '40000'
		then select * from student;
	else 
		select * from student;
	end if
end
delimiter;

简单来说就是if-then、elseif then、else

2.4 分支结构CASE

case语法结构

case 表达式
when 值1 then 结果1或者执行语句(语句需要加分号)
when 值2 then 结果2或者执行语句(语句需要加分号)

case-when

delimiter
create procedure test_case()
begin
	declare var int default 2;
	case var
		when 1 then select * from student;
		when 2 then select * from teacher;
		else select * from employee;
	end case;
end
delimiter;

第二种情况

delimiter
create procedure test_case()
begin
	declare var int default 10;
	case var
		when var >= 100 then select * from student;
		when var >= 10  then select * from teacher;
		else select * from employee;
	end case;
end
delimiter;

2.5 三种循环结构

LOOP循环语句用来重复执行某些语句,LOOP内的语句一致重复执行直到循环退出使用LEAVE子句,跳出循环的过程

[loop_label:]LOOP
	循环执行的语句
END LOOP[loop_label]

入门程序

delimiter
create procedure test_loop()
begin
	declare num int default 1;
	loop_label:LOOP
		set num = num + 1;
		if num >= 10
			then leave loop_label;
		end if;
	end loop loop_label;
	select num;
end
delimiter;

while循环

delimiter
create procedure test_loop()
begin
	while num <= 10 DO
		#循环体
		#迭代提交
	end while
end
delimiter;

repeat结构

delimiter
create procedure test_loop()
begin
	repeat
		update employee set salary = salary *1.15;
		select avg(salary) into avg_salary from employee;
		until avg_salary >= 13000
	end repeat;
end
delimiter;

iterate

delimiter
create procedure test_loop()
begin
	while num <= 10 DO
		#循环体
		if num >= 10
			then iterate;
        end if;
		#迭代提交
	end while
end
delimiter;

2.6 游标

游标提供了一种灵活的技术,它使得我们能够对结果集中的每一条记录进行定位,并对指向的记录中的数据进行操作的数据结构,游标让SQL这种面向集合的语言有了面向过程开发的能力。

在SQL中，游标是一种临时的数据库对象,可以指向数据库表中的数据行指针,这里游标充当了指针的作用,我们可以通过操作游标来对数据行进行操作。

游标是一种临时的数据库对象,可以指向存储在数据库表中的行指针,这里游标充当了指针的作用,可以通过操作游标来对数据行进行操作。游标可以结合存储过程和存储函数进行使用。

如何使用游标?

声明游标

使用declare关键字进行游标的声明

declare cursor_name cursor is select_statement;

打开游标

当我们定义好游标之后,如果想要使用游标,那么必须要打开游标,打开游标的时候select语句的查询结果集就会送到游标工作区,为后面游标的逐条读取结果集中的记录做准备。

open cursor;

使用游标

fetch cursor_name into var_name[,var_name]...

这句的作用是使用cursor_name这个游标来读取当前行,并且将数据保存到var_name这个变量中,游标指针自动知道下一行,如游标读取的数据行有多个列名,则在into关键字后面复制给多个变量名即可

注意:var_name必须在声明游标之前就定义好。

游标查询结果集中的字段数,必须跟into后面的字段名保持一致,否则在存储过程执行的过程中会导致错误。

关闭游标

close cursor;

如果你不关闭的话,那么就会导致内存泄漏。

入门程序

delimiter
create procedure get_count_by_limit(in limit_total_salary double,out total_count int)
begin
	#定义局部变量
	declare sum_salary double default 0;
    declare emp_sal double;
    declare emp_count int default 0;//累计人数
	#1.声明游标
	declare emp_cursor cursor for select salary from employees order by salary desc;
	#2.打开游标
	open emp_cursor;
	#3.使用游标
	while sum_salary <= 15000 do
			fetch emp_cursor into emp_sal;
			set sum_salary = sum_salary + emp_sal;
			emp_count = emp_count +1;
	end while;
	# 4.关闭游标
	set total_count = emp_count;
	clsoe emp_cursor;
end
delimiter;

全局变量的持久化

在Mysql的数据库中,全局变量可以通过set global语句进行设置,但是这个变量只会临时生效,在服务器重启之后,服务器又会从Mysql配置文件中读取变量的默认值。

使用set global语句设置的变量值只会临时生效,重启后会从mysql配置文件中读取变量的默认值。

mysql8.0新增了set persist命令,例如

set persist global max_connections = 1000

Mysql会将该命令的配置保存到数据目录下的mysqld-auto.cnf文件中,下次启动时会读取该文件,用其中的配置来覆盖默认的配置文件。

3. 变量

3.1 变量的分类

Mysql变量分为系统变量(以@@)开头,与用户自定义变量,用户自定义变量分为用户会话变量以@开头和局部变量,没有@开头的

用户会话变量

用户会话变量与会话系统变量相似,他们都与当前的session有密切的关系,简单地讲,Mysql客户机1定义的会话变量,会话期间,该会话变量一直有效,Mysql客户机2不能访问Mysql客户机1定义的会话变量。Mysql客户机1关闭或者Mysql客户机1与服务器断开连接后,Mysql客户机1定义的所有会话变量将会自动释放。

会话系统变量与用户会话变量的共同之处在于:变量大小写不敏感,用户会话以@开头,而会话系统变量以量@开头,会话系统变量无需定义可以直接使用

一般情况下,用户会话变量的定义与复制会同时进行,用户会话变量的定义与复制有两种语法,使用set命令或者使用select语句

4. 触发器

关于Mysql的触发器的定义

create trigger if not exists trigger_name
			   trigger_time trigger_event
			   on tabl_name for each row
			   [trigger_order]
			   trigger_body

trigger_time{before|after}
trigger_event:{insert|update|delete}
trogger_order:{follows|precedes}other_trigger_name

删除触发器

drop trigger trigger_name;

在trigger_body中可以直接使用new或者old指代进行更新的元组对象

穿山甲

https://lumxi.github.io/2022/11/15/dbs/dbs-(5)/

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源穿山甲 !

计算机基础

区块链基本问答题

2022-11-21 区块链

web3.0

数据库原理-中级SQL

2022-11-14 数据库原理

计算机基础

数据库原理-高级SQL

1. 存储过程概述

1.1 理解

1.2 分类

1.3 存储过程的创建与调用

1.4 存储函数

1.5 查看存储过程

1.6 修改特性

1.7 存储过程的优缺点

2. 条件与处理程序

2.1 定义条件

2.2 定义处理程序

2.3 分支结构IF

2.4 分支结构CASE

2.5 三种循环结构

2.6 游标

3. 变量

3.1 变量的分类

4. 触发器

你的赏识是我前进的动力