PLSQL Developer 好用的設定

※顯示行號

SQL的行號

PL SQL 的行號

※编辑用快速鍵

指的是下 SQL 時的快速鍵，Tools-->Preferences

※此時可以用如下的設定
S=SELECT * FROM
W=WHERE
GB=GROUP BY
OB=ORDER BY

=前後不能空，要使用時，只要下S空格，就會出=後面的字了

※此檔當然不能刪除

※功能快速鍵

※用滑鼠按右邊想改的地方會反白，此時按想設定的快速鍵即可
以下是個參考，設定成與 Eclipse 一樣
Edit / Undo：Ctrl+Z
Edit / Redo：Ctrl+Y
Edit / PL/SQL Beautifier：Shift+Ctrl+F
Edit / Selection / Indent：Tab
Edit / Selection / Unindent：Shift+Tab
Edit / Selection / Uppercase：Shift+Ctrl+X
Edit / Selection / Lowercase：Shift+Ctrl+Y
Editor: Delete Line：Ctrl+D

註解和反註解：
Edit / Comment：Shift+Ctrl+C
Edit / Selection / Uncomment：
這是多行註解 (/**/)，這個軟體必需要用滑鼠選完文字後，才有用，而且不能有兩個都是一樣的快速鍵

※執行一行 SQL

※大部分的人一行都是一句 SQL，一句是以「;」區分的

※假設在編輯區全部有三句 SQL，按下如上的快速鍵 (F8)，每句都會執行

※但大部分的時候，想要執行的只是游標所在的那一行(一句)，此時可以如下的設定

※此時只會執行游標當前行，但二句(含)以上會出錯

增加ORCL

做完此篇的動作，即可使用PL/SQL Developer

安裝完 Oracle 後，ORCL 的路徑在 app\BAU\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
想增加可用Net Manager


1.在程式集可看到如下的畫面
 
2.選到Service Naming 後，按旁邊綠色的加號，名字是待會在PL/SQL Developer 登入時的 Database 欄位會出現的 (做完後Service Naming下也會出現)


3.選預設的即可


4.這IP要和專案的人要


5.Service Name 也是要和專案的人要，如 192.168.11.22:1521:xxx 裡的xxx


6.不放心可按 Test... 測試一下，有自信就直接按 Finish 了


7.如果第 6 步是選 Test...，就會看到這個畫面，按 Change Login...，可用帳號密碼測試


按下完成後，會在app\BAU\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora 裡的內容發現已經增加了

12c 的 OFFSET (DML 十四)

取出資料的前幾筆或中間的資料(就是MS SQL的top)，終於在12c有了，官網連結，搜尋「OFFSET」可找到語法，再搜尋「Row Limiting: Examples」，有範例可參考

要放在SELECT的最下面，複習一下語法：
1.SELECT：必要
2.FROM：必要
3.WHERE：選擇使用
4.GROUP BY ：選擇使用
5.HAVING：有GROUP BY才可用
6.ORDER BY：選擇使用
7.OFFSET：選擇使用


語法如下(自己想的，不一定正確，看下方的紅字)：
[OFFSET n [ROW|ROWS]] FETCH [FIRST|NEXT] n [PERCENT] [ROW|ROWS] [ONLY|WITH TIES]

※最後的PERCENT和ONLY要寫其中一個

※筆數是從0開始的

※OFFSET不指定，預設是 OFFSET 0 [ROW|ROWS]，表示從第0筆開始

※OFFSET後的數字給負的，都表示0；如果給null或大於等於返回的行數，那就沒有結果返回

※FETCH [FIRST|NEXT] 表示取出幾筆資料，官方說不指定會將全部的結果返回，但我試的結果，不加就是編譯錯誤

※PERCENT是取百分筆用的(官方寫兩種，都一定是給數字，我懷疑它要如何分辯)，如果給負數視同0；給null沒有結果返回
官方說如不給數字，預設是1，但我試的結果是編譯錯誤

※ONLY|WITH TIES兩者選者其一，但官方有說使用WITH TIES要配合ORDER BY使用，不然沒有結果會返回，但我試的結果和ONLY一樣

※最後一定要有 ROW ONLY、ROW WITH TIES、ROWS ONLY、ROWS WITH TIES 這四者其中之一語法才是正確的

※範例

SELECT * FROM emp OFFSET 10 ROWS FETCH NEXT 2 ROW ONLY;
SELECT * FROM emp OFFSET 10 ROWS;
SELECT * FROM emp FETCH FIRST 3 ROWS ONLY;
SELECT * FROM emp FETCH NEXT 10 PERCENT ROWS ONLY;
SELECT * FROM emp FETCH FIRST 10 PERCENT ROWS WITH TIES;

※第一條SQL表示忽略前10筆，取兩筆記錄，所以顯示第11和12筆

※第二條SQL表示從第11筆之後開始到最後

※第三條SQL表示取出前三筆

※第四、五條SQL都表示取10%的記錄，因為全部有14筆，所以是1.4
官方說小數會被截斷，可是我試的結果並不是，也不會四捨五入，直接進1，結果是2條記錄

SEQUENCE、INDEX (DDL 六)

※SEQUENCE

其他的資料庫有自動流水號的功能，而Oracle在11g(含)之前都沒有，之前都一直用這個Sequence

※增刪改查

CREATE SEQUENCE xxx;
CREATE SEQUENCE ooo START WITH 5;
CREATE SEQUENCE zzz START WITH -5 MINVALUE -2000;
CREATE SEQUENCE yyy INCREMENT BY -2000;
CREATE SEQUENCE aaa MAXVALUE 2000 MINVALUE 1 INCREMENT BY 40 CACHE 48 CYCLE;
    
ALTER SEQUENCE aaa MAXVALUE 2500;
    
DROP SEQUENCE aaa;
    
SELECT xxx.NEXTVAL from dual;
SELECT ooo.NEXTVAL from dual;
SELECT zzz.CURRVAL from dual;
    
SELECT * from USER_SEQUENCES

※設定完用NEXTVAL和CURRVAL，可以得知下一個和目前的sequence是多少，但剛創建完一定要執行過NEXTVAL後，才能執行CURRVAL

※選項

官方文件：創建、修改、刪除
可以得知創建總共有16種選項，修改有15種(少了創建的START WITH)

以下的預設值沒特別寫就是NOxxx：
START WITH：這個選項ALTER沒有，是設定初始值的，當然不會有
    預設值正數是MINVALUE，負數是MAXVALUE
INCREMENT BY：一次增加多少，正數往上增加；負數往下，預設是1
MAXVALUE／NOMAXVALUE：設定最大值，預設是10的28次方減1
MINVALUE／NOMINVALUE：設定最小值，預設是0，可以設到10的27次方減1
CYCLE／NOCYCLE：要不要循環，不環循到臨界點的時候就會出錯誤訊息
CACHE／NOCACHE：預設是20，用完了再去資料庫要20，最主要是要降低和資料庫字典要值的次數，可以增加效能

以下六個我無法理解：
ORDER／NOORDER
KEEP／NOKEEP
SESSION／GLOBAL：預設是GLOBAL

※注意

設定CACHE容易出現的錯誤：

CREATE SEQUENCE aaa MAXVALUE 2000 MINVALUE -200 INCREMENT BY -40 CACHE 48 CYCLE;

如果沒設定好會出「ORA-04013: 要放到 CACHE 中的數目必須小於一個循環週期」的錯
官方有個公式(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)
如果CACHE大於這個數字就會報這個錯(等於不會)
CEIL是天花板，就是往上(正數)；ABS就是絕對值(絕對是正數的值)
以這個例子就是
(CEIL(2000-(-200))) / ABS(-40)
2200/40 = 55
所以只要CACHE設定超過55就會報這個錯

※流水號

流水號終於在12c這一版增加了

CREATE TABLE t1 (
    id NUMBER GENERATED AS IDENTITY,
    name VARCHAR2(10)
);
    
CREATE TABLE t2 (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10),
    name VARCHAR2(10)
);

※第一條SQL是不加選項，什麼都用預設的

※要加選項就用第二條SQL

※INDEX

官網連結
主要是加快查詢速度用的

語法：
CREATE [UNIQUE|BITMAP] INDEX INDEX ON 表(欄[,欄...]) [USABLE|UNUSABLE]
最後面什麼都不打，預設是USABLE，表示可以用的意思

使用sys登入後，下「SET AUTOTRACE ON;」(要關掉改OFF即可)

※要注意每次執行完SQL語句都要按一次上面的按鈕才會更新

※如果用sqlplus，還可以看到更詳細的資訊，如時間…等

※執行最後一條SQL後，紅框的FULL會變成ROWID，ROWID是效能最快的；FULL指的是一行一行去掃描

※由於INDEX在工作時，我從來沒用過，不知會出什麼問題，只能提供官網連結(上面有提供了)，拉到最下面也有很多範例，還有一個我覺得寫的很好的文章

※如要刪除，就下「DROP INDEX index名稱」

VIEW、SYNONYM (DDL 五)

※VIEW

官網連結
使用View時，在12c預設是沒有權限的
要用有權限的帳號登入後，下「GRANT CREATE VIEW TO 帳號;」

view的功能就是將很複雜的SQL變的很簡單，讓大家使用，所以寫view的SQL都很強，它是連到其他的table，所以其他的table改變，它也會跟著變

※範例

CREATE OR REPLACE NOFORCE VIEW v_xxx
AS
SELECT deptno dno, empno eno, dname, ename 
FROM emp natural join dept;

※一定要打AS，不能打IS

※FORCE／NOFROCE：預設是NOFORCE，所以上面的範例NOFORCE可以不打
FORCE表示連到的表不存在，也要創建view，雖然創建好了，但是還是不能SELECT
可能是多人開發時，有些table還沒創建好，但你負責的部分是要寫view，如果沒這個選項你就沒辦法寫了

※AS下面就是隨個人發揮，寫簡單的也可以，只是沒意義，欄位可以打別名

※OR REPLACE也是選項，有打的話，發現同名稱會覆蓋

※刪除view，就下「DROP VIEW v_xxx;」，沒有PURGE的選項，因為本來就是連別的表

※查詢view

SELECT * FROM tab WHERE TABTYPE = 'VIEW';
SELECT * FROM user_views;

※TAB是所有的TABLE，只是看大概的訊息

※更詳細的就要看USER_VIEWS了

※view的增刪改

增刪改當然是針對原表，而view的增刪改是有限的，這樣看你怎麼樣下SQL了
以一張表為例
正常情況和一般增刪改一樣
可是如果表裡有not null、PK的欄位，但你下SQL並沒有把它SELECT出來，所以新增就沒辦法了，修改當然也不能改成null，刪除如果有FK、PK的關係有是有可能刪不了

多張表的情況也類似這樣，反正它只能儘量增刪改
要以下出來的條件為準，view只是一個連結，而這個連結可以讓你增刪改，不能的話就要看你怎麼下SQL了

可以使用delete，但不能使用truncate

※WITH READ ONLY

CREATE OR REPLACE NOFORCE VIEW view_test
AS
SELECT * FROM EMP WHERE deptno = 30 AND job = 'SALESMAN'
WITH READ ONLY;

※加上這個語句就只能查詢而已

※WITH CHECK OPTION [CONSTRAINT xxx]

CREATE OR REPLACE NOFORCE VIEW view_test
AS
SELECT * FROM EMP WHERE deptno = 30 AND job = 'SALESMAN'
WITH CHECK OPTION;

※加上這個語句就不能修改where的欄位，也不能新增；但可以刪除

※CONSTRAINT是給一個約束名稱，不給系統會幫你產生

※以下這兩個都不能成功

UPDATE view_test SET job = 'xxx' WHERE empno = 7499;
INSERT INTO view_test(empno, ename)values(9999, 'BRUCE');

※SYNONYM

SYNONYM翻譯成代名詞，官網連結，簡單來說就是一個table的別名，這個別名等同原來的table，所以也可以增刪改查，而且原來的表也會更動


使用SYNONYM必須用sys登入才有權限

CREATE [PUBLIC] SYNONYM 代名詞名稱 FOR 帳號.表;
CREATE SYNONYM bruce FOR ooo.EMP;

※因為是用sys創建的，所以select * from bruce，只有sys帳號才看得到，如果想讓大家都看到，就要使用CREATE PUBLIC

※完成後也能在USER_SYNONYMS這張表找到

※刪除就下「DROP SYNONYM 代名詞名稱;」

完整性約束的型態 (DDL 三)

參考官網，用法和範例可參考這連結，總共分成六種：
1.NOT NULL：禁止放null

2.UNIQUE：同一欄位只能唯一，但允許放null

3.PRIMARY KEY(PK)：NOT NULL + UNIQUE

4.FOREIGN KEY(FK)：欄位指定FK後，表示此欄位的值必需是另外一張表的欄位值的其中之一

5.CHECK：符合指定的條件

6.REF：參考到另外一個物件，個人覺得這個比較不像約束，比較像自定型態，很多書寫完整性約束時也沒有提到這個，不過官網有就是了

※NOT NULL只能放在宣告欄位的最後面，其他的除了這種方式，還有一種是為約束命名的寫法，這個是官方說的，可是FK我試的結果只能用約束命名的寫法(而且只能使用CONSTRAINT獨立一行)，官網也沒有提供不寫約束名稱的範例

※以下以這個範例來練習，先寫沒有約束名稱的寫法

DROP TABLE xxx PURGE;
CREATE TABLE xxx (
    id number(5) PRIMARY KEY,
    name varchar2(20) UNIQUE,
    color varchar2(10) NOT NULL,
    price number(5,2) DEFAULT 0 CHECK(price BETWEEN 0 AND 1000),
    make_date timestamp DEFAULT sysdate UNIQUE
);
    
    
CREATE TABLE ooo (
    oid number(5) PRIMARY KEY,
    oname varchar2(20) UNIQUE,
    xid number(5),
    CONSTRAINT fk_xid FOREIGN KEY(xid) REFERENCES xxx(id)
);

※DEFAULT一定要在約束前面，如果有DEFAULT，INSERT時可以不塞值

※NOT NULL

INSERT INTO xxx(id, color) VALUES (1, null);
INSERT INTO xxx(id) VALUES (1);

※預設不打就是null，所以這兩條SQL是一樣的

※因為color是null，所以會出「ORA-01400: 無法將 NULL 插入 ("帳號"."XXX"."NAME")」的錯

※UNIQUE

INSERT INTO xxx(id, name, color, price) VALUES (1, 'apple', 'red', 50);
INSERT INTO xxx(id, name, color, price) VALUES (2, 'apple', 'blue', 40);

※第一條SQL新增成功，但第二條的name值一樣，所以會出「ORA-00001: 違反必須為唯一的限制條件」的錯

※null可以重覆，官網有說明

※PRIMARY KEY

一筆記錄的唯一值，所以是NOT NULL + UNIQUE

※CHECK

INSERT INTO xxx(id, name, color, price) VALUES (1, 'apple', 'red', 1002);

※因為price的範圍是0~1000，1002已經超過了，所以會出「ORA-02290: 違反檢查條件 (C##SCOTT.SYS_C0010287)」的錯

※FOREIGN KEY

一定要有兩張表而且FK對應的欄位必需有UNIQUE(所以PK也可以)，而且型態也要一樣

只要欄位有FK，那就表示它的值有多筆，對應到另外一個table的欄位值(除非另外一個table的欄位值只有一種)
因為這樣的原因，所以刪除時，預設也必須先刪除子項(FK)的欄位，才能刪除父項(PK)的欄位

INSERT INTO xxx(id, name, color) VALUES (1, 'aaa', 'red');
INSERT INTO xxx(id, name, color) VALUES (2, 'bbb', 'blue');
    
    
INSERT INTO ooo(oid, oname, xid) VALUES (2, 'qoo', 3);

※xxx新增兩條SQL，而ooo的xid是對應到xxx的id，而xxx的id值，全部的值有1和2，所以ooo的xid不塞1或2，就會發生「ORA-02291: 違反完整性限制條件 (C##SCOTT.FK_XID) - 找不到父項索引鍵」的錯

※null是可以的

※REF

-- 1.新增TYPE
DROP TYPE aaa;
CREATE TYPE aaa AS OBJECT (
    p1 varchar2(40),
    p2 varchar(2)
);
    
-- 2.新增Table，並使用REF型態
DROP TABLE bbb PURGE;
CREATE TABLE bbb (
    id number, 
    ref_name REF aaa
);
    
-- 3.新增另一張Table，並引用aaa TYPE，順便塞值
DROP TABLE table_a PURGE;
CREATE TABLE table_a OF aaa;
INSERT INTO table_a VALUES('1', 'a');
INSERT INTO table_a VALUES('2', 'b');
    
-- 4.新增資料
INSERT INTO bbb(id, ref_name)VALUES(1, 
    (SELECT REF(t) FROM table_a t WHERE P1 = '1')
);
INSERT INTO BBB(id, ref_name)VALUES(2, 
    (SELECT REF(t) FROM table_a t WHERE P1 = '2')
);

※主要是2的bbb表，它要使用一個TYPE，所以1才要新增TYPE
4要新增時，不知道怎麼塞值，所以3就引用的aaa TYPE
所以使用REF是不能直接在裡面塞值的，要像3這樣引用才可以

※修改可參考PL/SQL 三十三

※以上的6種約束可以混用，用空格隔開如「NOT NULL UNIQUE」，但不是全部都可以，詳情要看官網的說明，例如PRIMARY KEY UNIQUE就不行

※有約束名稱的寫法

CREATE TABLE xxx (
    id number(5),
    name varchar2(20),
    color varchar2(10) NOT NULL,
    price number(5,2) DEFAULT 0,
    make_date timestamp DEFAULT sysdate UNIQUE,
    
    CONSTRAINT pk_id PRIMARY KEY(id),
    CONSTRAINT uk_name UNIQUE(name),
    CONSTRAINT ck_price CHECK(price BETWEEN 0 AND 1000)
);
    
    
CREATE TABLE xxx (
    id number(5) CONSTRAINT pk_id PRIMARY KEY,
    name varchar2(20) CONSTRAINT uk_name UNIQUE,
    color varchar2(10) NOT NULL,
    price number(5,2) DEFAULT 0 CONSTRAINT ck_price CHECK(price BETWEEN 0 AND 1000),
    make_date timestamp DEFAULT sysdate UNIQUE
);

※PK、UNIQUE、CHECK都可以獨立成一行或宣告欄位的最後面

※獨立一行還可以設定多個欄位，下面會介紹

※PK、FK、UNIQUE多個欄位

DROP TABLE xxx PURGE;
CREATE TABLE xxx (
    id number(5),
    name varchar2(20),
    color varchar2(10) NOT NULL,
    price number(5,2) DEFAULT 0 UNIQUE,
    make_date timestamp DEFAULT sysdate UNIQUE,
    
    CONSTRAINT pk_id_name PRIMARY KEY(id, name),
    CONSTRAINT uk_name UNIQUE(name),
    CONSTRAINT ck_price CHECK(price BETWEEN 0 AND 1000)
);
    
DROP TABLE ooo PURGE;
CREATE TABLE ooo (
    oid number(5),
    oname varchar2(20),
    xid number(5),
    xname varchar2(20),
    
    CONSTRAINT pk_oid_xid PRIMARY KEY(oid, xid),
    CONSTRAINT fk_xid_xname FOREIGN KEY(xid, xname) REFERENCES xxx(id, name),
    CONSTRAINT uk_oname_xid UNIQUE(oname, xid)
);

※主要是看ooo表，而FK對應的表欄位兩個欄位也要有UNIQUE(所以PK也可以)

※新增／刪除約束

-- 1
ALTER TABLE xxx MODIFY(color varchar2(25));
    
-- 2
ALTER TABLE ooo DROP CONSTRAINT fk_xid;
ALTER TABLE xxx DROP CONSTRAINT pk_id;
ALTER TABLE xxx ADD CONSTRAINT pk_id PRIMARY KEY(id);
ALTER TABLE ooo ADD CONSTRAINT fk_xid FOREIGN KEY(xid) REFERENCES xxx(id);
    
-- 3
ALTER TABLE xxx DROP CONSTRAINT ck_price;
ALTER TABLE xxx ADD CONSTRAINT ck_price CHECK(price BETWEEN 500 AND 2000);
    
-- 4
ALTER TABLE xxx DISABLE /*ENABLE*/ CONSTRAINT ck_price;

※1是NOT NULL用的

※2我在官網沒有看到修改約束和修改約束名稱的功能，所以只好先刪除再新增了

※2和3是PK、FK、CHECK

※4是將約束啟用／停用的功能

※因為有這個功能，所以有些地方是先新增表，創建完了才用這種語法加一些限制條件

※FK還有重要的功能，下一篇再寫

ALTER、COMMENT (DDL 二)

這一篇還是DDL，所以ALTER、COMMENT執行完也會將前面沒有commit的進行commit

前一篇是針對表的增刪改，下面是針對表裡的欄位的增刪改

ALTER TABLE <table> ADD <field> <type> [default];-- 增加一個欄位

ALTER TABLE <table> ADD (<field> <type> [default], <field> <type> [default]);-- 增加多個欄位
    
ALTER TABLE <table> DROP COLUMN <field>;-- 刪除一個欄位
ALTER TABLE <table> DROP (<field>, <field>);-- 刪除多個欄位
    
ALTER TABLE <table> MODIFY <field> <type> [default] [not null];-- 修改欄位，不包括欄位重命名
ALTER TABLE <table> RENAME COLUMN <old_field> TO <new_field>;-- 欄位重命名用這個

※官網連結

※删除约束可用「ALTER TABLE <table> Drop CONSTRAINT <約束名稱>;」
※增加UK：ALTER TABLE <table> ADD CONSTRAINT <約束名稱> UNIQUE(欄位);
※增加PK：ALTER TABLE <table> ADD CONSTRAINT <約束名稱> PRIMARY KEY(欄位);
※增加複合主鍵：前面和「增加PK」一樣，最後是PRIMARY KEY(欄位,欄位...);
※增加FK：ALTER TABLE <table> ADD CONSTRAINT <約束名稱> FOREIGN KEY(欄位) REFERENCES 另一張表名稱(另一張表欄位);

※UNUSED

如果資料太多，會刪的很慢，所以可以先設定成不使用，等到沒什麼人用資料庫時再刪，例如凌晨時再刪

變成不使用的狀態後，SELECT看不到不使用的欄位

ALTER TABLE xxx SET UNUSED COLUMN price; -- 將一個欄位設為不使用
ALTER TABLE xxx SET UNUSED(name); -- 將一個欄位設為不使用
ALTER TABLE xxx SET UNUSED(name, id); -- 將多個欄位設為不使用
ALL_UNUSED_COL_TABS -- 這張表可以得知表裡有幾個不使用欄位
ALTER TABLE xxx DROP UNUSED COLUMNS; -- 刪除不使用的欄位

※官網我沒有看到恢復UNUSED變成使用狀態，所以小心使用

※如果想要恢復還可以用下面要講的VISIBLE

※VISIBLE／INVISIBLE

這個功能是Oracle 12c才有的，可以用V$VERSION這張表，看資料庫版本

在新增時，如果欄位名稱不夠，會編譯錯誤，而這個功能可以讓SELECT、DESC時，看不到這個欄位，新增也不需要用到這個欄位，它會直接給null

ALTER TABLE xxx MODIFY(name invisible);
    
CREATE TABLE xxx (
    id number(5),
    name varchar2(20) INVISIBLE
);

※第一條SQL是讓name欄位看不見，要回復只要改成VISIBLE即可

※也可以像第二條SQL那樣，在新增表時就使用

※雖然看不見欄位，但用下面要講的COMMENT，USER_COL_COMMENTS 這張表就能看到了

※COMMENT

就是為表和欄位增加註解，讓人家知道這個表和欄位是做什麼用的

COMMENT ON TABLE <table> IS 'xxx';
COMMENT ON COLUMN <table>.<field> IS 'ooo';
    
SYS.USER_TAB_COMMENTS -- 看所有表的註解
SYS.USER_COL_COMMENTS -- 看所有欄位的註解

CREATE、DROP、RENAME、TRUNCATE、回收表 (DDL 一)

※DDL

之前講的都是DML(資料處理語言)，針對表的增刪改查，做完要commit，其他的session才會看的到
而DDL(資料定義語言)，是創建、刪除、修改表，把它想成執行完會自動commit，所以待會講到CREATE、DROP、RENAME、TRUNCATE、FLASHBACK、PURGE(增刪改)時，假設有做update表的動作，但沒有commit，只要一執行DDL，那就會自動commit了，這點要注意

※新增

CREATE TABLE chess (
    id number(5),
    name varchar2(20),
    price number(5,2) DEFAULT 0,
    make_date1 date DEFAULT sysdate,
    make_date2 timestamp,
    pic clob
);

※chess要表名稱，自己想一個，「()」裡最左邊是欄位名稱，自己想名字，右邊為資料型態

※官網連結，內鍵的有23種

※常用的就是下面那幾種了
．number：
可用int和float代替，number(5,2)，5為全部位數，不包括小數點； 2為小數點位數
所以123.45是ok的；number(3, 0)等同number(3)，沒有小數點的意思，上面官網連結的Table 2-2有NUMBER的說明，已經很清楚了

．varchar、varchar2、char：
char(5)就是長度是5，varchar2(5)就是最多長度是5，如果有個值是abc，那char取出來後面會包括兩個空格，varchar和varchar2不會，所以才叫var(變動)
varchar和varchar2目前都一樣，官方推薦用varchar2

．date、timestamp：
date年月日；timestamp年月日時分秒

．clob、blob：
c就是character，所以放的是文字；b就是byte，所以放的是圖片

．nXXX：
如nchar、nvarchar2、nclob，主要是存中文字，因為你輸入長度3，它可能內部會將你輸入的長度乘2或3，看編碼而定

※複製表

CREATE TABLE xxx AS SELECT * FROM emp;
CREATE TABLE xxx AS SELECT * FROM emp WHERE 1=0;

※也就是從emp這張表整個複製一份出來變成xxx

※第二條SQL的where永遠不成立，所以只會複製表結構，裡面沒有內容，算是一種運用吧！

※要注意CONSTRAINT 不複製，所以PK、FK都沒有，過幾篇會說明

※AS一定要打，而且不能打IS

※修改

RENAME xxx TO ooo;

※xxx是舊表名，ooo是新表名，就算有PK、FK也可以改

※刪除

DROP TABLE chess;
DROP TABLE chess PURGE;

※兩種方式都可以，差別在下面的資源回收表有說

※清空表

TRUNCATE TABLE xxx;
DELETE FROM xxx;

※TRUNCATE為DDL；DELETE為DML

※DDL比較快；但DML因為還沒commit，所以多一個反悔的機會

※查看表結構

DESC ooo;

※只能看到欄位名稱、欄位類型、是否接受null

※查看目前帳號的表

SELECT * FROM tab;

※資料字典分為兩類
．動態資料字典：隨著資料庫運行而更新的表，通常以「V$」開頭

．靜態資料字典又分成以下三種：
user_開頭：目前帳號的表
all_開頭：目前帳號可以存取的表
dba_開頭：所有的表，必需有權限

※回收表

就是將表刪除後，類似Windows的資源回收桶，還有還原的機會

DROP TABLE xxx;--1
SELECT * FROM tab;--2
SELECT * FROM recyclebin;--3
FLASHBACK TABLE xxx TO BEFORE DROP;--4

※第一步刪除完後，看第二步的表，會發現TNAME多一個「BIN$」開頭，「==$0」結尾的，表示已經到回收表裡了

※第三步的表就是回收表，從ORIGINAL_NAME也會發現剛剛刪除的xxx，如果有多個，那就表示之前刪除了很多，剛好名稱也是xxx

※如果名稱有多個，它還會幫我們多兩個記錄，
「SYS_IL」開頭，「$$」結尾和「SYS_LOB」開頭，「$$」結尾的
而且CREATETIME時間欄位和刪除的表一樣

※第四步使用這種語法能恢復表，它會恢復最接近目前時間且名稱和ORIGINAL_NAME一樣的(例如有三筆資料：1分鐘前刪的、2分鐘前刪的、3分鐘前刪的，會恢復1分鐘前刪的)

※恢復完成後，第二和第三步的表都會少一行

※如果不想進資源回收表，就使用DROP TABLE xxx PURGE;

※刪除回收表

回收表沒辦法用DELETE和TRUNCATE刪的，要用以下的語法

PURGE TABLE xxx;
PURGE recyclebin;

※第一條SQL是刪除一筆，如果有「SYS_IL」開頭，「$$」結尾和「SYS_LOB」開頭，「$$」結尾的，而且CREATETIME時間欄位和刪除的表一樣的，也會一起刪除
刪除的是離目前時間最遠的那一筆(例如有三筆資料：1分鐘前刪的、2分鐘前刪的、3分鐘前刪的，會刪除3分鐘前刪的)

※一筆一筆刪除太累了，所以第二條SQL是清空整個回收表用的

&、DEFINE、ACCEPT (DML 十三)

互動變數並不是Oracle SQL裡，是在一本叫SQL*PlusR User's Guide and Reference裡面
我這篇的最後一張圖，去官網往下拉一點就會看到

首先要啟用互動變數的功能，其實預設已經啟用，但我不確定每個版本都預設啟用
官網有說明，我的這篇PL/SQL也有講到一些，如果不懂PL/SQL就看截圖就好，這篇就不截圖了

只要打&xxx，xxx自己隨便取，放在SQL的任何地方都可以，反正就是個字串，一執行就會跳出畫面，這時你打什麼就是什麼，但如果沒啟用就會出「ORA-01008: 部分變數未被連結」的錯誤

※啟用/停用 換符號

SET DEFINE ON/OFF;
SELECT * FROM emp where deptno = &p1;
    
SET DEFINE &c;

※第一行就是啟用/停用，第二行就可以用了，預設要用「&」

※如果不想用預設的「&」，那就打最後一行，一打會跳出視窗，你輸入什麼就用什麼

※官網的DEF[INE]表示打DEF和DEFINE都可以，「[]」不是必要的，下面介紹的語法還有很多都是這樣

※&、&&

SELECT * FROM emp where deptno = &p1;
SELECT * FROM emp where deptno = &&p1;

※第一行每次執行都要打一次

※第二行打一次就可以執行很多次，如果要清除下面會介紹

※第二行的變數名稱和第一行一樣，所以執行過第二行，第一行的變數值也有值了，就不會再跳出畫面了

※清除

SELECT &p, sum(sal) FROM emp GROUP BY &p;
SELECT &&p, sum(sal) FROM emp GROUP BY &p;

※第一行兩個變數一樣，但要打兩次，所以改成第二行的寫法

※第二行的&&要寫在前面，後面的變數才知道不用再打了

※變數存起來只是暫時的，重登入就消失了，或者執行以下的語法

UNDEFINE p;
    
UNDEFINE p p1;

※第一行清除一個變數的值

※第二行是要清除多個變數，中間用空格隔開

※DEFINE

DEFINE xxx = 20;
SELECT * FROM emp WHERE deptno = &xxx;

※除了用「&」，也可以用DEFINE，看官網介紹

※ACCEPT

前本介紹的互動還不夠好，ACCEPT可以有文字，看官網介紹
語法如下：
ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text|NOPR[OMPT]] [HIDE]

--1
ACCEPT yyy DATE FOR 'YYYY-MM-DD' NOPROMPT
SELECT * FROM emp WHERE hiredate < to_date('&yyy', 'YYYY-MM-DD');
    
--2
ACCEPT xxx DEFAULT 30 PROMPT '請輸入部門編號：'
SELECT * FROM emp WHERE deptno = &xxx;
    
--3
ACCEPT ooo FORMAT A2 PROMPT '請輸入部門編號：' HIDE
SELECT * FROM emp WHERE deptno = &ooo;

※第一組的NOPROMPT(NOPROMPT不寫也是一樣)和上面介紹的一樣，沒有文字訊息
但這一組有設定日期的格式

※第二組有預設值，但跳出的視窗有確定和取消，要按確定才有用

※第三組後本有個HIDE，表示打出來的字變成「*」號

增刪改、事務、鎖 (DML 十二)

※複製表

CREATE TABLE xxx AS SELECT * FROM emp;

※這是DDL的操作，再過幾篇會寫，先照做

※表的名稱叫xxx，內容和emp表一樣，但要注意CONSTRAINT 不複製，所以PK、FK都沒有

※複製這個表來練習，可以隨便亂搞，這裡的增刪改都是DML，所以增刪改完成後，只有目前的使用者才有用，或者重新登入就沒了，如果想讓其他使用者或者重新登入都有上次的資料，就要下COMMIT;，而如果在還沒COMMIT之前，目前的使用者感覺操作都有修改到，如果不想要這次的執行結果，可以下ROLLBACK;

※練習完可用「DROP TABLE xxx PURGE;」刪除表

※新增

INSERT INTO xxx(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(9999, 'x', 'CLERK', 7902, sysdate, 1000, 0, 20);
    
INSERT INTO xxx VALUES(8888, 'o', 'MANAGER', 7839, sysdate, 3000, 0, 30);
    
INSERT INTO xxx SELECT * FROM emp WHERE deptno = 30;

※新增有三種
第一個SQL有打欄位，可以視需要，新增想增加的欄位即可，而值打在VALUES後面，不過PK、not null一定要打，否則編譯會失敗

第二個SQL不打欄位，所以值要按照資料庫的順序全部打出來

第三個SQL可以一次新增多筆，查詢一張表，欄位的型態要和想新增的表一樣即可

※修改

UPDATE xxx SET job = 'MANAGER', sal = 1500 WHERE empno = 7369;
    
UPDATE xxx SET (job,sal) = (
    SELECT job, sal FROM emp
    WHERE empno = 7499
)
WHERE empno = 7369;

※要注意不加 WHERE 會將所有表都修改，如果表裡有10筆，10筆都會修改

※多個欄位用逗號隔開

※第二個SQL是配合 SELECT 使用的

※刪除

DELETE FROM xxx WHERE empno = 7369;
    
DELETE FROM xxx WHERE job = (
    SELECT job FROM xxx WHERE empno = 7902
);

※要注意不加 WHERE 會將所有表都刪除，如果表裡有10筆，10筆都會被刪除，如果不小心刪除了，記得ROLLBACK

※DELETE沒有「*」

※第二個SQL是配合 SELECT 使用的

※MERGE INTO USING ON

有找到就修改；沒找到就新增

MERGE INTO emp e
USING (SELECT deptno FROM dept WHERE deptno = 40) d
ON(
    e.deptno = d.deptno
)
    
WHEN NOT MATCHED THEN
    INSERT (empno, ename) VALUES (EMP_SEQ.nextval, 'xxx')
    
WHEN MATCHED THEN
    UPDATE SET comm = comm * 1.2
    DELETE WHERE (ename = 'TURNER');

※USING 和 ON 都是一定要有的

※MERGE INTO 後面接要修改或新增的table

※注意USING裡的SQL，欄位值只能唯一，否則會報「ORA-30926:無法取得來源表格中可信的資料列集」，可用distinct

※ON裡的欄位也不能修改，否則會報『ORA-38104:無法更新「ON子句」中參照的資料欄:table.column』

※UPDATE可以加上DELETE，但where條件是update後的條件

※假設t1表的tid有1、2、3，而t2表有2、3、4，那結果就會修改2筆
又假設t2表變成4、5、6，那就會執行新增

※MATCHED和NOT MATCHED可擇一使用，順序也可以互換

※注意新增和修改的語法變的比較精簡了，insert如果要每個欄位都新增，insert後的圓括號可不打

※事務

※Transaction 翻譯成事務

※commit 可以讓其他的使用者可以看到修改的結果

※rollbak：在還沒commit前，可以回復到修改前的狀態

※資料庫要有四個特性才能叫資料庫，就是ACID
．Atomicity(原子性)：整體一起commit或rollback

．Consistency(一致性)：假設A轉帳給B1000元，
成功時，A少1000元，B也多1000元 --> 一致
失敗時，A和B的錢都不會增加或減少 --> 一致

．Isolation(隔離性)：多個事務可以同時進行，而且彼此之間看不見

．Durability(持久性)：事務提交成功後，即使資料庫馬上就壞了，也必須通過某種機制恢復資料

在12c預設不自動提交，SET AUTOCOMMIT ON|OFF;可以設定要不要自動提交

※SAVEPOINT

可以將當前的狀態存起來，以下一定要使用不自動提交
已經commit的就不能rollback了

※例1

SELECT * FROM xxx;-- 1.看一下目前資料
UPDATE xxx SET comm = 50 WHERE empno = 7369;-- 2.修改
SELECT * FROM xxx;-- 3.確認有修改到
SAVEPOINT s1;-- 4.將目前狀態存起來
ROLLBACK;-- 5.回到修改前，也就是第一和第二行之間
ROLLBACK TO s1;-- 6.想回到s1

※因為第5步已經回到1和2之間，那時並沒有存s1，這時就會出
「ORA-01086: 從未在此階段作業建立儲存點 'S1' 或此儲存點無效」

※例2

SELECT * FROM xxx;-- 1.看一下目前資料
UPDATE xxx SET comm = 50 WHERE empno = 7369;-- 2.修改
SELECT * FROM xxx;-- 3.確認有修改到
SAVEPOINT s1;-- 4.將目前狀態存起來
UPDATE xxx SET comm = 50 WHERE empno = 7566;-- 5.修改
SELECT * FROM xxx;-- 6.確認有修改到
ROLLBACK TO s1;-- 7.回到s1

※第7步因為前面沒有commit或rollback，所以回復成功

※例3

SELECT * FROM xxx;-- 1.看一下目前資料
UPDATE xxx SET comm = 50 WHERE empno = 7369;-- 2.修改
SELECT * FROM xxx;-- 3.確認有修改到
SAVEPOINT s1;-- 4.將目前狀態存起來
UPDATE xxx SET comm = 50 WHERE empno = 7566;-- 5.修改
SELECT * FROM xxx;-- 6.確認有修改到
SAVEPOINT s2;-- 7.將目前狀態存起來
ROLLBACK TO s1;-- 8.回到s1
SELECT * FROM xxx;-- 9.確定回復成功
ROLLBACK TO s2;-- 10.想回到s2

※因為第8步已經回到4和5之間，那時並沒有存s2，這時就會出
ORA-01086: 從未在此階段作業建立儲存點 'S2' 或此儲存點無效

※例4

SELECT * FROM xxx;-- 1.看一下目前資料
UPDATE xxx SET comm = 50 WHERE empno = 7369;-- 2.修改
SELECT * FROM xxx;-- 3.確認有修改到
SAVEPOINT s1;-- 4.將目前狀態存起來
UPDATE xxx SET comm = 50 WHERE empno = 7566;-- 5.修改
SELECT * FROM xxx;-- 6.確認有修改到
SAVEPOINT s2;-- 7.將目前狀態存起來
UPDATE xxx SET comm = 50 WHERE empno = 7698;-- 8.修改
SELECT * FROM xxx;-- 9.確認有修改到
ROLLBACK TO s2;-- 10.回到s2
SELECT * FROM xxx;-- 11.確定回復成功
ROLLBACK TO s1;-- 12.回到s1
SELECT * FROM xxx;-- 13.確定回復成功

※第10步回到s2，也就是6和7之間，有包括第4步的s1記錄，所以12步可以順利的回到s1

※鎖

在執行UPDATE、DELETE都有鎖，也就是其他使用者無法看到你鎖定的資料
又分成行級鎖定和表級鎖定

說其他使用者不夠正確，應該是說其他session，每個登入進來的都有一個session，就算是同一個帳號也是分多個session，所以以下測試都是用同一個帳號，一個用SQL Developer，另一個用SQL plus

※行級鎖定

※例1sessionA

SELECT * FROM xxx WHERE deptno = 10 FOR UPDATE;

※FOR UPDATE 是SELECT的行級鎖，部門10的 empno 有7782、7839、7934

※例1sessionB

SELECT * FROM xxx WHERE deptno = 10 FOR UPDATE;
SELECT * FROM xxx WHERE empno = 7782 FOR UPDATE;
SELECT * FROM xxx FOR UPDATE;
UPDATE xxx SET comm = 50 WHERE empno = 7369;
DELETE FROM xxx WHERE empno = 7369;

※sessionB在執行上的其中一行SQL時，只要sessionA還沒commit或rollback，畫面就好像在讀什麼一樣，都不動，還以為是當機了

※FOR UPDATE在工作中，常有人執行後，配合類似SQL Developer的軟體，然後用滑鼠修改值，算是一種應用吧！

※修改或刪除，例2sessionA

UPDATE xxx SET comm = 50 WHERE empno = 7369;

※這時還沒commit或rollback

※例2sessionB

UPDATE xxx SET comm = 50 WHERE empno = 7369;
DELETE FROM xxx WHERE empno = 7369;

※這時一樣會停住不動

※表級鎖定

官網連結

LOCK TABLE emp IN EXCLUSIVE MODE NOWAIT/WAIT;
WAIT是預設的選項，如果表被鎖起來會停住不動，不想等就用NOWAIT
類似 EXCLUSIVE 的有6種模式，官網都有寫，我盡我的能力翻出來如下：

1.ROW SHARE：
允許同時存取鎖定的表，但禁止使用者鎖定整個表獨占存取
ROW SHARE 和SHARE UPDATE一樣，這是因為早期版本兼容性的關係

2.ROW EXCLUSIVE：
很像ROW SHARE，但禁止在SHARE模式鎖定
ROW EXCLUSIVE在增刪改時是自動取得的

3.SHARE UPDATE：
和ROW SHARE一樣

4.SHARE：
允許同時查詢，但禁止更新鎖定的表

5.SHARE ROW EXCLUSIVE：
用在尋找整個表，而且允許看到表中的行
可是在SHARE模式禁止其他人鎖定表或修改行

6.EXCLUSIVE：
只允許查詢鎖定的表

※例

--sessionA
LOCK TABLE xxx IN SHARE MODE NOWAIT;
    
--sessionB
DELETE FROM xxx;

※sessionA執行後，sessionB執行DELETE就會停住不動
sessionA一rollback，sessionB才會執行

※解鎖

官網連結

※例

SELECT * FROM xxx WHERE deptno = 10 FOR UPDATE;
SELECT SESSION_ID FROM V$LOCKED_OBJECT;-- 23、250
SELECT SID, SERIAL#, LOCKWAIT FROM V$SESSION WHERE SID IN (23,250);
/*  
 23   5289
250  18727
*/

ALTER SYSTEM KILL SESSION '250,18727';

※第二行以後都必須用有權限的帳號才能執行

※第一行用兩個session分別執行，其中一個session會停住不動

※查詢V$LOCKED_OBJECT表，會發現有二筆鎖定了

※將查到的兩筆查詢V$SESSION表，取得SID和SERIAL#也會有兩筆

※刪除其中一筆後，解除鎖定

正則表達式二 (語法) (DML 十一)

※*

SELECT
    REGEXP_COUNT('ac aac abc axyzc abccc', 'a\w*c') C,-- 5
    
    REGEXP_SUBSTR('ac aac abc axyzc abccc', 'a\w*c', 1, 1) S1,-- ac
    REGEXP_SUBSTR('ac aac abc axyzc abccc', 'a\w*c', 1, 2) S2,-- aac
    REGEXP_SUBSTR('ac aac abc axyzc abccc', 'a\w*c', 1, 3) S3,-- abc
    REGEXP_SUBSTR('ac aac abc axyzc abccc', 'a\w*c', 1, 4) S4,-- axyzc
    REGEXP_SUBSTR('ac aac abc axyzc abccc', 'a\w*c', 1, 5) S5,-- abccc
    REGEXP_SUBSTR('ac aac abc axyzc abccc', 'a\w*?c', 1, 5) S6-- abc
FROM DUAL;

※*等同「a\w{0,}c」預設都是貪婪模式，也就是能匹配多一點就會多匹配一點

※「a\w*?c」或「a\w{0,}?c」為非貪婪，所以S6只有abc

※*、+、?、{}這四個後面多個「?」就是非貪婪模式

※+

SELECT
    REGEXP_COUNT('ac aac abc axyzc abccc', 'a\w+c') C,-- 4
    
    REGEXP_SUBSTR('ac aac abc axyzc abccc', 'a\w+c', 1, 1) S1,-- aac
    REGEXP_SUBSTR('ac aac abc axyzc abccc', 'a\w+c', 1, 2) S2,-- abc
    REGEXP_SUBSTR('ac aac abc axyzc abccc', 'a\w+c', 1, 3) S3,-- axyzc
    REGEXP_SUBSTR('ac aac abc axyzc abccc', 'a\w+c', 1, 4) S4-- abccc
FROM DUAL;

※+等同「a\w{1,}c」

※?

SELECT
    REGEXP_COUNT('ac aac abc axyzc abccc', 'a\w?c') C,-- 4
    
    REGEXP_SUBSTR('ac aac abc axyzc abccc', 'a\w?c', 1, 1) S1,-- ac
    REGEXP_SUBSTR('ac aac abc axyzc abccc', 'a\w?c', 1, 2) S2,-- aac
    REGEXP_SUBSTR('ac aac abc axyzc abccc', 'a\w?c', 1, 3) S3,-- abc
    REGEXP_SUBSTR('ac aac abc axyzc abccc', 'a\w?c', 1, 4) S4-- abc
FROM DUAL;

※?等同「a\w{0,1}c」

※|

SELECT
    REGEXP_COUNT('ac aac aBc axyzc abccC', 'a(a|b|B)c') C,-- 3
    
    REGEXP_SUBSTR('ac aac aBc axyzc abccC', 'a(a|b|B)c', 1, 1) S1,-- aac
    REGEXP_SUBSTR('ac aac aBc axyzc abccC', 'a(a|b|B)c', 1, 2) S2,-- aBc
    REGEXP_SUBSTR('ac aac aBc axyzc abccC', 'a(a|b|B)c', 1, 3) S3-- abc
FROM DUAL;

※|等同「a[abB]c」和「a[=abB=]c」

※.

SELECT
    REGEXP_COUNT('ac aac aBc axyzc a8ccC', 'a.c') C,-- 3
    
    REGEXP_SUBSTR('ac aac aBc axyzc a8ccC', 'a.c', 1, 1) S1,-- aac
    REGEXP_SUBSTR('ac aac aBc axyzc a8ccC', 'a.c', 1, 2) S2,-- aBc
    REGEXP_SUBSTR('ac aac aBc axyzc a8ccC', 'a.c', 1, 3) S3,-- a8c
    
    REGEXP_SUBSTR('ac aac abc axyzc abccc', 'a.c', 1, 3) S4-- abc
FROM DUAL;

※只能是非貪婪模式，S4可看得出來

※^$

SELECT
    REGEXP_SUBSTR('a1c' || chr(10) || 'a2c' || chr(10) || 'a3cc' || chr(10) || 'ba4c',
        '^a.c$', 1, 1, 'm') S1,-- a1c
    REGEXP_SUBSTR('a1c' || chr(10) || 'a2c' || chr(10) || 'a3cc' || chr(10) || 'ba4c',
        '^a.c$', 1, 2, 'm') S2,-- a2c
    REGEXP_SUBSTR('a1c' || chr(10) || 'a2c' || chr(10) || 'a3cc' || chr(10) || 'ba4c',
        '^a.c$', 1, 3, 'm') S3,-- null
    REGEXP_SUBSTR('a1c' || chr(10) || 'a2c' || chr(10) || 'a3cc' || chr(10) || 'ba4c',
        '[a\A].[c\Z]', 1, 3, 'm') S4-- a3c
FROM DUAL;

※一整行為尋找的條件，為了要找到更多，所以我用了換行「chr(10)」，並配合m

※\A\Z也是開頭結尾的意思，所以這個也可以這樣用「[a\A].[c\Z]」，但這個是非貪婪模式，所以S4有匹配成功

※英文字、數字、空格

SELECT
    REGEXP_COUNT('ac aac aBc axyzc abccC', 'a\wc') C1,-- 3
    REGEXP_SUBSTR('ac aac aBc axyzc abccC', 'a\wc', 1, 1) S1,-- aac
    REGEXP_SUBSTR('ac aac aBc axyzc abccC', 'a\wc', 1, 2) S2,-- aBc
    REGEXP_SUBSTR('ac aac aBc axyzc abccC', 'a\wc', 1, 3) S3,-- abc
    
    REGEXP_COUNT('ac a3c aBca5ccC', 'a\dc') C2,-- 2
    REGEXP_SUBSTR('ac a3c aBca5ccC', 'a\dc', 1, 1) SD1,-- a3c
    REGEXP_SUBSTR('ac a3c aBca5ccC', 'a\dc', 1, 2) SD2,-- a5c
    
    REGEXP_COUNT('ac a c a cc', 'a\sc') C3,-- 1
    REGEXP_SUBSTR('ac a c a cc', 'a\sc', 1, 1) SS1,-- a c
    REGEXP_SUBSTR('ac a c a cc', 'a\sc', 1, 2) SS2-- a c
FROM DUAL;

※a\wc等同「a[a-zA-Z0-9]c」和「a[[:alnum:]]c」

※a\dc，等同「[0-9]」和「a[[:digit:]]c」

※a\sc等同「a c」和「a[ ]c」和「a[[:blank:]]c」

※大寫是相反的意思，如\W是非英文字，\D是非數字；\S非空格

※跳脫字元

SELECT
    REGEXP_INSTR('\*+?|^$.()[]', '\\') I1,-- 1
    REGEXP_INSTR('\*+?|^$.()[]', '\*') I2,-- 2
    REGEXP_INSTR('\*+?|^$.()[]', '\+') I3,-- 3
    REGEXP_INSTR('\*+?|^$.()[]', '\?') I4,-- 4
    REGEXP_INSTR('\*+?|^$.()[]', '\|') I5,-- 5
    REGEXP_INSTR('\*+?|^$.()[]', '\^') I6,-- 6
    REGEXP_INSTR('\*+?|^$.()[]', '\$') I7,-- 7
    REGEXP_INSTR('\*+?|^$.()[]', '\.') I8,-- 8
    REGEXP_INSTR('\*+?|^$.()[]', '\(') I9,-- 9
    REGEXP_INSTR('\*+?|^$.()[]', '\[') I10-- 11
FROM DUAL;

※如果要比對關鍵字，要在前面多個「\」，「{}」不用

※[::]

這個語法，我在Oracle官網搜尋，並沒有找到全部的語法
POSIX的官網在這裡，目前總共有14個，寫得很清楚
我列出10個比較常用和好理解的
[:alnum:] = [a-zA-Z0-9]、\w
[:alpha:] = [a-zA-Z]
[:blank:] = tab和空格
[:digit:] = [0-9]、\d
[:lower:] = [a-z]
[:punct:] = 標點符號
[:space:] = 空格，\s
[:upper:] = [A-Z]
[:word:] = [A-Za-z0-9_]，比[:alnum:]多個底線
[:xdigit:] = 十六進制，[0-9a-fA-F]

正則表達式一 (REGEXP方法) (DML 十)

官網連結
\：有四種意思
   1.代表本身
   2.引用下一個字符(跳脫字元)
   3.引入一個運算符
   4.沒做什麼
*：0~n
+：1~n
?：0~1
|：or
^：開頭
$：結尾
.：匹配任何字元，除了null
[]：[xyz]表示x或y或z，如xayaza，用a隔開還是可以匹配到，
    要注意在裡面使用「^」就不是開頭的意思了，是相反的意思
()：分組表達式，作為一個單獨的子表達式處理
{m}：m次
{m,}：至少m次
{m, n}：至少匹配m次，但不超過n次
\n：n為1~9的數字，用「()」包起來是第1個
[..]：指定一個對照元件，可以是一個多字串元件，如[.ch.]是西班牙語
[::]：POSIX語法，如[:alpha:]，下一篇會介紹
[==]：匹配相同的字元，如[=abc=]，裡面的字元，其中之一就匹配

\d：數字
\D：非數字
\w：字元
\W：非字元
\s：空格
\S：非空格
\A：只匹配字串的開頭，或前一個字串後面的換行字元
\Z：只匹配結尾的字串
*?：匹配前面的模式元素0次或更多次（非貪婪）
+?：匹配前面的模式元素1次或更多次（非貪婪）
??：匹配前面的模式元素0次或1次（非貪婪）
{m}?：匹配前面的模式元素m次（非貪婪）
{m,}?：匹配前面的模式元素至少m次（非貪婪）
{m,n}?：匹配前面的模式元素至少m次，但不超過n次（非貪婪）

※REGEXP_COUNT

正則表達式匹配的次數，官網連結

總共有4個參數，前兩個參數是必要的
第1個參數：要比對的字串
第2個參數：正則表達式
第3個參數：從第幾個index開始匹配
第4個參數一定是小寫，有5種形式
i：不區分大小寫
c：區分大小寫，預設
n：換行符號可以使用「.」來匹配
m：遇到換行符時，可以使用「^」和「$」來匹配
x：忽略空格，指的是正則表達式的空格(第2個參數)
也可以不放任何字，就等同沒有第四個參數，以上可以搭配使用

※i、c、x

SELECT
    REGEXP_COUNT('abc abc ab cabc', 'abc') C1,-- 3
    REGEXP_COUNT('abc abc ab cabc', 'AbC') C2,-- 0
    REGEXP_COUNT('abc abc ab cabc', 'abc', 3) C3,-- 2
    REGEXP_COUNT('abc abc ab cabc', 'AbC', 3, 'c') C4,-- 0
    REGEXP_COUNT('abc abc ab cabc', 'AbC', 3, 'i') C5,-- 2
    REGEXP_COUNT('abc abc ab cabc', 'a b c', 3, 'x') C6,-- 2
    REGEXP_COUNT('abc abc ab cabc', 'A b C', 3, 'xi') C7-- 2
FROM DUAL;

※說明：
C1可以匹配3次
C2一次都沒有，因為預設有區分大小寫
C3從第3個字元開始讀取(包括c)，所以是2次
C4其實就是預設的區分大小寫，所以一次都沒有
C5從第3個字元開始讀取(包括c)，不分大小寫，所以是2次
C6從第3個字元開始讀取(包括c)，而且忽略空格，所以是2次
C7從第3個字元開始讀取(包括c)，而且忽略空格，又不區分大小寫，所以是2次

※n和m

SELECT
    REGEXP_COUNT('123 ' || chr(10) || '67 ' || chr(10), '.', 1, 'i') N1,-- 7
    REGEXP_COUNT('123 ' || chr(10) || '67 ' || chr(10), '.', 1, 'in') N2,-- 9
    REGEXP_COUNT('aac' || chr(10) || 'abcaca' || chr(10) || 'abc', '^a', 1, '') M1,-- 1
    REGEXP_COUNT('aac' || chr(10) || 'abcaca' || chr(10) || 'abc', '^a', 1, 'm') M2-- 3
FROM DUAL;

※說明：
N1還沒有加「n」，所以匹配到的是「123空67空」，所以是匹配7次
N2有加「n」，所以會多2次
N3還沒有加「m」，所以預設算一行，匹配a開頭的，只有1次，最後的參數是空，所以也可以不要第4個參數
N4有加「m」，所以有3行，而且剛好都是a開頭的，所以是3次

※REGEXP_INSTR

正則表達式匹配後回傳第幾個index，沒有匹配到就回傳0，官網連結

總共有7個參數，前兩個參數是必要的
第1個參數：要比對的字串
第2個參數：正則表達式
第3個參數：從第幾個index開始匹配
第4個參數：匹配可能會有多筆，這個數字表示匹配第幾次成功的
第5個參數：不是0就是1，大於1就是1的意思，如果是0就回傳當下的index
如果是1，回傳當下的index之後，也就是再加1
第6個參數：和REGEXP_COUNT的第4個參數一樣，請參考上面的REGEXP_COUNT
第7個參數：巢狀匹配，不好說，直接看下面的第二個例子

※前6個參數

SELECT
    REGEXP_INSTR('123.56.89.BC', '\.') I1,-- 4
    REGEXP_INSTR('123.56.89.BC', '\.', 5) I2,-- 7
    REGEXP_INSTR('123.56.89.BC', '\.', 1, 3) I3,-- 10
    REGEXP_INSTR('123.56.89.BC', 'B', 1, 1, 0) I4,-- 11
    REGEXP_INSTR('123.56.89.BC', 'B', 1, 1, 1) I5,-- 12
    REGEXP_INSTR('123.56.89.BC', 'b', 1, 1, 0, 'i') I6-- 11
FROM DUAL;

※說明：
I1要匹配的是「.」，但它是關鍵字，所以用跳脫字元「\」
I2從第5個字元開始匹配，所後是5
I3從第1個字元開始匹配，但要回傳第三次成功的結果，所以是10
I4要比對「B」，而第5個參數是0，所以回傳當下的11
I5第5個參數是1，所以還要再加1，回傳12
I6的第6個參數不分大小寫

※第7個參數

SELECT
    REGEXP_INSTR('1234567890', '1234567', 1, 1, 0, 'i', 0) S1,-- 1
    REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 1) S2,-- 1
    REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 2) S3,-- 4
    REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3) S4,-- 5
    REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4) S5-- 7
FROM DUAL;

※說明：
 S1最後1個參數給0，匹配的到，其他不行

巢狀
(123)(4(56)(78))的順序如下：
一.123
二.45678
三.56
四.78
這樣就可以知道S2~S5的結果了，第5次以上，以這個例子就回傳0了

※REGEXP_REPLACE

取代正則表達式匹配成功的字串，沒有匹配到就回傳原來的字串，官網連結

總共有6個參數，前三個參數是必要的
第1個參數：要比對的字串
第2個參數：正則表達式
第3個參數：要取代的字串
第4個參數：從第幾個index開始匹配
第5個參數：匹配可能會有多筆，這個數字表示匹配第幾次成功的
第6個參數：和REGEXP_COUNT的第4個參數一樣，請參考上面的REGEXP_COUNT

※前3個參數

SELECT
    REGEXP_REPLACE('123     456    789,   abc   de,   FG', '( ){2,}', ' ') R1,
    REGEXP_REPLACE('123     456    789,   abc   de,   FG', '\s+', ' ') R2,
    REGEXP_REPLACE('ABC', '.', '\1 ') R3,-- \1 \1 \1 
    REGEXP_REPLACE('ABC', '(.)', '\1 ') R4,-- A B C
    REGEXP_REPLACE('A123 B456 C789', '(.*) (.*) (.*)', '\3, \2, \1') R5 -- C789, B456, A123
FROM DUAL;

※R1和R2都是匹配空格兩次以上的(包括2次)，取代成一個空格

※R3匹配任何字元，取代成\1空格

※R4因為有「()」，所以\1代表第1個()裡的正則表達式，而A、B、C都匹配成功，所以會將A、B、C後面多一個空格，看R5會比較容易了解

※R5會將第3個C789取代第1個A123；第2個還是第2個；第1個A123取代第三個C789

※第4~6個參數，，請參考上面的REGEXP_INSTR

※REGEXP_SUBSTR

從字串裡面取出子字串，如果都沒有匹配，回傳null，官網連結

總共有6個參數，前兩個參數是必要的
第1個參數：要比對的字串
第2個參數：正則表達式
第3個參數：從第幾個index開始匹配
第4個參數：匹配可能會有多筆，這個數字表示匹配第幾次成功的
第5個參數：和REGEXP_COUNT的第4個參數一樣，請參考上面的REGEXP_COUNT
第6個參數：巢狀匹配，請參考上面的REGEXP_INSTR

SELECT
    REGEXP_SUBSTR('12,345,678', ',[^,]+') S1,-- ,345
    REGEXP_SUBSTR('abc123def456', '[0-9]{1,}', 1, 2) S2,-- 456
    REGEXP_SUBSTR('aabab cde','^a.*b') S3-- aabab
FROM DUAL;

※S1是匹配以「,」開頭一直到非逗號的，所以是「,345」

※S2是匹配一次以上數字開頭的(\d無效)，從第1個index開始找，找到2次的結果，所以是456

※S3是匹配a開頭，然後停在b

※第4~6個參數，請參考上面的REGEXP_INSTR

※REGEXP_LIKE

使用在判斷條件時，通常是在WHERE後，官網連結

SELECT * FROM emp
WHERE REGEXP_LIKE (ename, 'N$');

※只要是N結尾的就匹配

※還有第三個參數，和REGEXP_COUNT的第4個參數一樣，請參考上面的REGEXP_COUNT

PIVOT (DML 九)

PIVOT

簡單來說就是行列轉換

※先看資料

SELECT deptno, sum(sal)
FROM emp GROUP BY deptno ORDER BY deptno;
    
SELECT deptno, job, sum(sal)
FROM emp GROUP BY deptno, job ORDER BY deptno;

※這兩個 SQL 分別 GROUP BY 一個和二個欄位

※結果：

※原來的做法使用decode

SELECT 
    deptno,
    sum(sal),
    sum(decode(job, 'CLERK', sal, 0)) C,
    sum(decode(job, 'SALESMAN', sal, 0)) S,
    sum(decode(job, 'PRESIDENT', sal, 0)) P,
    sum(decode(job, 'MANAGER', sal, 0)) M,
    sum(decode(job, 'ANALYST', sal, 0)) A
FROM emp GROUP BY deptno ORDER BY deptno;
    
SELECT 
    deptno,
    job,
    sum(sal),
    sum(decode(job, 'CLERK', sal, 0)) C,
    sum(decode(job, 'SALESMAN', sal, 0)) S,
    sum(decode(job, 'PRESIDENT', sal, 0)) P,
    sum(decode(job, 'MANAGER', sal, 0)) M,
    sum(decode(job, 'ANALYST', sal, 0)) A
FROM emp GROUP BY deptno, job ORDER BY deptno;

※結果：

※可以看出SUM的結果是上往下，而經過decode變成左往右了(SUM的值等於右邊五個欄位的加總)，也就是行列轉換

※語法

SELECT * FROM (
    SELECT job, sal FROM emp
)
PIVOT(
    sum(sal) FOR job IN(
        'CLERK' C,
        'SALESMAN' S,
        'PRESIDENT' P,
        'MANAGER' M,
        'ANALYST' A
    )
);

※結果：

※一定要用子查詢，否則編譯不會過

※這結果沒有GROUP BY，也就是CLERK的加總為C，SALESMAN的加總為S，依此類推

※隱藏的 GROUP BY

SELECT * FROM (
    SELECT deptno, job, sal FROM emp
)
PIVOT(
    sum(sal) FOR job IN(
        'CLERK' C,
        'SALESMAN' S,
        'PRESIDENT' P,
        'MANAGER' M,
        'ANALYST' A
    )
)
ORDER BY deptno;
    
    
    
SELECT * FROM (
  SELECT deptno, job j, sal, job FROM emp e
)
PIVOT(
    sum(sal) s FOR job IN(
        'CLERK' C,
        'SALESMAN' S,
        'PRESIDENT' P,
        'MANAGER' M,
        'ANALYST' A
    )
)
ORDER BY deptno;

※第一個SQL的job和sal，因為PIVOT會用到，所以不會GROUP BY，而deptno沒有用到，會自動的GROUP BY

※如果要GROUP BY多個就多打幾個欄位，但如果要打的欄位被PIVOT用了，所以只好如第二個SQL，打個別名

※PIVOT裡的IN一定要事先知道，無法用子查詢、ANY；如果用「>」、「NOT IN」編譯都不會過

※結果：

※這結果和使用decode一樣，只差在null的部分，但我用NVL無效

※多個彙總函數

SELECT * FROM (
    SELECT deptno, job, sal FROM emp
)
PIVOT(
    sum(sal) s, max(sal) m FOR job IN(
        'CLERK' C,
        'SALESMAN' S,
        'PRESIDENT' P,
        'MANAGER' M,
        'ANALYST' A
    )
)
ORDER BY deptno;

※結果：

※可以觀察它的命名方式

※多個條件

SELECT * FROM (
    SELECT e.deptno dno, job, sal, loc FROM emp e, dept d
    WHERE e.deptno = d.deptno
)
PIVOT(
    sum(sal) s FOR (job,loc) IN(
        ('CLERK', 'CHICAGO') C,
        ('SALESMAN', 'CHICAGO') S,
        ('PRESIDENT', 'CHICAGO') P,
        ('MANAGER', 'CHICAGO') M,
        ('ANALYST', 'CHICAGO') A
    )
)
ORDER BY dno;

※結果：

※注意 loc 有四個值，我只打出一個值(CHICAGO)

※轉成XML

SELECT * FROM (
    SELECT deptno, job, sal FROM emp
)
PIVOT XML(
    sum(sal) s FOR job IN(ANY)
)
ORDER BY deptno;

※結果：

※IN裡面放ANY，SOME和ALL都不行，而且只能配合PIVOT XML

※因為Oracle SQL Developer太長了，也不好觀看，所以我排列如下：

<PivotSet>
    <item>
        <column name = "JOB">CLERK</column>
        <column name = "S">1300</column>
    </item>
    
    <item>
        <column name = "JOB">MANAGER</column>
        <column name = "S">2450</column>
    </item>
    
    <item>
        <column name = "JOB">PRESIDENT</column>
        <column name = "S">5000</column>
    </item>
</PivotSet>

※UNPIVOT

也就是PIVOT的相反

WITH t AS (
    SELECT * FROM (
        SELECT deptno, job, sal FROM emp
    )
    PIVOT(
        sum(sal) S_SA FOR job IN(
            'CLERK' C,
            'SALESMAN' S,
            'PRESIDENT' P,
            'MANAGER' M,
            'ANALYST' A
        )
    )
    ORDER BY deptno
)
    
SELECT * FROM t
UNPIVOT(
    S_SA FOR job IN(
        C_S_SA as 'CLERK',
        S_S_SA as 'SALESMAN',
        P_S_SA as 'PRESIDENT',
        M_S_SA as 'MANAGER',
        A_S_SA as 'ANALYST'
    )
)
ORDER BY deptno;

※首先使用WITH將上面的「隱藏的GROUP BY」的寫法複製進去，然後下面再用UNPIVOT

※UNPIVOT裡面的名字，可以先執行上面的PIVOT，看到欄位名稱後打進去即可，但在UNPIVOT裡面，「as」一定要打，不然編譯不會過

※結果：

※可以看出結果又轉回來了

※INCLUDE NULLS／EXCLUDE NULLS

WITH t AS (
    SELECT * FROM (
        SELECT deptno, job, sal FROM emp
    )
    PIVOT(
        sum(sal) S_SA FOR job IN(
            'CLERK' C,
            'SALESMAN' S,
            'PRESIDENT' P,
            'MANAGER' M,
            'ANALYST' A
        )
    )
    ORDER BY deptno
)
SELECT * FROM t
UNPIVOT INCLUDE NULLS(
    S_SA FOR job IN(
        C_S_SA as 'CLERK',
        S_S_SA as 'SALESMAN',
        P_S_SA as 'PRESIDENT',
        M_S_SA as 'MANAGER',
        A_S_SA as 'ANALYST'
    )
)
ORDER BY deptno;

※這語法和上面一樣，只是在UNPIVOT後面多個 INCLUDE NULLS

※結果：

※可以看出null也顯示出來了，而 EXCLUDE NULLS是預設的，
也就是打PIVOT等同 PIVOT EXCLUDE NULLS

CONNECT BY (DML 八)

select date '2017-05-18' + (rownum - 1) dt
from dual
connect by rownum <= (date '2017-05-20' - date '2017-05-18' + 1)

※connect by 可以將連續的日期顯示出來



先看一下EMP的表

員工編號7369的上司員工編號是7902
員工編號7902的上司員工編號是7566
員工編號7566的上司員工編號是7839
員工編號7839的沒有上司，所以可能是老闆

※CONNECT BY可以方便的把這樣的關係做出來

※基本語法

SELECT LEVEL, empno, mgr, 
    lpad('=>', LEVEL * 2, ' ') || ename pic
FROM emp
CONNECT BY PRIOR empno = mgr
-- AND sal > 1500
START WITH mgr is null
ORDER BY LEVEL;

※START WITH 和 CONNECT BY 順序可調換

※不能有WHERE，要加條件直接在CONNECT BY 後面寫，如上面註解的部分

※CONNECT BY 是一定要寫的，START WITH、ORDER BY 可以視情況加進去

※START WITH 表示從哪裡開始，而LEVEL是搭配 CONNECT BY使用的，從圖中可以知道它顯示的是第幾層的關係

※結果：

※CONNECT_BY_ISLEAF、SYS_CONNECT_BY_PATH

SELECT LEVEL, empno, mgr, 
    lpad('=>', LEVEL * 2, ' ') || ename pic,
    decode(CONNECT_BY_ISLEAF, 0, 'root', 'leaf') leaf,
    SYS_CONNECT_BY_PATH(ename,'-->') all_path
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH mgr is null;

※CONNECT_BY_ISLEAF：看下圖可以知道是不是有下屬，沒有就是leaf
SYS_CONNECT_BY_PATH：從START WITH設定的欄位開始往下

※結果：

※PRIOR

SELECT LEVEL, empno, mgr, 
    lpad('=>', LEVEL * 2, ' ') || ename pic,
    decode(CONNECT_BY_ISLEAF, 0, 'root', 'leaf') leaf,
    SYS_CONNECT_BY_PATH(ename,'-->') all_path
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH empno = 7902;

※PRIOR可以在等號的左邊或右邊，或都不寫
CONNECT BY PRIOR empno = mgr：第一個結果，把7902的下屬都顯示出來(管多少人)
CONNECT BY empno = PRIOR mgr：第二個結果，把7902的上司都顯示出來(被多少人管)
CONNECT BY empno = mgr 或 CONNECT BY PRIOR empno = PRIOR mgr(兩邊都寫)：
只會顯示 7902

結果：

※CONNECT_BY_ROOT

SELECT LEVEL, empno, mgr, 
    lpad('=>', LEVEL * 2, ' ') || ename pic,
    CONNECT_BY_ROOT ename conn_name
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH empno = 7566;

※結果：

※開始是7566，7566叫JONES，所以以下都是JONES，PRIOR不管寫在哪都一樣，也就是START WITH設定誰，誰就是root

※ORDER siblings BY

SELECT LEVEL, empno, mgr, 
    lpad('=>', LEVEL * 2, ' ') || ename pic,
    decode(CONNECT_BY_ISLEAF, 0, 'root', 'leaf') leaf,
    SYS_CONNECT_BY_PATH(ename,'-->') all_path
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH mgr is null
-- ORDER BY ename
ORDER siblings BY ename;

※ORDER BY結果可以看出，把整個PIC從小到大排好了，但大部分的人不會這樣用

※ORDER siblings BY結果可以看出是以層級為單位做排序的

※CONNECT_BY_ISCYCLE、NOCYCLE

假設自己的上司是123，123的上司是456，而456的上司又是123，這就是循環，使用這個語法可以判斷有沒有循環

UPDATE emp SET mgr = 7902 WHERE empno = 7839;
    
SELECT LEVEL, empno, mgr, 
    lpad('=>', LEVEL * 2, ' ') || ename pic,
    decode(CONNECT_BY_ISLEAF, 0, 'root', 'leaf') leaf,
    decode(CONNECT_BY_ISCYCLE, 0, 'x', 1, 'o') 有循環嗎
FROM emp
CONNECT BY NOCYCLE PRIOR empno = mgr
START WITH empno = 7839
ORDER siblings BY ename;

※因為目前沒有循環，所以執行第一個SQL，不要 commit 就可以執行了，測試完記得 rollback

※結果：

※7902有上司有下屬，但循環只會顯示7902

※使用 CONNECT_BY_ISCYCLE 一定要使用 NOCYCLE，不然編譯過不了(但使用 NOCYCLE 可以不用 CONNECT_BY_ISCYCLE)

※如果有循環但不打 NOCYCLE 會出現
ORA-01436: CONNECT BY 子句造成使用者資料迴路
01436. 00000 -  "CONNECT BY loop in user data"

分析函數二 (ROW_NUMBER、DENSE_RANK、KEEP、FIRST_VALUE、LEAD、CUME_DIST、NTILE、RATIO_TO_REPORT) (DML 七)

※ROW_NUMBER

顯示行號

SELECT
    deptno,
    sal,
    ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal) rn1, 
    ROW_NUMBER() OVER(ORDER BY sal) rn2
FROM EMP ORDER BY deptno;

※結果：

可以看出RN1是照部門排列的，因為有PARTITION BY，而RN2很亂，所以將最外面的ORDER BY 改成sal後，結果如下：

※RANK、DENSE_RANK

按照排序的序號

SELECT 
    RANK() OVER(PARTITION BY deptno ORDER BY sal) r, 
    DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal) d, 
    sal,
    deptno 
FROM EMP WHERE deptno = 30;

※結果：

※第四行可以看出RANK會跳號，而DENSE_RANK不會跳號

※KEEP

必需要搭配GROUP BY使用

SELECT 
    -- MAX(sal) OVER(ORDER BY sal) r, 
    -- MIN(sal) OVER(ORDER BY sal) d, 
    deptno,
    MAX(sal) KEEP(DENSE_RANK FIRST ORDER BY sal) max, 
    MIN(sal) KEEP(DENSE_RANK FIRST ORDER BY sal) min,
    MAX(sal) KEEP(DENSE_RANK LAST ORDER BY sal) maxk, 
    MIN(sal) KEEP(DENSE_RANK LAST ORDER BY sal) mink
FROM EMP
GROUP BY deptno;

※結果：

※註解的部分不能執行，因為不能有GROUP BY

※FIRST_VALUE、LAST_VALUE

取出第一／最後的記錄

SELECT
    sal,
    FIRST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal) first, 
    LAST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal) last
FROM EMP
WHERE deptno = 20;

※結果：

※看左邊的SAL再對照右邊的，可以看出FIRST因為是第一筆，所以都是800

※LEAD、LAG

將資料往前／往後移動
LEAD是領導的意思，以資料為單位，所以會將後面的資料往上帶，下面變null(預設)
LAG是拖的意思，以資料為單位，所以會將上面的資料往下帶，上面變null(預設)

SELECT
    sal,
    LAG(sal) OVER(PARTITION BY deptno ORDER BY sal) lag1,
    LAG(sal, 1) OVER(PARTITION BY deptno ORDER BY sal) lag2,
    LAG(sal, 2) OVER(PARTITION BY deptno ORDER BY sal) lag3,
    LAG(sal, 2, -99) OVER(PARTITION BY deptno ORDER BY sal) lag4,
    LEAD(sal, 2) OVER(PARTITION BY deptno ORDER BY sal) lead
FROM EMP
WHERE deptno = 30;

※結果：

※LAG(sal)不加參數，等同LAG(sal, 1)，1表示往下一行

※加第3個參數，可將移動的變成你加的第3個參數，不加就會顯示null，型態要和第2個參數一樣

※CUME_DIST

針對位置的百分比

SELECT
    deptno,
    sal,
    ROUND(CUME_DIST() OVER(PARTITION BY deptno ORDER BY sal), 2) c
FROM emp;

※結果：

※以20部門為例，總共有五筆，所以每筆是1/5=0.2，但要注意薪水一樣，全部都會以最後的為主

※NTILE

NTILE要看結果比較好說明

SELECT
    deptno,
    sal,
    -- SUM(sal) OVER(PARTITION BY deptno ORDER BY sal) sum,
    NTILE(1) OVER(PARTITION BY deptno ORDER BY sal) n1,
    NTILE(2) OVER(PARTITION BY deptno ORDER BY sal) n2,
    NTILE(3) OVER(PARTITION BY deptno ORDER BY sal) n3,
    NTILE(4) OVER(PARTITION BY deptno ORDER BY sal) n4,
    NTILE(5) OVER(PARTITION BY deptno ORDER BY sal) n5,
    NTILE(6) OVER(PARTITION BY deptno ORDER BY sal) n6,
    NTILE(7) OVER(PARTITION BY deptno ORDER BY sal) n7
FROM emp;

※結果：

※以30部門的N3為例，30部門有6筆，6/3=2，所以2筆資料為一組

20部門的N3則是5/3四捨五入後=2，所以也是2筆資料為一組

※RATIO_TO_REPORT

相對於欄位值的百分比

SELECT deptno, count(deptno) c FROM emp GROUP BY deptno ORDER BY deptno;
    
SELECT
    deptno,
    sum(sal) sum,
    ROUND(RATIO_TO_REPORT(sum(sal)) OVER(), 2) rs,
    ROUND(avg(sal), 2) avg,
    ROUND(RATIO_TO_REPORT(avg(sal)) OVER(), 2) ra
FROM emp
GROUP BY deptno
ORDER BY deptno;

※結果：

※第一張圖，可以看到10部門有3筆；20部門有5筆；30部門有6筆

※3個部門全部的薪水是 8750 + 10875 + 9400 = 29025
10部門的RS是用8750 / 29025算出來的，其他部門依此類推

※3個部門全部的平均薪水是2916.67 + 2175 + 1566.67 = 6658.34
10部門的RA是用 2916.67 / 6658.34算出來的，其他部門依此類推

分析函數一 (OVER) (DML 六)

官網
彙總函數OVER(xxx)的語法，彙總函數之前有介紹到，這裡主要說明OVER後面的語法，彙總函數OVER(xxx)是個整體，xxx一改，彙總函數也會不一樣

※彙總函數和欄位一起顯示

SELECT sum(sal) s, e.* FROM emp e;
SELECT sum(sal) OVER() s, e.* FROM emp e;

※結果：

※第一個SELECT會發生錯誤，因為彙總函數不能和欄位一起顯示

※使用上一篇的 GROUP BY 不能顯示其他的欄位，所以用分析函數

※GROUP BY 因為其他的欄位都不一樣，所以不知道要顯示什麼？這裡用分析函數最前面是sum，所以它會加總，然後每一行都一樣

※分區

SELECT deptno, sum(sal) FROM emp GROUP BY deptno ORDER BY deptno;
SELECT distinct sum(sal) OVER(PARTITION BY deptno) s, e.deptno FROM emp e;
SELECT sum(sal) OVER(PARTITION BY deptno) s, e.sal, e.deptno FROM emp e;

※結果：

※第一和第二個SELECT結果一樣，但如果想顯示其他的欄位，就只好用分析函數了

※分區多個欄位

SELECT sum(sal) OVER(PARTITION BY deptno, job) s, e.sal, e.job, e.deptno FROM EMP e;

※結果：

※會以deptno分區完後，再分區job，注意順序

※注意改成兩個分區後，會以兩個分區加總，看S欄位的變化

※分區排序

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY ename) s, e.sal, e.ename, e.deptno FROM EMP e;

※結果：

※可看出ename由小到大排序了(ASC)，也可以 ORDER BY 多個欄位

※因為此範例 ENAME 沒有重覆值，所以S欄位，每一個都會累加

※以 DEPTNO 排序會有重覆值，如下：

SELECT sum(sal) OVER(ORDER BY deptno) s, e.sal, e.deptno FROM emp e;

※結果：

※這時會以DEPTNO為單位加總，和上面的分區結果圖對照(第二張圖)，會發現這裡的語法沒有PARTITION BY，所以不會分區，而S欄位一開始會以10部門加總，這時看起來一樣，但第二次會將20部門加總再加上10部門的總和，這時就和第二張圖不一樣了，然後依此類推

※NULL顯示在前/後

SELECT sum(sal) OVER(ORDER BY comm NULLS FIRST) s, e.sal, e.comm, e.deptno FROM emp e;

※結果：

※加上NULLS FIRST會將有null的放在最前面，而NULLS LAST會放在最後面，如果都不打，等同NULLS LAST

※向上範圍

關鍵字是PRECEDING

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE 300 PRECEDING) s, e.sal, e.deptno FROM emp e;

※結果：

※sal RANGE 300 PRECEDING，表示薪水欄位往上在300之內(含)才會加總，如果往上在300之內會一直往上判斷，直到沒有或300之外才結束判斷

※以20部門為例，看中間的薪水欄
．第一個800，往上沒有值，所以左欄還是800
．第二個1100，減第一個800是300，所以加總(中間欄往上是300之內都會加)
左欄變成1100 + 800 = 1900
．第三個2975，減第二個1100並沒有在300之內，所以左欄還是2975
注意因為是以sal排序，所以一樣的會是一組
而因為第四、五個的薪水都是3000，所以是一組
．第四、五個3000，減第三個2975在300之內，所以會加總(中間欄往上是300之內都會加)，而且一次將第四、五個加起來，變成 3000 + 3000 + 2975 = 8975

※再練習一題，如果將RANGE改成350，會變成如下的狀況

※以30部門為例，看中間的薪水欄
．第一個950，往上沒有值，所以左欄還是950
．第二、三個是1250，減第一個950在350之內，所以加總(中間欄往上是350之內都會加)
左欄變成1250 + 1250 + 950 = 3450
．第四個1500，減第三個1250在350之內，所以加總(中間欄往上是350之內都會加)，
左欄變成1500 + 1250 + 1250 = 4000
．第五個1600，減第四個1500在350之內，所以加總(中間欄往上是350之內都會加)，
左欄變成1600 + 1500 + 1250 + 1250 = 5600(這個是上個範例沒有的，之所以會加1250是因為1600 - 350 = 1250，所以1250以上(含)都會加)
．第六個2850，減第五個1600，並沒有在350之內，所以左欄還是2850

※向下範圍

關鍵字是FOLLOWING

但並不是sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE 350 FOLLOWING)

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN 0 PRECEDING AND 350 FOLLOWING) s, e.sal, e.deptno FROM emp e;

※結果：

※以30部門為例，看中間的薪水欄
．第一個950，看第二、三個是1250，在350之內，所以加總(中間欄往下是350之內都會加)
左欄變成950 + 1250 + 1250 = 3450
．第二、三個是1250，看第四個是1500，在350之內，所以加總(中間欄往下是350之內都會加)
左欄變成1250 + 1250 + 1500 + 1600 = 5600
．第四個1500，看第五個是1600，在350之內，所以加總(中間欄往上是350之內都會加)，
左欄變成1500 + 1600 = 3100
．第五個1600，看第六個是2850，不在350之內，所以左欄還是1600
．第六個2850，往下沒有值，所以左欄還是2850

※向上加向下範圍

也就是向上和向下的合併

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN 300 PRECEDING AND 350 FOLLOWING) s, e.sal, e.deptno FROM emp e;

※結果：

※以30部門為例，看中間的薪水欄，此範例是往上300、往下350
．第一個950，往上沒有值，看第二、三個是1250，在350之內
左欄變成 950 + 1250 + 1250 = 3450

．第二、三個是1250，減第一個950在350之內，往上要加
看第四個是1500，在350之內，往下也要加
左欄變成 950 + 1250 + 1250 + 1500 + 1600 = 6550

．第四個1500，減第二、三個在300之內，往上要加
看第五個是1600，在350之內，往下也要加
左欄變成 1250 + 1250 + 1500 + 1600 = 5600

．第五個1600，減第四個在300之內，往上要加
看第六個是2850，不在350之內，往下不加
左欄變成 1500 + 1600 = 3100

．第六個2850，減第五個不在300之內，往上不加
往下沒有值，往下也不加
左欄還是2850


※當前行

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE CURRENT ROW) s, e.sal, e.deptno FROM emp e;
SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN 0 PRECEDING AND CURRENT ROW) s, e.sal, e.deptno FROM emp e;
SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN 300 PRECEDING AND CURRENT ROW) s, e.sal, e.deptno FROM emp e;

※結果：

※前二條SQL的執行結果都和上面一樣，只有相同的薪水才會加總

※第三條SQL的執行結果和向上範圍一樣

※無範圍

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) s, e.sal, e.deptno FROM emp e;
SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal) s, e.sal, e.deptno FROM emp e;

※結果：

※因為向上無範圍，所以和第二條SQL(分區排序)一樣

※如果向下也無範圍，如下：

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) s, e.sal, e.deptno FROM emp e;
SELECT sum(sal) OVER(PARTITION BY deptno) s, e.sal, e.deptno FROM emp e;

※那結果就和第二條SQL差不多(分區)，只差在沒排序

※以行當範圍

SELECT sum(sal) OVER(PARTITION BY deptno ORDER BY sal ROWS 2 PRECEDING) s, e.sal, e.deptno FROM emp e;

※結果：

※此範例是當前行和往上2行薪水的加總，這時並沒有薪水一樣會一起加的問題