oracle递归
从子节点递归到根节点:(id='子节点id',parent_id='根节点id')
从根节点递归到所有叶子节点:(有几条叶子节点,就查询到几条记录)
查询是否为叶子节点: LEVEL:查询节点层次,从1开始。 CONNECT_BY_ISLEAF:查询节点是否是叶子节点,是则为1,不是则为0
|
分别统计下级各类信息总数
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
- with temp as(
- select 'China' nation ,'Guangzhou' city from dual union all
- select 'China' nation ,'Shanghai' city from dual union all
- select 'China' nation ,'Beijing' city from dual union all
- select 'USA' nation ,'New York' city from dual union all
- select 'USA' nation ,'Bostom' city from dual union all
- select 'Japan' nation ,'Tokyo' city from dual
- )
- select nation,listagg(city,',') within GROUP (order by city)
- from temp
- group by nation
未完待续……