目錄
前言:
問題現象:
問題分析:
解決方法:
1、insert into?... union all句式
2、insert all into ...select 1 from dual句式
總結:
前言:
????????最近項目中使用到了Oracle數據庫,由于Oracle數據庫我已經好幾年沒用過了,最近幾年用的幾乎都是MySQL數據庫。
? ? ? ? 在使用Oracle數據庫編寫SQL語句的過程中,發現了有一些函數、語法、句式是不太熟悉的,或者說是和MySQL有些區別的;因此最近我打算復習一下Oracle,同時在接下來的一段時間內也會不定期更新一些關于Oracle數據庫的學習日記(文章),結合我個人的學習心得,也算是溫故而知新吧,希望能幫助到有需要的同伴。
問題現象:
????????今天在學習中遇到一個問題:
? ? ? ? 如何在Oracle中,使用單個inert語句實現插入多行記錄呢?
問題分析:
? ? ? ?由于最近幾年都是使用MySQL數據庫比較多,因此一開始遇到這個問題的時候,我其實是直接使用了MySQL中語法來解決這個問題的,下面將使用Oracle最經典的鏈接工具【PLSQL Developer】來進行測試,過程如下:
? ? ? ? 先簡單建表:
-- 建表語句
CREATE TABLE BIZ_BREED_INFO (breed_id INTEGER PRIMARY KEY,biz_breed VARCHAR2(255),memo VARCHAR2(255)
);COMMENT ON TABLE BIZ_BREED_INFO IS '業務品種表';
COMMENT ON COLUMN BIZ_BREED_INFO.breed_id IS '品種id';
COMMENT ON COLUMN BIZ_BREED_INFO.biz_breed IS '業務品種名稱';
COMMENT ON COLUMN BIZ_BREED_INFO.memo IS '備注';
? ? ? ? 然后使用一個insert語句插入多行記錄:
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES
(1, '稻谷', '用于糧食生產和飼料'),
(2, '小麥', '用于面粉生產和飼料'),
(3, '玉米', '用于飼料、淀粉和乙醇生產'),
(4, '大豆', '用于食用油和蛋白質飼料'),
(5, '棉花', '用于紡織工業'),
(6, '花生', '用于食用油和蛋白質'),
(7, '馬鈴薯', '用于食品和工業淀粉'),
(8, '甘蔗', '用于制糖和乙醇生產'),
(9, '蘋果', '用于鮮食和果汁'),
(10, '橙子', '用于鮮食和果汁'),
(11, '茶葉', '用于飲品和出口'),
(12, '蔬菜', '用于鮮食和加工'),
(13, '奶牛', '用于牛奶生產'),
(14, '豬', '用于肉類生產'),
(15, '雞', '用于蛋和肉類生產'),
(16, '魚類', '用于水產養殖'),
(17, '蝦類', '用于水產養殖'),
(18, '蟹類', '用于水產養殖'),
(19, '蜂產品', '用于蜂蜜、蜂王漿等'),
(20, '煙草', '用于卷煙和其他煙草制品');
????????沒想到一執行就報錯:
????????查了一下發現,原來是Oracle不支持這樣的寫法。
? ? ? ? 雖然我們都知道可以執行多個inert語句來實現多行數據記錄的插入,如下:
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (1, '稻谷', '用于糧食生產和飼料');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (2, '小麥', '用于面粉生產和飼料');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (3, '玉米', '用于飼料、淀粉和乙醇生產');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (4, '大豆', '用于食用油和蛋白質飼料');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (5, '棉花', '用于紡織工業');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (6, '花生', '用于食用油和蛋白質');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (7, '馬鈴薯', '用于食品和工業淀粉');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (8, '甘蔗', '用于制糖和乙醇生產');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (9, '蘋果', '用于鮮食和果汁');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (10, '橙子', '用于鮮食和果汁');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (11, '茶葉', '用于飲品和出口');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (12, '蔬菜', '用于鮮食和加工');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (13, '奶牛', '用于牛奶生產');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (14, '豬', '用于肉類生產');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (15, '雞', '用于蛋和肉類生產');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (16, '魚類', '用于水產養殖');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (17, '蝦類', '用于水產養殖');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (18, '蟹類', '用于水產養殖');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (19, '蜂產品', '用于蜂蜜、蜂王漿等');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (20, '煙草', '用于卷煙和其他煙草制品');
? ? ? ? 但是,難道Oracle并不支持使用單個inert語句實現插入多行記錄嗎?
? ? ? ? 抱著這樣的疑問,我再次搜集了相關資料,才發現其實方法還是有的,只是sql寫起來會顯得很冗余。
解決方法:
1、insert into?... union all句式
????????句式規則如下:
INSERT INTO 表名(字段列表)?
select 字段值列表 from dual?
union all
select 字段值列表 from dual?
union all......
????????SQL示例如下:
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO)
SELECT 1, '稻谷', '用于糧食生產和飼料' FROM DUAL UNION ALL
SELECT 2, '小麥', '用于面粉生產和飼料' FROM DUAL UNION ALL
SELECT 3, '玉米', '用于飼料、淀粉和乙醇生產' FROM DUAL UNION ALL
SELECT 4, '大豆', '用于食用油和蛋白質飼料' FROM DUAL UNION ALL
SELECT 5, '棉花', '用于紡織工業' FROM DUAL UNION ALL
SELECT 6, '花生', '用于食用油和蛋白質' FROM DUAL UNION ALL
SELECT 7, '馬鈴薯', '用于食品和工業淀粉' FROM DUAL UNION ALL
SELECT 8, '甘蔗', '用于制糖和乙醇生產' FROM DUAL UNION ALL
SELECT 9, '蘋果', '用于鮮食和果汁' FROM DUAL UNION ALL
SELECT 10, '橙子', '用于鮮食和果汁' FROM DUAL UNION ALL
SELECT 11, '茶葉', '用于飲品和出口' FROM DUAL UNION ALL
SELECT 12, '蔬菜', '用于鮮食和加工' FROM DUAL UNION ALL
SELECT 13, '奶牛', '用于牛奶生產' FROM DUAL UNION ALL
SELECT 14, '豬', '用于肉類生產' FROM DUAL UNION ALL
SELECT 15, '雞', '用于蛋和肉類生產' FROM DUAL UNION ALL
SELECT 16, '魚類', '用于水產養殖' FROM DUAL UNION ALL
SELECT 17, '蝦類', '用于水產養殖' FROM DUAL UNION ALL
SELECT 18, '蟹類', '用于水產養殖' FROM DUAL UNION ALL
SELECT 19, '蜂產品', '用于蜂蜜、蜂王漿等' FROM DUAL UNION ALL
SELECT 20, '煙草', '用于卷煙和其他煙草制品' FROM DUAL;
????????執行成功:
????????執行成功后記得提交事務:
表數據如下:
2、insert all into ...select 1 from dual句式
? ? ? ? ? ? 句式規則如下:
INSERT ALL
INTO 表名(字段列表) VALUES (字段值列表)
INTO 表名(字段列表) VALUES (字段值列表)
? ? ...
SELECT 1 FROM DUAL;
? ? ? ? 由于在測試完第1種方法后,表中已經插入了20行數據,所以在測試第2種方法之前,需要先刪除掉,執行刪除sql:
? ? ? ? 選擇是,然后提交事務:
????????再次查詢表數據:
? ? ? ? 然后使用第2種句式插入數據,SQL示例如下:
INSERT ALLINTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (1, '稻谷', '用于糧食生產和飼料')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (2, '小麥', '用于面粉生產和飼料')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (3, '玉米', '用于飼料、淀粉和乙醇生產')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (4, '大豆', '用于食用油和蛋白質飼料')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (5, '棉花', '用于紡織工業')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (6, '花生', '用于食用油和蛋白質')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (7, '馬鈴薯', '用于食品和工業淀粉')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (8, '甘蔗', '用于制糖和乙醇生產')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (9, '蘋果', '用于鮮食和果汁')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (10, '橙子', '用于鮮食和果汁')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (11, '茶葉', '用于飲品和出口')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (12, '蔬菜', '用于鮮食和加工')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (13, '奶牛', '用于牛奶生產')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (14, '豬', '用于肉類生產')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (15, '雞', '用于蛋和肉類生產')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (16, '魚類', '用于水產養殖')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (17, '蝦類', '用于水產養殖')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (18, '蟹類', '用于水產養殖')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (19, '蜂產品', '用于蜂蜜、蜂王漿等')INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (20, '煙草', '用于卷煙和其他煙草制品')
SELECT 1 FROM DUAL;
????????執行成功:
????????提交事務:
????????表數據如下:
總結:
? ? ? ? 可以看到在Oracle中,確實是可以通過單個inert語句實現插入多行記錄的!!!
? ? ? ? 但是缺點也很明顯,就是和MySQL的語句相比,SQL代碼???很冗余。尤其是第2種句式,書寫起來的代碼,甚至比直接寫20個insert語句還要多。當然第一種句式書寫起來的代碼和直接寫20個insert語句其實也沒差多少。
????????相信大家都已經了解并學會了在Oracle中使用單個inert語句實現插入多行記錄。