数据库中的存储过程、游标、触发器与常用的内置函数

目录

1 存储过程（本节使用MySQL描述）

1.1 什么是存储过程

（1）概念

（2）作用

1.2 存储过程的定义

（1）语法：

（2）示例

 2 游标（本节使用Oracle描述）

2.1 什么是游标

2.2 使用语法

2.3 示例

3 触发器

3.1 什么是触发器

（1）触发器（trigger）

（2）触发器的定义语法：

（3）MySQL中可以创建 6 种触发器

3.2 示例（本节使用MySQL描述）

（1）after insert 触发器，在插入数据之后获得@id变量以显示最新的自增长ID

（2）after delete 触发器，把被删除的行数据保存在一个存档表中

（3）before update 触发器，确保更新后name字段的值总是大写的

4 常用的数据库内置函数

 4.1 文本函数

4.2 日期/时间函数

---

1 存储过程（本节使用MySQL描述）

1.1 什么是存储过程

（1）概念

存储过程（Stored Procedure）是是数据库中的一个重要对象，是一组为了完成特定功能 的SQL 语句的集合，存储在数据库中，经过第一次编译后调用不需要再次编译，用户通过 指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

（2）作用

存储过程吧处理封在容易使用的单元中，简化复杂的操作； 确保大家都使用到统一的代码； 独立授权，简化管理，增加安全性； 编译执行，提高性能。

1.2 存储过程的定义

（1）语法：

#创建存储过程
delimeter //
create procedure 存储过程名(
in 参数名 类型,
out 参数名 类型，
...
)
begin
执行的一组语句
end //
delimeter ;
#删除存储过程
drop procedure 存储过程名
#调用
call 存储过程名(参数列表)

（2）示例

#示例1 简单存储过程
#删除存储过程
DROP PROCEDURE album_avg_pricing;
#创建存储过程
DELIMITER // #delmiter 切换 // 作为分隔符
CREATE PROCEDURE album_avg_pricing()
BEGIN
SELECT AVG(price) avg_price FROM album;
END //
DELIMITER ; #delmiter 还原 ; 作为分隔符
#调用存储过程
CALL album_avg_pricing();
#示例2 带输入参数
#删除
DROP PROCEDURE album_stocking;
#创建
DELIMITER //
CREATE PROCEDURE album_stocking(
albumId INT,
amount INT
)
BEGIN
UPDATE album SET stock=stock+amount WHERE id=albumId;
END //
DELIMITER ;
#调用
CALL album_stocking(1,15);
#示例3 输出参数
#删除
DROP PROCEDURE album_pricing;
#创建
DELIMITER //
CREATE PROCEDURE album_pricing(
OUT min_price DECIMAL(8,2),
OUT max_price DECIMAL(8,2),
OUT avg_price DECIMAL(8,2)
)
BEGIN
SELECT MIN(price) INTO min_price FROM album;
SELECT MAX(price) INTO max_price FROM album;
SELECT AVG(price) INTO avg_price FROM album;
END //
DELIMITER ;
#调用
CALL album_pricing(@min, @max, @avg);
SELECT @min,@max,@avg;
#示例4 混合参数
#删除
DROP PROCEDURE order_total_qty;
#创建
DELIMITER //
CREATE PROCEDURE order_total_qty(
IN order_id INT,
OUT total_qty INT
)
BEGIN
SELECT SUM(quantity) INTO total_qty FROM orderdetail WHERE
orderId=order_id;
END //
DELIMITER ;
#调用
CALL order_total_qty(1, @qty);
SELECT @qty;

 2 游标（本节使用Oracle描述）

2.1 什么是游标

游标是一组查询结果集中的行级指针。在查询结果集中，通过游标可以单独针对一行数据执 行操作，也可以前进或后对一行或多行。

2.2 使用语法

declare
    cursor 游标 is select语句;
begin
open 游标;
loop
    fetch cur into item;
    if cur%notfound then
        exit;
    end if;
    ......
    end loop;
    close 游标;
end;

2.3 示例

游标的创建、打开、循环获取和关闭：

declare
    cursor cur is select * from movie;
    item movie%rowtype;
begin
    open cur;
    loop
        fetch cur into item;
        if cur%notfound then
            exit;
        end if;
        dbms_output.put_line(item.title);
    end loop;
    close cur;
end;

游标参数与循环游标：

declare
    cursor cur(vid number) is select m.*, c.name cname from movie m
        inner join category c on m.categoryid=c.id
        where categoryid=vid;
begin
    for record in cur(2) loop
    dbms_output.put_line(to_char(record.cname) || ',' ||
    record.title);
    end loop;
end;

3 触发器

3.1 什么是触发器

（1）触发器（trigger）

是一种数据库对象，用于监控某些语句，在满足定义条件时触发， 并执行触发器中定义的一组语句。

（2）触发器的定义语法：

CREATE TRIGGER trigger_name
    trigger_time
    trigger_event ON table_name
    FOR EACH ROW trigger_statement

trigger_name： 触发器的名称

tirgger_time： 触发时机，为BEFORE或者AFTER

trigger_event： 触发事件，为INSERT、DELETE或者UPDATE

table_name： 表示建立触发器的表明，就是在哪张表上建立触发器

trigger_stmt： 触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条 语句

（3）MySQL中可以创建 6 种触发器

#(1)BEFORE INSERT
#(2)BEFORE DELETE
#(3)BEFORE UPDATE
#(4)AFTER  INSERT
#(5)AFTER  DELETE
#(6)AFTER  UPDATE

3.2 示例（本节使用MySQL描述）

（1）after insert 触发器，在插入数据之后获得@id变量以显示最新的自增长ID

#删除触发器
DROP TRIGGER new_genre;
#添加触发器
CREATE TRIGGER new_genre
AFTER INSERT ON genre
FOR EACH ROW
SELECT new.id INTO @id;
#引发触发器
INSERT INTO genre VALUES(0,'123','123');
SELECT @id;

注意：在insert触发器内，可以引用一个名为new的虚拟表，访问被插入的行。

（2）after delete 触发器，把被删除的行数据保存在一个存档表中

CREATE TABLE genre_bak( id INT, NAME VARCHAR(120), description TEXT);
#删除触发器
DROP TRIGGER delete_genre;
#添加触发器
CREATE TRIGGER delete_genre
AFTER DELETE ON genre
FOR EACH ROW
INSERT INTO genre_bak(id, NAME, description)
VALUES(old.id, old.name, old.description);
#引发触发器
DELETE FROM genre WHERE id=6;

注意：在delete触发器内，可以引用一个名为old的虚拟表，访问被删除的行

（3）before update 触发器，确保更新后name字段的值总是大写的

#删除触发器
DROP TRIGGER update_genre;
#添加触发器
CREATE TRIGGER update_genre
BEFORE UPDATE ON genre
FOR EACH ROW
SET new.name=UPPER(new.name);
#引发触发器
UPDATE genre SET NAME='abc' WHERE id=8;

注意：在update触发器内，可以引用一个名为old的虚拟表，访问更新前的行；new一个名为的虚拟表，访问新更新的值

4 常用的数据库内置函数

MySQL中预定义了很多数据处理函数：https://www.cnblogs.com/xuyulin/p/5468102.html

 4.1 文本函数

 

4.2 日期/时间函数

（1）now() 

  返回当前日期和时间

（2）year(date) / month(date) / day(date) / hour(data) / minute(date) / second(date) 

  返回日期date的年 / 月 / 日 / 时 / 分 / 秒

（3）DayOfWeek()

  返回日期date对应的星期几（1代表星期日）

（4）AddDate(date,interval expr type) / SubDate(date,interval expr type)

在date的基础上添加expr时间间隔，如：SELECT ADDDATE(NOW(), INTERVAL 1 DAY);

在date的基础上减去expr时间间隔 ，如：SELECT SUBDATE(NOW(), INTERVAL 1 DAY);

[type值 含义 期望的expr格式]:

second 秒 seconds
minute 分钟 minutes
hour 时间 hours
day 天 days
month 月 months
year 年 years
minute_second 分钟和秒 "minutes:seconds"
hour_minute 小时和分钟 "hours:minutes"
day_hour 天和小时 "days hours"
year_month 年和月 "years‐months"
hour_second 小时, 分钟， "hours:minutes:seconds"
day_minute 天, 小时, 分钟 "days hours:minutes"
day_second 天, 小时, 分钟, 秒 "days

（5）DateDiff(date1， date2)

   计算两个日期之差，例如: 

SELECT DATEDIFF(NOW(), '2019‐2‐28')