oracle 的语句用法,ORACLE RETURNING 语句的使用方法

1.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.

DROPTABLEt1;

DROPSEQUENCEt1_seq;

CREATETABLEt1 (

IDNUMBER(10),

DESCRIPTIONVARCHAR2(50),

CONSTRAINTt1_pkPRIMARYKEY(id));

CREATESEQUENCEt1_seq;

INSERTINTOt1VALUES(t1_seq.nextval,'ONE');

INSERTINTOt1VALUES(t1_seq.nextval,'TWO');

INSERTINTOt1VALUES(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.

SETSERVEROUTPUTON

DECLARE

v_id t1.id%TYPE;

BEGIN

INSERTINTOt1

VALUES(t1_seq.nextval,'FOUR')

RETURNINGidINTOv_id;

COMMIT;

DBMS_OUTPUT.put_line('ID='|| v_id);

END;

/

ID=4

3.The syntax is also available for update and delete statements.

SETSERVEROUTPUTON

DECLARE

v_id t1.id%TYPE;

BEGIN

UPDATEt1

SETdescription=description

WHEREdescription='FOUR'

RETURNINGidINTOv_id;

DBMS_OUTPUT.put_line('UPDATE ID='|| v_id);

DELETEFROMt1

WHEREdescription='FOUR'

RETURNINGidINTOv_id;

DBMS_OUTPUT.put_line('DELETE ID='|| v_id);

COMMIT;

END;

/

UPDATEID=4

DELETEID=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.

SETSERVEROUTPUTON

DECLARE

TYPEt_tabISTABLEOFt1.id%TYPE;

v_tab t_tab;

BEGIN

UPDATEt1

SETdescription=description

RETURNINGidBULKCOLLECTINTOv_tab;

FORiINv_tab.first.. l_tab.lastLOOP

DBMS_OUTPUT.put_line('UPDATE ID='|| l_tab(i));

ENDLOOP;

COMMIT;

END;

/

UPDATEID=1

UPDATEID=2

UPDATEID=3

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

SETSERVEROUTPUTON

DECLARE

TYPEt_desc_tabISTABLEOFt1.description%TYPE;

TYPEt_tabISTABLEOFt1%ROWTYPE;

l_desc_tab t_desc_tab := t_desc_tab('FIVE','SIX','SEVEN');

l_tab t_tab;

BEGIN

FORALLiINl_desc_tab.first.. l_desc_tab.last

INSERTINTOt1

VALUES(t1_seq.nextval, l_desc_tab(i))

RETURNINGid,descriptionBULKCOLLECTINTOl_tab;

FORiINl_tab.first.. l_tab.lastLOOP

DBMS_OUTPUT.put_line('INSERT ID='|| l_tab(i).id||' DESC='|| l_tab(i).description);

ENDLOOP;

COMMIT;

END;

/

INSERTID=5DESC=FIVE

INSERTID=6DESC=SIX

INSERTID=7DESC=SEVEN

6.This functionality is also available from dymanic SQL.

SETSERVEROUTPUTON

DECLARE

TYPEt_tabISTABLEOFt1.id%TYPE;

l_tab t_tab;

BEGIN

EXECUTEIMMEDIATE'UPDATE t1

SET description = description

RETURNING id INTO :l_tab'

RETURNINGBULKCOLLECTINTOl_tab;

FORiINl_tab.first.. l_tab.lastLOOP

DBMS_OUTPUT.put_line('UPDATE ID='|| l_tab(i));

ENDLOOP;

COMMIT;

END;

/

UPDATEID=1

UPDATEID=2

UPDATEID=3