Oracle 數據庫特殊查詢總結

1. 查詢本節點及本節點以下的所有節點：

select * from table1 c start with c.p_id='0000000' connect by prior c.id=c.p_id and c.use_yn='Y' order by id ; 

2. 查詢節點中所有的層級關系

SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",CONNECT_BY_ROOT DEPNAME "ROOT",CONNECT_BY_ISLEAF "ISLEAF",LEVEL ,SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH" FROM DEP START WITH UPPERDEPID IS NULL CONNECT BY PRIOR DEPID = UPPERDEPID;
1> CONNECT_BY_ROOT 返回當前節點的最頂端節點 
2> CONNECT_BY_ISLEAF 判斷是否為葉子節點，如果這個節點下面有子節點，則不為葉子節點 
3> LEVEL 偽列表示節點深度 
4> SYS_CONNECT_BY_PATH函數顯示詳細路徑，並用“/”分隔

3. 對數據庫表結構的操作

alter table taxasset add (NEXTDATE varchar2(30));
alter table tax_dep_manager modify FDDBRXM varchar2(120);
alter table test1 drop column name;

4. 其他查詢

/*用戶被占用的查詢*/
select 'alter system kill session '''||sid||','||serial#||''';' from v$session where username = 'USERS';
/* 系統數據庫相關查詢 */
select * from user_tablespaces;
select username,default_tablespace from dba_users where username='ZZS'
select count(*) from user_views; --yb53 zzs 53
select count(*) from user_tables; --yb413 zzs 413
--查詢表空間使用情況
SELECT Upper(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 (SELECT TABLESPACE_NAME,Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE 
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1
--查詢表空間的free space
select tablespace_name,
count(*) AS extends,
round(sum(bytes) / 1024 / 1024, 2) AS MB,
sum(blocks) AS blocks
from dba_free_space
group BY tablespace_name;
--查詢表空間的總容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name;
--表空間容量查詢
SELECT TABLESPACE_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 (SELECT A.TABLESPACE_NAME TABLESPACE_NAME,
Floor(A.BYTES / ( 1024 * 1024 )) BYTES,
Floor(B.FREE / ( 1024 * 1024 )) FREE,
Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED
FROM (SELECT TABLESPACE_NAME TABLESPACE_NAME,
Sum(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME TABLESPACE_NAME,
Sum(BYTES) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME)
ORDER BY Floor(10000 * USED / BYTES) DESC;

6. loop 的使用

DECLARE
con number;
BEGIN
con :=1;
LOOP
DBMS_OUTPUT.PUT_LINE(con);
con:=con+1;
EXIT WHEN con>100;
END LOOP;
DBMS_OUTPUT.PUT_LINE('完了');
END;

7. 存儲過程的書寫

create or replace procedure InsertBranch(tablename in varchar2) as
counts number;
num number;
begin
create table tempdata (column1 nvarchar2,column2 nvarchar2,column3 nvarchar2);
insert tempdata 
num := 1;
select count(*) into counts from tablename;
dbms_output.put_line('數據總數'+counts);
while num <= counts loop
dbms_output.put_line('循環開始:');
dbms_output.put_line('第'+num+'條數據');
select column1
into column1
from (select tablename.*, rownum as con from tablename)
where con = num;
select column2
into column2
from (select tablename.*, rownum as con from tablename)
where con = num;
select column3
into column3
from (select tablename.*, rownum as con from tablename)
where con = num;
insert into COM_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;
end loop;
end;

以上所述是小編給大家介紹的Oracle 數據庫特殊查詢總結，希望對大家有所幫助！