动态SQL，通过变量实现动态更新、删除记录

mysql中prepare、execute、deallocate

MySQL官方将prepare、execute、deallocate统称为PREPARE STATEMENT，即预处理语句

mysql预处理，可以防止注入攻击；能够通过占位符的方式，按照自己的指令安全的操作数据库以及数据库中的记录

• prepare 进行预处理
• execute 执行预处理SQL
• deallocate 解除预处理SQL

一、更新记录

创建set_col_value执行过程，通过传入表名、字段名、字段值和条件，进行记录的更新操作

delimiter $$  -- 界定符，即SQL语句的结束符。mysql中默认为英文分号';'
DROP PROCEDURE IF EXISTS set_col_value;
# 创建执行过程：通过动态SQL来更新记录
CREATE PROCEDURE set_col_value  
        (in_table   VARCHAR(128),  -- 表名
        in_column  VARCHAR(128),  -- 更新的字段
        in_new_value VARCHAR(1000),  -- 更新在字段值
        in_where   VARCHAR(4000))  -- 条件
    BEGIN
        DECLARE l_sql VARCHAR(4000);  -- 声明 sql 语句 变量
        # 拼接 sql 语句
        SET l_sql=CONCAT_ws(' ',  
           'UPDATE',in_table, -- 需要更新的表
             'SET',in_column,'=',in_new_value, -- 更新 in_column 字段的值为 in_new_value
           ' WHERE',in_where  -- 更新条件
        );
        SET @sql=l_sql;
        PREPARE s1 FROM @sql;  -- 预处理 SQL
        EXECUTE s1;  -- 执行 SQL
        DEALLOCATE PREPARE s1;  -- 释放 SQL
    END $$;
-- 调用
CALL set_col_value('DEPT','DNAME',"\'技术部\'",'DEPTNO=26');

二、删除记录

同理，创建执行过程，传入表名和条件来删除相对应的记录

DELIMITER $$
DROP PROCEDURE IF EXISTS DELETE_VALUES;
CREATE PROCEDURE DELETE_VALUES(IN_TABLE VARCHAR(100),IN_WHERE VARCHAR(5000))
    BEGIN
        DECLARE EXC_SQL VARCHAR(4000);  -- 声明被执行的sql变量
        # 拼接 sql 语句
        SET EXC_SQL = CONCAT_ws(
            ' ',
            'DELETE','FROM',IN_TABLE,  -- 需要删除记录的表
            'WHERE',IN_WHERE  -- 删除的条件
            );
        SET @EXC_SQL = EXC_SQL;
        PREPARE TO_EXC_SQL FROM @EXC_SQL;  -- 预处理sql
        EXECUTE TO_EXC_SQL;  -- 执行 预处理SQL
        DEALLOCATE PREPARE TO_EXC_SQL;  -- 接触预处理sql
    END;
# 调用执行过程
CALL DELETE_VALUES('EMP','EMPNO=10');

三、测试的数据

# 创建数据库 ------- START
CREATE DATABASE DynamicSQL;
USE DynamicSQL;
# ------- END

# 创建表 ------- START
-- 创建部门表
DROP TABLE IF EXISTS DEPT;
CREATE TABLE DEPT(
    `DEPTNO` INT(11) NOT NULL AUTO_INCREMENT COMMENT '部门编号',
    `DNAME` VARCHAR(32) NOT NULL COMMENT '部门名称',
    `LOC` VARCHAR(200) DEFAULT 'CHINA' COMMENT '部门所在地',
    PRIMARY KEY DEPT(DEPTNO)
)AUTO_INCREMENT=01;

-- 创建职员表
DROP TABLE IF EXISTS EMP;
CREATE TABLE EMP(
    `EMPNO` INT(11) NOT NULL AUTO_INCREMENT UNIQUE COMMENT '雇员的编号',
    `ENAME` VARCHAR(32) NOT NULL COMMENT '雇员的名字',
    `JOB` VARCHAR(32) NOT NULL COMMENT '雇员的的职位',
    `MGR` INT(11) NULL COMMENT '上级主管编号',
    `HIREDATE` DATETIME NOT NULL COMMENT '入职(受雇)日期',
    `SAL` FLOAT NOT NULL DEFAULT 2000 COMMENT '薪金',
    `COMM` FLOAT NOT NULL DEFAULT 0 COMMENT '佣金',
    `DEPTNO` INT(11) NOT NULL COMMENT '部门编号',
    PRIMARY KEY EMP(EMPNO),
    FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
)AUTO_INCREMENT=1001;
# ------- END
SHOW TABLES;  -- 查看表是否创建成功
SHOW FULL COLUMNS FROM DEPT;  -- 查看DEPT表结构
SHOW FULL COLUMNS FROM EMP;  -- 查看EMP表结构

# 插入记录 ------- START
-- 插入部门表记录
INSERT INTO DEPT
VALUES
    (10,'总经理办公室','100'),
    (11,'CEO办公室','101'),
    (12,'行政部','102'),
    (13,'财务部','103'),
    (14,'管理部','104'),
    (21,'生产技术部','201'),
    (22,'人力资源部','202'),
    (23,'计划营销部','203'),
    (24,'安全监察部','204'),
    (25,'党群工作部','205'),
    (26,'后勤部','205'),
    (30,'股东会','300'),
    (31,'董事会','301'),
    (32,'监事会','302')
;
-- 插入职员表记录
INSERT INTO EMP
VALUES
    (01,'林青霞','总裁',NULL,'1997-01-01',100000,0,11),
    (02,'张玉漫','副总裁',01,'2000-01-01',80000,1000,11),
    (03,'郭富城','副总裁',01,'2003-03-01',80000,1000,11),
    (04,'刘德华','销售总经理',02,'2000-05-01',50000,0,10),
    (05,'古天乐','财务总经理',02,'2005-05-01',50000,800,10),
    (06,'陈奕迅','办事员',03,'2009-03-01',1000,800,30),
    (07,'张三','办事员',03,'2014-05-01',800,500,32),
    (08,'李四','保安',03,'2000-05-01',1500,200,22),
    (09,'Susan','前台',03,'2006-05-01',2200,90,13),
    (11,'WuSir','销售',03,'2018-05-01',2200,80,13),
    (12,'小晓','前台组长',NULL,'2002-02-02',5000,10000,11),
    (13,'李丽','前台副组长',NULL,'2003-02-02',4500,10000,11),
    (14,'Bob','技术部组长',NULL,'2008-02-02',1200,30000,11),
    (10,'Alex','临时督导',NULL,'2020-02-02',3000,10000,11)
;