Oracle 在函数或存储过程中执行一条插入语句并返回主键ID值

时间:2022-01-07 17:33:43

有时,我们需要往一张表插入一条记录,同时返回主键ID值。

假定主键ID的值都是通过对应表的SEQUENCE来获得,然后进行ID赋值

这里有几种情况需要注意:

1)如果建表语句含有主键ID的触发器,通过触发器来实现主键ID的自增,实现方式如下:

INSERT INTO GP_MONTH_BILL (
MONTH,
BONUS_VALUE,
CUR_WAY,
CUR_TIME,
STATUS,
IS_USE,
CREATE_TIME)
VALUES (
CUR_MONTH,
CUR_BONUS_VALUE,
'包函数固化',
SYSDATE,
'固化中',
1,
SYSDATE)
RETURNING ID
INTO CUR_MONTH_BILL_ID; --CUR_MONTH_BILL_ID为变量,接收返回的ID值

关键语法: INSERT INTO TABLE VALUES(,) RETURNING ID INTO  VAL

补充:此种情况,触发器的语句可能如下

CREATE OR REPLACE TRIGGER GPS.TR_EM_FILES 
before
insert on "GPS"."EM_FILES"
for each row
begin
select "GPS"."SQ_EM_FILES".nextval into :new."ID" from dual;
end;
/

oralce12c,主键是通过default设置SEQUENCE下一个值

CREATE TABLE C##EM.GP_TARGET_TYPE
(
ID
NUMBER(19) DEFAULT "C##EM"."ISEQ$$_124895".nextval NOT NULL, --DEFAULT默认值为seq的下个值
NAME NVARCHAR2(50) NOT NULL,
REMARK NVARCHAR2(
200),
CREATE_TIME DATE
NOT NULL,
CREATE_UID
NUMBER(19),
DELETE_UID
NUMBER(19),
DELETE_TIME DATE,
IS_DELETE
NUMBER(1) NOT NULL,
UPDATE_TIME DATE,
UPDATE_UID
NUMBER(19)
)

 

2)如果主键ID的值由用户自主获得SEQUENCE的值,然后赋值,实现方式可以如下:

SELECT YOUR_SEQ.NEXTVAL  INTO MID_ID FROM DUAL; --MID_ID为变量

INSERT INTO TABLE(ID) VALUES(MID_ID);

补充:此种情况,可以有触发器,语句可能如下:

CREATE OR REPLACE TRIGGER C##WL.TR_BULLETIN BEFORE INSERT
ON C##WL.WL_BULLETIN
FOR EACH ROW
WHEN (
NEW.ID
IS NULL OR NEW.ID = 0
)
BEGIN
SELECT WL_BULLETIN_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

当前插入的主键ID为空值或0时,获取对应SEQUENCE 的NEXTVAL值来赋值主键ID