Insert 句法实例

1. inserting values  into tables

> INSERT INTO departments VALUES (280, 'Recreation', 121, 1700);

 

使用default值

> INSERT INTO departments  VALUES (280, 'Recreation', DEFAULT, 1700);

 

指定表列

> INSERT INTO employees (employee_id, last_name, email,
                                               hire_date, job_id, salary, commission_pct)
   VALUES (207, 'Gregory', 'pgregory@example.com', sysdate, 'PU_CLERK', 1.2E3, NULL);

使用子查询指定表列

> INSERT INTO
   (SELECT employee_id, last_name, email, hire_date, job_id,
                   salary, commission_pct FROM employees)
   VALUES (207, 'Gregory', 'pgregory@example.com', sysdate, 'PU_CLERK', 1.2E3, NULL);

2. Inserting Values with a Subquery子

查询作插入值

> INSERT INTO bonuses
   SELECT employee_id, salary*1.1
   FROM employees
   WHERE commission_pct > 0.25;

 

3. Inserting Into a Table with Error Logging

带错误记录插入

 

首先创建插入表raises

> CREATE TABLE raises (emp_id NUMBER, sal NUMBER
   CONSTRAINT check_sal CHECK(sal > 8000));

 

再调用dbms_errlog包中create_error_log过程创建错误记录表errlog,该表记录对raises插入记录时的失败情况

> execute dbms_errlog.create_error_log('RAISES','ERRLOG');

 

接着插入值到raises表，同时记录错误，若错误记录超过10条则回滚insert操作，错误记录标签为my_bad

> INSERT INTO raises
   SELECT employee_id, salary*1.1 FROM employees
   WHERE commission_pct > .2
   LOG ERRORS INTO errlog ('my_bad') REJECT LIMIT 10;

 

查看错误记录表errlog

SELECT ORA_ERR_MESG$, ORA_ERR_TAG$, emp_id, sal FROM errlog;

ORA_ERR_MESG$                   ORA_ERR_TAG$         EMP_ID        SAL
---------------------------------------     -----------------------          -----------        ------
ORA-02290: check constraint     my_bad                                161        7700
(HR.SYS_C004266) violated

 

 

(未完)

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

转载于:http://blog.itpub.net/27795363/viewspace-743727/