Miscellaneous Notes: Oracle

顯示具有 Oracle 標籤的文章。顯示所有文章

2012年8月20日星期一

SQLs From MySQL To Oracle

GROUP BY + LIMIT


SELECT COUNT(*) AS TOTAL, GENDER FROM MEMBERS_MERGE GROUP BY GENDER HAVING TOTAL > 0 ORDER BY GENDER DESC LIMIT 1, 1;


SELECT SUBQUERY2.* FROM (

SELECT SUBQUERY1.*, ROWNUM AS SUBQUERY1_ROWNUM FROM (
SELECT GENDER, COUNT(*) AS TOTAL FROM MEMBERS_MERGE GROUP BY GENDER HAVING
TOTAL > 0 ORDER BY GENDER ASC
) SUBQUERY1 WHERE ROWNUM <= 2

) SUBQUERY2 WHERE SUBQUERY1_ROWNUM >= 2;

ORDER BY + LIMIT


SELECT * FROM MEMBERS_MERGE WHERE DEPARTMENT > 0 ORDER BY ID DESC LIMIT 1, 5;


SELECT SUBQUERY2.* FROM (

SELECT SUBQUERY1.*, ROWNUM AS SUBQUERY1_ROWNUM FROM (
SELECT * FROM MEMBERS_MERGE WHERE DEPARTMENT > 0 ORDER BY ID DESC
) SUBQUERY1 WHERE ROWNUM <= 6

) SUBQUERY2 WHERE SUBQUERY1_ROWNUM >= 2;

JOIN


SELECT A.NAME AS ANAME, B.ID AS BID FROM TABLE1 AS A INNER JOIN TABLE2 AS B ON A.ID = B.ID;


SELECT A.NAME AS ANAME, B.ID AS BID FROM TABLE1 A INNER JOIN TABLE2 B ON A.ID = B.ID;

INSERT IGNORE INTO


INSERT IGNORE INTO TEMP (MEMBER_ID, TYPE) VALUES (6, 3);


MERGE INTO TEMP T

USING (SELECT 6 AS MEMBER_ID, 3 AS TYPE FROM DUAL) D
ON (T.MEMBER_ID = D.MEMBER_ID)
WHEN NOT MATCHED THEN

  INSERT (MEMBER_ID, TYPE) VALUES (D.MEMBER_ID, D.TYPE);

INSERT INTO... ON DUPLICATE KEY UPDATE


INSERT INTO TEMP (ID, FULL_NAME, AGE) VALUES (5, 'Vera Farmiga', 33) ON DUPLICATE KEY UPDATE AGE = AGE + 1;


MERGE INTO TEMP T

USING (SELECT 5 AS ID, 'Vera Farmiga' AS FULL_NAME, 33 AS AGE) D
ON (T.ID = D.ID)
WHEN MATCHED THEN
UPDATE SET T.AGE = T.AGE + 1
WHEN NOT MATCHED THEN

  INSERT (ID, FULL_NAME, AGE) VALUES (5, 'Vera Farmiga', 33);

2012年7月29日星期日

Oracle 仿 MySQL MGR_MyISAM 功能

新增三個 Schema 相同的資料表，分別取為 MEMBERS0, MEMBERS1, MEMBERS_MERGE，欄位 ID 為 PRIMARY KEY w/ AUTO INCREMENT ，分別為 MEMBERS0, 1 建立 after insert, after update 及 after delete 的 trigger

AD:


create or replace
TRIGGER MEMBERS0_AD

AFTER DELETE ON MEMBERS0
FOR EACH ROW

DECLARE
    OLD_ID NUMBER;

BEGIN
    OLD_ID := :OLD.ID;

    DELETE FROM MEMBERS_MERGE WHERE ID = OLD_ID;

END;

AI:


create or replace
TRIGGER MEMBERS0_AI

AFTER INSERT ON MEMBERS0
FOR EACH ROW

DECLARE
    NEW_ID NUMBER;
    NEW_FULL_NAME VARCHAR2(20);
    NEW_GENDER VARCHAR2(1);
    NEW_DEPARTMENT NUMBER;
    NEW_CREATE_DT DATE;

BEGIN
    NEW_ID := :NEW.ID;
    NEW_FULL_NAME := :NEW.FULL_NAME;
    NEW_GENDER := :NEW.GENDER;
    NEW_DEPARTMENT := :NEW.DEPARTMENT;
    NEW_CREATE_DT := :NEW.CREATE_DT;

    INSERT INTO MEMBERS_MERGE(ID, FULL_NAME, GENDER, DEPARTMENT, CREATE_DT) VALUES(NEW_ID, NEW_FULL_NAME, NEW_GENDER, NEW_DEPARTMENT, NEW_CREATE_DT);
END;

AU:


create or replace
TRIGGER MEMBERS0_AU

AFTER UPDATE ON MEMBERS0
FOR EACH ROW

DECLARE
    NEW_ID NUMBER;
    NEW_FULL_NAME VARCHAR2(20);
    NEW_GENDER VARCHAR2(1);
    NEW_DEPARTMENT NUMBER;
    NEW_CREATE_DT DATE;

BEGIN
    NEW_ID := :NEW.ID;
    NEW_FULL_NAME := :NEW.FULL_NAME;
    NEW_GENDER := :NEW.GENDER;
    NEW_DEPARTMENT := :NEW.DEPARTMENT;
    NEW_CREATE_DT := :NEW.CREATE_DT;

    UPDATE MEMBERS_MERGE SET ID = NEW_ID, FULL_NAME = NEW_FULL_NAME, GENDER = NEW_GENDER, DEPARTMENT = NEW_DEPARTMENT, CREATE_DT = NEW_CREATE_DT WHERE id = NEW_ID;

END;

考慮到若在 MERGE 資料表也建立 AU, AD triggers，會造成 trigger 的迴圈，故無法完全模擬 MySQL 的 MGR_MyISAM，替代方案就是讀寫分離，insert, update, delete 時使用各 MEMBERS，select 時使用 MERGE。

2012年7月27日星期五

Oracle 新增 TableSpace & DataFile 於指定路徑

Oracle 資料庫是將資料表存放在 TableSpace 裡的 DataFile 裡。Query data 只能跨 DataFile 去 Query 其它資料表。可以視 DataFile 為 MySQL 中的 Database，ex: select * from aDataBase.bTable;

Create TableSpace:

Create Tablespace I_MARRY_V3
Datafile 'C:\oraclexe\app\oracle\oradata\XE\I_MARRY_V3.DBF' size 100M
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
Extent Management Local
Segment Space Management Auto;

Drop TableSpace:

Drop tablespace I_MARRY_V3 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

參考資料:
http://mis.im.tku.edu.tw/~xman13a/oracle/tablespace/ora_1.htm
http://bluemuta38.pixnet.net/blog/post/47674212-oracle-create-tablespace

2012年3月13日星期二

Oracle 建立 trigger 及 sequence 達成類 MySQL auto increment

sequence: 在 Oracle Developer 裡新增，
最大值 999999999999999999999999999
增量 1
NOCACHE
(其中習慣命名以_seq結尾；trigger 以 _tri 結尾)

Trigger:

CREATE OR REPLACE TRIGGER MEMBERS_TRI
BEFORE INSERT OR DELETE OR UPDATE ON MEMBERS
FOR EACH ROW
BEGIN
SELECT MEMBERS_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

Oracle 取得 table 欄位的註解及屬性

Oracle 取得某 table 某欄位的註解

select * from ALL_COL_COMMENTS where table_name = ''

Array
(
[0] => Array
(
[OWNER] => ROOT
[TABLE_NAME] => MEMBER
[COLUMN_NAME] => ID
[COMMENTS] => id
)

[1] => Array
(
[OWNER] => ROOT
[TABLE_NAME] => MEMBER
[COLUMN_NAME] => ROCNUMBER
[COMMENTS] => rocNumber
)

)

Oracle 取得某 table 某欄位的屬性

select * from ALL_TAB_COLUMNS where table_name = ''

Array
(
[0] => Array
(
[OWNER] => ROOT
[TABLE_NAME] => MEMBER
[COLUMN_NAME] => ID
[DATA_TYPE] => NUMBER
[DATA_TYPE_MOD] =>
[DATA_TYPE_OWNER] =>
[DATA_LENGTH] => 22
[DATA_PRECISION] =>
[DATA_SCALE] =>
[NULLABLE] => N
[COLUMN_ID] => 1
[DEFAULT_LENGTH] =>
[DATA_DEFAULT] =>
[NUM_DISTINCT] =>
[LOW_VALUE] =>
[HIGH_VALUE] =>
[DENSITY] =>
[NUM_NULLS] =>
[NUM_BUCKETS] =>
[LAST_ANALYZED] =>
[SAMPLE_SIZE] =>
[CHARACTER_SET_NAME] =>
[CHAR_COL_DECL_LENGTH] =>
[GLOBAL_STATS] => NO
[USER_STATS] => NO
[AVG_COL_LEN] =>
[CHAR_LENGTH] => 0
[CHAR_USED] =>
[V80_FMT_IMAGE] => NO
[DATA_UPGRADED] => YES
[HISTOGRAM] => NONE
)

[1] => Array
(
[OWNER] => ROOT
[TABLE_NAME] => MEMBER
[COLUMN_NAME] => ROCNUMBER
[DATA_TYPE] => VARCHAR2
[DATA_TYPE_MOD] =>
[DATA_TYPE_OWNER] =>
[DATA_LENGTH] => 10
[DATA_PRECISION] =>
[DATA_SCALE] =>
[NULLABLE] => N
[COLUMN_ID] => 2
[DEFAULT_LENGTH] =>
[DATA_DEFAULT] =>
[NUM_DISTINCT] =>
[LOW_VALUE] =>
[HIGH_VALUE] =>
[DENSITY] =>
[NUM_NULLS] =>
[NUM_BUCKETS] =>
[LAST_ANALYZED] =>
[SAMPLE_SIZE] =>
[CHARACTER_SET_NAME] => CHAR_CS
[CHAR_COL_DECL_LENGTH] => 10
[GLOBAL_STATS] => NO
[USER_STATS] => NO
[AVG_COL_LEN] =>
[CHAR_LENGTH] => 10
[CHAR_USED] => B
[V80_FMT_IMAGE] => NO
[DATA_UPGRADED] => YES
[HISTOGRAM] => NONE
)

)

2012年8月20日 星期一