SQL 在 Oracle 中为 varchar2 字段创建序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14561210/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a sequence for a varchar2 field in Oracle
提问by ykombinator
I want to create a sequence for this varchar. It would have been easier had it been a number instead of varchar. In that case, I could do
我想为这个 varchar 创建一个序列。如果它是一个数字而不是 varchar 会更容易。在那种情况下，我可以做
seq_no := seq_no + 1;
seq_no := seq_no + 1;
But what can I do when I want to store next value in column as A0000002, when the previous value was A0000001 (to increment the number in the next varchar rowby 1)?
但是，当我想将列中的下一个值存储为 A0000002，而前一个值为 A0000001（将下一个 varchar rowby 1 中的数字增加）时，我该怎么办？
回答by GKV
This can be done by
这可以通过
to_char(seq_no,'FM0000000')
your example can be done by creating sequence in oracle
您的示例可以通过在 oracle 中创建序列来完成
create sequence seq_no start with 1 increment by 1;
then
然后
select 'A'||to_char(seq_no.nextval,'FM0000000') from dual;
Right now i have used in dual ..but place this
现在我已经在双重使用..但是把这个
'A'||to_char(seq_no.nextval,'FM0000000')
in your required query ..this will create sequence as you mentioned
在您所需的查询中..这将创建您提到的序列
回答by álvaro González
Sequences are purely numeric. However, you need a trigger anyway, so simply adapt such trigger to insert the desired prefix:
序列是纯数字的。但是，无论如何您都需要触发器，因此只需调整此类触发器以插入所需的前缀：
CREATE OR REPLACE TRIGGER FOO_TRG1
BEFORE INSERT
ON FOO
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF :NEW.FOO_ID IS NULL THEN
SELECT 'A' || TO_CHAR(FOO_SEQ1.NEXTVAL, 'FM0000000') INTO :NEW.FOO_ID FROM DUAL;
END IF;
END FOO_TRG1;
/
ALTER TRIGGER FOO_TRG1 ENABLE;
回答by Ben
If you're able I'd actually use a virtual column as defined in the CREATE TABLEsyntax. It makes it more easily extensible should the need arise.
如果可以，我实际上会使用CREATE TABLE语法中定义的虚拟列。如果需要，它使它更容易扩展。
Here's a working example.
这是一个工作示例。
SQL> create table tmp_test (
2 id number(7,0) primary key
3 , col1 number
4 , seq varchar2(8 char) generated always as (
5 'A' || to_char(id, 'FM0999999'))
6 );
Table created.
SQL>
SQL> create sequence tmp_test_seq;
Sequence created.
SQL>
SQL> create or replace trigger tmp_test_trigger
2 before insert on tmp_test
3 for each row
4 begin
5
6 :new.id := tmp_test_seq.nextval;
7 end;
8 /
Trigger created.
SQL> show errors
No errors.
SQL>
SQL> insert into tmp_test (col1)
2 values(1);
1 row created.
SQL>
SQL> select * from tmp_test;
ID COL1 SEQ
---------- ---------- --------------------------------
1 1 A0000001
Having said that; you would be better off if you did not do this unless you have an unbelievably pressing business need. There is little point to making life more difficult for yourself by prepending a constant value onto a number. As A will always be A it doesn't matter whether it's there or not.
话说回来; 如果您不这样做，您会过得更好，除非您有令人难以置信的紧迫业务需求。通过在数字上添加一个常数值来让自己的生活变得更加困难毫无意义。由于 A 将永远是 A，因此它是否存在并不重要。
回答by Rene
If the format is always a letter followed by 7 digits you can do:
如果格式始终是一个字母后跟 7 位数字，您可以执行以下操作：
sequence = lpad(substr(sequence,2,7)+1,7,'0')
