oracle insert returning,Oracle RETURNING INTO 用法示例

蔚落 2022-10-21 03:58 130阅读 0赞

The RETURNING INTO clause allows us to return column values for rows affected by DML statements. The following test table is used to demonstrate this clause.

DROP TABLE t1;

DROP SEQUENCE t1\_seq;

CREATE TABLE t1 (

id NUMBER(10),

description VARCHAR2(50),

CONSTRAINT t1\_pk PRIMARY KEY (id)

);

CREATE SEQUENCE t1\_seq;

INSERT INTO t1 VALUES (t1\_seq.nextval, 'ONE');

INSERT INTO t1 VALUES (t1\_seq.nextval, 'TWO');

INSERT INTO t1 VALUES (t1\_seq.nextval, 'THREE');

COMMIT;

When we insert data using a sequence to generate our primary key value, we can return the primary key value as follows.

SET SERVEROUTPUT ON

DECLARE

l\_id t1.id%TYPE;

BEGIN

INSERT INTO t1 VALUES (t1\_seq.nextval, 'FOUR')

RETURNING id INTO l\_id;

COMMIT;

DBMS\_OUTPUT.put\_line('ID=' || l\_id);

END;

ID=4

PL/SQL procedure successfully completed.

The syntax is also available for update and delete statements.

SET SERVEROUTPUT ON

DECLARE

l\_id t1.id%TYPE;

BEGIN

UPDATE t1

SET description = description

WHERE description = 'FOUR'

RETURNING id INTO l\_id;

DBMS\_OUTPUT.put\_line('UPDATE ID=' || l\_id);

DELETE FROM t1

WHERE description = 'FOUR'

RETURNING id INTO l\_id;

DBMS\_OUTPUT.put\_line('DELETE ID=' || l\_id);

COMMIT;

END;

UPDATE ID=4

DELETE ID=4

PL/SQL procedure successfully completed.

When DML affects multiple rows we can still use the RETURNING INTO, but now we must return the values into a collection using theBULK COLLECT clause.

SET SERVEROUTPUT ON

DECLARE

TYPE t\_tab IS TABLE OF t1.id%TYPE;

l\_tab t\_tab;

BEGIN

UPDATE t1

SET description = description

RETURNING id BULK COLLECT INTO l\_tab;

FOR i IN l\_tab.first .. l\_tab.last LOOP

DBMS\_OUTPUT.put\_line('UPDATE ID=' || l\_tab(i));

END LOOP;

COMMIT;

END;

UPDATE ID=1

UPDATE ID=2

UPDATE ID=3

PL/SQL procedure successfully completed.

SQL>

We can also use the RETURNING INTO clause in combination with bulk binds.

SET SERVEROUTPUT ON

DECLARE

TYPE t\_desc\_tab IS TABLE OF t1.description%TYPE;

TYPE t\_tab IS TABLE OF t1%ROWTYPE;

l\_desc\_tab t\_desc\_tab := t\_desc\_tab('FIVE', 'SIX', 'SEVEN');

l\_tab t\_tab;

BEGIN

FORALL i IN l\_desc\_tab.first .. l\_desc\_tab.last

INSERT INTO t1 VALUES (t1\_seq.nextval, l\_desc\_tab(i))

RETURNING id, description BULK COLLECT INTO l\_tab;

FOR i IN l\_tab.first .. l\_tab.last LOOP

DBMS\_OUTPUT.put\_line('INSERT ID=' || l\_tab(i).id ||

' DESC=' || l\_tab(i).description);

END LOOP;

COMMIT;

END;

INSERT ID=5 DESC=FIVE

INSERT ID=6 DESC=SIX

INSERT ID=7 DESC=SEVEN

PL/SQL procedure successfully completed.

SQL>

This functionality is also available from dymanic SQL.

SET SERVEROUTPUT ON

DECLARE

TYPE t\_tab IS TABLE OF t1.id%TYPE;

l\_tab t\_tab;

BEGIN

EXECUTE IMMEDIATE 'UPDATE t1

SET description = description

RETURNING id INTO :l\_tab'

RETURNING BULK COLLECT INTO l\_tab;

FOR i IN l\_tab.first .. l\_tab.last LOOP

DBMS\_OUTPUT.put\_line('UPDATE ID=' || l\_tab(i));

END LOOP;

COMMIT;

END;

UPDATE ID=1

UPDATE ID=2

UPDATE ID=3

PL/SQL procedure successfully completed.

SQL>

For more information see:

oracle insert returning,Oracle RETURNING INTO 用法示例

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracel merge into ,insert into

相关 oracle insert into select 很慢,insert into 疑问：select 很快，但是insert into ** select 性能很慢...

相关 insert oracle速度,[Oracle] Insert into速度测试nologging+parallel

相关 mysql insert into, insert ignore into, insert into on duplicate key update

相关 mysql replace into 与 insert into on duplicate key update、insert ignore、insert into

相关 oracle insert returning,Oracle RETURNING INTO 用法示例

相关 Oracle 批量插入（insert all into）

相关 INSERT IGNORE 与INSERT INTO与 REPLACE INTO

相关 oracle merge into 用法详解

相关 Oracle中insert into select和select into的用法(转)