oracle 查询总结,Oracle 数据库特殊查询总结

1. 查询本节点及本节点以下的所有节点：

sql;">

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. 查询节点中所有的层级关系

CONNECT_BY_ROOT 返回当前节点的最顶端节点

2> CONNECT_BY_ISLEAF 判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点

3> LEVEL 伪列表示节点深度

4> SYS_CONNECT_BY_PATH函数显示详细路径，并用“/”分隔

3. 对数据库表结构的操作

sql;">

alter table taxasset add (NEXTDATE varchar2(30));

alter table tax_dep_manager modify FDDBRXM varchar2(120);

alter table test1 drop column name;

4. 其他查询

sql;">

/*用户被占用的查询*/

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,'99990.00')

|| '' "实有",To_char(Round(FREE / 1024,'99990.00')

|| 'G' "现有",To_char(Round(( BYTES - FREE ) / 1024,'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 的使用

100;

END LOOP;

DBMS_OUTPUT.PUT_LINE('完了');

END;

7. 存储过程的书写

sql;">

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,'N',null,'1','Y','2',2,brno,'A',3,'0',null);

num := num + 1;

end loop;

end;

以上所述是小编给大家介绍的Oracle 数据库特殊查询总结，希望对大家有所帮助！

总结

以上是编程之家为你收集整理的Oracle 数据库特殊查询总结全部内容，希望文章能够帮你解决Oracle 数据库特殊查询总结所遇到的程序开发问题。

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。