oracle 備忘

出來混總是會碰到的～其實我對甲骨文的印象很不好～又肥又胖又貴...
（快十年前，硬碟還很小，RAM 2G就很強大的時後，oracle一裝就馬上讓電腦慢到爆！）
（然後買了Java...又把人家搞爛）

不過還是又看到他了一.一|||...
是說用的方法網路資源很多，就當個記錄就是～

好吧Orz...先說一下工具的部份。
工具大家似乎都是使用 PL/SQL Developer。不過他要吃 oracle 的 ora 檔，還得要先裝一個oracle client（還好有人有現成的copy來用就沒差）...
目前 oracle 官網放的是叫 SQL Developer，老實說，難用到不知道要怎麼用，然後他連接的方式並不會產生ora檔～所以...就直接放棄他了一.一...
另外我平常使用的是小松鼠squirrel-sql，是一個用純 jdbc 實作出來的opensource...當然是很陽春，不過輕量簡單～若只是做查詢的簡單工作，又不想裝太多是個選擇。不過他抓不到 schema內的 REMARK（註解），在看會很不方便。

最後～我是用 PLSQL Developer 啦，因為要做點管理的工作Orz...
另外他對 function 等的編輯畫面好很多～

建立table～可以透過介面新增。工具好的話都不會有啥問題～
（工具 用介面建，之後匯出SQL給上線的server用就好）
就是建好要 grant 給其他 user 使用。
grant select, insert, update, delete on XXX_table to ZZZ_user;

因為有用到 synonym 的部份，要把有 owner(schema) 的轉成 public 也可以讀到～
create or replace public synonym XXX_table  for YY_owner.XXX_table;

另有做Trigger Log table的方式是～
先建一個 要 trigger 的 table，一般是 XXX_table_LOG，做好跟開 table 一樣的事。
寫 trigger，可以參照
https://docs.oracle.com/cd/E17952_01/refman-5.1-en/create-tablespace.html
因為有前人的照抄，搞懂語法後是還蠻容易的就是～
唯一要注意的就是，因為我這邊用的統一都是在 BEFORE 之前Trigger，在 insert 時要寫入LOG時，因為有些欄位是有帶預設值的，所以要記得用NVL判斷一下，已免應該有值，但寫了null～

CREATE OR REPLACE TRIGGER TG_XXX_table_LOG
  BEFORE INSERT OR DELETE OR UPDATE
  on YY_owner.XXX_table
  REFERENCING NEW AS NEW OLD AS OLD
  for each row
declare
  -- local variables here
begin
  IF updating THEN
    INSERT INTO YY_owner.XXX_table_LOG
      (Col_A,
       Col_B,
       LOG_DATE,
       LOG_ACTION)
    VALUES
      (:NEW.Col_A,
       :NEW.Col_B,
       SYSDATE,
       'U');
  ELSIF inserting THEN
    INSERT INTO YY_owner.XXX_table_LOG
      (Col_A,
       Col_B,
       LOG_DATE,
       LOG_ACTION)
    VALUES
      (:NEW.Col_A,
       NVL(:NEW.Col_B,'Y'),
       SYSDATE,
       'I');
  ELSE
    INSERT INTO YY_owner.XXX_table_LOG
      (Col_A,
       Col_B,
       LOG_DATE,
       LOG_ACTION)
    VALUES
      (:OLD.Col_A,
       :OLD.Col_B,
       SYSDATE,
       'D');
  END IF;
end;

話說...Oracle他喵的居然....把空字串當 null...一整個無言了...
所以平常常用的  IFNULL(COL_A, '') != ''
在 oracle 裡要寫成  NVL(COL_A, '') is not null
Orz...

Oracle 在下sql時，對於日期的比對 （時間區間），
無法使用  XXX_DATE >= '2015-10-02 00:00:00' 這種的寫法來下～
要利用轉日期型態  to_date('2015-10-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 來表示 ，
要注意的是他的 format 跟一般用字也不一樣～～
可參考
http://www.techonthenet.com/oracle/functions/to_date.php