Oracle 11gR2 SQL Reference Tips

使用Collection

http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/05_colls.htm

Example:

DECLARE
  TYPE [USER_DEFINED_TYPE_NAME] IS TABLE OF [TABLE].[COLUMN]%TYPE;
  [VARIABLE_NAME] [USER_DEFINED_TYPE_NAME];

BEGIN
  SELECT [COLUMN] BULK COLLECT INTO [VARIABLE_NAME] FROM [TABLE] WHERE [CONDITIONS];
  FOR i IN 1..[VAIRABLE_NAME].COUNT LOOP
    BEGIN
      DBMS_OUTPUT.PUT_LINE(' DATA: '||[VARIABLE_NAME](i));
      ...
    END;
  END LOOP;
END;

清除資料表

TRUNCATE TABLE nnn;

DELETE FROM nnn;

複製整個 Table 資料

CREATE TABLE [Backup Table Name] AS SELECT * FROM [Original Table Name];  ←自動建出New Table

SELECT * INTO [Backup Table Name] FROM [Original Table Name];  ← New Table需先建好

日期格式化

(目前時間)

SELECT SYSDATE, CURRENT_TIMESTAMP, CURRENT_DATE, LOCALTIMESTAMP FROM DUAL;

(Date→String)

SELECT TO_CHAR(CURRENT_TIMESTAMP, ‘YYYY/MM/DD HH24:MI:SS’) FROM DUAL;

(String→Date)

SELECT TO_DATE(‘2014/03/01′,’YYYY/MM/DD’) FROM DUAL;

* 日期加減以天為單位

http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF51079

EXISTS

檢查Subquery是否回傳一筆以上的資料,只要有資料就是true.

範例: 只回傳有員工的部門資料.

SELECT department_id FROM departments d WHERE EXISTS
  (SELECT * FROM employees e
    WHERE d.department_id = e.department_id)
   ORDER BY department_id;

CASE

CASE [EXPRESSION]
  WHEN COND_1 THEN RESULT_1
  WHEN COND_2 THEN RESULT_2
  ELSE RESULT
END
範例 1 : 如果credit_limit = 1000,顯示Low;以此類推(不適用null, 因為null不是用=來比;不適用多欄比較)
SELECT cust_name,
   CASE credit_limit 
     WHEN 1000 THEN 'Low'
     WHEN 5000 THEN 'High'
     ELSE 'Medium' 
   END
FROM customers;
範例 2 : 如果credit_limit為null,顯示N/A;以此類推(null及多欄比較)

SELECT cust_name,
   CASE 
     WHEN credit_limit is null THEN 'N/A'
     WHEN credit_limit = 1000 THEN 'Low'
     WHEN credit_limit = 5000 THEN 'High'
     ELSE 'Medium' 
   END
FROM customers;

MAX – 找出每個群組中NNN最大的那一筆

範例:找出每個部門薪水最高的員工

SELECT * FROM (
SELECT DIV_ID, DEPT_ID, ID, NAME, SALARY, MAX(SALARY) OVER (PARTITION BY DIV_ID, DEPT_ID) AS MAX_SALARY
FROM EMPLOYEE
)
WHERE SALARY = MAX_SALARY

MAX – 找出最大的那一筆,如果有Null,Null最大

SELECT ID, MAX(END_DATE) KEEP (DENSE_RANK FIRST ORDER BY END_DATE DESC NULLS FIRST) FROM OFFER

ROW_NUMBER – 找出群組中排序後的N筆資料

範例:找出每組 ID+END_DATE 群組, 依END_DATE排序後第一筆資料
SELECT * FROM (
 SELECT ID, END_DATE, ROW_NUMBER() OVER (PARTITION BY ID, END_DATE ORDER BY END_DATE) RN FROM OFFER
) WHERE RN <= 1

 UPDATE (WITH SELECT)

UPDATE [TABLE] SET ([COL1], [COL2], ...) = (SELECT [COL1], [COL2],... FROM [TABLE] WHERE...)

從左邊取N碼,不足N碼,直接回傳欄位值

SELECT SUBSTR('COLUMN', GREATEST(-N, -LENGTH('COLUMN'))) FROM ...

 

Reference:

http://docs.oracle.com/cd/E11882_01/index.htm

http://docs.oracle.com/cd/E11882_01/server.112/e41085/toc.htm

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s