Oracle使用虚拟表dual一次插入多条记录

最新推荐文章于 2022-11-28 10:28:45 发布
最新推荐文章于 2022-11-28 10:28:45 发布
阅读量209 收藏
点赞数
分类专栏： oracle 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/intrepid2008/article/details/84407359
版权

BEGIN
           generate_tmpsht;
           execute immediate
                'insert into SHTSUPPLIERRATE (supplierid,supplierrate,supplierdialcode)
                select :var_TMPSupplier1ID,:var_TMPSupplier1Rate,:var_TMPSupplier1DialCode  from dual
                union all select :var_TMPSupplier2ID,:var_TMPSupplier2Rate,:var_TMPSupplier2DialCode from dual
                union all select :var_TMPSupplier3ID,:var_TMPSupplier3Rate,:var_TMPSupplier3DialCode from dual
                union all select :var_TMPSupplier4ID,:var_TMPSupplier4Rate,:var_TMPSupplier4DialCode from dual
                union all select :var_TMPSupplier5ID,:var_TMPSupplier5Rate,:var_TMPSupplier5DialCode from dual
                union all select :var_TMPSupplier6ID,:var_TMPSupplier6Rate,:var_TMPSupplier6DialCode from dual
                union all select :var_TMPSupplier7ID,:var_TMPSupplier7Rate,:var_TMPSupplier7DialCode from dual
                union all select :var_TMPSupplier8ID,:var_TMPSupplier8Rate,:var_TMPSupplier8DialCode from dual
                union all select :var_TMPSupplier9ID,:var_TMPSupplier9Rate,:var_TMPSupplier9DialCode from dual
                union all select :var_TMPSupplier10ID,:var_TMPSupplier10Rate,:var_TMPSupplier10DialCode from dual
                union all select :var_TMPSupplier11ID,:var_TMPSupplier11Rate,:var_TMPSupplier11DialCode from dual
               union all select :var_TMPSupplier12ID,:var_TMPSupplier12Rate,:var_TMPSupplier12DialCode from dual
                union all select :var_TMPSupplier13ID,:var_TMPSupplier13Rate,:var_TMPSupplier13DialCode from dual
                union all select :var_TMPSupplier14ID,:var_TMPSupplier14Rate,:var_TMPSupplier14DialCode from dual
                union all select :var_TMPSupplier15ID,:var_TMPSupplier15Rate,:var_TMPSupplier15DialCode from dual
                union all select :var_TMPSupplier16ID,:var_TMPSupplier16Rate,:var_TMPSupplier16DialCode from dual
                union all select :var_TMPSupplier17ID,:var_TMPSupplier17Rate,:var_TMPSupplier17DialCode from dual
                union all select :var_TMPSupplier18ID,:var_TMPSupplier18Rate,:var_TMPSupplier18DialCode from dual
                union all select :var_TMPSupplier19ID,:var_TMPSupplier19Rate,:var_TMPSupplier19DialCode from dual
                union all select :var_TMPSupplier20ID,:var_TMPSupplier20Rate,:var_TMPSupplier20DialCode from dual
                union all select :var_TMPSupplier11ID,:var_TMPSupplier21Rate,:var_TMPSupplier21DialCode from dual
               union all select :var_TMPSupplier22ID,:var_TMPSupplier22Rate,:var_TMPSupplier22DialCode from dual
                union all select :var_TMPSupplier23ID,:var_TMPSupplier23Rate,:var_TMPSupplier23DialCode from dual
                union all select :var_TMPSupplier24ID,:var_TMPSupplier24Rate,:var_TMPSupplier24DialCode from dual
                union all select :var_TMPSupplier25ID,:var_TMPSupplier25Rate,:var_TMPSupplier25DialCode from dual
                union all select :var_TMPSupplier26ID,:var_TMPSupplier26Rate,:var_TMPSupplier26DialCode from dual
                union all select :var_TMPSupplier27ID,:var_TMPSupplier27Rate,:var_TMPSupplier27DialCode from dual
                union all select :var_TMPSupplier28ID,:var_TMPSupplier28Rate,:var_TMPSupplier28DialCode from dual
                union all select :var_TMPSupplier29ID,:var_TMPSupplier29Rate,:var_TMPSupplier29DialCode from dual
                union all select :var_TMPSupplier30ID,:var_TMPSupplier30Rate,:var_TMPSupplier30DialCode from dual'
                using var_TMPSupplier1ID
                                            ,var_TMPSupplier1Rate
                                            ,var_TMPSupplier1DialCode
                                            ,var_TMPSupplier2ID
                                            ,var_TMPSupplier2Rate
                                            ,var_TMPSupplier2DialCode
                                            ,var_TMPSupplier3ID
                                            ,var_TMPSupplier3Rate
                                            ,var_TMPSupplier3DialCode
                                            ,var_TMPSupplier4ID
                                            ,var_TMPSupplier4Rate
                                            ,var_TMPSupplier4DialCode
                                            ,var_TMPSupplier5ID
                                            ,var_TMPSupplier5Rate
                                            ,var_TMPSupplier5DialCode
                                            ,var_TMPSupplier6ID
                                            ,var_TMPSupplier6Rate
                                            ,var_TMPSupplier6DialCode
                                            ,var_TMPSupplier7ID
                                            ,var_TMPSupplier7Rate
                                            ,var_TMPSupplier7DialCode
                                            ,var_TMPSupplier8ID
                                            ,var_TMPSupplier8Rate
                                            ,var_TMPSupplier8DialCode
                                            ,var_TMPSupplier9ID
                                            ,var_TMPSupplier9Rate
                                            ,var_TMPSupplier9DialCode
                                            ,var_TMPSupplier10ID  
                                            ,var_TMPSupplier10Rate
                                            ,var_TMPSupplier10DialCode
                                            ,var_TMPSupplier11ID  
                                            ,var_TMPSupplier11Rate
                                            ,var_TMPSupplier11DialCode
                                            ,var_TMPSupplier12ID  
                                            ,var_TMPSupplier12Rate
                                            ,var_TMPSupplier12DialCode
                                            ,var_TMPSupplier13ID  
                                            ,var_TMPSupplier13Rate
                                            ,var_TMPSupplier13DialCode
                                            ,var_TMPSupplier14ID  
                                            ,var_TMPSupplier14Rate
                                            ,var_TMPSupplier14DialCode
                                            ,var_TMPSupplier15ID  
                                            ,var_TMPSupplier15Rate
                                            ,var_TMPSupplier15DialCode
                                            ,var_TMPSupplier16ID  
                                            ,var_TMPSupplier16Rate
                                            ,var_TMPSupplier16DialCode
                                            ,var_TMPSupplier17ID  
                                            ,var_TMPSupplier17Rate
                                            ,var_TMPSupplier17DialCode
                                            ,var_TMPSupplier18ID  
                                            ,var_TMPSupplier18Rate
                                            ,var_TMPSupplier18DialCode
                                            ,var_TMPSupplier19ID  
                                            ,var_TMPSupplier19Rate
                                            ,var_TMPSupplier19DialCode
                                            ,var_TMPSupplier20ID  
                                            ,var_TMPSupplier20Rate
                                            ,var_TMPSupplier20DialCode   
                                            ,var_TMPSupplier21ID  
                                            ,var_TMPSupplier21Rate
                                            ,var_TMPSupplier21DialCode
                                            ,var_TMPSupplier22ID  
                                            ,var_TMPSupplier22Rate
                                            ,var_TMPSupplier22DialCode
                                            ,var_TMPSupplier23ID  
                                            ,var_TMPSupplier23Rate
                                            ,var_TMPSupplier23DialCode
                                            ,var_TMPSupplier24ID  
                                            ,var_TMPSupplier24Rate
                                            ,var_TMPSupplier24DialCode
                                            ,var_TMPSupplier25ID  
                                            ,var_TMPSupplier25Rate
                                            ,var_TMPSupplier25DialCode
                                            ,var_TMPSupplier26ID  
                                            ,var_TMPSupplier26Rate
                                            ,var_TMPSupplier26DialCode
                                            ,var_TMPSupplier27ID  
                                            ,var_TMPSupplier27Rate
                                            ,var_TMPSupplier27DialCode
                                            ,var_TMPSupplier28ID  
                                            ,var_TMPSupplier28Rate
                                            ,var_TMPSupplier28DialCode
                                            ,var_TMPSupplier29ID  
                                            ,var_TMPSupplier29Rate
                                            ,var_TMPSupplier29DialCode
                                            ,var_TMPSupplier30ID  
                                            ,var_TMPSupplier30Rate
                                            ,var_TMPSupplier30DialCode ;
               EXCEPTION
                WHEN OTHERS THEN
                NULL;
        END;

 

intrepid2008
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle表自动模拟多条数据,Oracle使用虚拟表dual一次插入多条记录
weixin_36243739的博客
04-02 583
Oracle使用虚拟表dual一次插入多条记录BEGINgenerate_tmpsht;execute immediate'insert into SHTSUPPLIERRATE (supplierid,supplierrate,supplierdialcode)select :var_TMPSupplier1ID,:var_TMPSupplier1Rate,:var_TMPSupplier1Di...
dual表查询多条记录
MZ_的博客
08-16 3550
案例记录1： 主要是用connect by select 'dual' from dual connect by level<=6 案例记录2： 主要使用level、connect by、regexp_substr。 select regexp_substr('1、2、3、4、5、6', '[^、]+', 1, level) num from dual connect by...
Oracle数据库 从虚拟表dual中查询多行数据 可使用union all
weixin_43384308的博客
05-21 1588
SQL语句如下： select '硫化氢' as test from dual union all select '油' as test from dual union all select '气' as test from dual union all select '水' as test from dual union all select '油气' as test from dual union all select '油水' as test from dual u
Oracle中同时添加多条数据
热门推荐
小小码农
09-07 1万+
假设现在有一个招聘会公司表，一个招聘岗位表。。其中招聘岗位的co_id关联招聘公司的id。一个招聘会公司可能会发布很多个招聘岗位。name在添加招聘岗位的时候 INSERT INTO T_JOB_FAIR_POSITION (id, co_id, name, hire_num, salary, address, summary, require) SEL
oracle 虚拟表 多行,Oracle使用虚拟表dual一次插入多条记录【摘录】
weixin_36211536的博客
04-06 1068
从一个CSV文件中读取所有的数据，并且插入到一个Oracle数据库中，并且几分钟内完成，大约有60万条。网上有人说了，你可以循环insert然后插入几千条以后Commit一次，我靠，你自己试试看！！如果没试过就不要误导别人好吧。还有人说了，Oracle根本不支持一次多条插入，我靠，你咋不说自己学艺不精呢？现在给大家介绍一个小技巧，话说在Oracle里有一个很奇特的“表”，名叫Dual。我们就要利用...
Oracle中多表关联批量插入批量更新与批量删除操作
09-08
总的来说，理解并熟练掌握Oracle中多表关联的批量插入、更新和删除操作，能够有效优化数据库性能，提高开发效率，并且减少因频繁的单条操作导致的错误可能性。在设计数据库方案时，应充分利用这些特性，为应用程序...
oracle通过1条语句插入多个值的方法示例
12-15
我想往数据库插入多条数据时，使用了如下语句： insert into 表1 (字段1,字段2) values (1,2),(2,3),(3,4); 这条语句在mysql中执行的话，语法一点毛病都没有，但是，在oracle上执行一直报错： ORA-00933: SQL ...
Oracle批处理：使用C# 自带Oracle驱动一次执行多条Sql语句
03-24
本文将深入探讨如何在C#中利用Oracle自带的驱动来实现批处理，以一次执行多条SQL语句。 首先，我们需要了解Oracle的数据驱动，即ODP.NET（Oracle Data Provider for .NET）。这是Oracle公司为.NET开发者提供的一个...
【Oracle】虚拟表Dual
weixin_33739523的博客
02-07 68
Dual是个虚拟表，用来构成SELECT语句的语法规则，Oracle保证Dual里面永远只有一条记录。可以用它来做很多事情，例如，查看当前用户；用来调用系统函数；得到序列的下一个值或者当前值；可以用作计算器等。 转载于:https://www.cnblogs.com/haha-9527/p/8427931.html...
Oracle学习之start with...connect by子句的用法
weixin_30295091的博客
01-14 2353
转自：http://www.blogjava.net/xzclog/archive/2010/03/05/314642.html，多谢博主分享 Oracle中start with…connect by prior子句用法 connect by 是结构化查询中用到的，其基本语法是： 1 select … from tablename 2 start with 条件1 3 conne...
oracle将一行记录显成多行
douyushan的专栏
11-21 1299
将一条记录按照指定的数量(a),显示成a行 table_name 表数据如下 序号，数量，名称 1 2    一剑 2 4    之风 需要显示结果 序号，数量，名称 1 1    一剑 1 1    一剑 2 1    之风 2 1    之风 2 1    之风 2 1    之风 select 序号，1 数量 ，名称 from table_name  left j
oracle伪表添加多行,ORACLE中伪表 dual 的用法
weixin_42577735的博客
04-09 547
dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情，如下：1、查看当前用户，可以在 SQL Plus中执行下面语句 select user from dual;2、用来调用系统函数select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss‘) from dual;--获得当前系统时间selec...
oracle中的dual虚拟表
Mr_Pang的专栏
12-13 605
dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情，如下： 　　1、查看当前用户，可以在 SQL Plus中执行下面语句 select user from dual; 　　2、用来调用系统函数 　　select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from du
ORACLE中dual的详解及其故障恢复
ijavaweb
03-25 1976
什么是dual呢？ 首先，dual是属于sys用户的； 它只有一个X varchar2(1)的列； 查询这个虚拟的列不会产生逻辑的IO； dual是oracle中的一个实际存在的表，任何用户都可读取，常用在没有目标表的select语句块中 以下是oracle manual中对dual的说明： The table named DUAL is a small tab
Oracle中dual表的用途介绍
weixin_34293902的博客
10-15 393
Oracle中dual表的用途介绍 导读]dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情。 　 dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情，...
Oracle中的dual表
KevinChen2019的博客
11-28 1969
2、DUAL表是一个单行单列的虚拟表，这个表只有1列DUMMY，数据类型为VARCHAR2(1),不论执行什么操作（不要删除记录），它都只有一条记录——“X”，Oracle有内部逻辑保证DUAL表中只有一条数据。1、DUAL表是Oracle提供的最小的工作表，是sys用户下的一张内部表，所有用户都可以使用DUAL名称访问，无论什么时候这个表总是存在。4、dual是Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select语句块中。2、查看当前日期、时间。1、查看当前连接用户。
oracle中的dual伪表、虚拟表
程宇寒
06-03 5580
oracle中的dual伪表、虚拟表 /* dual是一个虚拟表，用来构成select的语法规则，oracle保 证dual里面永远只有一条记录。我们可以用它来做很多事情，如下： */ --SYSDATE表示系统时间，dual表示伪表(虚拟表) SELECT SYSDATE FROM dual; --做运算 SELECT (6 + 6) * 2 FROM dual; --做字符串的拼接 SE...
oracle一次插入多条数据
最新发布
05-19
在Oracle中，可以使用INSERT ALL语句一次插入多条数据。 语法如下： ``` INSERT ALL INTO table_name (column1, column2, column3) VALUES (value1, value2, value3) INTO table_name (column1, column2, column3)...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值