Oracle中特殊的INSERT语句--转载

sajz1s01sw

　　原址 http://blog.iyunv.com/uid-10257388-id-2967180.html
不怎么常用的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: http://www.dba-oracle.com/oracle_news/2005_5_9_converting_long_lob_data_types.htm

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;
/
<!-- <div class="Blog_con3_1">管理员在2009年8月13日编辑了该文章文章。</div> -->