Oracle9i中SQL新特性

Oracle9i中SQL新特征本文是ORACLE9i中SQL增强的部分内容。

 

显示命名主键索引

通过在CREATE TABLE语句的子句USING INDEX子句中使用CREATE INDEX，可以实现主键索引的单独定义，语句如下：

CREATE TABLE employees

(

  empno NUMBER(6),

  name VARCHAR2(30),

  dept_no NUMBER(2),

  CONSTRAINT emp_pk primary key(empno)

    USING INDEX

    (CREATE INDEX emp_pk_idx ON employees(empno))

);

 

主键约束可以在保留索引的情况下删除，语句如下：

ALTER TABLE employees DROP PRIMARY KEY KEEP INDEX;

ALTER TABLE employees DROP CONSTRAINT empno_pk;

 

视图约束

从该版本可以在视图上创建主键约束、唯一约束及外键约束。NOT NULL约束从基表继承，不能显式声明。由于视图约束不进行验证，所以定义时要使用DISABLE NOVALIDATE子句，语句如下：

CREATE VIEW Emp_view

 (id PRIMARY KEY DISABLE NOVALIDATE, firstname)

AS SELECT employee_id, first_name

FROM employees

WHERE department_id = 10;

 

ALTER VIEW Emp_view

ADD CONSTRAINT emp_view_unq

UNIQUE (first_name) DISABLE NOVALIDATE;

视图约束不进行验证，定义了有什么用处呢？

上述约束的定义主要是提供给优化器使用。

 

MERGE语句

该语句可以根据目标表中记录是否存在来确定是进行UPDATE还是INSERT。该语句可以减少表的扫描次数并可以并发操作。下面的例子使用MERGER将数据从表HR_RECORDS合并到EMPLOYEES:

MERGE INTO employees e

    USING hr_records h

    ON (e.id = h.emp_id)

  WHEN MATCHED THEN

    UPDATE SET e.address = h.address

  WHEN NOT MATCHED THEN

    INSERT (id, address)

    VALUES (h.emp_id, h.address);

 

用于合并的源表还可以是一个查询，示例如下：

MERGE INTO employees e

    USING (SELECT * FROM hr_records WHERE start_date > ADD_MONTHS(SYSDATE, -1)) h

    ON (e.id = h.emp_id)

  WHEN MATCHED THEN

    UPDATE SET e.address = h.address

  WHEN NOT MATCHED THEN

    INSERT (id, address)

    VALUES (h.emp_id, h.address);

 

多表插入

使用一条单独的INSERT INTO .. SELECT语句实现有条件或无条件的多表插入。

1 无条件多表插入

INSERT ALL

  INTO sal_history VALUES(empid,hiredate,sal)

  INTO mgr_history VALUES(empid,mgr,sysdate)

SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR

  FROM employees WHERE employee_id > 200;

 

2 无条件行专列插入

INSERT ALL

  INTO Sales_info VALUES (employee_id,week_id,sales_MON)

  INTO Sales_info VALUES (employee_id,week_id,sales_TUE)

  INTO Sales_info VALUES (employee_id,week_id,sales_WED)

  INTO Sales_info VALUES (employee_id,week_id,sales_THUR)

  INTO Sales_info VALUES (employee_id,week_id, sales_FRI)

SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,

    sales_WED, sales_THUR,sales_FRI

FROM Sales_source_data;

 

3 有条件插入所有表

INSERT ALL

  WHEN SAL>10000 THEN

    INTO sal_history VALUES(EMPID,HIREDATE,SAL)

  WHEN MGR>200 THEN

    INTO mgr_history VALUES(EMPID,MGR,SYSDATE)

SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR

  FROM employees WHERE employee_id > 200;

 

4 条件第一次匹配时插入

INSERT FIRST

  WHEN SAL > 25000  THEN

    INTO special_sal VALUES(DEPTID,SAL)

  WHEN HIREDATE like ('%00%') THEN

    INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)

  WHEN HIREDATE like ('%99%') THEN

    INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)

  ELSE

    INTO hiredate_history_not_99 VALUES(DEPTID, HIREDATE)

 

SELECT department_id DEPTID, SUM(salary) SAL,

    MAX(hire_date) HIREDATE

  FROM employees GROUP BY department_id;

 

多表插入有以下限制：

①不能通过DATABASE LINK进行多表插入；

②不能在嵌套表上进行多表插入；

③多表插入的列不能超过999；

④多表插入的子查询不能使用序列；

 

增强的SELECT..FOR UPDATE语句

当选择的数据处于锁定状态时，该语句会进入等待。增强后，该语句可以使用WAIT关键字指定最大等待时间，超过最大时间即报错。

SELECT *

FROM   employees

WHERE  empno = 20

FOR UPDATE WAIT 30;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24867611/viewspace-739567/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24867611/viewspace-739567/