oracle如何创建表的自增ID(通过触发器)

时间:2022-05-21 12:14:14

Oracle中创建表的自增ID(通过触发器),序列的自增ID和触发器的自增ID的区别

1、新增数据(序列)

--创建示例表 --
create table Student(
stuId number(9) not null,
stuName varchar2(20) not null,
stuMsg varchar2(50) null
) -- 创建序列 Student_StuId_Seq --
create sequence Student_StuId_Seq
increment by 1
start with 1
minvalue 1
maxvalue 999999999; --调用序列 --
select Student_StuId_Seq.Nextval 序列号 from dual; --插入数据(序列)--
insert into Student(STUID,STUNAME) values(Student_StuId_Seq.Nextval,'王五');

2、新增数据(触发器)

--创建示例表 --
create table Student(
stuId number(9) not null,
stuName varchar2(20) not null,
stuMsg varchar2(50) null
) -- 创建序列 Student_StuId_Seq --
create sequence Student_StuId_Seq
increment by 1
start with 1
minvalue 1
maxvalue 999999999; --创建触发器 --
create or replace trigger Student_StuId_Trigger
before insert on Student ----(sysrole为表名)
for each row----触发每一行
begin
select Student_StuId_Seq.Nextval into :new.StuId from dual;
end; --新增数据(触发器)--
insert into Student(STUNAME) values('赵六');

3、查询结果

--查询数据--
select * from Student;

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAATEAAABOCAIAAADHBLafAAAJz0lEQVR4nO2dPa6DvBKGvSdLFGwlFFSsw51blnAUl9atrkT7RUJuvQHKT+nR3QC3AAzYxpBAsIF55SKHPzOTeTJm7OSgekYI/aO1uq7/QWhu1xb9/f01TbPxIqAaPLmrfDkTufYZsLVMuo/5QhBJewk8uaNCZPIwQSTtJfDkjgImIZJ2EHhyRwGTEEk7CDy5o27NJAgEUkJ1XfP//BcaNGiBtI7JBgQChaGPmSSE/O5uAulxi+q6Xn/wuUzbotu65QvDJ0yuGey2p/1sLB1Ejxu18m7//v5OZ9oW3dYtnxoOTO6v2wafW7d1CzDpX7cNPrdu65ZbMCl5GkWoVZRSTinvd/EUmYqorOta0v6clGtHtwfYzu4OHc7tTohSyuXMDW8Jvl+Y1u3tjZxcSx2vrDS3TE8deku5/Z60awTqFr5g2lK/ar+KiDSl1pC4AZM8jQZHSk6jkWMl7ffxtN8qaaRcTCM9XqTuZvthkyvWUtI00t9Ppe+D72emtbE23c+pdvuSplEU6UZJqlvKUz3KxxeWPLV5L0S3LJs232+tPsLk+A+bRZdncngPlHiq3gKuPqpGBA3RtxeT/d92Kr8Nvh+aJinlOjwGkzxNuW6kBec21cwy2V/JsDc8t7hNc/dbSxppruKp/UP6jkzWtZT9p6baMwmufv+uTFrelVY7Bt9epklKuX6/OpPtwIyn2mUkpemkw3Z452TSqvDc4jbN3a/NbFss1TdgsktPsw9zo8MsHt2VyblM+fUg7XemdS8ngaQx2R+tQ9ml2GhIPFE6Hp1qwWlkX6Xw3LJgmqvfuc9jm67PZPvc0z1zzzvlCCZn3pgNxYxfmda/HN/xlB51sH4hSSkfbeNpWx6xMynnxm91iG5ZMs3R7+rRQX0PJuu6rmvJlb+sb9M5mWwvu79pw8shmCZMSqr+0sZsklI+dClpV8g1KpnTkqdF4bllybT5fh0VHVO3YbJV5y+Ld07MZHfxPU3TSrARlRqTGlnROPy6x8yIylrSNOW1weSv8+TYqB3dsmSao18tT3Yl6yiKLJ8YN2OyruuZccRRz5M7Fhgt2su0iZXdRcdMTgqlUhu/Ua62tcfNM/mT50lTO7llyTRHv0YpQcpu0/aa39mYlNQycrG9GwcwOTcZ8rdah5k2tbJNlcNoVZ+7mFyrO1XSKKK0Pc7FZH+WGZvhucVp2kK/ptmzOH9q+NmYNGv189vs75CR6ywBtYZJOYfkBzrMNP2Th6fjAaqkWh/jgBvVY8frY5xMWuN5vY5yi9u0hX7NTOlMsat0UibbtRdDIUxyaq49qWfeof7ZfHyuJXiW1/Fwy7qYL3SYaQZ141gdLXsZd6Gm6fq8oc5wMin5B9MEdh3lFrdpi/3yFKFouPQnZR+n4adjsuY0pZzSfp2hrQw3KljYPjnnT/1kvevifFkwptkXt3bVGGXZsHPoI+Xd6/ZRK52c0D6E6Q6z9BS2W+ZMW9PvdDkstwzYvzH8fExeSRc2bYtu6xZg0r8ubNoW3dYtwKR/Xdi0LbqtW3QmCQgE8i09T64ROfz3ixBCB/d4mF6vl+9bCFGv16tp3jdsbTx8OXb990ABk3cTMAlMetPhTFZ5llfHdjnWYnS1h12PyRWGA5Nh6GsmiywrpluqYoWyGCGE9FOPEzC5iUlk/KvJHzJZPh+P5zWZrEx8BplMrkIrX4NWlcce6bMLmNzKpBXLfZksyQNjhBBCl2OyyrM4Rm54VuXJL+Gq8jj2OU61CZjcgUkTy73zZFmW5fOBr8dk01RVVRWZC6hfMllkwaVJYHInJjUsfzF2LcklmWyaJaB+yGSIabJRoTX3oj3sqkw6Df+QyTGWwORH8sZkkaFQmXSmi6a5LpNOwz9nUmEJTH6kA5msKq0OFOdazTXOCr+UApN7Mqm2A5Mf6Qsm9dJrnk3xyrMV8xnG0DWIsSwwuRuT4+3A5EfyNXY1KjxBMLlS12NyZQuk7gpMbrvE7DkagcDkCVoQ85PA5NZL2GVOhARZ85kRMOl/HQ8w+f0l5k7RAQxxunJOwKT39a7l84EQJuUFmayKDDny06+YtAAITJ6gBcFkSfCkvGsj86RMVu2qVCUbmVuYrKrZkajt2XFgsig8z4gsCpj0nicXdFIm12jKZGVfgK7PhQwTIgo87URjbnLYlvdfDQkZS2ASmPQm+E6zVcAkMOlNwKRVwGTov5GFEHqBQPdQc5bfyHo370u21+vl/R4CbK/X63/N+4bNwmSwY1fvUfK74JvZJUiWsMr/Hfpyi3c8gElgUmuCxJg4mCwSnAvXxQuSFM4DfLTF6FJu8Y7Hvm3RcGAylLaKyUq8m7comBgdwDK0wGTzZhlCMRHr7uSYBkyegcny+VCrBvCDPMvrMFmxRK0aiBNiy1oGk0IUjBWMFSSJcZIzViQYoaR4vxtBctYfxhLkzKLdDRCMltE9sgGTwTNZEowfPYblk2DrQp5TMlkRHKsHQsFyjGwUfTZ2LZKk6F6sS4CCxAhlTNsu/I1pgcnQmXw+dASfD8sP2J2RSXNsyTILHiuZFJW6CCaVeXGVXfVGsiTJpxszjBBC2WQkDEwCkyMCp1BavyByUia1VCZyvMTkGC01dmVtju0yZEVwjDFKmG8Dv2ttCJm/EKVeKLd4p+gXTDoMD4XJf8vn8zlG0pI5T8rku2JsOkS0VmUc85Mix9iWzaz5Vm8FsT6+em+L0aXc4p2iXzDpUDBM6ojav0Z5Sia1VhFrcrMxKdqRqsj73DhpLEEYu6dJWm7RcDrLQ6m+ApPnYrIkD1XvuRiTswsALExWBCNMKjuTIsc4F0s1Hpbo5VZBsiCGu8DkiZgsycP1nWbvwbShCZLN8mNhsudN5BjnXVUGt4PVinQvGkHi2UkOkWMLsZM6cOjtekyubOEw+XQAeXImmQPIt41JVQoy8qQg8ZDrRI6RvdLDkm4+U2/zpwTXgEm/TD4fTiDPzCRLnEC+bUyqgq3GJNNGvzMPqCzD84sEBIkRik+AJTDpkUkTyJKQK8yF2IAcL8QZgs/AJmEVSzLCRkyKnJjDTlEJvdMiwe61BEWCAlvZY23ApC8mS4IfpJzoSfAl5kIEiRNSCTFqLLdkMJ1J9cRYsSRGyWfwsGR5cU+bKkMpw841YNIXk8+H+T9JbIvrTsgkS6ymLTHJJtVRwTKMEMbmWpyCsZwkWZLkagJzeZzcNuvShdAaMOm9xrOgEzL5QfANf1aMWGozQhSEZEmSYRxP2zASFiz3s1bud27xjgcwCUxCm7jFOx7AJDAJbeIW73gEweSxP3z1geA3skD3kZ4nQSCQdwGTIFBYAiZBoLAETIJAYQmYBIHCEjAJAoUlYBIECkv/BzMQaiXc7T7EAAAAAElFTkSuQmCC" alt="" />

PS: 序列可以理解成一个获取表的自增ID的函数(手动),触发器是当新增数据(自动)时,获取表的自增ID