Oracle 数据库特殊查询总结
网络编程 2021-07-05 16:08www.168986.cn编程入门
这篇文章主要介绍了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 判断是否为叶子节点,如果这个节点狼蚁网站SEO优化有子节点,则不为叶子节点 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 数据库特殊查询,希望对大家有所帮助!
编程语言
- 甘肃哪有关键词排名优化购买方式有哪些
- 甘肃SEO如何做网站优化
- 河南seo关键词优化怎么做电话营销
- 北京SEO优化如何做QQ群营销
- 来宾百度关键词排名:提升您网站曝光率的关键
- 卢龙关键词优化:提升您网站排名的策略与技巧
- 山东网站优化的注意事项有哪些
- 四川整站优化怎样提升在搜索引擎中的排名
- 疏附整站优化:提升网站性能与用户体验的全新
- 海南seo主要做什么工作售后服务要做到哪些
- 荣昌百度网站优化:提升您网站的搜索引擎排名
- 河北seo网站排名关键词优化如何做SEO
- 江西优化关键词排名推广售后保障一般有哪些
- 古浪SEO优化:提升你的网站可见性
- 西藏网站排名优化怎么把网站排名在百度首页
- 如何提升阳东百度快照排名:详尽指南