用select子句在oracle中编写一个返回id的插入语句 [英] Write an insert statement with select clause returning id in oracle
本文介绍了用select子句在oracle中编写一个返回id的插入语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试按如下方式编写查询:
I am trying to write a query as follows:
例如:
DECLARE
V_Output varchar(20):='';
BEGIN
INSERT INTO T(ID, MY_PK, NAME, VALUE)
(SELECT ID, NEXT_TRAN_VALUE('T'), NAME, VALUE FROM T WHERE MY_PK = 'NO0000000000013');
RETURNING MY_PK INTO V_Output;
DBMS_OUTPUT.PUT(V_Output);
END;
使用下面的函数
create or replace FUNCTION NEXT_TRAN_VALUE (field IN VARCHAR2) RETURN
VARCHAR2
IS
n_value VARCHAR2 (20);
P_APR VARCHAR2(3);
CURSOR rec_exists IS SELECT * FROM TRANSACTION_SEQ where SEQ_NAME = field ;
jk_seq_rec TRANSACTION_SEQ%ROWTYPE;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT LC_CODE into P_APR FROM LOCATION_CONFIG;
OPEN rec_exists;
FETCH rec_exists INTO jk_seq_rec;
IF rec_exists%FOUND THEN
UPDATE TRANSACTION_SEQ SET curr_value = curr_value + 1 WHERE SEQ_NAME = field;
COMMIT;
END IF;
--SELECT curr_value INTO n_value FROM TRANSACTION_SEQ WHERE SEQ_NAME = field;
END;
但是,它显示错误.
select 语句总是返回要插入的单个语句,所以在我看来它应该返回插入行的 my_pk
.
the select statement shall always return single statement to be inserted, so in my opinion it should return the my_pk
of inserted row.
推荐答案
我使用 Cursor
来解决问题.
I use Cursor
to resolve the issue.
这篇关于用select子句在oracle中编写一个返回id的插入语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文