Oracle uses a single cursor to insert data. For example, oracle cursor

Oracle uses a single cursor to insert data. For example, oracle cursor

Original works, from "Deep Blue blog" blog, deep blue blog: http://blog.csdn.net/huangyanlong/article/details/47143731

Background

Recently, some friends asked me how to insert multiple pieces of data into the target table in a single way. To solve this problem, let's make a small experiment.

 

The communication process is as follows:

The following is an example of Using cursor to accomplish this goal.

(1) Prepare the experiment

First, create an experiment table as follows:

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 some experiment data as follows:

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);

Create an associated table emp_2 and another target table emp_3, as shown below:

Create table EMP_2 as select * from emp;

Create table EMP_3 as select * from emp where 1 = 2;

 

Delete data in the join table as follows:

Delete from EMP_2 where empno in (7771,7772, 7773 );

 

The Experiment Preparation is basically complete. Let's take a look at the data of each table using tools.

(2) Compile the Stored Procedure

The following describes how to use cursor to query a single data record and insert a single data record.

Create or replace procedure PD_CESHI IS v_ErrorCode NUMBER; -- error code v_ErrorText VARCHAR2 (4000); -- error message cursor c1 isSELECT. EMPNO,. ENAME,. JOB,. MGR,. HIREDATE,. SAL,. COMM,. deptno from emp a, EMP_2 B where. 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;/** record EXCEPTION data and error information **/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 during EXCEPTION handling **/exception when NO_DATA_FOUND then null; when others then v_ErrorCode: = SQLCODE; v_ErrorText: = SQLERRM;/** record abnormal data and error information **/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 a table that records error logs:

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) Start the experiment

The PL/SQL Developer tool is used to create a stored procedure. The following describes how to create a stored procedure:

After completion, let's take a look at the data in the target table, as shown below:

When we see the above result data, we have inserted the data into our target table in the form of traversal.

If this is not convincing, we need to further verify whether it is a single insert. Here we can have this idea. If this is a single insert, it means that if there is a problem with some data in the middle, it will not affect the insertion of other data, this is because it is inserted in units of a single entry ~~

To verify.

(4) handle simulated errors

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

Manufacturing problem data:

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

Modify data:

We use the PL/SQL Developer tool to change deptno with empno 7788 from 20 to 209999. In this case, when we insert it into the target table, an error will be thrown due to the length of the field.

Run the following stored procedure:

After the stored procedure is executed, we can foresee that 7788 of the data should be lost, and the other 13 data records should be inserted normally. Let's verify it as follows:

We can see that there is no empno 7788 data in the result.

Go to the error log table and check the following:

We can see that the error record has been recorded in the log table and throws a 1438 error (ORA-01438: value greater than the allowable precision specified for this column ).

In this way, we can also learn from the side that when a single piece of data is inserted, an error is thrown and other data insertion is not affected.

 

The experiment is complete.

 

Experiment.

 

**************************************** * ** Blue growth series ********************************* *******************

Original works, from the blog of "Deep Blue". You are welcome to reprint them. Please indicate the source (Http://blog.csdn.net/huangyanlong).

Blue growth note-chasing DBA (1): traveling on the road to Shandong

Blue growth notes-Chase DBA (2): Install! Install! Long-lost memories have aroused my new awareness of DBAs.

Blue growth note-chasing DBA (3): importing and exporting data on antiques becomes a problem

Blue growth note-chasing DBA (4): recalling the sorrow of teenagers, and exploring oracle Installation (10g and 11g in Linux)

Blue growth note-chasing DBA (5): Not talking about technology or business, annoying Application Systems

Blue growth note-chasing DBA (6): doing things and being human: Small technology, great human

Blue growth note-Chase DBA (7): Basic commands, foundation stone

Blue growth notes-chasing DBA (8): repicking SP reports and recalling oracle's STATSPACK Experiment

Blue growth note-chasing DBA (9): Chasing DBA, new planning, new departure

Blue growth note-chasing DBA (10): Flying knife defense, familiarity rather than expertise: Playing with middleware Websphere

Blue growth note-chasing DBA (11): It's easy to go home and wake up.

Blue growth notes-Chase DBA (12): seven days and seven gains of SQL

Blue growth note-chasing DBA (13): Coordinating hardware manufacturers, six stories: what you see as "servers, storage, switches ......"

Blue growth note-chasing DBA (14): An unforgettable "Cloud" end, started hadoop deployment

Blue growth note-chasing DBA (15): Who thinks FTP is "simple" and thinks it is a trigger

Blue growth note-chasing DBA (16): DBA also drank alcohol and was rejected

Blue growth note-Chase DBA (17): whether to share or consume, learn to grow in the Post-IOE Era

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

 

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

Original works, from the blog of "Deep Blue". You are welcome to reprint them. Please indicate the source (Http://blog.csdn.net/huangyanlong).

Football and oracle series (1): 32-way zhoudianbing, overall view of group A Brazil smon process of oracle32 process Alliance

Football and oracle series (2)

Football and oracle series (3): oracle process rankings, the World Cup round is about to fight!

Football and oracle series (4): from Brazil to Germany, think of the different RAC topology comparison! 

Football and oracle series (5): The directX library missing in the voda14 game is similar to the oracle rpm package!

Football and oracle series (6): Asian Cup with database creation-come on, Chinese Team

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

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.