主頁(yè) > 知識(shí)庫(kù) > Oracle 數(shù)據(jù)庫(kù)特殊查詢總結(jié)

Oracle 數(shù)據(jù)庫(kù)特殊查詢總結(jié)

熱門標(biāo)簽:地圖標(biāo)注養(yǎng)老院 愛(ài)客外呼系統(tǒng)怎么樣 哪個(gè)400外呼系統(tǒng)好 開(kāi)發(fā)外呼系統(tǒng) 慧營(yíng)銷crm外呼系統(tǒng)丹丹 圖吧網(wǎng)站地圖標(biāo)注 百度地圖標(biāo)注途經(jīng)點(diǎn) 山東crm外呼系統(tǒng)軟件 哈爾濱電話機(jī)器人銷售招聘

1. 查詢本節(jié)點(diǎn)及本節(jié)點(diǎn)以下的所有節(jié)點(diǎn):

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. 查詢節(jié)點(diǎn)中所有的層級(jí)關(guān)系

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 返回當(dāng)前節(jié)點(diǎn)的最頂端節(jié)點(diǎn) 
2> CONNECT_BY_ISLEAF 判斷是否為葉子節(jié)點(diǎn),如果這個(gè)節(jié)點(diǎn)下面有子節(jié)點(diǎn),則不為葉子節(jié)點(diǎn) 
3> LEVEL 偽列表示節(jié)點(diǎn)深度 
4> SYS_CONNECT_BY_PATH函數(shù)顯示詳細(xì)路徑,并用“/”分隔

3. 對(duì)數(shù)據(jù)庫(kù)表結(jié)構(gòu)的操作

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';
/* 系統(tǒng)數(shù)據(jù)庫(kù)相關(guān)查詢 */
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;
--查詢表空間的總?cè)萘?
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')
|| '' "實(shí)有",
To_char(Round(FREE / 1024, 2), '99990.00')
|| 'G' "現(xiàn)有",
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. 存儲(chǔ)過(guò)程的書(shū)寫(xiě)

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('數(shù)據(jù)總數(shù)'+counts);
while num = counts loop
dbms_output.put_line('循環(huán)開(kāi)始:');
dbms_output.put_line('第'+num+'條數(shù)據(jù)');
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 數(shù)據(jù)庫(kù)特殊查詢總結(jié),希望對(duì)大家有所幫助!

您可能感興趣的文章:
  • Oracle SQL樹(shù)形結(jié)構(gòu)查詢
  • Oracle數(shù)據(jù)庫(kù)中的級(jí)聯(lián)查詢、級(jí)聯(lián)刪除、級(jí)聯(lián)更新操作教程
  • Oracle、MySQL和SqlServe三種數(shù)據(jù)庫(kù)分頁(yè)查詢語(yǔ)句的區(qū)別介紹
  • 講解Oracle數(shù)據(jù)庫(kù)中的數(shù)據(jù)字典及相關(guān)SQL查詢用法
  • 一些Oracle數(shù)據(jù)庫(kù)中的查詢優(yōu)化建議綜合
  • ORACLE錯(cuò)誤一覽表 方便查詢
  • ORACLE 查詢被鎖住的對(duì)象,并結(jié)束其會(huì)話的方法
  • oracle跨庫(kù)查詢dblink的用法實(shí)例詳解
  • Oracle數(shù)據(jù)庫(kù)中ORDER BY排序和查詢按IN條件的順序輸出
  • Oracle中scott表結(jié)構(gòu)與簡(jiǎn)單查詢實(shí)例分析
  • mysql、mssql及oracle分頁(yè)查詢方法詳解

標(biāo)簽:周口 武漢 承德 固原 青島 和田 甘肅 開(kāi)封

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《Oracle 數(shù)據(jù)庫(kù)特殊查詢總結(jié)》,本文關(guān)鍵詞  Oracle,數(shù)據(jù)庫(kù),特殊,查詢,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《Oracle 數(shù)據(jù)庫(kù)特殊查詢總結(jié)》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于Oracle 數(shù)據(jù)庫(kù)特殊查詢總結(jié)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章