OracleSQLInsert和Create语句增强功能
程序员文章站
2022-07-04 09:38:17
oraclesqlinsert和create语句增强功能，create 语句强化学习 （1）普通的创建表的语句，创建表的时候为表添加各种约束条件。
create table s_emp ( id n...
oraclesqlinsert和create语句增强功能，create 语句强化学习 （1）普通的创建表的语句，创建表的时候为表添加各种约束条件。
create table s_emp ( id number(7) constraint s_emp_id_pk primary key, -- 每个字段的约束约束条件都应该取一个名称，以后可以直接修改约束条件，可以直接指定这个名称定位到指定的约束条件 last_name varchar2(25) constraint s_emp_last_name_nn not null, first_name varchar2(25),
userid varchar2(8) constraint s_emp_userid_nn not null constraint s_emp_userid_uk unique, -- 为一个字段添加两个约束条件，两个约束条件作用于同一个字段不用逗号隔开，用空格隔开
start_date date default sysdate, -- 默认值的设置 comments varchar2(25), manager_id number(7), title varchar2(25), dept_id number(7) constraint s_emp_dept_id_fk references departments(department_id), -- 外键的约束 salary number(11,2), commission_pct number(4,2) constraint s_emp_commission_pct_ck check(commission_pct in(10,12.5,15,17.5,20)) -- check函数范围的约束 );
创建这个标识后会创建三个键，两个索引 主键（primary）：id 唯一键（unique）：userid 外键（foreign）：dept_id oracle会为每一个唯一键创建一个唯一性索引：这里创建两个唯一性索引：id和userid
单独创建约束的语法： alter table cux_les_je_lines add constraint cux_les_je_lines_pk primary key(je_line_id);
外键约束类型： references: 表示列中的值必须在父表中存在 on delete cascade: 当父表记录删除的时候自动删除子表中的相应记录. on delete set null: 当父表记录删除的时候自动把子表中相应记录的值设为null
（2）使用子查询创建表，需要注意一些条件
再做一些数据迁移时候，会使用create table table_name2 as select * from table_name1 where id=-1的方式来年建立一摸一样的表，但是这样做有个很大的弊端，不能将原表中的default value也一同迁移过来。 从目标表中获取所有列： create table table02 as select * from table01 where id = -1 从目标表中获取指定的列 create table emp_copy_2 (empno,sal) as select empno, sal from employees where deptno=10; 在中移动数据最快的方法是将其从一个表移动到别一个表，而不是需要进入操作将数据从一个表移动到别一个表， 有一些常见的方法可用于提高数据迁移的性能：
1．调整表的结构（删除索引和触发器） 2．在数据迁移期间禁用约束 3． 使用提示和选项来改进事务性能
第一个技巧是调整表的结构，它涉及禁用目标表上面的任何触发器或索引。 例如，如果在目标表上有行级触发器，则插入到表中的每一行都会执行触发器。 如果可能的话，最好在数据插入之前将目标表的触发器禁用； 如果应该为每个插入的行执行该触发器，则可以在批量插入完成以后再创建此触发器，而不应该在批量插入期间每插入一条数据重复执行触发器。
除了禁用触发器，应该在启动数据加载之前删除目标表上面的索引，然后在数据加载完毕后再重新创建索引。 如果索引留在表上，oracle将在插入每一行时动态管理索引。 注意：删除索引和禁用触发器可以解决大多数数和大型表与表之间数据迁移中遇到的性能问题。 除了禁用索引，还要考虑到表上的约束。如果源数据已经在数据库的表中，在将数据加载到目标表之前， 可以检查该数据了解它的相关约束（例如外键或check约束），一旦加载数据完成就可以重新启用这些约束。
如果以上选项无法提供适当的性能，就应该调查oracle为数据迁移调整引入的选项。这些选项包括如下：
1．插入命令的apppend提示 类似于direct path loader,appened提示将数据块加载到表中，从表的高水位线开始。 使用appened提示可以增加空间利用率。 2．nologging选项 如果正在执行create table as select 命令，使用nologing选项可避免在操作期间写入重做日志文件。 3．并行选项 并行查询使用多个进程来完成一个任务。对于create table as select命令， 可并行化create table 部分和查询部分。如果使用并行选项，则也应该使用nologing选项，否则并行操作将不得不由于串行化写入到联机重做日志文件而等待。 在使用这些高级选项之前，应该首先调查目标表的结构，确保已经避免前面提到的一些常见问题。
inset语句的强化学习 （1） 无条件多表插入
insert all into sal_history -- 插入数据到sal_history中 (employee_id, hire_date, salary) values (empid, hiredate, sal)
into mgr_history -- 插入数据到 mgr_history中 (employee_id, manager_id, salary) values (empid, mgr_id, sal)
select employee_id empid, hire_date hiredate, salary sal, manager_id mgr_id -- 从别的表中获取数据，这里仅仅是获取数据，各个字段类型要相互对应 from employees where employee_id > 200;
-- 创建一个表，这个表的字段，和数据都来源于另一张表，这时候就不需要自己定义自己表的字段了。oracle真是强大 create table t_test as select object_id, object_name, object_type from dba_objects where rownum <11; -- 创建一个表，从dba_objects里面取三个字段，十条记录 （2）有条件的多表插入
insert all when sal>=10000 then -- 工资大于10000的时候数据插入到sal_history 表中 into sal_history(employee_id, hire_date, salary) values(empid, hiredate, sal) when sal<10000 then into mgr_history(employee_id, manager_id, salary) values(empid, mgr_id, sal) select employee_id empid, hire_date hiredate, salary sal, manager_id mgr_id from employees where employee_id between 100 and 120;
备注： 当使用all关键字时, oracle会从上至下判断每一个条件,当条件满足时就执行后面的into语句，当一条记录满足多个条件的时候就会执行多次插入语句。 当使用first关键字时,oracle会从上至下判断每一个条件,当遇到第一个满足时就执行后面的into语句,同时终止判断，后面符合条件的语句也不会再执行。
（3）旋转insert（pivoting insert）
其实是无条件的insert all 的一种，但可以把行转换为列，oracle中收录为旋转insert create table sales_source_data ( employee_id number(6), week_id number(2), sales_mon number(8,2), sales_tue number(8,2), sales_wed number(8,2), sales_thur number(8,2), sales_fri number(8,2) );
insert into sales_source_data values (176,6,2000,3000,4000,5000,6000);
select * from sales_source_data
create table sales_info ( employee_id number(6), week number(2), sales number(8,2) );
select * from sales_source_data
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;
select * from sales_info;
通过子查询作为插入目标 insert into -- 这里直接插入到表employees中，不要再写表的名称了，不过这样的意义是什么，还不是要写一大串表的列名称么？ (select employee_id, last_name, email, hire_date, job_id, salary, department_id from employees where department_id = 50) values (99999, 'taylor', 'dtaylor', to_date('2017-03-01', 'yyyy-mm-dd'), 'st_clerk', 5000, 50);
create table s_emp ( id number(7) constraint s_emp_id_pk primary key, -- 每个字段的约束约束条件都应该取一个名称，以后可以直接修改约束条件，可以直接指定这个名称定位到指定的约束条件 last_name varchar2(25) constraint s_emp_last_name_nn not null, first_name varchar2(25),
userid varchar2(8) constraint s_emp_userid_nn not null constraint s_emp_userid_uk unique, -- 为一个字段添加两个约束条件，两个约束条件作用于同一个字段不用逗号隔开，用空格隔开
start_date date default sysdate, -- 默认值的设置 comments varchar2(25), manager_id number(7), title varchar2(25), dept_id number(7) constraint s_emp_dept_id_fk references departments(department_id), -- 外键的约束 salary number(11,2), commission_pct number(4,2) constraint s_emp_commission_pct_ck check(commission_pct in(10,12.5,15,17.5,20)) -- check函数范围的约束 );
创建这个标识后会创建三个键，两个索引 主键（primary）：id 唯一键（unique）：userid 外键（foreign）：dept_id oracle会为每一个唯一键创建一个唯一性索引：这里创建两个唯一性索引：id和userid
单独创建约束的语法： alter table cux_les_je_lines add constraint cux_les_je_lines_pk primary key(je_line_id);
外键约束类型： references: 表示列中的值必须在父表中存在 on delete cascade: 当父表记录删除的时候自动删除子表中的相应记录. on delete set null: 当父表记录删除的时候自动把子表中相应记录的值设为null
（2）使用子查询创建表，需要注意一些条件
再做一些数据迁移时候，会使用create table table_name2 as select * from table_name1 where id=-1的方式来年建立一摸一样的表，但是这样做有个很大的弊端，不能将原表中的default value也一同迁移过来。 从目标表中获取所有列： create table table02 as select * from table01 where id = -1 从目标表中获取指定的列 create table emp_copy_2 (empno,sal) as select empno, sal from employees where deptno=10; 在中移动数据最快的方法是将其从一个表移动到别一个表，而不是需要进入操作将数据从一个表移动到别一个表， 有一些常见的方法可用于提高数据迁移的性能：
1．调整表的结构（删除索引和触发器） 2．在数据迁移期间禁用约束 3． 使用提示和选项来改进事务性能
第一个技巧是调整表的结构，它涉及禁用目标表上面的任何触发器或索引。 例如，如果在目标表上有行级触发器，则插入到表中的每一行都会执行触发器。 如果可能的话，最好在数据插入之前将目标表的触发器禁用； 如果应该为每个插入的行执行该触发器，则可以在批量插入完成以后再创建此触发器，而不应该在批量插入期间每插入一条数据重复执行触发器。
除了禁用触发器，应该在启动数据加载之前删除目标表上面的索引，然后在数据加载完毕后再重新创建索引。 如果索引留在表上，oracle将在插入每一行时动态管理索引。 注意：删除索引和禁用触发器可以解决大多数数和大型表与表之间数据迁移中遇到的性能问题。 除了禁用索引，还要考虑到表上的约束。如果源数据已经在数据库的表中，在将数据加载到目标表之前， 可以检查该数据了解它的相关约束（例如外键或check约束），一旦加载数据完成就可以重新启用这些约束。
如果以上选项无法提供适当的性能，就应该调查oracle为数据迁移调整引入的选项。这些选项包括如下：
1．插入命令的apppend提示 类似于direct path loader,appened提示将数据块加载到表中，从表的高水位线开始。 使用appened提示可以增加空间利用率。 2．nologging选项 如果正在执行create table as select 命令，使用nologing选项可避免在操作期间写入重做日志文件。 3．并行选项 并行查询使用多个进程来完成一个任务。对于create table as select命令， 可并行化create table 部分和查询部分。如果使用并行选项，则也应该使用nologing选项，否则并行操作将不得不由于串行化写入到联机重做日志文件而等待。 在使用这些高级选项之前，应该首先调查目标表的结构，确保已经避免前面提到的一些常见问题。
inset语句的强化学习 （1） 无条件多表插入
insert all into sal_history -- 插入数据到sal_history中 (employee_id, hire_date, salary) values (empid, hiredate, sal)
into mgr_history -- 插入数据到 mgr_history中 (employee_id, manager_id, salary) values (empid, mgr_id, sal)
select employee_id empid, hire_date hiredate, salary sal, manager_id mgr_id -- 从别的表中获取数据，这里仅仅是获取数据，各个字段类型要相互对应 from employees where employee_id > 200;
-- 创建一个表，这个表的字段，和数据都来源于另一张表，这时候就不需要自己定义自己表的字段了。oracle真是强大 create table t_test as select object_id, object_name, object_type from dba_objects where rownum <11; -- 创建一个表，从dba_objects里面取三个字段，十条记录 （2）有条件的多表插入
insert all when sal>=10000 then -- 工资大于10000的时候数据插入到sal_history 表中 into sal_history(employee_id, hire_date, salary) values(empid, hiredate, sal) when sal<10000 then into mgr_history(employee_id, manager_id, salary) values(empid, mgr_id, sal) select employee_id empid, hire_date hiredate, salary sal, manager_id mgr_id from employees where employee_id between 100 and 120;
备注： 当使用all关键字时, oracle会从上至下判断每一个条件,当条件满足时就执行后面的into语句，当一条记录满足多个条件的时候就会执行多次插入语句。 当使用first关键字时,oracle会从上至下判断每一个条件,当遇到第一个满足时就执行后面的into语句,同时终止判断，后面符合条件的语句也不会再执行。
（3）旋转insert（pivoting insert）
其实是无条件的insert all 的一种，但可以把行转换为列，oracle中收录为旋转insert create table sales_source_data ( employee_id number(6), week_id number(2), sales_mon number(8,2), sales_tue number(8,2), sales_wed number(8,2), sales_thur number(8,2), sales_fri number(8,2) );
insert into sales_source_data values (176,6,2000,3000,4000,5000,6000);
select * from sales_source_data
create table sales_info ( employee_id number(6), week number(2), sales number(8,2) );
select * from sales_source_data
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;
select * from sales_info;
通过子查询作为插入目标 insert into -- 这里直接插入到表employees中，不要再写表的名称了，不过这样的意义是什么，还不是要写一大串表的列名称么？ (select employee_id, last_name, email, hire_date, job_id, salary, department_id from employees where department_id = 50) values (99999, 'taylor', 'dtaylor', to_date('2017-03-01', 'yyyy-mm-dd'), 'st_clerk', 5000, 50);