SQL語句分為5類
1. 查詢語句 SELECT
2. 數據操縱語言（Data Manipulation Language, DML）語句
    INSERT
    UPDATE
    DELETE
3. 數據定義語言（Data Definition Language, DDL）語句
    CREATE
    ALTER
    DROP
    RENAME
    TRUNCATE
4. 事務控制（Transaction Control, TC）語句
    COMMIT
    ROLLBACK
    SAVEPOINT
5. 數據控制語言（Data Control Language, DCL）語句
    GRANT
    REVOKE

SYSDATE

CREATE USE username IDENTIFIED BY password
GRANT CONNECT, RESOURCE TO username

id INTEGER CONSTRAINT student_pk PRIMARY KEY

CONSTRAINT ~~~ REFERENCES product_type(id)

CONSTRAINT compose_pk PRIMARY KEY (column1, column2)

TO_DATE('25-JUL-2007')+2  => 27-JUL-2007

連接操作符(||)

NVL(phone, 'Unknown phone number')

ANY = SOME       where id > ANY(2, 3, 4)       where id > ALL(2, 3, 4)

WHERE name LIKE  '%\%%' ESCAPE '\'

LIKE, IN, BETWEEN, IS NULL, IS NAN, IS INFINITE

USING => ON    (+)

_(一個)   %(任意個)

AND 優先 OR

GROUP BY 可以不與HAVING字句一起使用，但是HAVING必須必須與GROUP BY字句一起使用。 GROUP BY分組，HAVING過濾。

SELECT   product_type_id, AVG(price) FROM products WHERE price < 15 GROUP BY product_type_id HAVING AVG(price) > 13 ORDER BY AVG(price)

兩種函數：單行函數（字符，數字，轉換，日期，正則表達式）和聚合函數

TO_CHAR(dob, 'MONTH DD, YYYY')      TO_DATE('7.4.07', 'MM.DD.YY')

ALTER SESSION SET NLS_DATE_FORMAT = 'MONTH-DD-YYYY';

ADD_MONTHS(x,y)    LAST_DAY(x)    MONTHS_BETWEEN(x, y)    NEXT_DAY(x, day)

EXTRACT 取出年月日等。。

EXISTS性能高于IN

UNION ALL (包括重復的)     UNION (不包括重復的)     類型必須一樣，列數必須一樣，名稱可以不一樣

INTERSECT   MINUS

TRANSLATE(x, from_string, to_string)
DECODE(value, search_value, result, default_value)  => CASE

SELECT division_id, SUM(salary) FROM employee GROUP BY ROLLUP(division_id) ORDER BY division_id;
SELECT division_id, SUM(salary) FROM employee GROUP BY ROLLUP(division_id, job_id) ORDER BY division_id;
任何聚合函數都可以和ROLLUP一起使用

SELECT division_id, SUM(salary) FROM employee GROUP BY CUBE(division_id, job_id) ORDER BY division_id;

GROUPING只能在使用ROLLUP或CUBE的查詢時候使用
SELECT GROUPING(division_id), division_id, SUM(salary) FROM employee GROUP BY ROLLUP(division_id) ORDER BY division_id;

使用GROUPING SETS子句可以只返回小計記錄

MERGE INTO products p
USING product_changes pc ON (
    p.product_id = pc.product_id
)
WHEN MATCHED THEN
    UPDATE
    SET
        p.product_type_id = pc.product_type_id,
        p.name = pc.name,
        p.description = pc.description,
        p.price = pc.price
WHEN NOT MATCHED THEN
    INSERT (
        p.product_id, p.product_type_id, p.name, p.description, p.price
    ) VALUES (
        pc.product_id, pc.product_type_id, pc.name, pc.description, pc.price
    );

user_tables, user_tab_columns, all_tables, all_tab_columns;

ALTER TABLE salary_grades
ADD (average_salary AS ((low_salary + high_salary)/2));

ALTER TABLE order_status
DROP COLUMN modified_by;

ALTER TABLE order_status
ADD CONSTRAINT order_status_modified_by_fk
modified_by REFERENCES employee(employee_id) ON DELETE CASCADE;

ALTER TABLE order_status
ADD CONSTRAINT order_status_modified_by_fk
modified_by REFERENCES employee(employee_id) ON DELETE SET NULL;

ALTER TABLE order_status
ADD CONSTRAINT order_status_status_ck
CHECK (status in ('PLACED', 'PENDING', 'SHIPPED'));

ALTER TABLE order_status
DISABLE CONSTRAINT order_status_status_ck;

ALTER TABLE order_status
ENABLE CONSTRAINT order_status_status_ck;

ALTER TABLE order_status
ENABLE NOVALIDATE CONSTRAINT order_status_status_uq;

SET SERVEROUTPUT ON

DECLARE
    v_product_id  products.product_id%TYPE;
    v_name           products.name%TYPE;
    v_price           products.price%TYPE;

    CURSOR v_product_cursor IS
        SELECT product_id, name, price
        FROM products
        ORDER BY product_id;
    
    BEGIN
        OPEN v_product_cursor;
        LOOP
            FETCH v_product_cursor INTO v_product_id, name, price;
            EXIT WHEN v_product_cursor%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(~~)
        END LOOP;
        CLOSE v_product_cursor;
    END;
/

SET SERVEROUTPUT ON

DECLARE
    CURSOR v_product_cursor IS
        SELECT product_id, name, price
        FROM products
        ORDER BY product_id;
    
    BEGIN
        FOR v_product IN v_product_cursor LOOP
            DBMS_OUTPUT.PUT_LINE(v_product.product_id || v_product.name || v_product.price)
        END LOOP;
    END;
/

Oracle預定義系統異常類型

命名的系統異常	產生原因
ACCESS_INTO_NULL	未定義對象
CASE_NOT_FOUND	CASE 中若未包含相應的 WHEN ，并且沒有設置 ELSE 時
COLLECTION_IS_NULL	集合元素未初始化
CURSER_ALREADY_OPEN	游標已經打開
DUP_VAL_ON_INDEX	唯一索引對應的列上有重復的值
INVALID_CURSOR	在不合法的游標上進行操作
INVALID_NUMBER	內嵌的 SQL 語句不能將字符轉換為數字
NO_DATA_FOUND	使用 select into 未返回行，或應用索引表未初始化的元素時
TOO_MANY_ROWS	執行 select into 時，結果集超過一行
ZERO_DIVIDE	除數為 0
SUBSCRIPT_BEYOND_COUNT	元素下標超過嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT	使用嵌套表或 VARRAY 時，將下標指定為負數
VALUE_ERROR	賦值時，變量長度不足以容納實際數據
LOGIN_DENIED	PL/SQL 應用程序連接到 oracle 數據庫時，提供了不正確的用戶名或密碼
NOT_LOGGED_ON	PL/SQL 應用程序在沒有連接 oralce 數據庫的情況下訪問數據
PROGRAM_ERROR	PL/SQL 內部問題，可能需要重裝數據字典＆ pl./SQL 系統包
ROWTYPE_MISMATCH	宿主游標變量與 PL/SQL 游標變量的返回類型不兼容
SELF_IS_NULL	使用對象類型時，在 null 對象上調用對象方法
STORAGE_ERROR	運行 PL/SQL 時，超出內存空間
SYS_INVALID_ID	無效的 ROWID 字符串
TIMEOUT_ON_RESOURCE	Oracle 在等待資源時超時

閱讀中...