Loading [MathJax]/jax/output/CommonHTML/config.js
前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >数据库中的存储过程、游标、触发器与常用的内置函数

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

作者头像
用户10175992
发布于 2022-11-21 02:18:04
发布于 2022-11-21 02:18:04
1.5K00
代码可运行
举报
文章被收录于专栏:辰远辰远
运行总次数:0
代码可运行

目录

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)语法:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
#创建存储过程
delimeter //
create procedure 存储过程名(
in 参数名 类型,
out 参数名 类型,
...
)
begin
执行的一组语句
end //
delimeter ;
#删除存储过程
drop procedure 存储过程名
#调用
call 存储过程名(参数列表)

(2)示例

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
#示例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 使用语法

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
declare
    cursor 游标 is select语句;
begin
open 游标;
loop
    fetch cur into item;
    if cur%notfound then
        exit;
    end if;
    ......
    end loop;
    close 游标;
end;

2.3 示例

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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;

游标参数与循环游标:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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)触发器的定义语法:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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 种触发器

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
#(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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
#删除触发器
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 触发器,把被删除的行数据保存在一个存档表中

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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字段的值总是大写的

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
#删除触发器
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 文本函数

left(str,len)/right(str,len)

返回左边的字符串

length(str)

返回字符串长度

lower(str)/upper(str)

转换为小写/大写

substring(str,pos,len)

返回字符串str的位置pos起len个字符

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格式]:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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)

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
SELECT DATEDIFF(NOW(), '2019‐2‐28')
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2022-11-17,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
Mysql之存储过程(下)
存储过程是由一组 SQL 语句构成的数据库对象,存储过程可以被存储在数据库中并在需要时执行。它支持控制流结构(如 IF 语句、CASE 语句、循环等),能够封装和重用数据库操作,提升代码的模块化和可维护性。
猫咪-9527
2025/04/29
1460
Mysql之存储过程(下)
Java面试之数据库常用语句
>id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, //id值,无符号、非空、递增——唯一性,可做主键。
黄桂期
2018/07/26
8620
MySQL视图,存储过程和触发器的使用
在视图创建后,可以用表的基本操作来使用视图,进行SELECT,WHERE,ORDER BY,联结等操作。视图仅仅是用来查看存储在别处的数据的一种工具而不是一个表,本身存储数据。
小末快跑
2019/07/03
1.2K0
【数据库原理与运用|MySQL数据库】MySQL存储函数和触发器
MySQL存储函数(自定义函数),函数一般用于计算和返回一个值,可以将经常需要使用的计算或功能写成一个函数。
小小程序员
2023/02/24
2.2K0
【数据库原理与运用|MySQL数据库】MySQL存储函数和触发器
存储过程和触发器
       存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。
用户9979303
2022/10/28
8320
MySQL 系列教程之(十二)扩展了解 MySQL 的存储过程,视图,触发器
如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图,可能会发现性能下降得很厉害。
ruochen
2021/08/17
1.1K0
MySQL 系列教程之(十二)扩展了解 MySQL 的存储过程,视图,触发器
MySQL 进阶之存储过程/存储函数/触发器
上面给大家演示了存储过程中的基本语法,现在只是在存储过程中定义了一条简单的select 语句 ,并没有任何逻辑。
叫我阿杰好了
2022/11/07
2.5K0
MySQL 进阶之存储过程/存储函数/触发器
SQL Server数据库:存储过程与触发器操作
  本文介绍基于Microsoft SQL Server软件,实现数据库存储过程与触发器的创建、执行、修改与删除等操作。
疯狂学习GIS
2025/03/27
2100
SQL Server数据库:存储过程与触发器操作
MySQL存储过程_触发器_游标——Baidu Comate
红目香薰
2024/05/26
1240
MySQL存储过程、函数、视图、触发器、索引和锁的基本知识
视图存储了查询,当调用的时候会生成查询语句对应的结果集,一个视图可以看成是一个虚拟的表。
青山师
2023/05/05
1.2K0
什么?还不了解MySQLl存储过程与触发器的创建使用?
或许你曾经去面试的时候被问到过关于mysql数据库的存储过程和触发器的问题,如果你还不懂可以看下这篇关于存储过程和触发器的文章,希望能帮助到有需要的朋友。
双面人
2019/04/10
8650
什么?还不了解MySQLl存储过程与触发器的创建使用?
数据库技术:MySQL 多表,外键约束,数据库设计,索引,视图,存储过程触发器,数据控制,数据备份与恢复
MySQL: Multi-Table, Foreign Key and Database Design
RendaZhang
2020/09/08
2.4K0
SQL视图、存储过程、触发器
视图(view)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的。 通俗的讲,视图只保存了查询的SQL逻辑,不保存查询结果。所以我们在创建视图的时候,主要的工作就落在创建这条SQL查询语句上。
海盗船长
2023/10/11
4310
oracle数据库安全,事务机制,触发器和存储过程
一、数据库安全机制 如果任何用户都可以随便查看和操作你的数据,那么数据的安全性将不复存在,可以通过限制用户操作权限防止数据被窃取、读脏和篡改。 1、创建用户 username为用户名,keyword为密码 create user username identified by keyword; 2、修改用户密码 username为用户名,keyword为密码 alter user username identified by keyword; 3、oracle系统角色 oracle系统角色 connect
lonelydawn
2018/02/09
1.6K0
SQLserver数据库之存储过程、游标等操作(3)
1.赋值 --简单赋值 declare @a int set @a=5 print @a --使用select语句赋值 declare @b nvarchar(50) select @b='张三' print @b declare @b1 nvarchar(50) select @b1=EmpName from student where EmpId=18 print @b1 --使用update语句赋值 declare @b2 nvarchar(50) update student set @b2=
闻语博客
2021/01/21
8920
MySQL 之 视图、触发器、存储过程、函数、事物与数据库锁
浏览目录: 1.视图 2.触发器 3.存储过程 4.函数 5.事物 6.数据库锁 7.数据库备份 1.视图 视图:是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据 视图有如下特点;   1. 视图的列可以来自不同的表,是表的抽象和逻辑意义上建立的新关系。   2. 视图是由基本表(实表)产生的表(虚表)。   3. 视图的建立和删除不影响基本表。   4. 对视图内容的更新(添加、删除和修改)直接影响基本表。
人生不如戏
2018/04/10
1.6K0
MySQL 之 视图、触发器、存储过程、函数、事物与数据库锁
存储过程和触发器
2.举例 (数据表链接: spj库) ①创建存储过程p1,查询所有信息;运行之。
唔仄lo咚锵
2020/09/15
1.2K0
MySQL基础-变量/流程控制/游标/触发器
在MySQL数据库的存储过程和函数中,可以使用变量来存储查询或计算的中间结果数据,或者输出最终的结果数据
用户9645905
2023/04/02
1.6K0
MySQL基础-变量/流程控制/游标/触发器
MySQL 之视图、 触发器、事务、存储
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
py3study
2020/01/16
9240
SQLserver数据库创表、操作表以及存储过程、游标等操作
--创建数据库表以及插入数据 15:40:34 USE master go if exists(select * from sysdatabases where name='EmployeeSys') drop database EmployeeSys go CREATE DATABASE EmployeeSys ON ( NAME = ' EmployeeSys_data', --主数据文件的逻辑名 FILENAME = 'D:
小语雀网
2022/05/06
7980
推荐阅读
相关推荐
Mysql之存储过程(下)
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
本文部分代码块支持一键运行,欢迎体验
本文部分代码块支持一键运行,欢迎体验