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 數據庫特殊查詢總結,希望對大家有所幫助!