【低调发布】Oracle进阶攻略第二版

最新推荐文章于 2025-04-16 20:16:26 发布

小V小V志

最新推荐文章于 2025-04-16 20:16:26 发布

阅读量686

点赞数 1

本文介绍Oracle中几种高级SQL技巧，包括WM_CONCAT函数的使用、临时表的两种类型、ROW_NUMBER()分析函数、游标替代方法、数据导出命令、数据库链创建绝杀技巧、MERGE INTO和MINUS语法、查看当前运行SQL、表空间使用状态及回收、PURGE RECYCLEBIN命令等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

0. 哈哈，想起来个好玩的函数WM_CONCAT，先上这个吧，执行完看看你看到了什么

with t as(
select '实习牧师' a,'Lv9' b from dual union all
select 'AI~非主流文文','起床成功' from dual  union all
select '拎砖四顾心茫然','浪客剑心' from dual  union all
select 'ToFishes','大胡子' from dual  union all
select '被X了的BUG','零波凌' from dual  union all
select '梦幻七彩瓶','ChanelＡ哆啦梦' from dual 
)
select wm_concat(b)over(order by a,b) from t

这个函数的用法也非常多，变种函数也很多，自己多多查找有个印象就好
1. Oracle的两种临时表
On Commit Delete Rows：数据在 Transaction 期间有效，一旦COMMIT后，数据就被自动 TRUNCATE 掉了；

 CREATE GLOBAL TEMPORARY TABLE QCUI_Temp_Trans2 ON COMMIT DELETE ROWS3 AS4 SELECT * FROM t_Department;

On Commit Preserve Rows ：数据在 Session 期间有效，一旦关闭了Session 或 Log Off 后，数据就被 ORACLE 自动 Truncate 掉。

 CREATE GLOBAL TEMPORARY TABLE QCUI_Temp_Sess2 ON COMMIT PRESERVE ROWS3 AS4 SELECT * FROM t_Department;

2. Oracle的分析函数之---ROW_NUMBER(),如果这个都不会那你一定要学习了，他会在你对待重复数据焦头烂额时帮你大忙（共26个分析函数，其余自己去谷歌，这里顺带给出with的一种示例用法）

with t as(
select '1' id, '1' item, 'A' name, '100' amount from dual union all
select '1' id, '2' item, 'B' name, '30' amount from dual union all
select '1' id, '3' item, 'A' name, '50' amount from dual union all
select '1' id, '4' item, 'B' name, '90' amount from dual union all
select '2' id, '1' item, 'A' name, '90' amount from dual union all
select '2' id, '2' item, 'B' name, '40' amount from dual union all
select '2' id, '3' item, 'C' name, '140' amount from dual)
SELECT id,item,name,amount
from
(
SELECT id,item,name,amount,
row_number() over(partition by id order by amount desc ) r1
from(
SELECT id,item,name,sum(amount) over(partition by id,name) amount
from t
) tt
)
WHERE r1=1

3. 游标，一直都是一个比较有争议的话题，之前在的一个公司写PL/SQL是不允许使用游标的，下面提供一种替代游标的方式，老规矩想深入请自己谷歌

TYPE items_type IS TABLE OF v_unit_items%ROWTYPE;
  unit_all_items items_type;

SELECT * BULK COLLECT INTO unit_all_items FROM v_unit_items
--这样就可以通过
For i In 1..unit_all_items.Last Loop  --来进行读取数据了。

4. 总是记不住数据导出命令是吧？哈哈，我也是，当做笔记了，随时查
--将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中

exp system/manager@TEST file=d:\daochu.dmp full=y

5. 10g数据库链无法创建绝杀
记得以前用PL/SQ Develop创建数据库链的时候总是出错，莫名其妙的无法创建，现在提供绝杀!!!!!!

create database link sdyy_wz
 connect to sdyy_wz
   identified by "sdyy_wz"
  using '(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521)) ) (CONNECT_DATA =(SERVICE_NAME = hbmaindb) ) )';

原因：由于10g之后区分大小写（只是区分并不是说区别）。而PL/SQL自动转换成大写。有时有创建数据库链无法使用的情况。使用上面语句。
6. 初级的程序员是很少遇到使用merge into和minus这两个语法的，但无论是作为面试只是，还是怎么都应该学习一下（由于例子涉及到公司数据库表结构，我又懒得修改就不贴上来了，Google、Google....）
7. 查看当前谁、在运行什么SQL

SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;

8. 表空间使用状态

select a.file_id "FileNo",a.tablespace_name "Tablespace_name",
round(a.bytes/1024/1024,4) "Total MB",
round((a.bytes-sum(nvl(b.bytes,0)))/1024/1024,4) "Used MB",
round(sum(nvl(b.bytes,0))/1024/1024,4) "Free MB",
round(sum(nvl(b.bytes,0))/a.bytes*100,4) "%Free"
from dba_data_files a, dba_free_space b
where a.file_id=b.file_id(+)
group by a.tablespace_name,
a.file_id,a.bytes order by a.tablespace_name

9. PURGE RECYCLEBIN

Oracle 10g和oracle 11g中有一个闪回的设置，如果在删除表时，没有进行truncate或者执行drop table table_names purge操作，而只执行drop table table_names 操作，则该表中的内容会被存储至oracle的备份（相当于回收站）中执行上句话PURGE RECYCLEBIN后，能够将其所占用的表空间收回！

原文地址：http://www.itips.me/view.jsp?a_id=72