oracle利用遊標單條插入資料舉例，oracle遊標

oracle利用遊標單條插入資料舉例，oracle遊標

原創作品，出自 “深藍的blog” 部落格，深藍的blog：http://blog.csdn.net/huangyanlong/article/details/47143731

背景

近日有哥們問我如何?多條資料以單條的方式插入到目標表裡，為解決這個問題，下面來做個小實驗。

 

交流過程如下：

下面，我們舉例用cursor來完成這個目標。

（1）、實驗準備

首先，建立一個實驗表，如下：

createtable EMP

(

 empno   NUMBER(4)  not  null,

 ename   VARCHAR2(1000),

 job     VARCHAR2(9),

 mgr     NUMBER(4),

 hiredate  DATE,

 sal         NUMBER(7,2),

 comm    NUMBER(7,2),

 deptno    NUMBER(6)

);

 

插入一些實驗資料，如下：

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800.00, null, 209999);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600.00, 300.00, 30);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250.00, 500.00, 30);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975.00, null, 20);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250.00, 1400.00, 30);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850.00, null, 30);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450.00, null, 10);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000.00, null, 20);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000.00, null, 10);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500.00, 0.00, 30);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100.00, null, 20);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950.00, null, 30);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000.00, null, 20);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300.00, null, 10);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7771, 'hyl', 'dba', null, to_date('19-04-1987', 'dd-mm-yyyy'), 8000.00, null, 10);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7772, 'hyl', 'dba', null, to_date('19-04-1987', 'dd-mm-yyyy'), 8000.00, null, 10);insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values (7773, 'hyl', 'dba', null, to_date('19-04-1987', 'dd-mm-yyyy'), 8000.00, null, 10);

建立一個關聯表emp_2、再建立一個目標表emp_3，如下：

CREATE  TABLE  EMP_2   AS  SELECT   *   FROM   EMP;

CREATE  TABLE   EMP_3  AS  SELECT   *   FROM   EMP  WHERE  1=2;

 

刪除一些關聯表裡的資料，如下：

DELETE   FROM  EMP_2  WHERE   EMPNO  IN (7771,7772,7773);

 

實驗準備基本上完成了，利用工具我們看一下每張表的資料情況。

（2）、編寫預存程序

下面我們利用cursor來實現對資料單條查詢，然後單條插入的目的。

CREATE OR REPLACE PROCEDURE PD_CESHI IS  v_ErrorCode NUMBER;           -- 錯誤碼  v_ErrorText VARCHAR2(4000);    -- 錯誤資訊cursor c1 isSELECT a.EMPNO,a.ENAME,a.JOB,a.MGR,a.HIREDATE,a.SAL,a.COMM,a.DEPTNO  FROM EMP a,EMP_2 b where a.empno=b.empno; BEGIN    For te in c1 loop        begin           insert into EMP_3(          EMPNO,          ENAME,          JOB,          MGR,          HIREDATE,          SAL,          COMM,          DEPTNO            )            values(            te.EMPNO,            te.ENAME,            te.JOB,            te.MGR,            te.HIREDATE,            te.SAL,            te.COMM,            te.DEPTNO       );       EXCEPTION         WHEN   NO_DATA_FOUND THEN           NULL;         WHEN OTHERS THEN           v_ErrorCode := SQLCODE;           v_ErrorText := SQLERRM;           /** 記錄異常資料及錯誤資訊 **/           insert   into   error_log(ID,TABLE_NAME,ERROR_ID,ERROR_CODE,ERROR_TEXT,CREATE_DATE)    values(sys_guid(),'EMP_2',te.EMPNO,v_ErrorCode,v_ErrorText,sysdate);       end;    End   loop;   commit;   /** 異常處理 過程異常**/   EXCEPTION     WHEN    NO_DATA_FOUND    THEN        NULL;     WHEN   OTHERS   THEN      v_ErrorCode := SQLCODE;      v_ErrorText := SQLERRM;      /** 記錄異常資料及錯誤資訊 **/      insert   into   error_log (ID,TABLE_NAME,ERROR_ID,ERROR_CODE,ERROR_TEXT,CREATE_DATE)   values(sys_guid(),'EMP_OBJECT','',v_ErrorCode,v_ErrorText,sysdate);END PD_CESHI;

建立一個記錄錯誤記錄檔的表：

create   table   ERROR_LOG(  id                       VARCHAR2(32),  table_name    VARCHAR2(100),  error_id            VARCHAR2(100),  error_code      VARCHAR2(100),  error_text        VARCHAR2(4000),  create_date    DATE   default   sysdate);

（3）、開始實驗

利用PL/SQL Developer工具完成預存程序的建立，我們下面來執行這個預存程序，如下：

完成後，我們來看一下目標表裡的資料，如下：

        看到以上的結果資料，我們已經把資料以遍曆的形式插入到了我們的目標表裡了。

        如果這麼說不具說服力，我們需要再進一步驗證一下是否是單條插入的話。這裡我們可以有這麼個思路，如果這個是實現單條插入的，那麼也就意味著如果中途有某條資料出現問題，將不會影響到其餘資料的插入，因為這是以單條為單位插入的嘛~~

來驗證一下。

（4）、類比錯誤處理

TRUNCATE   TABLE  EMP_3;SELECT * FROM EMP_3;<span style="font-size:14px;"></span>

製造問題資料：

alter   table   EMP   modify   deptno   NUMBER(6);<span style="font-size:14px;"></span> 

修改資料：

        我們利用PL/SQL Developer工具把empno為7788的deptno由20修改為209999。這樣的話，當我們插入到目標表中時，就會由於欄位長度拋出一個錯誤來。

我們執行一下預存程序，如下：

預存程序執行完畢後，我們可以預見到7788的那條資料應該沒有了，而其它的13條資料應該是可以正常插入的。我們來驗證一下，如下：

可以看到結果中沒有empno為7788的資料。

到錯誤記錄日誌表裡看一下，如下：

        我們可以看到，錯誤記錄已經被記錄到日誌表裡，拋出的1438錯誤（ORA-01438: 值大於為此列指定的允許精度）。

通過這種方式，我們從側面也可以瞭解到，單條資料插入時，錯誤被拋出，不影響其它的資料插入。

 

實驗完畢。

 

小實驗，隨手記之。

 

*******************************************藍的成長記系列****************************************************

原創作品，出自 “深藍的blog” 部落格，歡迎轉載，轉載時請務必註明出處（http://blog.csdn.net/huangyanlong）。

藍的成長記——追逐DBA（1）：奔波於路上，挺進山東

藍的成長記——追逐DBA（2）：安裝！安裝！久違的記憶，引起我對DBA的重新認知

藍的成長記——追逐DBA（3）：古董上操作，資料匯入匯出成了問題

藍的成長記——追逐DBA（4）：追憶少年情愁，再探oracle安裝（Linux下10g、11g）

藍的成長記——追逐DBA（5）：不談技術談業務，惱人的應用系統

藍的成長記——追逐DBA（6）： 做事與做人：小技術，大為人

藍的成長記——追逐DBA（7）：基礎命令，地基之石

藍的成長記——追逐DBA（8）：重拾SP報告，回憶oracle的STATSPACK實驗

藍的成長記——追逐DBA（9）：國慶漸去，追逐DBA，新規劃，新啟程

藍的成長記——追逐DBA（10）：飛刀防身，熟絡而非專長：擺弄中介軟體Websphere

藍的成長記——追逐DBA（11）：回家後的安逸，暈暈乎乎醒了過來

藍的成長記——追逐DBA（12）：七天七收穫的SQL

藍的成長記——追逐DBA（13）：協調硬體廠商，六個故事：所見所感的“伺服器、儲存、交換器......”

藍的成長記——追逐DBA（14）：難忘的“雲”端，起步的hadoop部署

藍的成長記——追逐DBA（15）：以為FTP很“簡單”，誰成想一波三折

藍的成長記——追逐DBA（16）：DBA也喝酒，被捭闔了

藍的成長記——追逐DBA（17）：是分享，還是消費，在後IOE時代學會成長

******************************************************************************************************************

 

********************************************足球與oracle系列*************************************************

原創作品，出自 “深藍的blog” 部落格，歡迎轉載，轉載時請務必註明出處（http://blog.csdn.net/huangyanlong）。

足球與oracle系列（1）：32路諸侯點兵，oracle32進程聯盟 之A組巴西SMON進程的大局觀

足球與oracle系列（2）：巴西揭幕戰預演，oracle體繫結構雜談

足球與oracle系列（3）：oracle進程排名，世界盃次回合即將戰罷！

足球與oracle系列（4）：從巴西慘敗於德國，想到，差異的RAC拓撲對比！ 

足球與oracle系列（5）：fifa14遊戲缺失的directX庫類比於oracle的rpm包！

足球與oracle系列（6）：伴隨建庫的亞洲杯——加油中國隊

******************************************************************************************************************

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。