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

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

#示例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;

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

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;

CREATE TRIGGER trigger_name
    trigger_time
    trigger_event ON table_name
    FOR EACH ROW trigger_statement

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

#删除触发器
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;

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;

#删除触发器
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;

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

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