【PL/SQL】Oracle数据库开发SQL积累

  整理一下在日常工作中，使用到的一些基本SQL语句，有一些是可以累积起来后面长期使用的，只需要调整一下相关的条件内容。如有错误之处，请及时指出。
  现在主要是基于Oracle 12C数据库，以及Oracle EBS 系统的基础环境，进行相关的开发工作。以下内容包括2中，一种是纯SQL，一种则是PL/SQL的存储过程。

1. 日期时间处理类：

(1) 从日期中直接提取年份和月份

SELECT *
  FROM bom.bom_calendar_dates bcd
 WHERE bcd.calendar_code = 'FIN_CAL'
   AND bcd.seq_num IS NOT NULL
   AND extract(YEAR FROM calendar_date) <= 2025
   AND extract(MONTH FROM calendar_date) = 6;

(2) 获取全年内全部星期

  仅计算出了所有的周一到周五的工作日，以及周六周天的休息日，并没有排除国家法定休息日和调休日期。

SELECT (to_date('20250101', 'YYYYMMDD') + LEVEL - 1) AS week_date,
       CASE to_char(to_date('20250101', 'YYYYMMDD') + LEVEL - 1,
                'DY',
                'NLS_DATE_LANGUAGE=AMERICAN')
         WHEN 'SAT' THEN
          0
         WHEN 'SUN' THEN
          0
         ELSE
          1
       END AS weekday
  FROM dual
CONNECT BY to_date('20250101', 'YYYYMMDD') + LEVEL - 1 <=
           (add_months(to_date('20250101', 'YYYYMMDD'), 12) - 1);

(3) 查找更新日期在距离当前时间前2小时内的数据

SELECT gjl.last_update_date,gjl.*
  FROM gl.gl_je_lines gjl
WHERE gjl.last_update_date >= SYSDATE - INTERVAL '2' hour;

(4) 查找更新日期在距离当前时间前20分钟内的数据

SELECT gjl.last_update_date,gjl.*
  FROM gl.gl_je_lines gjl
WHERE gjl.last_update_date >= SYSDATE - INTERVAL '20' minute;

(5) DATE 格式化日期

-- date 和 日期之间有一个空格
SELECT DATE '2025-01-01' FROM dual;

(6) 获取当前日期之后的下一个指定星期的日期

例如：计算从今天开始算起的下一个星期一的日期

SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM DUAL;

(7) 获取指定日期所在月份的最后一天

SELECT last_day(trunc(sysdate)) FROM dual;

2、WebService 接口报文查询(EBS)

  获取指定时间范围内接收到的webservice接口请求，找到其对应的 message_id ：

SELECT t.message_id,t.*
  FROM applsys.fnd_soa_request t
WHERE request_timestamp >=
       to_date('2025/2/17 14:42:00', 'YYYY/MM/DD HH24:MI:SS')
   AND request_timestamp <=
       to_date('2025/2/17 14:43:00', 'YYYY/MM/DD HH24:MI:SS')
   AND ip_address = '192.168.199.155';

  根据此 id 去获取对应的请求报文：

SELECT fsr.irep_name
      ,fsr.request_timestamp
      ,fsbp.belongs_to
      ,fsbp.body
      ,dbms_lob.substr(fsbp.body
                      ,400
                      ,dbms_lob.instr(fsbp.body, '92846.01') - 200)
  FROM apps.fnd_soa_request fsr, apps.fnd_soa_body_piece fsbp
WHERE fsr.message_id = fsbp.message_id
   AND fsr.irep_name = 'CUX_AP_IMP_WS_PKG'
   AND fsbp.message_id IN ('1494917', '1494918', '1494919')
ORDER BY fsr.request_timestamp DESC;

3、行转列函数

SELECT account
  FROM (SELECT '123' AS acct1,
               '126' AS acct2,
               '127' AS acct3,
               '129' AS acct4
          FROM dual) unpivot(account FOR col_name IN(acct1,
                                                     acct2,
                                                     acct3,
                                                     acct4)
       );

4、字符转移方法

  可以使用 escape 关键字对 \ 进行转义：

SELECT *
  FROM dba_tables t
WHERE t.owner = 'CUX'
   AND t.table_name LIKE 'CUX\_AP\_%' ESCAPE '\';

+++========================================================+++
整理不足，后面持续补充中......
以上内容仅为个人学习记录使用。
如有转载，请附有原文出处链接