gpt4 book ai didi

Oracle 数据库特殊查询总结

转载 作者:qq735679552 更新时间:2022-09-29 22:32:09 25 4
gpt4 key购买 nike

CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章Oracle 数据库特殊查询总结由作者收集整理,如果你对这篇文章有兴趣,记得点赞哟.

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 。

最后此篇关于Oracle 数据库特殊查询总结的文章就讲到这里了,如果你想了解更多关于Oracle 数据库特殊查询总结的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com