oracle sql
蚂蚁sb
这个作者很懒,什么都没留下…
展开
-
ORA-600
ORA-600 [kglobpn-bad-pga] On Create Trigger (Doc ID 1471078.1)原创 2021-05-15 16:41:14 · 121 阅读 · 0 评论 -
分析函数
# lag & lead select deptno,ename,hiredate, lag(hiredate) over(partition by deptno order by hiredate)last_a from emp; lead(field, num, defau) field需要查找的字段,num往后查找的num行的数据,defau没有符合条件的默认值 ...原创 2019-03-19 17:49:06 · 106 阅读 · 1 评论 -
sum() over
select sum(case job when 'CLERK' then sal end) as cl, sum(case job when 'MANAGER' then sal end) as ma from emp; select sum(decode(job,'CLERK',sal)) as cl, sum(decode(job,'MANAGER',sal)) ...原创 2019-03-19 16:40:39 · 1033 阅读 · 0 评论 -
串
select level from dual connect by level <=5; select regexp_count('ab,de,fg',',') from dual; select regexp_count('ab$cd$ef','\$') from dual; select length(regexp_replace('ab,de,fg','[^,]')) from dua...原创 2019-03-19 15:25:08 · 122 阅读 · 0 评论 -
日期类型
date×tamp select sysdate-to_date('2019/03/17','yyyy/mm/dd') from dual; --number select sysdate-to_timestamp('2019/03/17','yyyy/mm/dd') from dual; --interval select systimestamp-1 from dual; ...原创 2019-03-19 22:09:16 · 130 阅读 · 0 评论 -
更新插入
insert into t1 values(default,null,‘abc’); create view v1 as select a,b,c from t1;–建一个不包含有默认值的那一列的view insert into v1 values(); alter table emp add constraints constr1 check(sal>0); insert into(se...原创 2019-03-19 12:50:01 · 244 阅读 · 0 评论 -
单表查询
–conn scott/tiger select * from emp; select * from emp where (1=1); select * from ( select empno,sal 工资 from emp) where 工资<1000;–列别名在select以后才有效; select * from emp where comm is null; –coalesce(a,b,c,...原创 2019-03-19 12:50:39 · 130 阅读 · 0 评论 -
排序
select mid,us_phone,substr(us_phone,-3) as tail from tb_cy_memberinfo order by 3 null first/last;-- 3只能出现在order by 中, select translate(‘ab12cc’,’-12345’,’-’) from dual; select ename,sal, case when s...原创 2019-03-19 12:50:17 · 134 阅读 · 0 评论