SQL語句分為5類
1. 查詢語句 SELECT
2. 數(shù)據(jù)操縱語言（Data Manipulation Language, DML）語句
    INSERT
    UPDATE
    DELETE
3. 數(shù)據(jù)定義語言（Data Definition Language, DDL）語句
    CREATE
    ALTER
    DROP
    RENAME
    TRUNCATE
4. 事務(wù)控制（Transaction Control, TC）語句
    COMMIT
    ROLLBACK
    SAVEPOINT
5. 數(shù)據(jù)控制語言（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 優(yōu)先 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)

兩種函數(shù)：單行函數(shù)（字符，數(shù)字，轉(zhuǎn)換，日期，正則表達式）和聚合函數(shù)

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 (包括重復(fù)的)     UNION (不包括重復(fù)的)     類型必須一樣，列數(shù)必須一樣，名稱可以不一樣

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;
任何聚合函數(shù)都可以和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預(yù)定義系統(tǒng)異常類型

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

閱讀中...