oracle 特殊查询sql,oracle_Oracle 数据库特殊查询总结，1. 查询本节点及本节点以下的 - phpStudy...

Oracle 数据库特殊查询总结

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 数据库特殊查询总结，希望对大家有所帮助！相关阅读:

JavaScript中的原始值和复杂值

详解JavaScript的AngularJS框架中的表达式与指令

thinkphp3.2.3 分页代码分享

Android ExpandableListView展开列表控件使用实例

Linux系统如何使用iptables进行本地端口转发？

C++ COM编程之接口背后的虚函数表

win10系统提示无法显示此页在高级设置中启用TLS 1.0的解决方法

jquery判断元素的子元素是否存在的示例代码

C#中委托用法实例详解

JS获取鼠标坐标、获取鼠标像素点示例

理解Java当中的回调机制(翻译)

19个Android常用工具类汇总

PHP中preg_match函数正则匹配的字符串长度问题

Win10怎么调整输入法的顺序？Win10调整输入法顺序的方法