个人笔记-oracle

oracle递归

从子节点递归到根节点：(id='子节点id',parent_id='根节点id')

SELECT name,id,parent_id,sys_connect_by_path(name,'->')FROM   table_nameWHERE  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_ISLEAFFROM     sc_t_affair_typeSTART 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;

Meger

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

merge into table_name cjrusing dualon (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 asselect xz.geometry,jg.id from t_jgxx jg, XZQHCNTYPOLY xz where jg.xzqh=xz.cnty_code and xz.geometry is not nullunion allselect xz.geometry,jg.id from t_jgxx jg, XZQHPREFPOLY xz where jg.xzqh=TO_CHAR(xz.PREF_CODE) and xz.geometry is not nullunion allselect 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

selectCASE VERSION WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '其他' END from dutycostselectversion,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 ALLINTO a values('4')into a values('3')select * from dual;insert into a(select '1' from dualunion allselect '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,

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_textfrom v$sort_usage sort, v$session sess,v$sql sqlwhere sort.SESSION_ADDR = sess.SADDRand sql.ADDRESS = sess.SQL_ADDRESSorder 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

 

未完待续……