AutoNumber And Identity Functionality (Pre 12c)

最新推荐文章于 2025-03-25 09:18:14 发布

数据架构

最新推荐文章于 2025-03-25 09:18:14 发布

阅读量95

点赞数

分类专栏： DBA 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/u011868279/article/details/129026906

版权

DBA 专栏收录该内容

183 篇文章

订阅专栏

本文展示了如何在Oracle数据库中创建带有主键列的表，以及如何设置序列和触发器来自动填充主键。当在departments表中插入新记录且未指定ID时，触发器会自动为ID分配序列值。此外，还讨论了如何修改触发器以处理不同的插入场景，包括覆盖传递的值或在插入时引发错误。

摘要由CSDN通过智能技术生成

Create a table with a suitable primary key column and a sequence to support it.

[oracle@oracle-db-19c ~]$ 
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba                                   

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 14 15:14:25 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show user;
USER is "SYS"
SQL> 
SQL> conn maxwellpan/maxwellpan@PDB1
Connected.
SQL> 
SQL> CREATE TABLE departments (
  2    ID           NUMBER(10)    NOT NULL,
  3    DESCRIPTION  VARCHAR2(50)  NOT NULL);

Table created.

SQL> ALTER TABLE departments ADD (
  2    CONSTRAINT dept_pk PRIMARY KEY (ID));

Table altered.

SQL> CREATE SEQUENCE dept_seq;

Sequence created.

SQL>

Create a trigger to populate the ID column if it's not specified in the insert.

SQL> 
SQL> show user;
USER is "MAXWELLPAN"
SQL> CREATE OR REPLACE TRIGGER dept_bir 
  2  BEFORE INSERT ON departments 
  3  FOR EACH ROW
  4  WHEN (new.id IS NULL)
  5  BEGIN
  6    SELECT dept_seq.NEXTVAL
  7    INTO   :new.id
  8    FROM   dual;
  9  END;
 10  /

Trigger created.

SQL>

from 11g onward CURVAL and NEXTVAL sequence pseudocolumns can be used directly in PL/SQL expressions, so you could instead do the following.

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  :new.id := dept_seq.NEXTVAL;
END;
/

Test it using the automatic and manual population methods.

SQL> 
SQL> 
SQL> INSERT INTO departments (description) VALUES ('Development');

1 row created.

SQL>
SQL> set pagesize 200 linesize 200
SQL> col description format a20
SQL> SELECT * FROM departments;

        ID DESCRIPTION
---------- --------------------
         1 Development

SQL> INSERT INTO departments (id, description) VALUES (dept_seq.NEXTVAL, 'Accounting');

1 row created.

SQL> SELECT * FROM departments;

        ID DESCRIPTION
---------- --------------------
         1 Development
         2 Accounting

SQL>

The trigger can be modified to give slightly different results. If the insert trigger needs to perform more functionality than this one task you may wish to do something like the following.

SQL> 
SQL> CREATE OR REPLACE TRIGGER dept_bir 
  2  BEFORE INSERT ON departments 
  3  FOR EACH ROW
  4  BEGIN
  5    IF :new.id IS NULL THEN
  6      SELECT dept_seq.NEXTVAL
  7      INTO   :new.id
  8      FROM   dual;
  9    END IF;
 10  
 11    -- Do more processing here.
 12  END;
 13  /

Trigger created.

SQL>

To overwrite any values passed in you should do the following.

SQL> 
SQL> CREATE OR REPLACE TRIGGER dept_bir 
  2  BEFORE INSERT ON departments 
  3  FOR EACH ROW
  4  BEGIN
  5    SELECT dept_seq.NEXTVAL
  6    INTO   :new.id
  7    FROM   dual;
  8  END;
  9  /

Trigger created.

SQL>

To error if a value is passed in you should do the following.

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW
BEGIN
  IF :new.id IS NOT NULL THEN
    RAISE_APPLICATION_ERROR(-20000, 'ID cannot be specified');
  ELSE
    SELECT dept_seq.NEXTVAL
    INTO   :new.id
    FROM   dual;
  END IF;
END;
/