Oracle INSERT 語(yǔ)句
方法1
我估計(jì)有點(diǎn) SQL 基礎(chǔ)的人都會(huì)寫(xiě) INSERT 語(yǔ)句。下面是 SQL 標(biāo)準(zhǔn)寫(xiě)法。
INSERT INTO employees (employee_id, name) VALUES (1, 'Zhangsan');INSERT INTO employees VALUES (1, 'Shangbo');
方法2
其實(shí)， Oracle 還支持下面的寫(xiě)法，作用和上面的語(yǔ)句完全相同。
INSERT INTO (SELECT employee_id, name FROM employees) VALUES (2, 'Lisi');
方法3
此外，同其他數(shù)據(jù)庫(kù)一樣，Oracle 也支持下面這種寫(xiě)法。
INSERT INTO employeesSELECT 3, 'Wangwu' FROM DUAL;
方法4
下面這種寫(xiě)法可以實(shí)現(xiàn)列轉(zhuǎn)行，如我們有下面的表存儲(chǔ)原始數(shù)據(jù)，原始數(shù)據(jù)可能從文件中來(lái)。
create table sales_input_table (prod_id number(9,0),amt_mon number(9,6),amt_tue number(9,6),amt_wed number(9,6),amt_thu number(9,6),amt_fri number(9,6));insert into sales_input_table values (1, 100.0, 200.0, 300.0, 400.0, 500.0);
下面我們通過(guò)一個(gè) SQL 把上面的數(shù)據(jù)插入到下面的表中實(shí)現(xiàn)列轉(zhuǎn)行。
CREATE TABLE sales (prod_id number(9,0),time_id date,amount number(9,0));INSERT ALLINTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE, amt_mon)INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 1, amt_tue)INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 2, amt_wed)INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 3, amt_thu)INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 4, amt_fri)SELECT prod_id, amt_mon, amt_tue, amt_wed, amt_thu, amt_fri FROM sales_input_table;
方法5
下面這種寫(xiě)法可以幫我們一次性把一個(gè)表中的數(shù)據(jù)倒入到多個(gè)表中，否則我們必須寫(xiě)多條 SQL 實(shí)現(xiàn)同樣的功能。
INSERT ALLWHEN order_total <= 100000 THENINTO small_ordersWHEN order_total > 100000 AND order_total <= 200000 THENINTO medium_ordersWHEN order_total = 500000 THENINTO special_ordersWHEN order_total > 200000 THENINTO large_ordersELSEINTO large_ordersSELECT order_id, order_total, sales_rep_id, customer_id FROM orders;
注意，當(dāng) order_total 大于 200000 時(shí)，orders 會(huì)被插入到 large_orders 和 special_orders 中。這可能不是你想要的結(jié)果，如果你只想讓 orders 插入到 special_orders 表中，你只需要把 ALL 替換成 FIRST， 如下。
INSERT FIRSTWHEN order_total <= 100000 THENINTO small_ordersWHEN order_total > 100000 AND order_total <= 200000 THENINTO medium_ordersWHEN order_total = 500000 THENINTO special_ordersWHEN order_total > 200000 THENINTO large_ordersELSEINTO large_ordersSELECT order_id, order_total, sales_rep_id, customer_id FROM orders;
從Oracle數(shù)據(jù)庫(kù)中讀取數(shù)據(jù)，自動(dòng)生成INSERT語(yǔ)句
創(chuàng)建表
-- Create tablecreate table TB_ACCIDENT_TYPE( ID NUMBER(20) not null, NAME VARCHAR2(50), PATH VARCHAR2(20), PARENTPATH VARCHAR2(20), URL VARCHAR2(20), TYPE VARCHAR2(2), DESCR VARCHAR2(50))
顯示表中的數(shù)據(jù)
select 'INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES(' || '''' || ID ||'''' || ','|| '''' || NAME || '''' || ','|| '''' || PATH || '''' || ','|| '''' || PARENTPATH || '''' || ','|| '''' || URL || '''' || ','|| '''' || TYPE || '''' || ','|| '''' || DESCR || '''' || ');' From tb_accident_type order by ID顯示結(jié)果
INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('1','事故類型關(guān)聯(lián) ','1','0','','0','');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('2','危險(xiǎn)源類型關(guān)聯(lián)','2','0','','','');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('3','危險(xiǎn)品類型關(guān)聯(lián)','3','0','','','');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('4','生產(chǎn)企業(yè)類型關(guān)聯(lián)','4','0','','','');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('5','區(qū)域關(guān)聯(lián)','5','0','','0','');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('568','物體打擊','1.1','1','','1','物體打擊');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('569','車輛傷害','1.2','1','','1','車輛傷害');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('570','機(jī)器傷害','1.3','1','','1','機(jī)器傷害');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('571','起重傷害','1.4','1','','1','起重傷害');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('572','觸電','1.5','1','','1','觸電');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('573','淹溺','1.6','1','','1','淹溺');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('574','灼燙','1.7','1','','1','灼燙');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('575','火災(zāi)','1.8','1','','1','火災(zāi)');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('576','高處墜落','1.9','1','','1','高處墜落');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('577','坍塌','1.10','1','','1','坍塌');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('578','冒頂片幫','1.11','1','','1','冒頂片幫');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('580','透水','1.12','1','','1','透水');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('581','波炮','1.13','1','','1','波炮');INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('582','火藥爆炸','1.14','1','','1','火藥爆炸')INSERT INTO tb_accident_type (ID,NAME,PATH,PARENTPATH,URL,TYPE,DESCR) VALUES('583','瓦斯爆炸','1.15','1','','1','瓦斯爆炸');總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)武林網(wǎng)的支持。如果你想了解更多相關(guān)內(nèi)容請(qǐng)查看下面相關(guān)鏈接
新聞熱點(diǎn)
疑難解答
圖片精選
