Oracle 数据库特殊查询总结
1.查询本节点及本节点以下的所有节点：
select*fromtable1cstartwithc.p_id='0000000'connectbypriorc.id=c.p_idandc.use_yn='Y'orderbyid;
2.查询节点中所有的层级关系
SELECTRPAD('',2*(LEVEL-1),'-')||DEPNAME"DEPNAME",CONNECT_BY_ROOTDEPNAME"ROOT",CONNECT_BY_ISLEAF"ISLEAF",LEVEL,SYS_CONNECT_BY_PATH(DEPNAME,'/')"PATH"FROMDEPSTARTWITHUPPERDEPIDISNULLCONNECTBYPRIORDEPID=UPPERDEPID;
1>CONNECT_BY_ROOT返回当前节点的最顶端节点
2>CONNECT_BY_ISLEAF判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点
3>LEVEL伪列表示节点深度
4>SYS_CONNECT_BY_PATH函数显示详细路径，并用“/”分隔
3.对数据库表结构的操作
altertabletaxassetadd(NEXTDATEvarchar2(30)); altertabletax_dep_managermodifyFDDBRXMvarchar2(120); altertabletest1dropcolumnname;
4.其他查询
/*用户被占用的查询*/ select'altersystemkillsession'''||sid||','||serial#||''';'fromv$sessionwhereusername='USERS'; /*系统数据库相关查询*/ select*fromuser_tablespaces; selectusername,default_tablespacefromdba_userswhereusername='ZZS' selectcount(*)fromuser_views;--yb53zzs53 selectcount(*)fromuser_tables;--yb413zzs413 --查询表空间使用情况 SELECTUpper(F.TABLESPACE_NAME)"表空间名",D.TOT_GROOTTE_MB"表空间大小(M)",D.TOT_GROOTTE_MB-F.TOTAL_BYTES"已使用空间(M)", To_char(Round((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99')||'%'"使用比",F.TOTAL_BYTES"空闲空间(M)", F.MAX_BYTES"最大块(M)"FROM(SELECTTABLESPACE_NAME,Round(Sum(BYTES)/(1024*1024),2)TOTAL_BYTES,Round(Max(BYTES)/(1024*1024),2)MAX_BYTESFROMSYS.DBA_FREE_SPACE GROUPBYTABLESPACE_NAME)F, (SELECTDD.TABLESPACE_NAME, Round(Sum(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MB FROMSYS.DBA_DATA_FILESDD GROUPBYDD.TABLESPACE_NAME)D WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME ORDERBY1 --查询表空间的freespace selecttablespace_name, count(*)ASextends, round(sum(bytes)/1024/1024,2)ASMB, sum(blocks)ASblocks fromdba_free_space groupBYtablespace_name; --查询表空间的总容量 selecttablespace_name,sum(bytes)/1024/1024asMBfromdba_data_filesgroupbytablespace_name; --表空间容量查询 SELECTTABLESPACE_NAME"表空间", To_char(Round(BYTES/1024,2),'99990.00') ||''"实有", To_char(Round(FREE/1024,2),'99990.00') ||'G'"现有", To_char(Round((BYTES-FREE)/1024,2),'99990.00') ||'G'"使用", To_char(Round(10000*USED/BYTES)/100,'99990.00') ||'%'"比例" FROM(SELECTA.TABLESPACE_NAMETABLESPACE_NAME, Floor(A.BYTES/(1024*1024))BYTES, Floor(B.FREE/(1024*1024))FREE, Floor((A.BYTES-B.FREE)/(1024*1024))USED FROM(SELECTTABLESPACE_NAMETABLESPACE_NAME, Sum(BYTES)BYTES FROMDBA_DATA_FILES GROUPBYTABLESPACE_NAME)A, (SELECTTABLESPACE_NAMETABLESPACE_NAME, Sum(BYTES)FREE FROMDBA_FREE_SPACE GROUPBYTABLESPACE_NAME)B WHEREA.TABLESPACE_NAME=B.TABLESPACE_NAME) ORDERBYFloor(10000*USED/BYTES)DESC;
6.loop的使用
DECLARE
connumber;
BEGIN
con:=1;
LOOP
DBMS_OUTPUT.PUT_LINE(con);
con:=con+1;
EXITWHENcon>100;
ENDLOOP;
DBMS_OUTPUT.PUT_LINE('完了');
END;
7.存储过程的书写
createorreplaceprocedureInsertBranch(tablenameinvarchar2)as
countsnumber;
numnumber;
begin
createtabletempdata(column1nvarchar2,column2nvarchar2,column3nvarchar2);
inserttempdata
num:=1;
selectcount(*)intocountsfromtablename;
dbms_output.put_line('数据总数'+counts);
whilenum<=countsloop
dbms_output.put_line('循环开始:');
dbms_output.put_line('第'+num+'条数据');
selectcolumn1
intocolumn1
from(selecttablename.*,rownumasconfromtablename)
wherecon=num;
selectcolumn2
intocolumn2
from(selecttablename.*,rownumasconfromtablename)
wherecon=num;
selectcolumn3
intocolumn3
from(selecttablename.*,rownumasconfromtablename)
wherecon=num;
insertintoCOM_DEPARTMENT
values
(brno,
brname,
upbrno,
upbrno,
'N',
null,
null,
null,
'1',
null,
'Y',
'2',
null,
null,
null,
2,
'N',
null,
null,
null,
'N',
brno,
upbrno,
null,
null,
null,
'A',
'N',
'N',
0,
0,
3,
null,
null,
null,
'0',
'0',
0,
null,
null,
null,
null,
null,
null,
null);
num:=num+1;
endloop;
end;
以上所述是小编给大家介绍的Oracle数据库特殊查询总结，希望对大家有所帮助！