本文共 4196 字，大约阅读时间需要 13 分钟。

Oracle SQL技巧总结

随着对Oracle数据库的使用不断深入，很多开发者都会遇到各种问题，尤其是在处理复杂查询、数据处理以及系统信息获取等方面。为了帮助大家解决这些问题，以下将整理一些常用的Oracle SQL技巧，供大家参考。

---

一、时间与日期处理

在处理时间和日期时，Oracle提供了丰富的函数和工具，可以帮助我们高效地完成时间格式转换和比较等操作。

1. 获取当前时间

SELECT SYSTIMESTAMP FROM DUAL;

2. 获取指定时区的时间

SELECT SYSTIMESTAMP AT TIME ZONE dbtimezone FROM DUAL;

3. 处理时间为null时的时区转换

SELECT SYSTIMESTAMP AT TIME ZONE COALESCE(null,DBTIMEZONE) FROM DUAL;

4. 判断是否为null

SELECT COALESCE(null,DBTIMEZONE) FROM DUAL;

5. 截断时间或日期

SELECT TRUNC(SYSTIMESTAMP AT TIME ZONE COALESCE(null,DBTIMEZONE)) FROM DUAL;

6. 日期转换

SELECT TO_DATE('031095','MMDDYY') FROM DUAL;

---

二、会话信息查询

通过USERENV函数，我们可以获取当前会话的详细信息，包括语言、终端、会话ID等。

1. 获取当前会话信息

SELECT USERENV('LANGUAGE') FROM DUAL;

2. 获取更多会话信息

SELECT SYS_CONTEXT('USERENV','TERMINAL') terminal,
       SYS_CONTEXT('USERENV','LANGUAGE') language,
       SYS_CONTEXT('USERENV','SESSIONID') sessionid,
       SYS_CONTEXT('USERENV','INSTANCE') instance,
       SYS_CONTEXT('USERENV','ENTRYID') entryid,
       SYS_CONTEXT('USERENV','ISDBA') isdba,
       SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
       SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
       SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
       SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
       SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
       SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
       SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
       SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
       SYS_CONTEXT('USERENV','SESSION_USER') session_user,
       SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
       SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
       SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
       SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
       SYS_CONTEXT('USERENV','DB_NAME') db_name,
       SYS_CONTEXT('USERENV','HOST') host,
       SYS_CONTEXT('USERENV','OS_USER') os_user,
       SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
       SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
       SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
       SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
       SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
       SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
       SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
FROM DUAL;

---

三、数据处理技巧

在处理数据时，Oracle提供了一些高级函数可以帮助我们简化操作。

1. 获取多个记录的数据

SELECT column FROM table WHERE ROWNUM <= 10;

2. 获取表或视图的DDL脚本

SELECT dbms_metadata.get_ddl('VIEW', 'RESERVATION_GENERAL_VIEW') FROM DUAL;

3. 获取包的代码

SELECT text FROM user_views where rownum < 100;

4. 查看包或存储过程的脚本

SELECT * FROM ALL_SOURCE WHERE name='GET_SPRCIAL_REQUESTS' AND rownum < 10;

5. 解密包的代码

包中的代码通常会使用DfUnWraper进行加密，可以通过下载工具解密。

---

四、常用函数

1. DECODE函数

DECODE(value, if1, then1, if2, then2, ..., else, default)

2. COALESCE函数

COALESCE(expression [ ,...n ])

3. NVL / NVLN函数

NVL(expression1, expression2)

4. SIGN函数

SIGN(n)

返回数字n的符号：大于0返回1，小于0返回-1，等于0返回0。

---

五、其他操作

1. 获取当前架构

SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;

2. 查看用户表

SELECT * FROM all_users ORDER BY username;

3. 查看对象

SELECT * FROM dba_objects WHERE rownum < 10;

---

六、常见错误处理

1. 列转行错误

WITH temp AS (
    SELECT 500 population, 'China' nation, 'Guangzhou' city FROM DUAL
    UNION ALL
    SELECT 1500 population, 'China' nation, 'Shanghai' city FROM DUAL
    UNION ALL
    SELECT 500 population, 'China' nation, 'Beijing' city FROM DUAL
    UNION ALL
    SELECT 1000 population, 'USA' nation, 'New York' city FROM DUAL
    UNION ALL
    SELECT 500 population, 'USA' nation, 'Bostom' city FROM DUAL
    UNION ALL
    SELECT 500 population, 'Japan' nation, 'Tokyo' city FROM DUAL
)
SELECT nation, wmsys.wm_concat(city) 
FROM (
    SELECT nation, city FROM temp ORDER BY city
) 
GROUP BY nation;

2. 日期比较

UPDATE_DATE <= TO_DATE('', 'yyyy-mm-dd') 
AND (rn.UPDATE_DATE <= TO_DATE('', 'yyyy-mm-dd') OR rn.UPDATE_DATE IS NULL);

3. 截断小数

SELECT trunc(1.787) FROM DUAL;

4. 判断空字符串

'' IS NULL

---

七、执行查询

SELECT * FROM v$sqlarea ORDER BY first_load_time DESC;
SELECT * FROM v$sql ORDER BY first_load_time DESC;

---

八、函数开发

CREATE FUNCTION get_products(in_test IN VARCHAR2) RETURN VARCHAR2 IS
...
RETURN rets;

---

以上技巧希望能为大家提供帮助，感兴趣的话可以继续探索Oracle数据库的更多奥秘。

转载地址：http://qmpfk.baihongyu.com/