AutoNumber And Identity Functionality (Pre 12c)
and 12C identity Pre
2023-09-14 09:13:31 时间
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;
/
相关文章
- STA分析(二) multi_cycle and false
- Creating PDF Reports with Pandas, Jinja and WeasyPrint
- Automatically generating nice graphs at end of your Load Test with Apache JMeter and JMeter-Plugins
- [Functional Programming] liftA2 and converge
- [React] Define defaultProps and PropTypes as static methods in class component
- [TypeScript] Configuring TypeScript Which Files to Compile with "Files" and "OutDir"
- [React] Scaffold a React application with pnpm and vite
- [AngularJS] Store the entry url and redirect to entry url after Logged in
- [Javascript] Identify and Deal with NaN in JavaScript
- [RxJS] Creation operators: interval and timer
- [D3] 14. Line and Area Charts with D3
- [AngularJS]3. ng-hide and ng-show
- Linux CentOS 6.x报错解决:错误:Cannot retrieve metalink for repository: epel. Please verify its path and tr
- UI5 repository mapping and Component-preload.js UI5RepositoryPathMapping.xml
- atitit.软件gui按钮and面板---os区-----软链接,快捷方式
- 【Codeforces 1118D1】Coffee and Coursework (Easy version)
- 【codeforces 791C】Bear and Different Names
- 【codeforces 760A】Petr and a calendar
- 已解决If this call came from a _pb2.py file, your generated code is out of date and must be regenerated
- WSMBT and WSMBS TCP/IP 控制与调试