Oracle SQL自动创建VARCHAR增量PK [英] Oracle SQL automatically create VARCHAR incremented PK

问题描述

我试图编写一个Oracle SQL表，以便每当插入一行(通过Oracle APEX表单)时，PK就会自动填充为自动递增的varchar类型.例如. TNT00000001，TNT00000002，TNT00000003 ...

I'm trying to write an Oracle SQL table such that whenever a row is inserted (through an Oracle APEX form), the PK is automatically populated with a varchar type that automatically increments. Eg. TNT00000001, TNT00000002, TNT00000003...

 create table sample (
Tnt_ID CHAR(8) NOT NULL,
wtv NUMBER(3)
);

CREATE SEQUENCE Tnt_ID_Auto 
 MINVALUE 1  START WITH 1 INCREMENT BY 1  CACHE 10;

CREATE OR REPLACE TRIGGER sample_on_insert
  BEFORE INSERT ON sample
  FOR EACH ROW
BEGIN
  SELECT CONCAT('TNT', LPAD(Tnt_ID_Auto.nextval, 5, '0'))
  INTO :new.Tnt_ID
  FROM dual;
END;

当我尝试运行此代码时:

When I try to run this:

INSERT INTO SAMPLE (wtv) VALUES (1);

我收到错误

PLS-00103:遇到符号"INSERT"

PLS-00103: Encountered the symbol "INSERT"

推荐答案

这是 IDENTITY 和 VIRTUAL 列的很好的用例.因此，不需要其他 SEQUENCE 和 TRIGGER .

This is a good use case for IDENTITY and VIRTUAL columns. So, no need of an additional SEQUENCE and TRIGGER.

演示:

CREATE TABLE sample (
    serial_no NUMBER GENERATED ALWAYS AS IDENTITY,
    tnt_id GENERATED ALWAYS AS (CONCAT('TNT', LPAD(serial_no, 5, '0'))) VIRTUAL,
    wtv    NUMBER(3)
);

insert into sample (wtv) values(100);
insert into sample (wtv) values(200);
insert into sample (wtv) values(300);

结果:

select * from sample;

 SERIAL_NO TNT_ID                         WTV
---------- ----------------------- ----------
         1 TNT00001                       100
         2 TNT00002                       200
         3 TNT00003                       300

工作原理:

1. NUMBER GENERATED ALWAYS AS IDENTITY:这会自动为您创建所需的序列，从1开始，默认情况下以1递增.
2. GENERATED ALWAYS AS VIRTUAL:这将创建一个虚拟列，其值将使用其他列值自动计算.在此演示中，它使用了标识列.
3. LPAD:这将确保在增加列值时保留零，但也将确保在增加数字时不会增加字符串的长度.
4. CONCAT:这会将TNT合并为上述值.

1. NUMBER GENERATED ALWAYS AS IDENTITY: This automatically creates the required sequence for you, starts with 1 and increments by 1 by default.
2. GENERATED ALWAYS AS VIRTUAL: This creates a virtual column whose values are calculated automatically using other column values. In this demo it uses the identity column.
3. LPAD: This will ensure zeroes are left padded while the column value is incremented, but will also ensure that when the number is incremented it doesn't increase the length of the string.
4. CONCAT: This will concat TNT to above value.

这篇关于Oracle SQL自动创建VARCHAR增量PK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！