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 在等待资源时超时

阅读中...