insert某张表必须要有对 该表的insert权限，如果是insert 视图则需要有对 该视图的base
able的insert权限。如果有insert any
table系统权限则可以对任意的表执行插入操作，如果是对远程计算机上的表执行insert操作，除了需要有insert权限之外还需要有select权限。
一按照插入方式可以分为常规插入以及直接路径插入：
1.常规的insert
常规的insert会重新使用表中的空闲空间，而且是经过cache的。
2.dirct－path insert（直接路径插入）
该种方式的insert，不经过cache，直接将记录写入数据文件中，且是直接append到表中已有记录的后面，如果表中有空闲空间不会重新利用。
直接路径插入的表不能有任何的触发器和参照完整性约束存在；不能被replicated。直接路径插入一旦开始不能中途结束。直接路径插入完成后不能马上进行查询或者修改，否则会报ora-12838错误，需要先commit才可以查询和修改。
有关常规insert以及dirct-path insert的官方文档：
When you insert into a table that has been created in parallel
mode, direct-path
INSERT is the default.
-
Conventional INSERT always generates maximal redo
and undo for changes to both data and metadata, regardless of the
logging setting of the table and the archivelog and force logging
settings of the database.
-
Direct-path INSERT generates both redo and undo for
metadata changes, because these are
needed for operation recovery. For data
changes, undo and redo are generated as follows:
-
Direct-path INSERT always bypasses undo generation
for data changes.
-
If the database is not in ARCHIVELOG or
FORCE LOGGING mode, then no redo is
generated for data changes, regardless of the logging setting of
the table.
-
If the database is in ARCHIVELOG mode (but not in
FORCE LOGGING mode), then direct-path
INSERT generates data redo for LOGGING
tables but not for NOLOGGING tables.
-
If the database is in ARCHIVELOG and FORCE LOGGING mode,
then direct-path SQL generate data redo for both
LOGGING and NOLOGGING tables.、
有关insert的官方文档为：oracle insert
二按照插入对象可以分单表插入以及多表插入
1.单表插入
(1).insert [hint] insert into table/view name [alias]
[column1,column2,...] values( expr,..,default value)
[return/returning expr,expr1.... into date_item1,date_item2....]
[log errors [into [schema.table] [(sample_expr)] [reject limit
intrger]|[reject limit unlimited]]];
(2)insert [hint] insert into table/view name [alias]
[column1,column2,...]
subqquery [return/returning
expr,expr1.... into date_item1,date_item2....] [log errors [into
[schema.table] [(sample_expr)] [reject limit intrger]|[reject limit
unlimited]]];
example1 带with check option，需要满足check条件的才可以插入成功:
insert into(select department_id,department_name,location_id from
departments where
location_id<2000)values(999,'entertainment',1900);
example 2 insert into ....select:
insert into t1 select id,name from t2;(id,name与在t1中的顺序要一致)
example 3 带有错误日志的insert：
SQL> CREATE TABLE raises (emp_id NUMBER, sal NUMBER
2
CONSTRAINT check_sal CHECK(sal > 8000));
表已创建。
创建错误日志
SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('raises',
'errlog');
PL/SQL 过程已成功完成。
如果有10个错误产生则该insert就中断了。
SQL> INSERT INTO raises
2
SELECT employee_id, salary*1.1 FROM employees
3
WHERE commission_pct > .2
4
LOG ERRORS INTO errlog ('my_bad') REJECT LIMIT 10;
已创建 16 行。
SQL> commit;
提交完成。
SQL> SELECT ORA_ERR_MESG$, ORA_ERR_TAG$, emp_id, sal FROM
errlog;
ORA_ERR_MESG$
ORA_ERR_TA
EMP_ID
SAL
--------------------------------------------- ---------- ----------
---------
ORA-02290: 违反检查约束条件
(HR.CHECK_SAL)
my_bad
161
7700
插入了2次，所以会有2条错误日志，raises有32条记录
SQL> select count(*) from raises;
COUNT(*)
----------
32
错误超过了1个，所以该事务不会执行（执行之后回退了）也不会将错误信息写入到错误日志中，还是2条记录
SQL> INSERT INTO raises
2
SELECT employee_id, salary*1.1 FROM employees
3
WHERE commission_pct > .6
4
LOG ERRORS INTO errlog ('my_bad') REJECT LIMIT 1;
已创建 0 行。
SQL> SELECT ORA_ERR_MESG$, ORA_ERR_TAG$, emp_id, sal FROM
errlog;
ORA_ERR_MESG$
ORA_ERR_TA
EMP_ID
SAL
--------------------------------------------- ---------- ----------
---------
ORA-02290: 违反检查约束条件
(HR.CHECK_SAL)
my_bad
161
7700
ORA-02290: 违反检查约束条件
(HR.CHECK_SAL)
my_bad
161
7700
example 4 带有returnning的insert：
SQL> declare
2 bnd1 number(8,2);
3 bnd2 varchar2(15);
4 begin
5 INSERT INTO employees
6
(employee_id, last_name, email, hire_date, job_id, salary)
7
VALUES
8
(employees_seq.nextval, 'Doe', 'john.doe@example.com',
9
SYSDATE, 'SH_CLERK', 2400)
10
RETURNING salary*12, job_id INTO bnd1, bnd2;
11 end;
12 /
PL/SQL 过程已成功完成。
2.多表插入
SQL> select * from t1;
AGE NAME
---------- ----------
1 t1
10 t2
30 t3
t3
30 t4