重点知识学习(9.1)--[数据库视图,存储过程,函数,触发器]

---

1.数据库视图

---

之前学习数据库的时候,也仅学习了数据库的基本操作知识,便于开发使用,
当时大三上学期的数据库原理及应用那门课,也是学习了一部分数据库视图,存储过程,函数,以及触发器等知识,也进行了实际的实验操作.

可在视图中存入查询语句,可实现重复使用;

视图是基于查询的虚拟表。就是一条 SELECT 语句执行后返回的结果集。

SELECT 语句所查询的表就是视图的基表，而查询的结果集称为虚拟表，视
图本身并不存储具体的数据，视图的数据存在于视图的基表中，基本表数据发生
了改变，视图的数据也会跟着改变。

基本使用

在指定的数据表中创建视图

# 视图创建;
CREATE VIEW myadmininfo AS
SELECT * FROM t_admin 
LEFT JOIN t_admin_role  
ON t_admin.`id`=t_admin_role.`admin_id`

使用视图的信息

# 使用视图信息;
SELECT * FROM myadmininfo;

删除视图

# 删除视图;
DROP VIEW myadmininfo;

---

2.存储过程

---

存储过程—>编写在数据库中的 SQL 语句集。

如果实现用户的某些需求时，需要编写一组复杂的 SQL 语句才能实现，那么可以将这组复杂的 SQL 语句集编写在数据库中，由 JDBC 调用来执行这组 SQL语句。

• 存储过程（PROCEDURE）是事先经过编译并存储在数据库中的一段 SQL 语句的集合。
• 调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
• 存储过程类似于 JAVA 语言中的方法，需要先定义，使用时需要调用。
• 存储过程可以定义参数，参数分为 IN、OUT、INOUT。
  IN 类型表示接收调用者传入的数据，OUT 类型表示向调用者返回数据，
  INOUT类型既可以接收调用者传入的参数，也可以向调用者返回数据。

存储过程需要事先被编写好,存储在数据库中, 使用时直接调用即可,可进行逻辑处理;

优点: 处理某个逻辑的过程时,直接存储在数据库中, 运行速度较快

缺点: 对数据库依赖度高,移植性差.

---

存储过程的语法

create procedure 存储过程名（[in 变量名 类型，out 参数 2，…]）
begin
[declare 变量名 类型 [DEFAULT 值];]
存储过程语句块;
end;

具体说明:

• in :输入参数（默认使用 in 参数），表示该参数的值需要在调用程序指定。(即实参)
• out :输出参数，该参数的值经存储过程计算后，将 out 参数的计算结果返回至调用程序。
• inout : 输入参数/输出参数，该参数的值即可由调用程序指定，也可将 inout 参数的计算结果返回给调用程序。
• 存储过程中的语句需要放在 begin 和 end 中。
• declare 中可以声明变量,变量默认赋值使用 default,语句块中可改变变量值,使用 set 变量=值;

---

基本案例使用

存储过程案例(1)

# 存储过程练习;
DELIMITER $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `day20211126_manage_db`.`test1`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
	BEGIN
	#声明变量;
        DECLARE v_name VARCHAR(20) DEFAULT '';
        -- 变量赋值;
        SET v_name = 'xiaozhi';
        -- 测试;
        SELECT v_name; 
	END$$
DELIMITER ;

测试使用

-- 调用存储过程;
CALL test1();

---

存储过程案例(2) — 进行传参

例:根据菜单的传入菜单的类型查询菜单的数量;

-- 存储过程的入参和出参;
DELIMITER $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `day20211126_manage_db`.`type_count`(IN p_pypeid INT,OUT p_count INT)
	BEGIN
	-- 存储的查询语句;
	SELECT count(*) INTO p_count FROM t_menu WHERE TYPE = p_pypeid;
	SELECT p_count;
	END$$
DELIMITER ;

测试调用,例如查询类型为0的菜单业务数量;

-- 在一个存储过程中要调用另一个存储过程;
CALL type_count(0,@p_count);

---

存储过程案例(3) — 进行逻辑判断;

例,对于传入的参数进行判断,返回不同的值;

DELIMITER $$
CREATE   PROCEDURE  test2(IN p_num INT,OUT p_account VARCHAR(10))
  BEGIN
         IF p_num = 1  THEN 
           SET p_account  = "杰哥";
            SELECT p_account;     
         ELSEIF p_num = 2 THEN 
           SET p_account  = "小智";
            SELECT p_account;
         ELSE   
           SET p_account = "得斯";
           SELECT  p_account;  
         END IF; 
  END$$
DELIMITER ;

测试传参调用;

# 测试
CALL test2(1,@p_account);

---

存储过程之执行SQL时返回逻辑信息

例如:在用户注册时,判断当前账号是否存在,若存在就不去注册了;

DELIMITER $$
CREATE   PROCEDURE  toRegistAdmin(IN p_account VARCHAR(6),IN p_password VARCHAR(10),OUT p_result VARCHAR(10))
  BEGIN
       -- 声明变量,接收查询结果;
       DECLARE v_count INT DEFAULT 0;
       
       SELECT COUNT(*) INTO v_count FROM t_admin WHERE `account`= p_account AND `password`=p_password;
          IF v_count = 0 THEN
             INSERT INTO t_admin(account,PASSWORD)VALUES(p_account,p_password);
              SET p_result = "Ok,注册成功";
          ELSE 
             SET p_result = "抱歉,账号已存在";
             SELECT p_result;   
          END IF; 
  END$$
DELIMITER ;

测试执行;

# 测试注册;
CALL toRegistAdmin('小智RE0','123',@p_result);

再次测试执行

# 测试注册;
CALL toRegistAdmin('杰哥','123',@p_result);

---

在Mybatis中使用存储过程案例

比如用户在注册账号时,需要判断该账号是否存在,若存在就不注册且给出提示信息;若不存在就执行插入语句,注册账号;

当然,这个数据库首先要定义一个存储过程

DELIMITER $$
CREATE   PROCEDURE  toRegistUser(IN p_account VARCHAR(6),IN p_password VARCHAR(10),OUT p_result VARCHAR(10))
  BEGIN
       -- 声明变量,接收查询结果;
       DECLARE v_count INT DEFAULT 0;
       
       SELECT COUNT(*) INTO v_count FROM t_user WHERE `account`= p_account AND `password`=p_password;
          IF v_count = 0 THEN
             INSERT INTO t_user(account,PASSWORD)VALUES(p_account,p_password);
              SET p_result = "Ok,注册成功";
          ELSE 
             SET p_result = "抱歉,账号已存在";
             SELECT p_result;   
          END IF; 
  END$$
DELIMITER ;

在用户持久层UserMapper定义方法

//使用存储过程,传参判断当前管理员是否可以注册;
void toRegistUser(Map<String, String> map);

在映射文件中处理sql;

<!--参数集合,这里会声明存储过程,指定输入输出的参数-->
    <parameterMap id="storeProcedureStudy" type="map">
        <parameter property="account" jdbcType="VARCHAR" mode="IN"></parameter>
        <parameter property="password" jdbcType="VARCHAR" mode="IN"></parameter>
        <parameter property="result" jdbcType="VARCHAR" mode="OUT"></parameter>
    </parameterMap>

    <!--用户注册账号-->
    <insert id="toRegistUser" parameterMap="storeProcedureStudy" statementType="CALLABLE">
          {call toRegistUser(?,?,?)}
    </insert>

测试使用

/**
 * @author by 信计1801 李智青 学号:1809064012
 */
public class TestProcess {
    @Test
    public void method() {
        //使用工具类创建sqlSession;
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        //获取代理对象;
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        //定义用户;
        User user = new User("张三","888888");
        Map<String,String> map = new HashMap<>();
        map.put("account",user.getAccount());
        map.put("password",user.getPassword());
        //调用注册方法;
        mapper.toRegistUser(map);

        //返回的信息;
        System.out.println(map.get("result"));
    }
}

注册成功

再测试一个已经注册的用户

---

3.函数

---

类似于存储过程,主要用于查询

创建函数基本语法

create function 函数名([参数列表]) returns 数据类型
begin
DECLARE 变量；
sql 语句;
return 值;
end;

• 参数列表包含两部分：参数名 参数类型
• 函数体：肯定会有 return 语句，如果没有会报错
• 如果函数体中只有一句，则可省略begin end
• 使用 delimter 语句设置结束标记.

设置函数可以没有参数

SET GLOBAL log_bin_trust_function_creators=TRUE;

删除函数

DROP FUNCTION 函数名

---

案例(1):
例如;写个计算管理员数量的函数

#设置函数可以没有参数
SET GLOBAL log_bin_trust_function_creators=TRUE;

DELIMITER$$
CREATE FUNCTION test1() RETURNS INT
BEGIN
DECLARE v_num INT;
SELECT COUNT(*) INTO v_num FROM t_admin;
RETURN v_num;
END$$

调用

# 测试
SELECT test1() FROM DUAL;

---

案例(2)
例如还可以进行逻辑判断

例如,根据管理员表的type类型判断;

# 函数2;
DELIMITER$$
CREATE FUNCTION findAdminTypeById(p_typeId INT) RETURNS VARCHAR(10)
BEGIN
IF p_typeId = 0 THEN
RETURN '系统超管';
ELSE
RETURN '普通管理';
END IF;
END$$

测试

# 测试;
SELECT account,findAdminTypeById(TYPE) FROM t_admin

---

案例(3)

新闻类型表

新闻表

例如,这个函数会根据输入的参数值,判断出新闻的类型名称;

#函数3;
DELIMITER $$
CREATE  FUNCTION  findNewsType(p_type INT)  RETURNS VARCHAR(10)
   
    BEGIN
       DECLARE v_type VARCHAR(10) DEFAULT '';
        SELECT type_name INTO v_type FROM news_type WHERE  id = p_type;
       RETURN v_type;      
    END$$
DELIMITER ;

测试执行; 在查询新闻表时,查询出对应的新闻类型名称;

SELECT id,title,findNewsType(type_id) FROM news;

---

4.触发器

---

触发器是特殊的存储过程，不需要用户直接调用，只有对表添加、修改、删除之前或者之后自动执行.

特点:

• 与表相关联

触发器定义在特定的触发器表上

• 自动激活触发器

当执行 INSERT、UPDATE 或 DELETE 操作时，若已经对当前操作定义了触发器，那么该触发器就会自动执行，不可撤销。

• 不能直接调用

与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。

• 可作为事务的一部分

触发器与激活触发器的语句一起做为对一个单一的事务来对待，可以从触发器中的任何位置回滚。
就是说他能设置前置执行事件或者后置执行事件

---

基本创建语法

CREATE TRIGGER 触发器名称 触发时机 触发事件
ON 表名称
FOR EACH ROW -- 行级触发
BEGIN
语句
END;

• 触发器名称：用户自定义标识触发器。
• 触发时机：可以是 before 或 after [前置,后置]。
• 触发事件：可以是 insert，update 和 delete [添加,更新,删除];
• 表名称：建立触发器的表名，[在指定数据表上建立触发器];
• 语句：触发器程序主体，可使用 begin 和 end 作为开始和结束，中间包含多条语句；

---

案例练习

(1)在删除管理员信息时,触发前置sql,删除关联的信息;

例如搭建一个前置执行的触发器;
我们之前在删除信息时,由于使用外键,耦合度过高;首先要删除关联的信息,然后才能删除当前的信息;

管理员表;

管理员与角色的关联表;

若当前的管理员已经关联了角色;
是不能强行删除管理员信息的;

在之前的做法就是,需要配置事务;先删除关联信息,在删除管理员的信息;

---

而使用触发器的话,可以利用它的前置触发效果,
在删除管理员信息时就自动触发这个提前配置的触发器;达到快速删除的效果;
例如,配置触发器;

#前置触发器;
DELIMITER $$
CREATE
    TRIGGER delete_t_admin_role 
    BEFORE 
    DELETE
    ON t_admin
    FOR EACH ROW 
    BEGIN
           DELETE FROM t_admin_role WHERE admin_id = old.id;
    END$$
DELIMITER ;

注意,里面的old就是要删除的信息行;
FOR EACH ROW表示采用行级触发器;操作多行时,每行都会触发触发器;

测试,删除掉5号管理员

# 测试使用;
DELETE FROM t_admin WHERE id = 5

就很快啊,一下就删了

---

案例(2) 在注册添加管理员时,在日志表中添加信息;

创建后置执行触发器

#后置触发器
DELIMITER $$
CREATE
    TRIGGER registlog_admin_log
    AFTER 
    INSERT
    ON t_admin
    FOR EACH ROW 
    BEGIN
           INSERT  INTO  t_regist_logs(id,admin_account,regist_time) VALUES(new.id,new.account,now());
    END$$
DELIMITER ;

注意:里面的new就是要添加的管理员的信息行;

测试执行

#测试使用;
INSERT INTO t_admin(account,`password`,sex) VALUES('不悔','123','男');

在日志表也是自动填充了数据

---