Oracle、Mysql、Postgresql数据库存储过程和触发器总结

最新推荐文章于 2024-06-17 15:00:11 发布
最新推荐文章于 2024-06-17 15:00:11 发布
阅读量1.7k 收藏 9
点赞数
分类专栏： 数据库知识 文章标签： oracle postgresql mysql 存储过程 触发器
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/shuoyu816/article/details/81952580
版权

一、Postgresql
1、启用Postgresql中的UUID
CREATE EXTENSION "UUID-OSSP";
SELECT uuid_generate_v4();
2、创建测试表
-- Table: sys_user
CREATE TABLE sys_user
(
  id character varying(50) NOT NULL,
  name character varying(50),
  status character varying(20),
  CONSTRAINT user_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE sys_user
  OWNER TO postgres;

-- Table: sys_role
CREATE TABLE sys_role
(
  id character varying(50) NOT NULL,
  name character varying(50),
  type character varying(21),
  CONSTRAINT sys_role_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE sys_role
  OWNER TO postgres;

3、创建触发器函数
CREATE OR REPLACE FUNCTION user_function()
  RETURNS trigger AS
$BODY$
declare ii integer;
declare roletype character varying(20);
begin
    IF(TG_OP = 'INSERT') THEN
        INSERT INTO sys_role values(uuid_generate_v4(), '角色', '0');
        RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        IF(NEW.status = '1') THEN 
            INSERT INTO sys_role values(uuid_generate_v4(), '角色', '1');
            RETURN NEW;
        ELSIF (NEW.status = '2') THEN 
            ii = 1;
            FOR ii IN 1..2 LOOP
                INSERT INTO sys_role values(uuid_generate_v4(), '角色', '2');
            END LOOP;
            RETURN NEW;
        ELSIF (NEW.status = '3') THEN 
            roletype = '';
            FOR roletype IN (SELECT status FROM sys_user) LOOP
                INSERT INTO sys_role values(uuid_generate_v4(), '角色', roletype);
            END LOOP;
            RETURN NEW;
        END IF;
    END IF;
    RETURN NULL;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION user_function()
  OWNER TO postgres; 
 
4、创建触发器（sys_user）
CREATE TRIGGER user_trigger 
AFTER INSERT OR UPDATE OF status
ON sys_user
FOR EACH ROW
EXECUTE PROCEDURE user_function();

  
5、测试触发器
insert into sys_user values(uuid_generate_v4(), 'lisi', '0');
执行sql，sys_role表中增加一条type为0的数据。
update sys_user set status='1' where name='lisi';
执行sql，sys_role表中增加一条type为1的数据。
update sys_user set status='2' where name='lisi';
执行sql，sys_role表中增加二条type为2的数据。
update sys_user set status='3' where name='lisi';
执行sql，sys_role表中增加一条type为3的数据。

6、创建存储过程（Postgresql中存储过程和函数一样）
CREATE OR REPLACE FUNCTION user_insert() 
RETURNS integer AS $total$  
declare  
    total integer;
    ii integer default 1;
BEGIN
   FOR ii IN 1..2 LOOP
    insert into sys_user values(uuid_generate_v4(), 'lisi'||ii, '0');
   END LOOP;
   SELECT count(*) into total FROM sys_user;  
   RETURN total;
END;  
$total$ LANGUAGE plpgsql;

7、调用存储过程
select user_insert();

二、Mysql
1、创建测试表
-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
  `id` varchar(50) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `type` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
  `id` varchar(50) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、创建触发器
实列一
create trigger user_insert_trigger
after insert on sys_user 
for each row
begin
    INSERT INTO sys_role values(uuid(), '角色', '0');
end;

实列二
create trigger user_update_trigger
after update on sys_user 
for each row
begin
    declare i int default 0;
    if new.status='1' then 
        INSERT INTO sys_role values(uuid(), '角色', '1');
    elseif new.status='2' then 
        while i < 2 do
            INSERT INTO sys_role values(uuid(), '角色', '2');
            set i=i+1;
        end while;    
    end if;
end;

3、删除触发器
drop trigger user_insert_trigger;
drop trigger user_update_trigger;

4、测试触发器
insert into sys_user values(uuid(), 'lisi', '0');
执行sql，sys_role表中增加一条type为0的数据。
update sys_user set status='1' where name='lisi';
执行sql，sys_role表中增加一条type为1的数据。
update sys_user set status='2' where name='lisi';
执行sql，sys_role表中增加二条type为2的数据。

5、创建存储过程
create procedure user_procedure()
begin
    DECLARE name VARCHAR(32);
    DECLARE status VARCHAR(32);
    DECLARE s int DEFAULT 0;
 
    DECLARE user CURSOR FOR SELECT a.name, a.status from sys_user a ;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1; 
    
    open user;
        fetch user into name, status;
        while s<>1 do  
            INSERT INTO sys_role VALUES(uuid(), 'role', '5');
            fetch user into name, status;
        end while;
    close user;
end;

6、删除存储过程
drop PROCEDURE user_procedure;
7、调用存储过程
call user_procedure();

三、Oracle
1、创建测试表
CREATE TABLE "SYS_USER" 
   (    
    "ID" VARCHAR2(50), 
    "NAME" VARCHAR2(50), 
    "STATUS" VARCHAR2(20), 
    CONSTRAINT "SYS_USER_PRIMARY_KEY" PRIMARY KEY ("ID")
   )

CREATE TABLE "SYS_ROLE" 
   (  
  "ID" VARCHAR2(50), 
  "NAME" VARCHAR2(50), 
  "TYPE" VARCHAR2(20), 
  CONSTRAINT "SYS_ROLE_PRIMARY_KEY" PRIMARY KEY ("ID")
   )

2、创建触发器
实例一
create or replace trigger user_trigger     
 after insert on sys_user 
begin 
    insert into sys_role values((select sys_guid() from dual), '角色', '0');
end;
实列二
CREATE OR REPLACE TRIGGER "USER_TRIGGER" 
after insert or update or delete on sys_user
for each row
declare
      cursor c_role
      is
      select * from sys_role;
      temp_row c_role%rowtype;
begin
 if inserting then
    insert into sys_role values((select sys_guid() from dual), '角色', '0');
 elsif updating then
       if :new.status='1' then
          insert into sys_role values((select sys_guid() from dual), '角色', '1');
       elsif :new.status='2' then
          insert into sys_role values((select sys_guid() from dual), '角色', '2');
       elsif :new.status='3' then
          for temp_row in c_role loop
              insert into sys_role values((select sys_guid() from dual), '角色', temp_row.type);
          end loop;
       end if;
 elsif deleting then
       delete from sys_role where type='3';
 end if;
end;
3、删除触发器
drop trigger user_trigger;

4、测试触发器
insert into sys_user values((select sys_guid() from dual), 'lisi', '0');
执行sql，sys_role表中增加一条type为0的数据。
update sys_user set status='1' where name='lisi';
执行sql，sys_role表中增加一条type为1的数据。
update sys_user set status='2' where name='lisi';
执行sql，sys_role表中增加二条type为2的数据。
update sys_user set status='3' where name='lisi';
执行sql，sys_role表中所有数据重新插入。

5、游标介绍
FOR循环游标测试
declare
       cursor c_user
       is
       select * 
       from sys_user;
       c_row c_user%rowtype;
begin
       for c_row in c_user loop
         dbms_output.put_line(c_row.id||'-'||c_row.name||'-'||c_row.status);
       end loop;
end;
Fetch游标测试
declare 
       cursor c_user
       is
       select * from sys_user;
       c_row c_user%rowtype;
begin
       open c_user;
         loop
           fetch c_user into c_row;
           exit when c_user%notfound;
           dbms_output.put_line(c_row.id||'-'||c_row.name||'-'||c_row.status);
         end loop;
      close c_user;
end;

6、创建存储过程
create or replace procedure user_insert 
as
begin
       for  v_temp  in  1..2  loop
            insert into sys_user values((select sys_guid() from dual), 'lisi'||v_temp, '0');
       end loop;  
end;
7、调用存储过程
方式一
begin
user_insert;
end;
方式二-命令
execute user_insert;

朔语
关注
  • 0
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql、Oracle、PostgreSQL、Sqlserver等数据库操作工具Navicat_Premium_15.0.8.zip mac版本
02-26
Navicat Premium是一款强大的数据库管理工具，支持多种数据库系统，包括MySQL、Oracle、PostgreSQL和SQL Server等。这款软件提供了一站式的解决方案，使得数据库管理员和开发者能够在不同的数据库之间进行数据迁移、...
支持MySQL、Oracle、SQL Server、PostgreSQL的代码生成工具
06-10
对于Oracle，可能还会支持PL/SQL存储过程和触发器的生成。对于SQL Server，可能会有对Entity Framework的支持，同时包括ADO.NET的数据访问层代码。对于PostgreSQL，工具可能会提供对JPA或JOOQ等现代Java库的集成，...
数据库总结之触发器存储过程
我相信青春的力量！
08-24 798
1、索引总结：
【MySQL数据库】触发器和存储过程
EasyChillの博客
07-18 662
1、触发器 1.1 概念 个人理解：触发器就是：当某个你规定的事件发生以后触发的一些操作。 是一种特殊的存储过程。 百度百科的概念 1.2 语法 创建一条执行语句的语法： CREATE TRIGGER 触发器名 AFTER|BEFORE 触发事件 ON 表名 FOR EACH ROW 执行语句 创建多条执行语句的语法： CREATE TRIGGER 触发器名 AF...
数据库中存储过程，看这一篇就够了！！
最新发布
Z_CH8648的博客
06-17 1070
介绍存储过程是事先经过编译并存储在数据库中的一段 SOL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的存储过程思想上很简单，就是数据库 SOL 语言层面的代码封装与重用。有什么特点呢？封装，复用可以接收参数，也可以返回数据，减少网络交互，效率提升。注意:在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。
Mysql 存储过程、事件、触发器学习总结
彩虹哥哥的博客
01-29 618
1、存储过程是通过手动调用才会运行对应对象内容，创建存储过程的部分SQL语句如下： CREATE PROCEDURE procedure_name（param int(11)） BEGIN     SQL BODY END 其中，在最开始和最后面本来应该加上DELIMITER的，但是因为我使用的是MYSQL-Front，然后通过纯命令的方式去添加对应的存储过程，最后都以失败告终；不过，...
数据库视频总结四（存储过程和触发器）
宋文轩
05-10 496
1、存储过程 概念：它是一组未来完成特定功能的SQL语句集，经编译后存储在数据库中。存储过程可包含程序流，逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。 作用： 与其他程序共享应用程序逻辑，因而确保了数据访问和修改的一致性。 具有安全性和所有权链接，以及可以附件到它们的证书。 提供了安全机制。 允许模块化程序设计。 可以减少网络通信流量。 分类...
数据库存储过程与触发器
u014596135的博客
03-19 1223
数据库存储过程 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。 优点 ①重复使用。存储过程可以重复使用，从而可以减少数据库开发人员的工作量。 ②减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过
Oracle至PostgreSQL数据库迁移方案.pptx
10-14
Oracle至PostgreSQL数据库迁移方案是使用Ora2PG工具实现的，该工具是一个Perl语言编写的开源工具，用于将Oracle或MySQL数据库迁移到PostgreSQL数据库。下面是该方案的详细知识点： 一、Ora2PG简介 Ora2PG是一个...
dbForge.Studio.for(mysql,sql,Oracle,PostgreSQL)
12-08
dbForge Studio for Oracle 则针对Oracle数据库，提供了对PL/SQL的支持，包括PL/SQL调试、触发器管理、存储过程开发等功能，满足Oracle数据库开发的专业需求。 至于PostgreSQL，dbForge Studio for PostgreSQL 提供...
MySQL和PostgreSQL的对比选择.pdf
10-10
MySQL 和 PostgreSQL 是两种广泛使用的开源关系型数据库管理系统（RDBMS），它们各有特色，适用于不同的应用场景。MySQL由瑞典MySQL AB公司开发，后被Oracle公司收购，而PostgreSQL源自加州大学伯克利分校的研究项目...
Mysql Oracle Sql server 三种类型的存储过程
dreamparks的专栏
07-05 801
Oracle存储过程 1.通用查询 CREATE OR REPLACE PACKAGE CommonSelPack AS TYPE T_CURSOR IS REF CURSOR; PROCEDURE CommonSelect (     G_typeId number,     G_sum number,     G_column varchar2,
一天学会PostgreSQL应用开发与管理 - 7 函数、存储过程和触发器
weixin_33897722的博客
04-12 482
本章大纲 一、运算符与函数 1 逻辑运算 2 比较运算 3 算数 4 字符串 5 bytea 6 bit 7 规则表达式 8 日期、数字、字符串格式化输出 9 时间 10 枚举 11 几何 12 网络地址 13 全文检索 14 XML 15 JSON、JSONB 16 序列 17 条件表达式 18 数组 19 范围...
数据库存储过程总结
a3060858469的博客
02-04 1037
存储过程 1：创建存储过程create proc stu_proc1 as select count(*) from student exec stu_proc1 执行2：修改、删除、重命名alter proc stu_proc1 as select * from studentdrop proc stu_proc1 as select * from studentrename proc stu_
PostgreSQL 触发器 (trigger
weixin_33893473的博客
08-03 220
触发器：在一条INSERT, UPDATE, DELETE语句执行前，后的函数。（自己定义）。 PostgreSQL语法： CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE ...
触发器的使用细节（pgsql与oracle）
qq_35388136的博客
12-31 979
触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到（引用于百度百科------触发器（数据库原理术语）_百度百科）。 ...
Postgresql和Oracle异常处理的异同
InternalsOf
03-29 1388
exception内核改造简单研究。 PG 1 实例 PG只支持预定义异常的处理，即异常code都是定义好的，触发后走EXCEPTION部分代码逻辑。 DROP TABLE db; CREATE TABLE db (a INT PRIMARY KEY, b TEXT); CREATE OR REPLACE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS $$ DECLARE text_var1 text; text_var2 text;
mysql数据库和postgreSql数据库行级触发器常用操作
tiger_Angle
04-08 315
在各个数据库中我们经常会对数据进行备份操作,可以借助触发器来实现 emp为数据操作表 emp_audit理解为数据备份表 mysql行级别触发器(mysql非常简单): 创建表emp: CREATE TABLE emp ( empname text NOT NULL, salary integer ); 创建表emp_audit: CREATE TABLE emp_audit( operation char(1)
oracle 全量存储过程,Oracle到PG存储过程自动转换实践（12页）-原创力文档
weixin_28848317的博客
04-08 833
O rac le 到PG存储过程自动转换实践- - 全量语法解析技术Oracle转PG的痛中之痛 -- 过程语言的转换简单语法差异Oracle PGdelete delete [from] tab delete from tabdaul select 1*4 from du...
oracle、mysql、sqlserver、postgresql根据触发器名获取触发器的DDL
06-01
1. Oracle: 在Oracle中，可以使用以下语句获取指定触发器的DDL： ``` SELECT DBMS_METADATA.GET_DDL('TRIGGER', '<TRIGGER_NAME>', '<OWNER>') FROM DUAL; ``` 其中，`<TRIGGER_NAME>`是指要获取DDL的触发器的名称，`<OWNER>`是指触发器所属的用户或模式。 2. MySQL: 在MySQL中，可以使用以下语句获取指定触发器的DDL： ``` SHOW CREATE TRIGGER `<TRIGGER_NAME>`; ``` 其中，`<TRIGGER_NAME>`是指要获取DDL的触发器的名称。 3. SQL Server: 在SQL Server中，可以使用以下语句获取指定触发器的DDL： ``` SELECT OBJECT_DEFINITION(OBJECT_ID('<TRIGGER_SCHEMA>.<TRIGGER_NAME>')) AS [TRIGGER_DEFINITION]; ``` 其中，`<TRIGGER_SCHEMA>`是指触发器所属的模式，`<TRIGGER_NAME>`是指要获取DDL的触发器的名称。 4. PostgreSQL: 在PostgreSQL中，可以使用以下语句获取指定触发器的DDL： ``` SELECT PG_GET_TRIGGERDEF('<TRIGGER_NAME>', '<TABLE_NAME>', '<SCHEMA_NAME>'); ``` 其中，`<TRIGGER_NAME>`是指要获取DDL的触发器的名称，`<TABLE_NAME>`是指触发器所作用的表的名称，`<SCHEMA_NAME>`是指触发器所属的模式。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值