oracle利用游标单条插入数据举例

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/huangyanlong/article/details/47143731

原创作品,出自 “深蓝的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 is

SELECT 

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):伴随建库的亚洲杯——加油中国队

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

展开阅读全文

mysql 游标插入数据

01-13

写一个触发器,每次更新jz_contect表的时候,会同时更新另一张表jz_stream,在触发器中调用一个存储过程,用来插入数据,这个存储过程单独测试是没有问题的,我的触发器如下:rn[code=Java]rnDECLARE no_more_gbmes int default 0;rn DECLARE gbmes_csr CURSOR FOR SELECT a.g_id from jz_gbmes a where a.ctid = new.ctid;rn DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_gbmes=1; rn delete from jz_stream where g_id in (select g_id from jz_gbmes where ctid = new.ctid);rnOPEN gbmes_csr; rn REPEAT rn FETCH gbmes_csr INTO gid;rn CALL businessRecord(gid);rn UNTIL no_more_gbmes rn END REPEAT; rn CLOSE gbmes_csr;rn[/code]rn存储过程如下:rn[code=SQL]rnDECLARE sfcd timestamp;rn DECLARE tps int;rn DECLARE gid int;rn DECLARE cend timestamp;rn DECLARE stcaution timestamp;rn DECLARE endcaution timestamp;rn DECLARE orgid int;rn DECLARE ye int;rn DECLARE yue int;rn DECLARE dd int;rn DECLARE caution timestamp;rn DECLARE efcd timestamp;rn SELECT a.remindtype,a.sdate,a.edate,a.bstdate,a.g_id,b.contectend,a.org_id into tps,stcaution,endcaution,caution,gid,cend,orgid from jz_gbmes a left JOIN jz_contect brn on a.ctid = b.ctid where a.g_id = gd;rn if(tps = 0) thenrn set ye = Year(caution);rn set yue = month(endcaution);rn set dd = dayofmonth(endcaution);rn set efcd = STR_TO_DATE(concat(ye,'/',yue,'/',dd),'%Y/%m/%d');rn set sfcd = STR_TO_DATE(concat(ye,'/',month(stcaution),'/',dayofmonth(stcaution)),'%Y/%m/%d');rn if(caution > efcd) thenrn set sfcd = DATE_ADD(sfcd,interval 1 year);rn set efcd = DATE_ADD(efcd,interval 1 year);rn end if;rn while(sfcd < cend) dorn insert into jz_stream(g_id,results,sdate,edate,org_id) values(gid,0,sfcd,efcd,orgid);rn set sfcd = DATE_ADD(sfcd,interval 1 year);rn set efcd = DATE_ADD(efcd,interval 1 year);rn end while;rn [/code]rn开始的数据库数据如下:rn[code=SQL]rnmysql> select * from jz_stream;rn+-------+------------+------+---------+----------+---------+--------------------rn-+---------------------+---------------------+--------+rn| op_id | account_id | g_id | results | comments | op_time | autotimern | sdate | edate | org_id |rn+-------+------------+------+---------+----------+---------+--------------------rn-+---------------------+---------------------+--------+rn| 35 | NULL | 14 | 0 | NULL | NULL | 2009-01-13 17:09:55rn | 2008-01-01 00:00:00 | 2008-01-19 00:00:00 | 9 |rn+-------+------------+------+---------+----------+---------+--------------------rn-+---------------------+---------------------+--------+rn[/code]rn调用触发器后数据库如下:rn[code=SQL]rnmysql> select * from jz_stream;rn+-------+------------+------+---------+----------+---------+--------------------rn-+---------------------+---------------------+--------+rn| op_id | account_id | g_id | results | comments | op_time | autotimern | sdate | edate | org_id |rn+-------+------------+------+---------+----------+---------+--------------------rn-+---------------------+---------------------+--------+rn| 36 | NULL | 14 | 0 | NULL | NULL | 2009-01-13 17:13:38rn | 2008-01-01 00:00:00 | 2008-01-19 00:00:00 | 9 |rn| 37 | NULL | 14 | 0 | NULL | NULL | 2009-01-13 17:13:38rn | 2009-01-01 00:00:00 | 2009-01-19 00:00:00 | 9 |rn| 38 | NULL | 14 | 0 | NULL | NULL | 2009-01-13 17:13:38rn | 2008-01-01 00:00:00 | 2008-01-19 00:00:00 | 9 |rn| 39 | NULL | 14 | 0 | NULL | NULL | 2009-01-13 17:13:38rn | 2009-01-01 00:00:00 | 2009-01-19 00:00:00 | 9 |rn+-------+------------+------+---------+----------+---------+--------------------rnrn[/code]rn虽然是删掉以前的了,但是重复了上一次的内容。是什么原因呢? 论坛

没有更多推荐了,返回首页