# 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() 計算字串長度](https://hackmd.io/@nfu-johnny/Hk8oaCxod)
* [`EXTRACT()`](https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions059.htm#SQLRF00639) 抽取日期時間(datetime)格式資料中的指定資訊
> [MySQL的`EXTRACT()`](https://www.fooish.com/sql/mysql-extract-function.html)
> [PHP的`extract()`](https://www.php.net/manual/en/function.extract.php)

    -- 範例：以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() 來實現群組編排序號](https://hackmd.io/@nfu-johnny/HywpWAgj_)
> [使用 ratio_to_report() 計算百分比](https://hackmd.io/@nfu-johnny/rJNueRxoO)

    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分的組就是分母

## 應用面
* [日期或時間區間衝堂檢查語法](https://hackmd.io/@nfu-johnny/rJCTz0giO)
* 依日期區間找符合星期條件的日期

    <?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表空間不足，相關查詢和處理](https://www.twblogs.net/a/5bf37cb8bd9eee37a1430165#google_vignette)

   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;