SQL语句学习
文章平均质量分 61
深圳gg
这个作者很懒,什么都没留下…
展开
-
字符串有不明字符的定位方法
开发反馈字符串里面有不明字符如何定位,可以将字符串的每个字符转成ascIIselect ASCII(substr(‘abcde’,level,1)),level from dual connect by level <= length(‘abcde’);ASCII(SUBSTR(‘ABCDE’,LEVEL,1)) LEVEL 97 1 98 2原创 2021-12-15 16:04:10 · 416 阅读 · 0 评论 -
历史数据转换性能优化的一种方式
一张表几个亿的数据,需要按年份切分,还有转换逻辑,insert all派上用场了。drop table t_source purge;drop table t_target_2019 purge;drop table t_target_2020 purge;drop table t_target_2021 purge;create table t_source (id number, create_time date);create table t_source_detail (id numbe原创 2021-06-05 18:25:25 · 182 阅读 · 0 评论 -
Oracle递归死循环怎么办?
drop table test1 purge;create table test1(id number, parent_id number,name varchar2(10));insert into test1 values(1,2,’’);insert into test1 values(2,1,’’);insert into test1 values(3,2,’’);commit;...原创 2019-11-12 16:05:24 · 2985 阅读 · 0 评论 -
Oracle 批量insert报错处理
你可能会碰到这样的业务,大批量插入数据,但可能有一两条数据有问题,导致插入失败,回滚就太不划算了。可以使用insert的一个特殊属性,如下面的例子。 SQL> select * from v$version; BANNER --------------------------------------------------------------------------------原创 2016-02-23 16:41:24 · 5490 阅读 · 0 评论 -
oracle 11g drop table 后闪回
--初始化数据drop table test purge;create table test as select * from dba_objects;delete from test where object_id is null;alter table test add constraint pk_test_object_id primary key(object_id);原创 2015-05-13 18:19:15 · 1944 阅读 · 0 评论 -
Oracle 中,函数如何返回结果集
在Oracle中,用函数返回结果集有时候要用到,下面是demo:create or replace type t_test as object( id integer, create_time date, object_name varchar2(60));create or replace type t_test_table as table of t_test原创 2015-04-07 18:53:23 · 2694 阅读 · 0 评论 -
merge into的妙用
今天遇到一个奇葩的需求:系统A是主系统,终端PDA会上传数据到系统A。当有单据id在系统A的id能查到,则update状态;当单据id在系统A中查不到则insert。下面来做一个简单的测试。drop table test;create table test(id number primary key, name varchar2(10),state number(1));insert原创 2015-03-10 18:27:13 · 5901 阅读 · 0 评论 -
Oracle树形汇总--connect_by_root
有个需求:统计上级部门的销售额,制造数据如下:drop table dept;create table dept( DEPTNO number, DEPTNAME varchar2(50), PARENT_DEPTNO number);insert into dept values(1,'市场部',-1);insert into dept values(2原创 2015-03-17 17:54:15 · 4512 阅读 · 0 评论 -
Oracle 简单的列转行
需求是,统计每个部门不同工种的薪水总和。SQL> select deptno,ename,job,sal from emp; DEPTNO ENAME JOB SAL---------- ---------- --------- ---------- 20 SMITH CLERK 800原创 2014-11-19 14:53:27 · 2489 阅读 · 1 评论 -
Oracle 11g pivot列转行
之前写过一篇行转列的文章:Oracle 简单的列转行 SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.原创 2014-12-02 10:04:19 · 2383 阅读 · 0 评论 -
数据的挪移--变得紧凑
现在的需求是讲下列的结果集变得紧凑: COL1 COL2 COL3 ---------- ---------- ---------- 1 2 3 4 5 6 7原创 2015-03-19 09:53:21 · 994 阅读 · 0 评论 -
如何统计每个小时用户在线人数?
系统有一个表记录用户登录、登出的日志,现在以一个小时为段,统计出在线人数,这是一个数学问题。情况1: |___________一个小时____________| <=登录时间 登出时间情况2: |___________一个小时____________| 登录时间原创 2015-02-10 16:29:58 · 7075 阅读 · 0 评论 -
数据校验脚本-检验null值
在做数据校验,需要检验多个字段是否为Null值,那怎么可以写的高效,如下例子:drop table T1 purge;create table T1( ID NUMBER, OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(128), OBJECT_TYPE VARCHAR2(19), STAT原创 2016-03-18 10:19:11 · 2659 阅读 · 0 评论 -
oracle 11g dblink 查询的bug --10053用处
在oracle11g上,外网建的dblink访问内网,好奇怪的SQL,问题SQL在内网是可以的,在外网查不出数据。做为实验,把内网的数据同步到外网,然后查询,是可以的。最后通过调整SQL写法解决了问题。问题SQL:SELECT T.P_TYPE_CODE, T.TYPE_CODE TYPE_ID, T.TYPE_NAME, TYPE_COD原创 2016-03-04 17:59:23 · 2094 阅读 · 0 评论 -
Oracle insert all一次插入多个表中
有一个需求:将一个含多个clob的表,分别插入到两个表中(一个是不含clob的表,一个是含clob的表)。insert all此时派上用场了。create table test ( id number, col1 number, col2 number, col3 number, col4 number);create table test1 ( id number...原创 2018-07-10 17:06:26 · 1347 阅读 · 0 评论 -
Oracle skip locked的应用
开发提了一个需求,就是取合同编码,原先是通过webserver去取,但有时候有性能问题,导致批量处理合同的时候有性能问题。现在要改造成先批量取一批合同编码放在本地。现在遇到的问题是怎么在并发情况下保证取的是不同的合同编码。数据库的skip locked可以解决。 1.通过webservice取1000个编号写到本地表中,表中应该有个标志表示是否使用。 2.当程序获取表示未使用的原创 2017-08-24 10:21:47 · 762 阅读 · 0 评论 -
Oracle merge into原来可以这么写
前不久发现merge into原来还可以这么写:drop table test purge;drop table test_bak purge;create table test as select * from user_objects where rownum <1000 order by object_name;create table test_bak as select *原创 2017-04-26 11:14:38 · 7338 阅读 · 0 评论 -
Oracle 正则表达式行转列
原始的数据:aa,bb,cc,ddee,ff,gg,hh想要得到的结果:aabbccdd...先弄一条试试:SQL> with t as( select 1 id,'aa,bb,cc,dd' names from dual ) select id,REGEXP_SUBSTR(names, '[^,]+', 1, l原创 2017-04-17 16:55:08 · 1298 阅读 · 0 评论 -
Oracle最常见的行转列
初始化数据:组名 分数 得分项开发一组 100 1开发二组 90 1开发三组 60 1开发四组 80 1开发一组 60 2开发二组 70 2开发三组 50 2开发四组 80 2需要得到: 开发一组 开发二组 开发三组 开发四组1 100 90 60原创 2017-04-11 09:42:15 · 909 阅读 · 0 评论 -
Oracle merge into delete语法
merge into也有delete语法。SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.原创 2017-03-20 19:25:15 · 16033 阅读 · 1 评论 -
Oracle逐行相加
有时候有这种需求,查出来的结果集要逐行相加。create table t ( id number, value number);insert into t values(1,10);insert into t values(2,10);insert into t values(3,20);insert into t values(4,20);inser原创 2017-01-03 11:12:51 · 1171 阅读 · 0 评论 -
Oracle树形统计--子节点汇总到父节点
初始的树形数据状态: |--2(0)--4(100) 0--1(0)--| |--3(0)--5(200)汇总后的树形数据状态: |--2(100)--4(100) 0--1(300)--| |--3(200)--5(200) create table t原创 2017-01-03 10:38:32 · 6926 阅读 · 2 评论 -
Oracle NLSSORT
今天第一次看到Oracle中的NLSSORT函数,有点意思,可以根据汉字做个性化的排序。create table test(name varchar2(20));insert into test values('中国');insert into test values('美国');insert into test values('日本');insert into test va原创 2016-11-11 10:48:18 · 3421 阅读 · 0 评论 -
DML过程中记录错误日志
当你插入几百万数据时,因为有几条脏数据而导致插入失败,是不是非常恼火。10g R2之后有个新功能,将插入过程中失败的记录插入到另一张表中。SQL> drop table test purge;SQL> drop table test_bad purge;SQL> create table test as select * from dba_objects where 1SQL>原创 2014-12-22 09:17:02 · 1385 阅读 · 0 评论 -
Oracle 11g下 ORA-01417的解决办法
今天有兄弟反馈,有一条SQL在12C上没有问题,在11g上就报错,报错内容是ORA-01417: 表可以外部连接到至多一个其他的表。分析了下SQL,抽象为下列的例子。在12c下没有问题:SQL> select * from v$version;BANNER原创 2014-11-27 17:50:38 · 7431 阅读 · 0 评论 -
巧用分析函数解决ORA-06502--字符串缓冲区太小
现在有这么一个需求,业务单据上面有很多流程跟踪的信息,要显示在列表页面的一个字段中。可能出现一个问题,那就是如果合并的字段长度超过4000个字节,SQL语句会报错,ORA-06502 字符串缓冲区太小,即使你用substr()截取也是这样的错误。其实就是一个行转列的过程。那怎么解决这个问题,来做个试验:drop table test;create table test(track_i原创 2013-07-29 14:21:12 · 7564 阅读 · 0 评论 -
SQL语句做字符串分割
在平时的工作中经常碰到字符串分割的业务,如“广东,广西,海南,贵州,云南”按照逗号进行分割:--用正则表达式with t as (select '广东,广西,海南,贵州,云南' str from dual)SELECT REGEXP_SUBSTR(str,'[^,]+',1,ROWNUM) province FROM tCONNECT BY ROWNUM<=LENGTH(str)原创 2013-09-29 14:58:06 · 2682 阅读 · 0 评论 -
SQL多层嵌套引起的ORA-00904
近期在开发环境上运行一条SQL是正常的,换到测试环境上就出问题了,报ORA-00904 invalid identifier 。查询程序先处理内层子查询,它并不知道外层表的别名。故报错。ORACLE认为是个BUG, 后来就改掉了。下面来做一个实验:C:\Documents and Settings\guogang>sqlplus test/test@10.10.15.110原创 2013-07-10 16:36:14 · 4681 阅读 · 1 评论 -
SQL的执行顺序
select a,b,count(sum(c)) over () from test where t.d=1 start with e is not null connect by prior f=ggroup by原创 2013-05-31 14:46:48 · 1021 阅读 · 0 评论 -
oracle 全角转半角
有这么一个需求,一个时间字段在当初设计的时候设计成varchar2,现在要该回成date,但发现里面的数据五花八门,其中有一类数据就是全角,要改为半角,不然转换为报错。SQL> drop table test purge;SQL> create table test (record_time varchar2(100));表已创建。SQL> insert into test v原创 2013-08-07 18:40:38 · 1522 阅读 · 0 评论 -
ROW_NUMBER() OVER用法
row_number()over(partition by col1 order by col2)表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。 与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码。 row_numb原创 2013-02-19 15:20:43 · 4036 阅读 · 0 评论 -
skip locked
create table EMPLOYEE( EMPID INTEGER, DEPTID INTEGER, SALARY NUMBER(10,2));insert into employee (EMPID, DEPTID, SALARY)values (1, 10, 5500.00);insert into employee (EMPID, DEPTID,原创 2013-03-04 08:19:15 · 865 阅读 · 0 评论 -
Union与Union All的区别
union和union all的区别是,union会自动压缩多个结果集合中的重复结果,而union all则将所有的结果全部显示出来,不管是不是重复。Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序; Union All:对两个结果集进行并集操作,包括重复行,不进行排序; Intersect:对两个结果集进行交集操作,不包括重复行,同时进行默认规则原创 2012-12-17 12:39:26 · 1129 阅读 · 0 评论 -
INLIST ITERATOR与CONCATENATION区别
INLIST ITERATOR :迭代查询,lnlist 里面几个值就要扫描几次。CONCATENATION :联合返回二个或多个行集。官方文档:When the Optimizer Uses IN-List Iterators The optimizer uses an IN-list iterator when an IN clause is specified with val原创 2012-12-26 19:24:24 · 3433 阅读 · 0 评论 -
over(partition by cloumn_name)语法
create table test( a number, b number, c number);insert into test (A, B, C) values (1, 1, 1);insert into test (A, B, C) values (1, 2, 2);insert into test (A, B, C) values (1,原创 2013-04-15 11:21:15 · 1324 阅读 · 0 评论 -
rownum原理与topn输出性能比较
原理:ROWNUM是一个虚字段,只有产生结果集时才会有值,步骤为:1 Oracle executes your query. 执行查询操作2 Oracle fetches the first row and calls it row number 1. 将第一行的row num置为13 Have we gotten past row number me原创 2012-12-25 09:02:32 · 1124 阅读 · 0 评论 -
oracle 视图可以update吗?
一说到视图是否可以update,我就在想,如果视图只包含一张表,应该是可以update的,因为SQL会执行查询转换,将视图转成表。那多个表关联的视图,是否可以update呢,下面我们来做个试验:SQL> create table t1 as select * from dba_objects;表已创建。SQL> create table t2 as select * from dba_原创 2013-10-15 08:52:29 · 5134 阅读 · 0 评论 -
Oracle分析函数ntile
有这么一个需求,将课程的成绩分成四个等级,为学生打A、B、C、D的绩效。drop table course purge;create table course( id number, grade number);insert into course values(1,50);insert into course values(2,55);insert int原创 2014-11-19 09:29:19 · 2301 阅读 · 0 评论 -
Oracle分析函数PERCENTILE_CONT
查询各部门中薪水分布处于25%、50%、75%位置的人的薪水,percent_rank()是确定排行中的相对位置。SQL> select e.ename,e.sal,e.deptno, percent_rank() over(partition by deptno order by sal desc) p_rank, PERCENTILE_CONT(0) w原创 2014-11-19 16:28:40 · 9855 阅读 · 0 评论 -
Oracle update+with的使用场景
drop table test purge;create table test( id number, code varchar(20), name varchar(20) );insert into test values(1,'201401','aaa');insert into test values(2,'201402','b原创 2014-11-14 20:26:14 · 4829 阅读 · 0 评论