用 PL/SQL 编写版本号函数 [英] Writing a Version Number Function in PL/SQL

问题描述

我想编写一个函数，该函数将为我提供表的下一个版本号.该表存储每条记录上的现有版本.例如，我有猫桌

I want to write a function that will give me the next version number for a table. The table stores the existing version on each record. For example, I have the cat table

猫

seqid    1
name     Mr Smith
version number  1.2b.3.4

如何编写能够根据各种条件递增这些值的程序?

How can I write a program that will be able to increment these values based on various conditions?

这是我第一次尝试

if v_username is not null
then v_new_nbr = substr(v_cur_nbr, 1,7)||to_number(substr(v_cur_nbr, 8,1))+1

应该是1.2b.3.5

推荐答案

substr(v_cur_nbr, 1,7)||to_number(substr(v_cur_nbr, 8,1))+1

这会抛出ORA-01722:无效号码.原因很微妙.似乎 Oracle 在添加之前应用了连接运算符，因此您实际上是在字符串 '1.2b.3.4' 中添加了一个.

This hurls ORA-01722: invalid number. The reason is a subtle one. It seems Oracle applies the concatenation operator before the additions, so effectively you're adding one to the string '1.2b.3.4'.

一个解决方案是在将结果与第一个子字符串连接之前，使用 TO_CHAR 函数将加法与第二个子字符串括起来:

One solution is using a TO_CHAR function to bracket the addition with the second substring before concatenating the result with the first substring:

substr(v_cur_nbr, 1,7) ||to_char(to_number(substr(v_cur_nbr, 8,1))+1)

db<>fiddle 上的工作演示.

顺便说一句，像这样的键是一个糟糕的数据建模.智能钥匙是愚蠢的.它们总是会导致可怕的 SQL(如您所见)并有数据损坏的风险.正确的模型应该为版本号的每个元素单独的列.我们可以使用虚拟列来连接版本号以显示情况.

Incidentally, a key like this is a bad piece of data modelling. Smart keys are dumb. They always lead to horrible SQL (as you're finding) and risk data corruption. A proper model would have separate columns for each element of the version number. We can use virtual columns to concatenate the version number for display circumstances.

create table cats(
seqid    number
,name     varchar2(32)
,major_ver_no1 number
,major_ver_no2 number
,variant varchar2(1)
,minor_ver_no1 number
,minor_ver_no2 number
,v_cur_nbr  varchar2(16) generated always as (to_char(major_ver_no1,'FM9') ||'.'||
                                              to_char(major_ver_no2,'FM9')  ||'.'||
                                              variant  ||'.'||
                                              to_char(minor_ver_no1,'FM9')  ||'.'||
                                              to_char(minor_ver_no2,'FM9') ) );

所以设置有点麻烦，但增加版本号是小菜一碟.

So the set-up is a bit of a nause but incrementing the version numbers is a piece of cake.

update cats
set major_ver_no1 = major_ver_no1 +1 
    , major_ver_no2 = 0
    , variant = 'a';

还有一个db<>fiddle.

这篇关于用 PL/SQL 编写版本号函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！