1. 查询本节点及本节点以下的所有节点:
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. 查询节点中所有的层级关系
1
2
3
4
5
|
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. 对数据库表结构的操作
1
2
3
|
alter table taxasset add (NEXTDATE varchar2(30)); alter table tax_dep_manager modify FDDBRXM varchar2(120); alter table test1 drop column name ; |
4. 其他查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
/*用户被占用的查询*/ 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 的使用
1
2
3
4
5
6
7
8
9
10
11
|
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. 存储过程的书写
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
|
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 数据库特殊查询总结,希望对大家有所帮助!
原文链接:http://www.cnblogs.com/baoyi/p/Oracle_001.html