3423565e2ed774dc21.png (10.96 KB, 下载次数: 101)
2024-3-2 17:12 上传
ALWAYS
If you specify ALWAYS, then Oracle Database always uses the sequence generator to assign avalue to the column.
If you attempt to explicitly assign a value to the column using INSERT orUPDATE, then an error will be returned.
This is the default.
BY DEFAULT
If you specify BY DEFAULT, then Oracle Database uses the sequence generator to assign avalue to the column by default,
but you can also explicitly assign a specified value to thecolumn.
If you specify ON NULL, then Oracle Database uses the sequence generator to assigna value to the column when a subsequent INSERT statement attempts to assign a value thatevaluates to NULL.
identity_options
Use the identity_options clause to configure the sequence generator. Theidentity_options clause has the same parameters as the CREATE SEQUENCE statement.
Refer to CREATE SEQUENCE for a full description of these parameters and characteristics.
The exception is START WITH LIMIT VALUE, which is specific to identity_options and canonly be used with ALTER TABLE MODIFY.
Refer to identity_options for more information.
下面举一个例子：
alter table employees add (empno number GENERATED AS IDENTITY);
Table altered.
HR@pdbprod1> desc employees
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
EMPNO NOT NULL NUMBER
SQL> select employee_id,last_name,email,hire_date,job_id,empno from employees
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
100 King SKING 17-JUN-03 AD_PRES 1
101 Kochhar NKOCHHAR 21-SEP-05 AD_VP 2
102 De Haan LDEHAAN 13-JAN-01 AD_VP 3
103 Hunold AHUNOLD 03-JAN-06 IT_PROG 4
104 Ernst BERNST 21-MAY-07 IT_PROG 5
105 Austin DAUSTIN 25-JUN-05 IT_PROG 6
106 Pataballa VPATABAL 05-FEB-06 IT_PROG 7
107 Lorentz DLORENTZ 07-FEB-07 IT_PROG 8
108 Greenberg NGREENBE 17-AUG-02 FI_MGR 9
109 Faviet DFAVIET 16-AUG-02 FI_ACCOUNT 10
110 Chen JCHEN 28-SEP-05 FI_ACCOUNT 11
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
111 Sciarra ISCIARRA 30-SEP-05 FI_ACCOUNT 12
112 Urman JMURMAN 07-MAR-06 FI_ACCOUNT 13
113 Popp LPOPP 07-DEC-07 FI_ACCOUNT 14
114 Raphaely DRAPHEAL 07-DEC-02 PU_MAN 15
115 Khoo AKHOO 18-MAY-03 PU_CLERK 16
116 Baida SBAIDA 24-DEC-05 PU_CLERK 17
117 Tobias STOBIAS 24-JUL-05 PU_CLERK 18
118 Himuro GHIMURO 15-NOV-06 PU_CLERK 19
119 Colmenares KCOLMENA 10-AUG-07 PU_CLERK 20
120 Weiss MWEISS 18-JUL-04 ST_MAN 21
121 Fripp AFRIPP 10-APR-05 ST_MAN 22
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
122 Kaufling PKAUFLIN 01-MAY-03 ST_MAN 23
123 Vollman SVOLLMAN 10-OCT-05 ST_MAN 24
124 Mourgos KMOURGOS 16-NOV-07 ST_MAN 25
125 Nayer JNAYER 16-JUL-05 ST_CLERK 26
126 Mikkilineni IMIKKILI 28-SEP-06 ST_CLERK 27
127 Landry JLANDRY 14-JAN-07 ST_CLERK 28
128 Markle SMARKLE 08-MAR-08 ST_CLERK 29
129 Bissot LBISSOT 20-AUG-05 ST_CLERK 30
130 Atkinson MATKINSO 30-OCT-05 ST_CLERK 31
131 Marlow JAMRLOW 16-FEB-05 ST_CLERK 32
132 Olson TJOLSON 10-APR-07 ST_CLERK 33
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
133 Mallin JMALLIN 14-JUN-04 ST_CLERK 34
134 Rogers MROGERS 26-AUG-06 ST_CLERK 35
135 Gee KGEE 12-DEC-07 ST_CLERK 36
136 Philtanker HPHILTAN 06-FEB-08 ST_CLERK 37
137 Ladwig RLADWIG 14-JUL-03 ST_CLERK 38
138 Stiles SSTILES 26-OCT-05 ST_CLERK 39
139 Seo JSEO 12-FEB-06 ST_CLERK 40
140 Patel JPATEL 06-APR-06 ST_CLERK 41
141 Rajs TRAJS 17-OCT-03 ST_CLERK 42
142 Davies CDAVIES 29-JAN-05 ST_CLERK 43
143 Matos RMATOS 15-MAR-06 ST_CLERK 44
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
144 Vargas PVARGAS 09-JUL-06 ST_CLERK 45
145 Russell JRUSSEL 01-OCT-04 SA_MAN 46
146 Partners KPARTNER 05-JAN-05 SA_MAN 47
147 Errazuriz AERRAZUR 10-MAR-05 SA_MAN 48
148 Cambrault GCAMBRAU 15-OCT-07 SA_MAN 49
149 Zlotkey EZLOTKEY 29-JAN-08 SA_MAN 50
150 Tucker PTUCKER 30-JAN-05 SA_REP 51
151 Bernstein DBERNSTE 24-MAR-05 SA_REP 52
152 Hall PHALL 20-AUG-05 SA_REP 53
153 Olsen COLSEN 30-MAR-06 SA_REP 54
154 Cambrault NCAMBRAU 09-DEC-06 SA_REP 55
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
155 Tuvault OTUVAULT 23-NOV-07 SA_REP 56
156 King JKING 30-JAN-04 SA_REP 57
157 Sully PSULLY 04-MAR-04 SA_REP 58
158 McEwen AMCEWEN 01-AUG-04 SA_REP 59
159 Smith LSMITH 10-MAR-05 SA_REP 60
160 Doran LDORAN 15-DEC-05 SA_REP 61
161 Sewall SSEWALL 03-NOV-06 SA_REP 62
162 Vishney CVISHNEY 11-NOV-05 SA_REP 63
163 Greene DGREENE 19-MAR-07 SA_REP 64
164 Marvins MMARVINS 24-JAN-08 SA_REP 65
165 Lee DLEE 23-FEB-08 SA_REP 66
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
166 Ande SANDE 24-MAR-08 SA_REP 67
167 Banda ABANDA 21-APR-08 SA_REP 68
168 Ozer LOZER 11-MAR-05 SA_REP 69
169 Bloom HBLOOM 23-MAR-06 SA_REP 70
170 Fox TFOX 24-JAN-06 SA_REP 71
171 Smith WSMITH 23-FEB-07 SA_REP 72
172 Bates EBATES 24-MAR-07 SA_REP 73
173 Kumar SKUMAR 21-APR-08 SA_REP 74
174 Abel EABEL 11-MAY-04 SA_REP 75
175 Hutton AHUTTON 19-MAR-05 SA_REP 76
176 Taylor JTAYLOR 24-MAR-06 SA_REP 77
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
177 Livingston JLIVINGS 23-APR-06 SA_REP 78
178 Grant KGRANT 24-MAY-07 SA_REP 79
179 Johnson CJOHNSON 04-JAN-08 SA_REP 80
180 Taylor WTAYLOR 24-JAN-06 SH_CLERK 81
181 Fleaur JFLEAUR 23-FEB-06 SH_CLERK 82
182 Sullivan MSULLIVA 21-JUN-07 SH_CLERK 83
183 Geoni GGEONI 03-FEB-08 SH_CLERK 84
184 Sarchand NSARCHAN 27-JAN-04 SH_CLERK 85
185 Bull ABULL 20-FEB-05 SH_CLERK 86
186 Dellinger JDELLING 24-JUN-06 SH_CLERK 87
187 Cabrio ACABRIO 07-FEB-07 SH_CLERK 88
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
188 Chung KCHUNG 14-JUN-05 SH_CLERK 89
189 Dilly JDILLY 13-AUG-05 SH_CLERK 90
190 Gates TGATES 11-JUL-06 SH_CLERK 91
191 Perkins RPERKINS 19-DEC-07 SH_CLERK 92
192 Bell SBELL 04-FEB-04 SH_CLERK 93
193 Everett BEVERETT 03-MAR-05 SH_CLERK 94
194 McCain SMCCAIN 01-JUL-06 SH_CLERK 95
195 Jones VJONES 17-MAR-07 SH_CLERK 96
196 Walsh AWALSH 24-APR-06 SH_CLERK 97
197 Feeney KFEENEY 23-MAY-06 SH_CLERK 98
198 OConnell DOCONNEL 21-JUN-07 SH_CLERK 99
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
199 Grant DGRANT 13-JAN-08 SH_CLERK 100
200 Whalen JWHALEN 17-SEP-03 AD_ASST 101
201 Hartstein MHARTSTE 17-FEB-04 MK_MAN 102
202 Fay PFAY 17-AUG-05 MK_REP 103
203 Mavris SMAVRIS 07-JUN-02 HR_REP 104
204 Baer HBAER 07-JUN-02 PR_REP 105
205 Higgins SHIGGINS 07-JUN-02 AC_MGR 106
206 Gietz WGIETZ 07-JUN-02 AC_ACCOUNT 107
107 rows selected.
sql> insert into employees (employee_id,last_name,email,hire_date,job_id)
2 values(300,'test1','test1',sysdate,'SA_REP');
HR@pdbprod1> select employee_id,last_name,email,hire_date,job_id,empno from employees;
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
100 King SKING 17-JUN-03 AD_PRES 1
101 Kochhar NKOCHHAR 21-SEP-05 AD_VP 2
102 De Haan LDEHAAN 13-JAN-01 AD_VP 3
103 Hunold AHUNOLD 03-JAN-06 IT_PROG 4
104 Ernst BERNST 21-MAY-07 IT_PROG 5
105 Austin DAUSTIN 25-JUN-05 IT_PROG 6
106 Pataballa VPATABAL 05-FEB-06 IT_PROG 7
107 Lorentz DLORENTZ 07-FEB-07 IT_PROG 8
108 Greenberg NGREENBE 17-AUG-02 FI_MGR 9
109 Faviet DFAVIET 16-AUG-02 FI_ACCOUNT 10
110 Chen JCHEN 28-SEP-05 FI_ACCOUNT 11
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
111 Sciarra ISCIARRA 30-SEP-05 FI_ACCOUNT 12
112 Urman JMURMAN 07-MAR-06 FI_ACCOUNT 13
113 Popp LPOPP 07-DEC-07 FI_ACCOUNT 14
114 Raphaely DRAPHEAL 07-DEC-02 PU_MAN 15
115 Khoo AKHOO 18-MAY-03 PU_CLERK 16
116 Baida SBAIDA 24-DEC-05 PU_CLERK 17
117 Tobias STOBIAS 24-JUL-05 PU_CLERK 18
118 Himuro GHIMURO 15-NOV-06 PU_CLERK 19
119 Colmenares KCOLMENA 10-AUG-07 PU_CLERK 20
120 Weiss MWEISS 18-JUL-04 ST_MAN 21
121 Fripp AFRIPP 10-APR-05 ST_MAN 22
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
122 Kaufling PKAUFLIN 01-MAY-03 ST_MAN 23
123 Vollman SVOLLMAN 10-OCT-05 ST_MAN 24
124 Mourgos KMOURGOS 16-NOV-07 ST_MAN 25
125 Nayer JNAYER 16-JUL-05 ST_CLERK 26
126 Mikkilineni IMIKKILI 28-SEP-06 ST_CLERK 27
127 Landry JLANDRY 14-JAN-07 ST_CLERK 28
128 Markle SMARKLE 08-MAR-08 ST_CLERK 29
129 Bissot LBISSOT 20-AUG-05 ST_CLERK 30
130 Atkinson MATKINSO 30-OCT-05 ST_CLERK 31
131 Marlow JAMRLOW 16-FEB-05 ST_CLERK 32
132 Olson TJOLSON 10-APR-07 ST_CLERK 33
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
133 Mallin JMALLIN 14-JUN-04 ST_CLERK 34
134 Rogers MROGERS 26-AUG-06 ST_CLERK 35
135 Gee KGEE 12-DEC-07 ST_CLERK 36
136 Philtanker HPHILTAN 06-FEB-08 ST_CLERK 37
137 Ladwig RLADWIG 14-JUL-03 ST_CLERK 38
138 Stiles SSTILES 26-OCT-05 ST_CLERK 39
139 Seo JSEO 12-FEB-06 ST_CLERK 40
140 Patel JPATEL 06-APR-06 ST_CLERK 41
141 Rajs TRAJS 17-OCT-03 ST_CLERK 42
142 Davies CDAVIES 29-JAN-05 ST_CLERK 43
143 Matos RMATOS 15-MAR-06 ST_CLERK 44
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
144 Vargas PVARGAS 09-JUL-06 ST_CLERK 45
145 Russell JRUSSEL 01-OCT-04 SA_MAN 46
146 Partners KPARTNER 05-JAN-05 SA_MAN 47
147 Errazuriz AERRAZUR 10-MAR-05 SA_MAN 48
148 Cambrault GCAMBRAU 15-OCT-07 SA_MAN 49
149 Zlotkey EZLOTKEY 29-JAN-08 SA_MAN 50
150 Tucker PTUCKER 30-JAN-05 SA_REP 51
151 Bernstein DBERNSTE 24-MAR-05 SA_REP 52
152 Hall PHALL 20-AUG-05 SA_REP 53
153 Olsen COLSEN 30-MAR-06 SA_REP 54
154 Cambrault NCAMBRAU 09-DEC-06 SA_REP 55
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
155 Tuvault OTUVAULT 23-NOV-07 SA_REP 56
156 King JKING 30-JAN-04 SA_REP 57
157 Sully PSULLY 04-MAR-04 SA_REP 58
158 McEwen AMCEWEN 01-AUG-04 SA_REP 59
159 Smith LSMITH 10-MAR-05 SA_REP 60
160 Doran LDORAN 15-DEC-05 SA_REP 61
161 Sewall SSEWALL 03-NOV-06 SA_REP 62
162 Vishney CVISHNEY 11-NOV-05 SA_REP 63
163 Greene DGREENE 19-MAR-07 SA_REP 64
164 Marvins MMARVINS 24-JAN-08 SA_REP 65
165 Lee DLEE 23-FEB-08 SA_REP 66
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
166 Ande SANDE 24-MAR-08 SA_REP 67
167 Banda ABANDA 21-APR-08 SA_REP 68
168 Ozer LOZER 11-MAR-05 SA_REP 69
169 Bloom HBLOOM 23-MAR-06 SA_REP 70
170 Fox TFOX 24-JAN-06 SA_REP 71
171 Smith WSMITH 23-FEB-07 SA_REP 72
172 Bates EBATES 24-MAR-07 SA_REP 73
173 Kumar SKUMAR 21-APR-08 SA_REP 74
174 Abel EABEL 11-MAY-04 SA_REP 75
175 Hutton AHUTTON 19-MAR-05 SA_REP 76
176 Taylor JTAYLOR 24-MAR-06 SA_REP 77
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
177 Livingston JLIVINGS 23-APR-06 SA_REP 78
178 Grant KGRANT 24-MAY-07 SA_REP 79
179 Johnson CJOHNSON 04-JAN-08 SA_REP 80
180 Taylor WTAYLOR 24-JAN-06 SH_CLERK 81
181 Fleaur JFLEAUR 23-FEB-06 SH_CLERK 82
182 Sullivan MSULLIVA 21-JUN-07 SH_CLERK 83
183 Geoni GGEONI 03-FEB-08 SH_CLERK 84
184 Sarchand NSARCHAN 27-JAN-04 SH_CLERK 85
185 Bull ABULL 20-FEB-05 SH_CLERK 86
186 Dellinger JDELLING 24-JUN-06 SH_CLERK 87
187 Cabrio ACABRIO 07-FEB-07 SH_CLERK 88
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
188 Chung KCHUNG 14-JUN-05 SH_CLERK 89
189 Dilly JDILLY 13-AUG-05 SH_CLERK 90
190 Gates TGATES 11-JUL-06 SH_CLERK 91
191 Perkins RPERKINS 19-DEC-07 SH_CLERK 92
192 Bell SBELL 04-FEB-04 SH_CLERK 93
193 Everett BEVERETT 03-MAR-05 SH_CLERK 94
194 McCain SMCCAIN 01-JUL-06 SH_CLERK 95
195 Jones VJONES 17-MAR-07 SH_CLERK 96
196 Walsh AWALSH 24-APR-06 SH_CLERK 97
197 Feeney KFEENEY 23-MAY-06 SH_CLERK 98
198 OConnell DOCONNEL 21-JUN-07 SH_CLERK 99
EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID EMPNO
----------- ------------------------- ------------------------- --------- ---------- ----------
199 Grant DGRANT 13-JAN-08 SH_CLERK 100
200 Whalen JWHALEN 17-SEP-03 AD_ASST 101
201 Hartstein MHARTSTE 17-FEB-04 MK_MAN 102
202 Fay PFAY 17-AUG-05 MK_REP 103
203 Mavris SMAVRIS 07-JUN-02 HR_REP 104
204 Baer HBAER 07-JUN-02 PR_REP 105
205 Higgins SHIGGINS 07-JUN-02 AC_MGR 106
206 Gietz WGIETZ 07-JUN-02 AC_ACCOUNT 107
300 test1 test1 02-MAR-24 SA_REP 108
108 rows selected.
HR@pdbprod1> insert into employees (employee_id,last_name,email,hire_date,job_id,EMPNO)
2 values(300,'test1','test1',sysdate,'SA_REP',109);
insert into employees (employee_id,last_name,email,hire_date,job_id,EMPNO)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |