精华内容
下载资源
问答
  • Oracle查询结果增加一个自增的伪列

    千次阅读 2019-03-23 19:23:35
    因为查询结果要去重,所以使用distinct进行去重: select distinct l.CUSTOMER_CODE customerCode , l.PARTNER_ID partnerId from efsw_loan_info l where 1=1 ...但是前端要求增加一列自增键,他需...

    因为查询结果要去重,所以使用distinct进行去重:

    select distinct l.CUSTOMER_CODE customerCode  , l.PARTNER_ID partnerId 
    from efsw_loan_info l where 1=1
    and l.LOAN_GEN_DATE >= @startDate

    这样的一组唯一的查询结果。但是前端要求增加一列自增键,他需要用来做复选的时候能用到,于是:

    select customerCode , partnerId, ROW_NUMBER() OVER (ORDER BY  customerCode ,partnerId) AS id
    from ( select distinct l.CUSTOMER_CODE customerCode  , l.PARTNER_ID partnerId 
    from efsw_loan_info l where 1=1
    and l.LOAN_GEN_DATE >= @startDate)

    在最外层增加一个ROW_NUMBER() OVER (ORDER BY  customerCode ,partnerId) AS id作为和一组customerCode ,partnerId对应的唯一键。

    展开全文
  • 介绍如何在 Oracle 中利用虚拟列加唯一约束,以及函数索引两种方式实现一个只能存储一行数据的表,以及如何通过触发器禁止数据删除的方法。

    大家好,我是只谈技术不剪发的 Tony 老师。今天我们来讨论一个有趣的话题:如何在 Oracle 中实现一个只能存储一行数据的表。

    📝如果你使用的是 MySQL,可以通过这篇文章了解如何在 MySQL 中实现一个只有一行数据的表
    📝如果你使用的是 PostgreSQL,可以通过这篇文章了解如何在 PostgreSQL 中实现一个只有一行数据的表

    假如我们有一个表 t_version,用于记录应用系统的版本信息:

    create table t_version(version varchar2(100) not null, update_at timestamp not null);
    

    在第一次安装应用程序时需要生成一条记录,以后升级系统时需要更新版本信息,但不允许用户删除该记录。这种需求该如何实现?

    基于虚拟列的唯一约束

    Oracle 11g 增加了虚拟列(Virtual Column)的支持,也就是基于一个表达式的虚拟字段。我们可以为 t_version 表增加一个虚拟列:

    alter table t_version add only_one_row generated always as (1);
    

    虚拟列 only_one_row 的值永远只能等于 1,然后我们再基于该字段创建一个唯一索引。例如:

    alter table t_version add constraint uk_t_version unique (only_one_row);
    

    以上语句限制了字段 only_one_row 中的值具有唯一性,加上取值只能等于 1,所以表 t_version 中最多只能存在一条记录。

    初始化安装时插入数据的效果如下:

    -- 初始化安装
    insert into t_version(version, update_at) values ('XYZ 系统版本 1.0.0', current_timestamp);
    
    select * from t_version;
    VERSION          |UPDATE_AT          |ONLY_ONE_ROW|
    -----------------|-------------------|------------|
    XYZ 系统版本 1.0.0|2020-07-19 17:02:05|           1|
    

    第一次插入数据时可以使用 INSERT 语句,但是随后升级应用程序时继续插入数据就会返回错误:

    -- 升级软件版本
    insert into t_version(version, update_at) values ('XYZ 系统版本 2.0.0', current_timestamp);
    ORA-00001: unique constraint (TONY.UK_T_VERSION) violated
    

    第二次插入数据时返回了唯一约束冲突。显然,系统升级时需要使用 UPDATE 语句更新版本信息:

    update t_version
    set version = 'XYZ 系统版本 2.0.0',
        update_at = current_timestamp;
    
    select * from t_version;
    VERSION          |UPDATE_AT          |ONLY_ONE_ROW|
    -----------------|-------------------|------------|
    XYZ 系统版本 2.0.0|2020-07-19 17:06:42|           1|
    

    但是我们需要判断表中是否已经存在数据,然后执行不同的语句。为了解决这个问题,可以使用 MERGE 语句,它可以同时实现插入和更新操作:

    -- 清除数据
    truncate table t_version;
    
    -- 初始化安装
    merge into t_version dst
    using (select 1 from dual) src
    on (only_one_row = 1)
    when matched then
      update set dst.version = 'XYZ 系统版本 1.0.0',
                 dst.update_at = current_timestamp
    when not matched then
      insert (dst.version, dst.update_at) 
      values ('XYZ 系统版本 1.0.0', current_timestamp);
     
    select * from t_version;
    VERSION          |UPDATE_AT          |ONLY_ONE_ROW|
    -----------------|-------------------|------------|
    XYZ 系统版本 1.0.0|2020-07-19 17:17:46|           1|
    
    -- 升级软件版本
    merge into t_version dst
    using (select 1 from dual) src
    on (only_one_row = 1)
    when matched then
      update set dst.version = 'XYZ 系统版本 2.0.0',
                 dst.update_at = current_timestamp
    when not matched then
      insert (dst.version, dst.update_at) 
      values ('XYZ 系统版本 2.0.0', current_timestamp);
    
    select * from t_version;
    VERSION          |UPDATE_AT          |ONLY_ONE_ROW|
    -----------------|-------------------|------------|
    XYZ 系统版本 2.0.0|2020-07-19 17:18:35|           1|
    

    MERGE 语句使用 only_one_row = 1 作为数据冲突的判断条件,如果已经存在则更新数据,如果不存在则插入数据。

    基于函数的唯一索引

    除了上面的方法之外,我们还可以利用 Oracle 函数索引(Function-Based Index)直接基于常量值创建一个唯一的索引。例如:

    -- 删除并重建 t_version 表
    drop table t_version;
    create table t_version(version varchar2(100) not null, update_at timestamp not null);
    
    -- 创建一个唯一的函数索引
    create unique index uk_t_version on t_version ( (1) );
    

    索引 uk_t_version 是一个基于常量表达式 (1) 的函数索引,并且具有唯一性。也就是说,表中任何数据行对应的索引值都是 1,而唯一索引只允许一个 1,因此该表中最多只能存储一行数据。函数索引和虚拟列索引类似,但是不需要创建额外的字段。

    接下来的操作和上一节类似,但是需要将 MERGE 语句修改如下:

    merge into t_version dst
    using (select 1 from dual) src
    on (1 = 1)
    when matched then
      update set dst.version = 'XYZ 系统版本 1.0.0',
                 dst.update_at = current_timestamp
    when not matched then
      insert (dst.version, dst.update_at) 
      values ('XYZ 系统版本 1.0.0', current_timestamp);
    

    由于不存在字段 only_one_row,我们使用 1=1 作为数据冲突的判断条件。

    通过触发器禁止数据删除

    最后一个问题就是需要避免版本信息被误删除,这个可以通过触发器来实现。

    首先为 t_version 表创建一个删除触发器:

    create or replace trigger tri_disable_version_del
    	before delete on t_version
    begin
    	raise_application_error(-20000, '禁止删除版本信息!');
    end;
    

    该触发器是一个语句级 BEFORE 触发器,在任何删除语句之前返回错误信息。

    我们执行以下语句删除版本信息:

    delete
    from t_version;
    ORA-20000: 禁止删除版本信息!
    ORA-06512: at "TONY.TRI_DISABLE_VERSION_DEL", line 2
    ORA-04088: error during execution of trigger 'TONY.TRI_DISABLE_VERSION_DEL'
    
    select * from t_version;
    VERSION          |UPDATE_AT          |ONLY_ONE_ROW|
    -----------------|-------------------|------------|
    XYZ 系统版本 2.0.0|2020-07-19 17:30:16|           1|
    

    以上删除语句返回了错误信息,t_version 中的数据仍然存在。不过需要注意的是,TRUNCATE TABLE 语句仍然可以清除表中的数据,因为它不会触发 DML 触发器。

    总结

    本文介绍了在 Oracle 中利用虚拟列加唯一约束,以及函数索引两种方式实现一个只能存储一行数据的表,同时介绍了通过触发器禁止数据删除的方法。

    除了本文介绍的方法之外,你还有没有其他的实现方法?欢迎关注❤️、评论📝、点赞👍

    展开全文
  • oracle 查询表中数据行(row)上最后的DML时间  2011-07-27 13:01:53| 分类: ORACLE 数据库杂|字号 订阅 oracle 查询表中数据行(row)上最后的DML时间 在这介绍oracle 10G开始提供的个伪列ORA_ROWSCN...

    oracle 查询表中数据行(row)上最后的DML时间  

    2011-07-27 13:01:53|  分类: ORACLE 数据库杂|字号 订阅

    oracle 查询中数据(row)上最后DML时间

    在这介绍oracle 10G开始提供的一个伪列ORA_ROWSCN,它又分为两种模式一种是基于block这是默认的模式(块级跟踪);还有一种是基于row上,这种模式只能在建里表时指定ROWDEPENDENCIES(行级跟踪),不可以通过后期的alter table语句来将表修改为ROWDEPENDENCIES

    我们知道,每个Block在头部是记录了该block最近事务的SCN的,所以默认情况下,只需要从block头部直接获取这个值就可以了,不需要其他任何的开销,Oracle就能做到这一点。但是这明显是不精确的,一个block中会有很多行记录,每次事务不可能影响到整个block中所有的行,所以这是一个非常不精准的估算值,同一个block的所有记录的ORA_ROWSCN都会是相同的,基本上没有多大的使用价值。

    如果在建表的时候开启行级跟踪选项,Oracle则可以为每一行记录精确的SCN,那么显然不能再直接从block头部获取。要获得足够的信息,肯定要付出一定的代价,Oracle必须为每一行存储这个实际的SCN。所以这个行级跟踪的选项,只能在建表的时候指定,而不能通过alter table来修改现有的表,否则需要修改每一行记录的物理存储格式,代价是可想而知的。

    10g之前,很多系统要实现增量数据抽取,要么通过解析日志,要么加触发器,要么就在表上加一个时间截字段。ORA_ROWSCN其实就是第三种方式,只是这个字段由Oracle来维护,这样可以避免一些应用绕过时间截去更新其他字段带来的问题。

    下面做一个实验来证明,首先在默认状态下修改同一个块上的其中一条数据,然后再启用行级跟踪,修改同一块上的一条数据,观察ora_rowscn变化。

    第一种方式(块级跟踪):

    select ora_rowscn,
           dbms_rowid.ROWID_BLOCK_NUMBER(
    rowid) blockid,
           scn_to_timestamp(ora_rowscn)
      
    from hs_futures.fuentrust t
     
    order by scn_to_timestamp(ora_rowscn);

    dbms_rowid.ROWID_BLOCK_NUMBER(rowid):是为获取数据所在块的ID

    scn_to_timestamp(ora_rowscn):获取数据最所修改的时间

    数据发现变化后通过上面SQL语句可以查看到数据最后修改的时间,注意因为是同一个块上,所以这个块上只要有DML操作那么所有数据的scn都更新了,所以凡是跟这条记录在同一个块上数据获取到的ora_rowscnscn_to_timestamp(ora_rowscn)两个值都发生了相应的变化。

    第二种方式(行级跟踪):

    create table hs_futures.fuentrust_test1 rowdependencies  as
      
    select * from hs_futures.fuentrust

    用以上语句创建一个基于ROWDEPENDENCIES模式的表,然后用第一种方法中的SQL去查询此表数据中的ora_rowscnscn_to_timestamp(ora_rowscn)两个值,修改其中的某一条记录然后再去查询那个值发现发生变化的只是被修改那条记录的这两个值发生了变化,而在同一个块中没有被修改的其它记录这两个值是不会产生变化的。

    注意DDL操作:只要现有表记录中的数据有发生变化那么SCN肯定就会发生更新,如删除有数据的列,但是如果索引删除/修改/增加及增加/者删除没有任何数据的列那么SCN是不会有任何变化。

    展开全文
  • oracle数据库查询数据添加换行

    千次阅读 2019-12-04 18:56:19
    oracle数据库中查询到的数据拼接到一起,并且在中间添加换行,以方便后台获取到数据库记录后,直接写入文件也能将格式一并带上。 方案: 使用oracle中的回车换符:其中回车是chr(13)对应java中的\r,换符是...

    需求:

    将oracle数据库中查询到的数据拼接到一起,并且在中间添加换行,以方便后台获取到数据库记录后,直接写入文件也能将格式一并带上。

    方案:

    使用oracle中的回车换行符:其中回车是chr(13)对应java中的\r,换行符是chr(10)对应java中的\n。

    场景示例:

    需要从tradetable表中查询记录总数和money字段的总和,并且写入文件a.txt中,第一行写总数,第二行写金额。

    1. 查询表内容如:select count(1)||chr(13)||chr(10)||sum(money) from tradetable。
    2. 将查询到的内容直接写入文件中,换行格式会一并带入文件里。
    展开全文
  • Oracle针对数据库某一行进行操作的时候,如何将这一行加行锁的实现方法
  • Oracle行数据相减

    千次阅读 2019-11-13 11:10:51
    突然接到一个需求,所有的数据都在一张大表里,...上图为数据样例,就是上一行减去下一行,以此类推知道最后。 现在使用Lag函数将CreateAt列,平移添加另一列Last_CreateAt,这里不是简单的平移而是把CreateAt的第一...
  • 结果列中,有某列因为条件关联原因,查询结果会报:ORA-01427: single-row  subquery returns more than one row,顾名思义就是查询结果出现多行了,与其他列 保持不一致。对于这种状况,之前开发...
  • 可以将查询结果放入到一张自定义表中,同时可以再从这个自定义的表中查询数据,详细的sql如下,感兴趣的朋友不要错过 如下的实际例子代码可以将查询结果放入到一张自定义表中,同时可以再从这个自定义的表中...
  • ![图片说明](https://img-ask.csdn.net/upload/201812/01/1543634633_494719.png) 要求用 table_1 的数据,生成 table_2
  • Oracle查询数据数据很少却很慢

    千次阅读 2017-02-09 15:09:51
    Oracle查询数据数据很少却很慢(查询空表很很耗时) 有个表的数据我要去看一下到底数据是什么样子的,所以我就 select * from tableName 这个表。但是执行了90多秒后,没有任何数据。我就一直很纳闷,为什么空...
  • oracle查询数据库表的最后数据

    千次阅读 2018-08-30 09:25:11
    试过很多方式,就是这种效率高
  • 最近基于公司的业务做报表的时候...----查询排序,特定300-400数据 SELECT B.R,B.* FROM (SELECT ROWNUM R, T.* FROM SYS_ACTIVECODE T WHERE ROWNUM <= 400 and t.USEDFLAG='0' and t.ACTCODE_TYPE='2D...
  • 例:将vote_result字段相同的ID放在一个字段中展示 listagg (字段, ',') WITHIN GROUP (ORDER BY T .ENAME) 将多行记录的某个字段在一行显示,group(order by t)是这一行内容的排序 ...
  • 新增字段:alter table 表名 add(字段名,字段类型);...插入一行数据:insert into 表名(colum1,colum2,...) values(value1,value2,.....);例: INSERT into SYS_PA_CODE(ID,CODE_TYPE_ID,CODE_NU...
  • oracle 查询结果自动换行

    千次阅读 2013-04-06 09:46:05
    oracle 查询结果自动换行 2011-12-28 17:19:08|分类:oracle|字号订阅 你可以用column 命令设置每列的显示宽度呀。同时通过设置linesize 可以控制一行显示的长度。例如 SQL> SET LINESIZE 150 SQL&...
  • 解析Oracle数据扫描 Oracle SQL查询优化 (2) 2、 提高局部范围数据扫描执行性能的原理: 2.1查询条件的类别与作用:  当我们发起个带有条件的查询SQL语句时,通常会赋予该语句多个查询条件,在这些查询...
  • 2.向TEST表中添加数据 INSERT INTO TEST(STUDENT,COURSE,SCORE) select '张三','语文',78 from dual union select '张三','数学',87 from dual union  select '张三','英语',82 from dual union select '...
  • Oracle 查询之date类型数据格式化

    千次阅读 2017-09-19 15:52:00
    今天在做一个数据查询的时候接触到了物化视图,第一次使用,进行了一番研究,这里不做描述,物化视图中同事写了这样一行代码: to_date(concat(to_char( sysdate+1,'dd-mm-yyyy'),'00:30:00'),'dd-mm-yyyy hh24:mi:...
  • oracle 查询优化

    千次阅读 2019-01-10 14:45:21
    oracle查询优化() 1、IN 操作符 用 IN 写出来的 SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。 但是用 IN 的 SQL 性能总是比较低的,从 ORACLE 执行的步骤来分析用 IN 的 SQL 与不用 IN 的...
  • 解决方案使用带有values子句的insert语句来插入一行。insert into dept(deptno,dname,loc) values(19,'xgj','BEIJING');讨论作为一种简便方式,在insert语句中,可以省略字段列表,然而,如果语句中没有列出要插入行...
  • Oracle数据库插入数据

    千次阅读 2014-05-05 16:44:54
    这几天搞了一下Oracle数据库,可能用sql server...向Oracle服务器插入数据 --不管什么格式通通用''引号引起来。不过INTEGER格式的还是别用引号 insert into T_SITE VALUES( 8, --注意主键别与表里的其他主键冲
  •  oracle数据库支持多用户间同时对同个表进行操作,但是数据不一定同步,因为oracle数据库是支持脏数据的,比如A用户删除了表的数据但没有提交,B用户也能查询访问到,如果要避免这种情况只能加锁,A用户在操作表...
  • 在目前绝大部分数据库有分布式查询的需要。下面简单的介绍如何在oracle中配置实现跨库访问。 比如现在有2个数据库服务器,安装了2个数据库。数据库server A和B。现在来实现在A库中访问B的数据库。 第步、配置A...
  • Oracle一次插入多条数据

    千次阅读 2020-01-17 16:16:07
    Oracle一次插入多条数据 oracle不像mysql那样可以在values后面添加多个插入的值,mysql具体操作 mysql插入多条数据 -- mysql插入多行代码 insert into CB_PRACTICE(id_, type_,remark) values (5,'物理','浮力'),(6,...
  •  今天查了一下资料,对如何通过SQL语句加锁有了一些了解,特整理一下:. session的概念 session,即“会话”,在plsql中,个新的SQL WINDOW就是个新的session会话。二. 建表并初始化数...
  • Oracle数据查询优化

    万次阅读 2018-01-18 22:31:51
    前言:平常写的SQL可能主要以实现查询结果为主,但如果数据大,就会突出SQL查询语句优化的性能独特之处.一般的数据库设计都会建索引查询,这样较全盘扫描查询的确快了不少.下面总结下SQL查询语句的几个优化效率的...
  • 执行后,在查询结果中就会多出列col1值为1的列 如果要在oracle中实现这样的效果也是可以的,不过写法不一样了,下面是oracle中的写法。 select 1 col1,**** from tablename 直接写值,然后再写字段名即可。
  • Oracle数据很少,但是select时候却执行很长时间; Oracle数据很少,但是select时候却执行很长时间; Oracle数据很少,但是select时候却执行很长时间;
  • 我简单地测试了下,对视图的更改,基表里的数据也会随之更改。希望哪位大牛能帮我解释下。

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 182,651
精华内容 73,060
关键字:

oracle查询结果添加一行数据