记忆角落

  • {{ item.name }}
  • 首页
  • 关于
  • 归档
  • 邻居
  • 捐赠

oracle设置id自增sql

  • 郭良俊只狗
  • 2018-09-30
  • 0

记录自己的步骤...

---创建SEQUENCE实现自增长,SEQUENCE的命名规范(重要):表名_SEQ
CREATE SEQUENCE IOT_BASE_CM_SEQ  
MINVALUE 1  
MAXVALUE 99999999
START WITH 1  
INCREMENT BY 1;

-- 创建INSERT操作创建触发器,命名规范(重要):名称为表名_TRG
CREATE OR REPLACE TRIGGER IOT_BASE_CM_TRG BEFORE INSERT ON IOT_BASE_CM/*源表*/ FOR EACH ROW WHEN(NEW.ID IS NULL)
BEGIN
  /** 
  1、(TO_CHAR(IOT_base_INDUSTRY_SEQ.NEXTVAL),4,'0') 中的4指4位数字,不足4位前面补充0,
  2、IOT_base_INDUSTRY_SEQ.NEXTVAL是具体表的序列,需要根据实际修改(重要)
  */
SELECT LPAD(TO_CHAR(IOT_BASE_CM_SEQ.NEXTVAL),8,'0') INTO :NEW.ID FROM DUAL;
END;
--查看当前序列
SELECT IOT_BASE_CM_SEQ.CURRVAL FROM DUAL;
--查看下一个序列
SELECT IOT_BASE_CM_SEQ.NEXTVAL FROM DUAL;

例子:
步骤一:
CREATE SEQUENCE IOT_TAKE_OPER_LOGS_SEQ  
MINVALUE 1  
MAXVALUE 99999999
START WITH 1  
INCREMENT BY 1;
步骤二;
CREATE OR REPLACE TRIGGER IOT_TAKE_OPER_LOGS_TRG 
BEFORE INSERT ON IOT_TAKE_OPER_LOGS/*原表名称*/ FOR EACH ROW WHEN(NEW.LOGID IS NULL)
BEGIN
SELECT IOT_TAKE_OPER_LOGS_SEQ.NEXTVAL INTO :NEW.LOGID FROM DUAL;
END;
--查看当前序列
SELECT IOT_TAKE_OPER_LOGS_SEQ.CURRVAL FROM DUAL;
--查看下一个序列
SELECT IOT_TAKE_OPER_LOGS_SEQ.NEXTVAL FROM DUAL;
ps:
Oracle刚创建完序列后用currval查询不了当前值的,得先用nextval查询过后才能进行currval的查询。
不然报ORA-08002错误:序列 XXXX.CURRVAL 尚未在此会话中定义

© 2012 - 2023 记忆角落 网站统计
Theme by Wing
粤ICP备14056850号-1 又拍云CDN赞助