posts - 431,  comments - 344,  trackbacks - 0
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 在等待资源时超时


阅读中...
posted on 2009-09-03 20:00 周锐 阅读(273) 评论(0)  编辑  收藏 所属分类: Oracle

只有注册用户登录后才能发表评论。


网站导航: