oracle 如何在生成的序列号中附加字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13140381/
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
How to attache a String in generated sequence number
提问by Gunjan Shah
In my application, I am using Struts2 for UI and JPA for DAO layer. And Oracle 11G database.
在我的应用程序中，我将 Struts2 用于 UI，将 JPA 用于 DAO 层。和Oracle 11G 数据库。
In my database I have created a sequence ..
在我的数据库中，我创建了一个序列..
CREATE SEQUENCE "PERK"."EMP_CODE_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 501 CACHE 500 NOORDER NOCYCLE ;
Now my requirement is changed. As per the requirement I want to attache "TMP" to my generated sequence number.
现在我的要求改变了。根据要求，我想将“TMP”附加到我生成的序列号。
For example : If my sequence no is : 1 then It will give me TMP000001.
例如：如果我的序列号是：1 那么它会给我 TMP000001。
For this the SQL query will be like this ..
为此，SQL 查询将是这样的 ..
select 'TMP'||lpad(EMP_CODE_SEQ.nextval,6, '0') test from dual;
But is there any way to implement the above query in JPA ? OR is there any way in ORACLE,so we can change the output sequence?
但是有没有办法在 JPA 中实现上述查询？或者在 ORACLE 中有什么方法可以改变输出顺序吗？
回答by Nick Krasnov
OR is there any way in ORACLE,so we can change the output sequence?
或者在 ORACLE 中有什么方法可以改变输出顺序吗？
No. Sequence is used to generate a number sequence only. So you cannot force it to generate anything else except numbers. To that end you will need to use implicit or explicit data type conversion and concatenation to produce a string of desired format. As an alternative, you can use to_charfunction to convert number generated by a sequence to left-zero padded string.
No. Sequence 仅用于生成编号序列。所以你不能强迫它产生除数字以外的任何东西。为此，您将需要使用隐式或显式数据类型转换和连接来生成所需格式的字符串。作为替代方案，您可以使用to_char函数将序列生成的数字转换为左填充零的字符串。
SQL> select to_char(sq_id.nextval, '000000') res
2 from dual
3 /
RES
-------
000004
Concatenation (bars or `concat' function):
连接（条形或`concat' 函数）：
SQL> select 'TMP' || to_char(sq_id.nextval, '000000') res
2 from dual
3 /
RES
----------
TMP 000006
SQL> select concat('TMP',to_char(sq_id.nextval, '000000')) res
2 from dual
3 /
RES
----------
TMP 000007
回答by Gunjan Shah
I have solved this problem by writing the logic in JPA setter method itself. Here is the example of JPA entity.
我通过在 JPA setter 方法本身中编写逻辑解决了这个问题。这是 JPA 实体的示例。
@Entity
@Table(name = "MST_EMP")
public class MstEmp implements Serializable, IsEntity {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "EMP_CODE")
@SequenceGenerator( name = "appEmpSeq", sequenceName = "EMP_CODE_SEQ")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "appEmpSeq" )
private String empCode;
public MstEmp() {
}
public String getEmpCode() {
return this.empCode;
}
public void setEmpCode(String empCode) {
this.empCode = getSequencePrefix(empCode)+empCode;
}
/**
* FUNCTION USED TO GENERATE SEQUENCE PREFIX: TEMP+"REQUIRED ZEROs"+EMP CODE
* @param empCode
* @return
*/
public String getSequencePrefix(String empCode){
String temp = "TEMP";
if(empCode.length()<6){
int zeroCount = 6 - empCode.length();
for(int index=0;index<zeroCount;index++){
temp = temp + "0";
}
}
return temp;
}
}
Here .. as while setting the employee code, it will call a function to concat required prefix to the sequence number.
这里 .. 在设置员工代码时，它将调用一个函数将所需的前缀连接到序列号。
