extension to dml in oracle

1 over of multitable insert statements
 1)the insert...select statement can be userd to insert row into multiple table as part of a single dml statement.
 2) multitable insert statements can be used in data warehousing systems to transfer data from one or more operational sources to source to a set of target table.
 3) they providde significant performance improvement over
    single dml versuls multiple insert...select statement
    single dml versus a proceedduree to do mutiple inserts using if ,,, then syntax.
2
  unconditional insert
  insert all
    into sal_history values (EMPID,HIREDATE,SAL)
    into mgr_history values (EMPID,MGR,SAL)
  select employee_id EMPID,hire_date JIREDATE,
         salary SAL,manager_id MGR
  from employees
  where employee_id>200;
3 Conditional insert all
  insert all
   when sal>1000 then
     into sal_history values(empid,hiredate,sal)
   when mgr>200 then
     into mgr_history values(empid,mgr,sal)
   select emp_id empid,hire_date hiredate,salary sal,manager_id mgr,
   from employees
   where employee_id>200;
4 Conditional first insert
  insert first
    when sal >25000  then
      into special_sal values(deptid,sal)
    when hiredate like ('%00%') then
      into hiredate_history_00 values(deptid,hiredate)
    when hiredate like ('%99%') then
      insert hiredate_history_99 values(ddeptid,hiredate)
    else
      into hiredate_history values(deptid,hiredate)
    select ddepartmeent_id deptid,sum(salary) sal,
     max(hire_date) hiredate
    from employees
    group by department_id;
5 Pivoting insert
 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,weekid,sales_mon,sales_tue,sales_wed,sales_thur,sales_fri
  from sales_source_data;
6 create index with create table statement
  create table new_emp
  (employee_id number(6)
         primary key using index
         (create index emp_id_idx on new_emp(employee_id)),
   first_name varchar2(20),
   last_name varchar2(25)
  )

posted on 2006-10-11 14:41 康文 阅读(194) 评论(0)  编辑  收藏 所属分类: 数据库


只有注册用户登录后才能发表评论。


网站导航:
 
<2006年10月>
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234

导航

统计

常用链接

留言簿(1)

随笔分类

随笔档案

文章档案

搜索

最新评论

阅读排行榜

评论排行榜