Oracle PL/SQL 語法筆記

tags: Oracle

基本語法

• 時間、日期
  • SYSTIMESTAMP 會傳回資料庫所在系統的日期，時間，毫秒及時區資訊。
  • SYSDATE 預設返回日期格式資訊，其仍包含了時間資訊，但不包含毫秒資訊，也不包含時區(time zone)資訊。
• 字串串接
  • 合併兩個字串可使用串接符(Concatenation Operator)||。
    ​​​​​​​​SELECT 'hello' || ' ' || 'world' GREETING FROM DUAL;
    

  • 也可使用CONCAT函式來連接字串。
    ​​​​​​​​SELECT CONCAT('hello' , ' ' , 'world') FROM DUAL;
    

• 修改密碼
  ​​​​ALTER USER 帳號 IDENTIFIED BY 密碼;
  ​​​​--如果要unlock帳號
  ​​​​ALTER USER account IDENTIFIED BY password ACCOUNT UNLOCK;
  

• 從另一個表格更新資料
  ​​​​-- 假設 History table 只有 Product_id，現在新增 Product_Name column，
  ​​​​-- 但資料再另一個 table 有，要怎樣更新。
  ​​​​UPDATE history h
  ​​​​SET product_name = (
  ​​​​    SELECT product_name 
  ​​​​    FROM product p 
  ​​​​    WHERE p.product_id = h.product_id
  ​​​​)
  ​​​​WHERE EXISTS( 
  ​​​​    SELECT product_name 
  ​​​​    FROM product p 
  ​​​​    WHERE p.product_id = h.product_id
  ​​​​)
  

內建函式

• 使用 lengthb() 或 length() 計算字串長度
• EXTRACT() 抽取日期時間(datetime)格式資料中的指定資訊

  MySQL的EXTRACT()
  PHP的extract()

  ​​​​-- 範例：以EXTRACT抽取系統日期的各種時間資訊。
  ​​​​SELECT 
  
  ​​​​    SYSDATE "System datetime",
  ​​​​    EXTRACT(YEAR FROM SYSDATE) "Year",
  ​​​​    EXTRACT(MONTH FROM SYSDATE) "Month",
  ​​​​    EXTRACT(DAY FROM SYSDATE) "Day",
  
  ​​​​    EXTRACT(HOUR FROM SYSTIMESTAMP) "Hour",
  ​​​​    EXTRACT(MINUTE FROM SYSTIMESTAMP) "Minute",
  ​​​​    EXTRACT(SECOND FROM SYSTIMESTAMP) "Second"
  
  ​​​​FROM DUAL;
  ​​​​-- 抽取時分秒資訊時，只能從SYSTIMESTAMP取得
  

  ​​​​+-----------------+------+-------+-----+------+--------+----------+
  ​​​​| System datetime | Year | Month | Day | Hour | Minute |  Second  |
  ​​​​+-----------------+------+-------+-----+------+--------+----------+
  ​​​​| 16-NOV-19       | 2019 |    11 |  16 |   13 |     32 | 31.75173 |
  ​​​​+-----------------+------+-------+-----+------+--------+----------+
  

• CEIL()、FLOOR()
  
  
• over(partition by)函式的寫法

  使用 row_number() 來實現群組編排序號
  使用 ratio_to_report() 計算百分比

  ​​​​row_number() over(partition by ... order by ...)：返回分組排序後的順序
  ​​​​rank() over(partition by ... order by ...)：返回分組排序後的排名(並列第一名的情況下返回：第一名，第一名，第三名)
  ​​​​dense_rank() over(partition by ... order by ...)：返回分組排序後的排名（並列第一名的情況下返回：第一名，第一名，第二名)
  ​​​​count(A) over(partition by ... order by ...)：返回分組排序後的總數。
  ​​​​max(A) over(partition by ... order by ...)：返回分組排序後的最大值。
  ​​​​min(A) over(partition by ... order by ...)：返回分組排序後的最小值。
  ​​​​avg(A) over(partition by ... order by ...)：返回分組排序後的平均值。
  ​​​​sum(A) over(partition by ... order by ...)：返回分組排序後的累加求和。
  ​​​​lag(A,1) over(partition by ... order by ...)：取出上一列的A的值放到本列中。　　
  ​​​​lead(A,1) over(partition by ... order by ...)：取出下一列的A的值放到本列中。　　
  ​​​​ratio_to_report(A) over(partition by B) 返回分組後的A在其分割槽B內的佔比，A就是分子，B分的組就是分母
  

應用面

• 日期或時間區間衝堂檢查語法
• 依日期區間找符合星期條件的日期
  ​​​​<?php
  ​​​​// 
  ​​​​$beg = "開始日期";
  ​​​​$end = "結束日期";
  ​​​​$day = "星期幾(阿拉伯數字)";
  ​​​​// 抓符合星期的日期
  ​​​​$date = DB::select("SELECT TO_CHAR(to_date('$beg', 'YYYY-MM-DD')+ (LEVEL-1), 'YYYY-MM-DD') thedate
  ​​​​                    FROM DUAL
  ​​​​                    WHERE DECODE(TO_CHAR(to_date('$beg', 'YYYY-MM-DD')+ (LEVEL-1), 'D'),'1','7','2','1','3','2','4','3','5','4','6','5','6') = '$day'
  ​​​​                    CONNECT BY LEVEL < ( to_date('$end', 'YYYY-MM-DD') -  to_date('$beg', 'YYYY-MM-DD') +2)");
  

  ​​​​--取2022-01一每週一
  ​​​​SELECT 'INSERT tablea (ba_date) VALUES ('''||thedate||''');'
  ​​​​FROM (
  ​​​​    SELECT TO_CHAR(to_date('2022-01-01', 'YYYY-MM-DD')+ (LEVEL-1), 'YYYY-MM-DD') thedate
  ​​​​    FROM DUAL
  ​​​​    WHERE DECODE(TO_CHAR(to_date('2022-01-01', 'YYYY-MM-DD')+ (LEVEL-1), 'D'),'1','7','2','1','3','2','4','3','5','4','6','5','6') = '1'
  ​​​​    CONNECT BY LEVEL < ( to_date('2022-01-31', 'YYYY-MM-DD') -  to_date('2022-01-01', 'YYYY-MM-DD') +2)
  ​​​​);
  

• 所有的View重新compile
  ​​​​SELECT 'ALTER VIEW '||owner||'.'||view_name||' COMPILE;'
  ​​​​FROM dba_views where owner='DPC009';
  

• TABLE或COLUMN註解
  ​​​​SELECT 'COMMENT ON TABLE '||OWNER||'.'||TABLE_NAME||' IS '''||COMMENTS||''';' FROM ALL_TAB_COMMENTS WHERE OWNER='使用者帳號';
  ​​​​SELECT 'COMMENT ON COLUMN '||TABLE_NAME||'.'||COLUMN_NAME||' IS '''';' FROM ALL_COL_COMMENTS WHERE TABLE_NAME='表名稱';
  
  

表空間查詢 (TABLESPACE)

oracle表空間不足，相關查詢和處理

   SELECT a.tablespace_name "表空間名",
          a.bytes / 1024 / 1024 "表空間大小(MB)",
          (a.bytes - b.bytes) / 1024 / 1024 "已使用空間(MB)",
          b.bytes / 1024 / 1024 "空閒空間(MB)",
          round(((a.bytes-(a.bytes - b.bytes)) / a.bytes) * 100, 2)||'%' "剩餘空間比例"
   FROM (
      SELECT tablespace_name, sum(bytes) bytes
      FROM dba_data_files
      GROUP BY tablespace_name) a,
   (
      SELECT tablespace_name, sum(bytes) bytes, max(bytes) largest
      FROM dba_free_space
      GROUP BY tablespace_name) b
   WHERE a.tablespace_name = b.tablespace_name
   ORDER BY ((a.bytes-(a.bytes - b.bytes)) / a.bytes) ASC;