Need to discuss an approach: We've a count column in oracle table. Dataype of column is char(3 Byte). On each insertion of a row, the value of count column increment by 1(But it is not an identity column).
需要讨论一种方法：我们在oracle表中有一个count列。列的Dataype是char（3 Byte）。在每次插入一行时，count列的值递增1（但它不是标识列）。
There is a possibility that value will go beyond 999. So how can we handle it. Till now, the approach I'm able to justified is:
价值有可能超过999.那么我们如何处理它。直到现在，我能够证明的方法是：
Increasing the column datatype at runtime. Steps are:
在运行时增加列数据类型。步骤是：
- First, we'll check the length of value.
- 首先，我们将检查价值的长度。
- For eg, if length is 4 then Alter the column of table and change the size of column to
char(4 BYTE) - 例如，如果length为4，则更改表的列并将列的大小更改为char（4 BYTE）
Any better approach you people can provide...
人们可以提供更好的方法......
2 个解决方案
#1
0
Increasing the column datatype at runtime.
在运行时增加列数据类型。
It proves that the design is flawed. You should never modify a table at runtime.
它证明了设计存在缺陷。您永远不应该在运行时修改表。
In your requirement, there is no reason to store the NUMBER as a STRING. This will be for sure the primary reason for degraded performance. You should chose proper data types, which in your case should be NUMBER.
在您的要求中，没有理由将NUMBER存储为STRING。这肯定是性能下降的主要原因。您应该选择适当的数据类型，在您的情况下应该是NUMBER。
On each insertion of a row, the value of count column increment by 1
在每次插入一行时，count列的值递增1
You should ideally be using a sequence for this. Depending on your database version, there are two options:
理想情况下，您应该使用序列。根据您的数据库版本，有两个选项：
- Pre 12c releases - See trigger-sequence approach
- Pre 12c版本 - 请参阅触发序列方法
- 12c onward - See IDENTITY column
- 12c以后 - 请参阅IDENTITY列
On a side note, CHAR datatype stores fixed-length character strings. You should ideally use VARCHAR2 datatype, which stores variable-length character strings.
另外，CHAR数据类型存储固定长度的字符串。理想情况下，您应该使用VARCHAR2数据类型，它存储可变长度的字符串。
#2
0
You really, really shouldn't be storing numbers in a char or varchar column. If at all possible, refactor the table design that's got you into this problem.
你真的，真的不应该在char或varchar列中存储数字。如果可能的话，重构表设计，让你解决这个问题。
If you can't do that, are you sure the column can't be changed from char(3) to something more useful? Char(4) is only 1 byte more storage, varchar(4) would probably be no extra storage usually if char(3) has been working elsewhere.
如果你不能这样做，你确定该列不能从char（3）更改为更有用的东西吗？ Char（4）只有1个字节的存储空间，如果char（3）一直在其他地方工作，varchar（4）通常可能没有额外的存储空间。
If you really can't do any of them and you're still stuck in this pickle and you've absolutely got to get a number over 999 into a char(3) column even after kicking and screaming that it's not a good idea....
如果你真的不能做任何一个并且你仍然坚持这个泡菜，你必须得到一个超过999的数字进入char（3）专栏甚至在踢和尖叫之后，这不是一个好主意。 ...
Store it in hex. Note this very clearly or it'll confuse people, but hex to decimal conversion isn't that much of a black art and it'll give you a range up to 4095 in the same character space.
存储在十六进制中。请注意这一点，否则会让人感到困惑，但十六进制到十进制的转换并不是一个黑色的艺术，它会在同一个字符空间中给你一个高达4095的范围。
#1
0
Increasing the column datatype at runtime.
在运行时增加列数据类型。
It proves that the design is flawed. You should never modify a table at runtime.
它证明了设计存在缺陷。您永远不应该在运行时修改表。
In your requirement, there is no reason to store the NUMBER as a STRING. This will be for sure the primary reason for degraded performance. You should chose proper data types, which in your case should be NUMBER.
在您的要求中，没有理由将NUMBER存储为STRING。这肯定是性能下降的主要原因。您应该选择适当的数据类型，在您的情况下应该是NUMBER。
On each insertion of a row, the value of count column increment by 1
在每次插入一行时，count列的值递增1
You should ideally be using a sequence for this. Depending on your database version, there are two options:
理想情况下，您应该使用序列。根据您的数据库版本，有两个选项：
- Pre 12c releases - See trigger-sequence approach
- Pre 12c版本 - 请参阅触发序列方法
- 12c onward - See IDENTITY column
- 12c以后 - 请参阅IDENTITY列
On a side note, CHAR datatype stores fixed-length character strings. You should ideally use VARCHAR2 datatype, which stores variable-length character strings.
另外，CHAR数据类型存储固定长度的字符串。理想情况下，您应该使用VARCHAR2数据类型，它存储可变长度的字符串。
#2
0
You really, really shouldn't be storing numbers in a char or varchar column. If at all possible, refactor the table design that's got you into this problem.
你真的，真的不应该在char或varchar列中存储数字。如果可能的话，重构表设计，让你解决这个问题。
If you can't do that, are you sure the column can't be changed from char(3) to something more useful? Char(4) is only 1 byte more storage, varchar(4) would probably be no extra storage usually if char(3) has been working elsewhere.
如果你不能这样做，你确定该列不能从char（3）更改为更有用的东西吗？ Char（4）只有1个字节的存储空间，如果char（3）一直在其他地方工作，varchar（4）通常可能没有额外的存储空间。
If you really can't do any of them and you're still stuck in this pickle and you've absolutely got to get a number over 999 into a char(3) column even after kicking and screaming that it's not a good idea....
如果你真的不能做任何一个并且你仍然坚持这个泡菜，你必须得到一个超过999的数字进入char（3）专栏甚至在踢和尖叫之后，这不是一个好主意。 ...
Store it in hex. Note this very clearly or it'll confuse people, but hex to decimal conversion isn't that much of a black art and it'll give you a range up to 4095 in the same character space.
存储在十六进制中。请注意这一点，否则会让人感到困惑，但十六进制到十进制的转换并不是一个黑色的艺术，它会在同一个字符空间中给你一个高达4095的范围。