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);
使用子查询指定表列
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);
(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;
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));
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;
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
ORA_ERR_MESG$ ORA_ERR_TAG$ EMP_ID SAL
--------------------------------------- ----------------------- ----------- ------
ORA-02290: check constraint my_bad 161 7700
(HR.SYS_C004266) violated
(未完)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27795363/viewspace-743727/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
