文章存档
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2007-01-18 18:16:32
不怎么常用的insert功能：
---------------------------------------
insert into table-name select ...
---------------------------------------
insert into table-name values(col-values, ... default)
---------------------------------------
create table raises (emp_id number, sal number
constraint check_sal check(sal > 8000));
execute dbms_errlog.create_error_log('raises', 'errlog');
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 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
-----------------------------------------
the multitable insert statement looks like this:
insert all
into sales (prod_id, cust_id, time_id, amount)
values (product_id, customer_id, weekly_start_date, sales_sun)
into sales (prod_id, cust_id, time_id, amount)
values (product_id, customer_id, weekly_start_date 1, sales_mon)
into sales (prod_id, cust_id, time_id, amount)
values (product_id, customer_id, weekly_start_date 2, sales_tue)
into sales (prod_id, cust_id, time_id, amount)
values (product_id, customer_id, weekly_start_date 3, sales_wed)
into sales (prod_id, cust_id, time_id, amount)
values (product_id, customer_id, weekly_start_date 4, sales_thu)
into sales (prod_id, cust_id, time_id, amount)
values (product_id, customer_id, weekly_start_date 5, sales_fri)
into sales (prod_id, cust_id, time_id, amount)
values (product_id, customer_id, weekly_start_date 6, sales_sat)
select product_id, customer_id, weekly_start_date, sales_sun,
sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
from sales_input_table;
insert all
when order_total < 100000 then
into small_orders
when order_total > 100000 and order_total < 200000 then
into medium_orders
else
into large_orders
select order_id, order_total, sales_rep_id, customer_id
from orders;
insert all
when order_total < 1000000 then
into small_orders
when order_total > 1000000 and order_total < 2000000 then
into medium_orders
when order_total > 2000000 then
into large_orders
select order_id, order_total, sales_rep_id, customer_id
from orders;
insert first
when ottl < 100000 then
into small_orders
values(oid, ottl, sid, cid)
when ottl > 100000 and ottl < 200000 then
into medium_orders
values(oid, ottl, sid, cid)
when ottl > 290000 then
into special_orders
when ottl > 200000 then
into large_orders
values(oid, ottl, sid, cid)
select o.order_id oid, o.customer_id cid, o.order_total ottl,
o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
from orders o, customers c
where o.customer_id = c.customer_id;
ref:
using to_lob one can easily convert longs to lobs.
using the same conversion function one can convert long into clob and longraw to blob.
have a look into following example
sql>create table tlong(itemcd number(30),itemdesc long);
/
table created.
sql>create table tlob(itemcd number(30),itemdesc clob);
table created
now dump some values from some table into table tlong
sql>insert into tlong select icode,iname from invtab;
2000 rows created.
now try to insert into lob table from long table
sql>insert into tlob select itemcd,to_lob(itemdesc) from tlong
2000 rows created.
but in pl/sql you need to handle different way
normal select into insert will not work,but excute immediate will be the workaround in oracle 8.1.7
let's see how it can be done pl/sql
begin
insert into tlob select itemcd,to_lob(itemdesc) from tlong;
end;
/
the above pl/sql block works well with oralce 9i and oracle 10g but fails in oracle 8.1.7 with the following error
error at line 2:
ora-06550: line 2, column 33:
pls-00201: identifier 'to_lob' must be declared
ora-06550: line 2, column 1:
pl/sql: sql statement ignored
then you should use dynamic sql (execute immediate 'sql statement') as follows
begin
execute immediate 'insert into tlob select itemcd,to_lob(itemdesc) from tlong';
end;
/
---------------------------------------
insert into table-name select ...
---------------------------------------
insert into table-name values(col-values, ... default)
---------------------------------------
create table raises (emp_id number, sal number
constraint check_sal check(sal > 8000));
execute dbms_errlog.create_error_log('raises', 'errlog');
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 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
-----------------------------------------
the multitable insert statement looks like this:
insert all
into sales (prod_id, cust_id, time_id, amount)
values (product_id, customer_id, weekly_start_date, sales_sun)
into sales (prod_id, cust_id, time_id, amount)
values (product_id, customer_id, weekly_start_date 1, sales_mon)
into sales (prod_id, cust_id, time_id, amount)
values (product_id, customer_id, weekly_start_date 2, sales_tue)
into sales (prod_id, cust_id, time_id, amount)
values (product_id, customer_id, weekly_start_date 3, sales_wed)
into sales (prod_id, cust_id, time_id, amount)
values (product_id, customer_id, weekly_start_date 4, sales_thu)
into sales (prod_id, cust_id, time_id, amount)
values (product_id, customer_id, weekly_start_date 5, sales_fri)
into sales (prod_id, cust_id, time_id, amount)
values (product_id, customer_id, weekly_start_date 6, sales_sat)
select product_id, customer_id, weekly_start_date, sales_sun,
sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
from sales_input_table;
insert all
when order_total < 100000 then
into small_orders
when order_total > 100000 and order_total < 200000 then
into medium_orders
else
into large_orders
select order_id, order_total, sales_rep_id, customer_id
from orders;
insert all
when order_total < 1000000 then
into small_orders
when order_total > 1000000 and order_total < 2000000 then
into medium_orders
when order_total > 2000000 then
into large_orders
select order_id, order_total, sales_rep_id, customer_id
from orders;
insert first
when ottl < 100000 then
into small_orders
values(oid, ottl, sid, cid)
when ottl > 100000 and ottl < 200000 then
into medium_orders
values(oid, ottl, sid, cid)
when ottl > 290000 then
into special_orders
when ottl > 200000 then
into large_orders
values(oid, ottl, sid, cid)
select o.order_id oid, o.customer_id cid, o.order_total ottl,
o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
from orders o, customers c
where o.customer_id = c.customer_id;
ref:
using to_lob one can easily convert longs to lobs.
using the same conversion function one can convert long into clob and longraw to blob.
have a look into following example
sql>create table tlong(itemcd number(30),itemdesc long);
/
table created.
sql>create table tlob(itemcd number(30),itemdesc clob);
table created
now dump some values from some table into table tlong
sql>insert into tlong select icode,iname from invtab;
2000 rows created.
now try to insert into lob table from long table
sql>insert into tlob select itemcd,to_lob(itemdesc) from tlong
2000 rows created.
but in pl/sql you need to handle different way
normal select into insert will not work,but excute immediate will be the workaround in oracle 8.1.7
let's see how it can be done pl/sql
begin
insert into tlob select itemcd,to_lob(itemdesc) from tlong;
end;
/
the above pl/sql block works well with oralce 9i and oracle 10g but fails in oracle 8.1.7 with the following error
error at line 2:
ora-06550: line 2, column 33:
pls-00201: identifier 'to_lob' must be declared
ora-06550: line 2, column 1:
pl/sql: sql statement ignored
then you should use dynamic sql (execute immediate 'sql statement') as follows
begin
execute immediate 'insert into tlob select itemcd,to_lob(itemdesc) from tlong';
end;
/
给主人留下些什么吧！~~