?1.标准Insert --单表单行插入
insert into dep (dep_id,dep_name) values(1,'技术部');
?2. 无条件 Insert all --多表多行插入
insert allINTO sal_history(emp_id,hire_date,salary) values (empid,hiredate,sal)INTO mgr_history(emp_id,manager_id,salary) values (empid,hiredate,sal)SELECT employee_id empid,hire_date hiredate,salary sal,manager_id mgrFROM employeesWHERE employee_id>200;
?3. 有条件的Insert
? ??? ?备注:
? ? ? a.当使用ALL关键字时,oracle会从上至下判断每一个条件,当条件满足时就执行后面的into语句
? ? ? ? ?在上面的例子中,如果ID=6 那么将会在z_test1中插入一条记录,同时也在z_test2中插入一条记录
? ? ? b.当使用FIRST关键字时,oracle会从上至下判断每一个条件,当遇到第一个满足时就执行后面的into语句,
? ? ? ? ?同时中断判断的条件判断,在上面的例子中,如果ID=6,仅仅会在z_test1中插入一条数据
Insert Allwhen id>5 then into z_test1(id, name) values(id,name)when id<>2 then into z_test2(id) values(id)else into z_test3 values(name)select id,name from z_test;
4. 旋转Insert (pivoting insert)
insert allinto 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_frifrom sales_source_data;
?5.常用查询保留小数位数的函数
select round(100/3,4) from dual;select trunc(100/3,4) from dual;
?