首页 \ 问答 \ Oracle SQL:如何读取和增加字段(Oracle SQL: How to read-and-increment a field)

Oracle SQL:如何读取和增加字段(Oracle SQL: How to read-and-increment a field)

我正在重构企业应用程序的数据导入过程,并遇到了一个片段,我想找到一个更好的解决方案。 在导入数据时,我们必须为每个数据集创建一个唯一的实体,并在字段中有一个计数器用于顺序分配此ID。 您阅读该字段以获取下一个免费ID,然后递增,以备下次使用。


   SELECT idnext FROM mytable;
   UPDATE mytable SET idnext = idnext + 1;



我们正在用perl重写,并且我想要做同样的事情,但是更好。 原子解决方案会很好。 不幸的是,我的SQL技能是有限的,所以我转向集体智慧:-)

I'm refactoring the data import procedure for an enterprise application and came across a snippet I'd like to find a better solution. When importing data we have to create a unique entity for each data set and there is a counter in a field to be used to assign this id sequentially. You read the field to get the next free id and increment it afterwards to prepare for the next time.

At the moment this is done in two steps in the original app, written in 'C':

   SELECT idnext FROM mytable;
   UPDATE mytable SET idnext = idnext + 1;

Obviously there is a race condition here, if multiple processes do the same thing.

Edit: Important corequisite: I can not touch the database/field definition, this rules out a sequence.

We are rewriting in perl, and I'd like to do the same thing, but better. An atomic solution would be nice. Unfortunately my SQL skills are limited, so I'm turning to collective wisdom :-)

更新时间:2019-06-23 01:10


在这个特殊情况下,一个序列是正确的解决方案。 但是,如果在将来某种情况下需要同时更新某些内容并返回一个值,则可以使用RETURNING子句:

UPDATE atable SET foo = do_something_with(foo) RETURNING foo INTO ?

如果调用代码是PL / SQL,请将? 与本地PL / SQL变量; 否则你可以将它绑定为程序中的输出参数。


my $sth = $dbh->prepare('UPDATE mytable SET idnext = idnext + 1 returning idnext into ?');
my $idnext;
$sth->bind_param_inout(1, \$idnext, 8);
$sth->execute; # now $idnext should contain the value


In this particular case, a sequence is the right solution as mentioned. But if in some future situation you need to both update something and return a value in the same statement, you can use the RETURNING clause:

UPDATE atable SET foo = do_something_with(foo) RETURNING foo INTO ?

If the calling code is PL/SQL, replace the ? with a local PL/SQL variable; otherwise you can bind it as an output parameter in your program.

Edit: Since you mentioned Perl, something like this ought to work (untested):

my $sth = $dbh->prepare('UPDATE mytable SET idnext = idnext + 1 returning idnext into ?');
my $idnext;
$sth->bind_param_inout(1, \$idnext, 8);
$sth->execute; # now $idnext should contain the value

See DBI.




自动增量列:Oracle和MySQL之间SQL语法的差异(Auto-increment column: differences in SQL syntax between Oracle and MySQL)

并非所有的SQL都是一样的。 Oracle和MySQL都不支持IDENTITY的实际SQL标准。 Oracle不使用反引号...你实际上不需要引用你的标识符。 最好不要这样做,否则不会无意中在标识符中使用无效字符。 Oracle数字称为NUMBER,可以采用可选的精度和比例。 CREATE TABLE Category ( id NUMBER(11) NOT NULL, title VARCHAR2(45) NULL, PRIMARY KEY (id) ) 要执行AUTO_ ...

如何将Map传递给Oracle PL / SQL函数?(How to pass Map to Oracle PL/SQL function?)

有关AskTom有关将Java对象传递给Oracle的有趣讨论 。 特别是海事组织, Tom Kyte提供的这些出色建议 : 我,我会玩 在提交删除行上创建全局临时表gtt(fname varchar2(20),lname varchar2(20)); 并将java应用程序BATCH插入到此处,然后调用该过程,该过程只是使用该表数据作为其输入。 根据我的经验,“我”和“正在完成”之间的代码量最少, 即:只需使用一组关系临时表,使用java写入它并让pl / sql从表中读取。 它可能几乎与传递对象 ...

Oracle:SQL查询,它返回只有数字值的行(Oracle: SQL query that returns rows with only numeric values)

您可以使用REGEXP_LIKE函数: SELECT X FROM myTable WHERE REGEXP_LIKE(X, '^[[:digit:]]+$'); 样品运行: SQL> SELECT X FROM SO; X -------------------- 12c 123 abc a12 SQL> SELECT X FROM SO WHERE REGEXP_LIKE(X, '^[[:digit:]]+$'); X -------------------- 123 SQL> ...


其实没有那么为麻烦,你可以打开ORACLE视图编辑器。 中显示ORACLE语句,想要什么语句你自己试吧! 包括表的一些属性项都可以加上的。

java.lang.ClassCastException:oracle.sql.BLOB无法强制转换为oracle.sql.BLOB(java.lang.ClassCastException: oracle.sql.BLOB cannot be cast to oracle.sql.BLOB)

两个oracle jar文件可能在您的类路径中。 请删除一个jar并再次部署。 Two oracle jar files may be in your classpath. Please remove one jar and deploy again.

Oracle SQL对版本号进行排序(Oracle SQL to Sort Version Numbers)

这是一种方法。 先按顺序排序. 然后是之后的数字. select version_number from mytable order by substr(version_number, 1, instr(version_number,'.')-1) desc ,length(substr(version_number, instr(version_number,'.')+1)) desc ,substr(version_number, instr(version ...

创建唯一索引的 sql语句

sql语句创建唯一索引的方法: 1、在sql server数据库中: CREATE UNIQUE INDEX AK_UnitMeasure_Name ON Production.UnitMeasure (Name); GO 2、在oracle中床创建 CREATE unique INDEX 索引名 ON 表名 (列名) TABLESPACE 表空间名;

SQL如何添加具有相同字段值的所有字段并按降序排序(SQL how to add all fields with the same field value and order it by descending order)

尝试 SELECT field1, SUM(field2) AS total FROM table1 GROUP BY field1 ORDER BY SUM(field2) DESC Try SELECT field1, SUM(field2) AS total FROM table1 GROUP BY field1 ORDER BY SUM(field2) DESC




  • Android宽度:100%修复(网站接管问题)(Android width:100% fix (website takeover issue))
  • C ++函数/方法设计的良好实践(Good practice in C++ function/method design)
  • 计算其他表中不存在的所有记录 - SQL查询(Count all records that does not exist to other table - SQL Query)
  • 为什么我要用JPA共享ID?(Why do I get shared Ids with JPA?)
  • asp.net - 如何显示来自html格式的数据行的字段(asp.net - how to display a field from data row that is in html format)
  • 我们如何使用ActiveRecord从连接表中删除行?(How can we delete rows from a join table by using ActiveRecord?)
  • ng-class搞乱了类的顺序(ng-class messing with the order of classes)
  • oracle 12g无效数字错误(oracle 12g invalid number error)
  • 更改ng-src值onclick(Change ng-src value onclick)
  • 如何在android中自动添加自定义依赖项以创建新项目?(How to add custom dependencies automatically in android for ever a new project is created?)
  • datetime函数在PHP中(datetime function in php)
  • 在javascript中获取会话数组的值(in javascript get the value of a session array)
  • 如何在UTF8中编译LaTeX?(How can I compile LaTeX in UTF8? [closed])
  • Rspec:“array.should == another_array”,但不用担心订单(Rspec: “array.should == another_array” but without concern for order)
  • Logcat错误:无法在android片段中加载视图(Logcat error: unable to load view in android fragments)
  • JavaFX的。(JavaFX. Adding items to the list in different threads is not working)
  • 从GDATA日历资源迁移到Google Calendar Resource api(Migrate from GDATA calendar resource to Google Calendar Resource api)
  • SSRS 2008 - 以零情景处理分割(SSRS 2008 - Dealing with division by zero scenarios)
  • 我如何以编程方式添加一个listView列标题的点击事件(How can I add a listView column header a click event programmatically)
  • Wxpython:无法检索有关列表控件项XXX的信息(Wxpython: Couldn't retrieve information about list control item XXX)
  • 使用Tortoise SVN在SVN存储库中移动目录(Move Directory across SVN repository using Tortoise SVN)
  • 天蓝色服务结构集群中的web api无状态服务是否在一段时间不活动后进入休眠状态?(Do web api stateless services in azure service fabric cluster go to sleep after a period of inactivity?)
  • 我可以设置intelliJ来突出显示PHP编码风格吗?(Can I set intelliJ to highlight php coding style?)
  • 用javafx创建一个Truetype字体文件(Creating a Truetype Font file with javafx)
  • Spring ftp配置错误(Spring ftp configuration is wrong)
  • 使用gsub去除多个字符(Using gsub to strip multiple characters)
  • 续订推送证书并保持当前的App Store App正常工作(Renew Push certificate and keep current App Store App working)
  • js:ES5和ES6之间关于'this'关键字用法的一个令人困惑的观点(js: one confusing point about 'this' keyword usage between ES5 and ES6)
  • window.onload vs $(document).ready()(window.onload vs $(document).ready())
  • 在Swift中,如何声明一个符合一个或多个协议的特定类型的变量?(In Swift, how can I declare a variable of a specific type that conforms to one or more protocols?)