个人笔记-oracle

oracle递归

从子节点递归到根节点：(id='子节点id',parent_id='根节点id') SELECT name,id,parent_id,sys_connect_by_path(name,'->') FROM table_name WHERE id='b1fb0f06ae3a42258351023311227d4f' START WITH PARENT_ID='98dbd014377e4c979555a457cadcd8b1' CONNECT BY PRIOR ID=PARENT_ID 从根节点递归到所有叶子节点：（有几条叶子节点，就查询到几条记录） select name,id,parent_id,sys_connect_by_path(name,'->') from table_name start with id='a30bdddf09534ba78ff19ccbee46d818' connect by prior id=parent_id; 查询是否为叶子节点： LEVEL:查询节点层次，从1开始。 CONNECT_BY_ISLEAF：查询节点是否是叶子节点，是则为1，不是则为0 SELECT ID, NAME, LEVEL, CONNECT_BY_ISLEAF FROM sc_t_affair_type START WITH id='00001' CONNECT BY PRIOR ID=PARENT_ID ORDER BY ID;

分别统计下级各类信息总数

select count(nvl(qylx,'null')) TOTAL,QYLX, parentid SSWGID ,name GRIDNAME from 
(
        select qy.qylx,grid.name,grid.parentid from table_qy qy ,
        (
                select substr(sys_connect_by_path(name,'->'),3,instr(sys_connect_by_path(name,'->'),'->',1,2)-3) name,id,nvl(substr(sys_connect_by_path(id,'->'),3,instr( replace(substr(sys_connect_by_path(id,'->'),3),'5FAC09DB07AA47CE9B81047A92B519FF','') ,'->')-1),id) parentId
                from  table_grid
                start with sjgrid_id ='5FAC09DB07AA47CE9B81047A92B519FF' connect by prior id=sjgrid_id
        ) grid
        where qy.sswgid=grid.id
) group by qylx,name,parentid

序列

select * from user_sequences 
select last_number from user_sequences where sequence_name='T_SEQ';
alter sequence T_SEQ increment by 1;
SELECT T_SEQ.NEXTVAL FROM DUAL;

select a-(T_DEPT_SEQ.currval) from  (select max(objectid) a from T_DEPT);

Meger

merge into t jg
using t_view v
on (jg.id=v.id and v.geometry is not null)
WHEN MATCHED THEN  
update set jg.geometry = v.geometry
WHEN NOT MATCHED THEN   
INSERT  (id,geometry) values(v.id,v.geometry)

merge into table_name cjr
using dual
on (cjr.id='<span style="font-family: Arial, Helvetica, sans-serif;">idvalue</span><span style="font-family: Arial, Helvetica, sans-serif;">')</span>
WHEN MATCHED THEN  
update set cjr.tpzt='1234'
WHEN NOT MATCHED THEN   
INSERT  (id,tpzt) values('idvalue','123');

view

create or replace view t_view as
select xz.geometry,jg.id from t_jgxx jg, XZQHCNTYPOLY xz where jg.xzqh=xz.cnty_code and xz.geometry is not null
union all
select xz.geometry,jg.id from t_jgxx jg, XZQHPREFPOLY xz where jg.xzqh=TO_CHAR(xz.PREF_CODE) and xz.geometry is not null
union all
select xz.geometry,jg.id from t_jgxx jg, XZQHPROVINCEPOLY xz where jg.xzqh=TO_CHAR(xz.PROV_CODE) and xz.geometry is not null;

游标

declare 
 cursor rks_cur is select * from sc_t_rk where csrq is  null and length(gmsfzhm)=18 and to_number(substr(gmsfzhm,11,2))<13 and to_number(substr(gmsfzhm,11,2))>0 and to_number(substr(gmsfzhm,13,2))<32 and to_number(substr(gmsfzhm,13,2))>0;
 rk_cur sc_t_rk%rowtype;
 begin 
 open rks_cur; 
  loop 
  exit when rks_cur%notfound; 
  fetch rks_cur into rk_cur;
  dbms_output.put_line(rk_cur.gmsfzhm);   
  update sc_t_rk a  set a.csrq=to_date(substr(a.gmsfzhm,7,8),'yyyymmdd') where  a.gmsfzhm=rk_cur.gmsfzhm;
  end loop; 
 close rks_cur; 
 end; 

约束找表：

select table_name from all_constraints where constraint_name='FK978DFD6E45915F30';

修复索引

alter index SYS_C0052894 rebuild;

sys_guid()、生成主键

查看重复数据大于3的

select count(ID),ID from tab group by id having(count(id))>3

查询时判断字段值做不同返回

select (case when VERSION>5 then '优秀' WHEN VERSION>3 then '及格' else '不及格' end) from dutycost

select
CASE 
VERSION 
WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '其他' END from dutycost
select
version,
CASE WHEN  version in(1,2) THEN '男'  
WHEN version=3 THEN '女'  
ELSE '其他' END  
from dutycost

oralce 一次插入多条数据

create table a( id varchar2(10));
alter table a add constraint zhtt_a_pk primary key(id);
alter table a drop constraint zhtt_z_pk;
select * from a;
INSERT ALL INTO  a VALUES('a')  INTO a VALUES ('b') INTO a VALUES('c') select * from a;
select * from a;
drop table a;

INSERT ALL
INTO a values('4')
into a values('3')
select * from dual;

insert into a
(
select '1' from dual
union all
select '2' from dual
)

oracle一次性插入多条记录（2）

create table b as select * from a;
insert into b select * from a;
select * from b;

EXCEPT 返回两个结果集的差（即从左查询中返回右查询没有找到的所有非重复值）。

INTERSECT 返回 两个结果集的交集（即两个查询都返回的所有非重复值）。

时间默认值

DB_SERVER_DATE TIMESTAMP(6) DEFAULT SYSDATE,

主键UUID默认值

alter table t_authority modify id default guid_uuid;

EXP（导出数据）

exp username/password@sid file=/home/oracle/dept.dmp  tables='dept'  query=\" where id  in \(\'uuid\'\,\'uuid'\) \"

查看SQL语句占用临时表空间情况：

select sess.SID, segtype, blocks*8/1000 "MB" ,sql_text
from v$sort_usage sort, v$session sess,v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sess.SQL_ADDRESS
order by blocks desc;
select'the ' || name || ' temp tablespaces ' || tablespace_name ||
       ' idle ' ||
       round(100 - (s.tot_used_blocks / s.total_blocks) * 100, 3) ||
       '% at ' || to_char(sysdate, 'yyyymmddhh24miss')
from (select d.tablespace_name tablespace_name,
               nvl(sum(used_blocks), 0) tot_used_blocks,
               sum(blocks) total_blocks
          from v$sort_segment v, dba_temp_files d
         where d.tablespace_name = v.tablespace_name(+)
         group by d.tablespace_name) s,
       v$database;

一次返回表中所有数据

 SELECT rtrim(xmlagg(xmlparse(content id||','||name||','||parent_id || ';' wellformed) ORDER BY id)
              .getclobval(),
              ',') attributes
        
   FROM test_table

Oracle 列转行函数 Listagg()，来源：http://dacoolbaby.iteye.com/blog/1698957

 

1. with temp as(  
2.   select 'China' nation ,'Guangzhou' city from dual union all  
3.   select 'China' nation ,'Shanghai' city from dual union all  
4.   select 'China' nation ,'Beijing' city from dual union all  
5.   select 'USA' nation ,'New York' city from dual union all  
6.   select 'USA' nation ,'Bostom' city from dual union all  
7.   select 'Japan' nation ,'Tokyo' city from dual   
8. )  
9. select nation,listagg(city,',') within GROUP (order by city)  
10. from temp  
11. group by nation 

未完待续……