Oracle RETURNING INTO 用法示例

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;
 
2.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
    v_id t1.id%TYPE;
BEGIN
    INSERT INTO t1
    VALUES (t1_seq.nextval, 'FOUR')
    RETURNING id INTO v_id;
    COMMIT;
    DBMS_OUTPUT.put_line('ID=' || v_id);
END;
/
ID=4 
 
3.The syntax is also available for update and delete statements.
 
SET SERVEROUTPUT ON
DECLARE
    v_id t1.id%TYPE;
BEGIN
    UPDATE t1
    SET description = description
    WHERE description = 'FOUR'
    RETURNING id INTO v_id;
    DBMS_OUTPUT.put_line('UPDATE ID=' || v_id);
 
    DELETE FROM t1
    WHERE description = 'FOUR'
    RETURNING id INTO v_id;
    DBMS_OUTPUT.put_line('DELETE ID=' || v_id);
    COMMIT;
END;
/
UPDATE ID=4
DELETE ID=4
 
4.When DML affects multiple rows we can still use the RETURNING INTO, but now we must return the values into a collection using the BULK COLLECT clause.
 
SET SERVEROUTPUT ON
DECLARE
    TYPE t_tab IS TABLE OF t1.id%TYPE;
    v_tab t_tab;
BEGIN
    UPDATE t1
    SET description = description
    RETURNING id BULK COLLECT INTO v_tab;
 
    FOR i IN v_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
 
5.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
    FOR 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;
 
    FORALL 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
 
6.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