{"id":163,"date":"2010-12-29T10:00:48","date_gmt":"2010-12-29T02:00:48","guid":{"rendered":"http:\/\/www.royalwzy.com\/?p=163"},"modified":"2013-12-30T10:58:18","modified_gmt":"2013-12-30T02:58:18","slug":"oracle-returning-into-%e7%94%a8%e6%b3%95%e7%a4%ba%e4%be%8b","status":"publish","type":"post","link":"http:\/\/www.royalwzy.com\/?p=163","title":{"rendered":"Oracle RETURNING INTO \u7528\u6cd5\u793a\u4f8b"},"content":{"rendered":"

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.<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

DROP<\/span>\u00a0TABLE<\/span>\u00a0t1;<\/span><\/span><\/p>\n

DROP<\/span>\u00a0SEQUENCE<\/span>\u00a0t1_seq;<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

CREATE<\/span>\u00a0TABLE<\/span>\u00a0t1 (<\/span><\/span><\/p>\n

ID<\/span>\u00a0<\/span>NUMBER<\/span>(<\/span>10<\/span>),<\/span><\/span><\/span><\/p>\n

DESCRIPTION<\/span>\u00a0<\/span>VARCHAR2<\/span>(<\/span>50<\/span>),<\/span><\/span><\/span><\/p>\n

CONSTRAINT<\/span>\u00a0t1_pk\u00a0PRIMARY<\/span>\u00a0KEY<\/span>\u00a0(id<\/span>));<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

CREATE<\/span>\u00a0SEQUENCE<\/span>\u00a0t1_seq;<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

INSERT<\/span>\u00a0<\/span>INTO<\/span>\u00a0t1\u00a0<\/span>VALUES<\/span>\u00a0(t1_seq.<\/span>nextval<\/span>,\u00a0<\/span>‘ONE’<\/span>);<\/span><\/span><\/span><\/p>\n

INSERT<\/span>\u00a0<\/span>INTO<\/span>\u00a0t1\u00a0<\/span>VALUES<\/span>\u00a0(t1_seq.<\/span>nextval<\/span>,\u00a0<\/span>‘TWO’<\/span>);<\/span><\/span><\/span><\/p>\n

INSERT<\/span>\u00a0<\/span>INTO<\/span>\u00a0t1\u00a0<\/span>VALUES<\/span>\u00a0(t1_seq.<\/span>nextval<\/span>,\u00a0<\/span>‘THREE’<\/span>);<\/span><\/span><\/span><\/p>\n

COMMIT<\/span>;<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

2.When we insert data using a sequence to generate our primary key value, we can return the primary key value as follows.<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

SET<\/span>\u00a0SERVEROUTPUT\u00a0ON<\/span><\/span><\/span><\/p>\n

DECLARE<\/span><\/span><\/p>\n

v_id t1.id<\/span>%TYPE<\/span>;<\/span><\/span><\/p>\n

BEGIN<\/span><\/span><\/p>\n

INSERT<\/span>\u00a0INTO<\/span>\u00a0t1<\/span><\/span><\/p>\n

VALUES<\/span>\u00a0(t1_seq.<\/span>nextval<\/span>,\u00a0<\/span>‘FOUR’<\/span>)<\/span><\/span><\/span><\/p>\n

RETURNING<\/span>\u00a0id<\/span>\u00a0INTO<\/span>\u00a0v_id;<\/span><\/span><\/p>\n

COMMIT<\/span>;<\/span><\/span><\/p>\n

DBMS_OUTPUT.put_line(<\/span>‘ID=’<\/span>\u00a0|| v_id);<\/span><\/span><\/span><\/p>\n

END<\/span>;<\/span><\/span><\/p>\n

\/<\/span><\/span><\/p>\n

ID<\/span>=<\/span>4<\/span>\u00a0<\/span><\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

3.The syntax is also available for update and delete statements.<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

SET<\/span>\u00a0SERVEROUTPUT\u00a0ON<\/span><\/span><\/span><\/p>\n

DECLARE<\/span><\/span><\/p>\n

v_id t1.id<\/span>%TYPE<\/span>;<\/span><\/span><\/p>\n

BEGIN<\/span><\/span><\/p>\n

UPDATE<\/span>\u00a0t1<\/span><\/span><\/p>\n

SET<\/span>\u00a0description<\/span>\u00a0=\u00a0description<\/span><\/span><\/span><\/p>\n

WHERE<\/span>\u00a0<\/span>description<\/span>\u00a0=\u00a0<\/span>‘FOUR’<\/span><\/span><\/span><\/p>\n

RETURNING<\/span>\u00a0id<\/span>\u00a0INTO<\/span>\u00a0v_id;<\/span><\/span><\/p>\n

DBMS_OUTPUT.put_line(<\/span>‘UPDATE ID=’<\/span>\u00a0|| v_id);<\/span><\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

DELETE<\/span>\u00a0FROM<\/span>\u00a0t1<\/span><\/span><\/p>\n

WHERE<\/span>\u00a0<\/span>description<\/span>\u00a0=\u00a0<\/span>‘FOUR’<\/span><\/span><\/span><\/p>\n

RETURNING<\/span>\u00a0id<\/span>\u00a0INTO<\/span>\u00a0v_id;<\/span><\/span><\/p>\n

DBMS_OUTPUT.put_line(<\/span>‘DELETE ID=’<\/span>\u00a0|| v_id);<\/span><\/span><\/span><\/p>\n

COMMIT<\/span>;<\/span><\/span><\/p>\n

END<\/span>;<\/span><\/span><\/p>\n

\/<\/span><\/span><\/p>\n

UPDATE<\/span>\u00a0<\/span>ID<\/span>=<\/span>4<\/span><\/span><\/span><\/p>\n

DELETE<\/span>\u00a0<\/span>ID<\/span>=<\/span>4<\/span><\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

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.<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

SET<\/span>\u00a0SERVEROUTPUT\u00a0ON<\/span><\/span><\/span><\/p>\n

DECLARE<\/span><\/span><\/p>\n

TYPE<\/span>\u00a0t_tab\u00a0IS<\/span>\u00a0TABLE<\/span>\u00a0OF<\/span>\u00a0t1.id<\/span>%TYPE<\/span>;<\/span><\/span><\/p>\n

v_tab t_tab;<\/span><\/span><\/p>\n

BEGIN<\/span><\/span><\/p>\n

UPDATE<\/span>\u00a0t1<\/span><\/span><\/p>\n

SET<\/span>\u00a0description<\/span>\u00a0=\u00a0description<\/span><\/span><\/span><\/p>\n

RETURNING<\/span>\u00a0id<\/span>\u00a0BULK<\/span>\u00a0COLLECT<\/span>\u00a0INTO<\/span>\u00a0v_tab;<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

FOR<\/span>\u00a0i\u00a0IN<\/span>\u00a0v_tab.first<\/span>\u00a0.. l_tab.last<\/span>\u00a0LOOP<\/span><\/span><\/span><\/p>\n

DBMS_OUTPUT.put_line(<\/span>‘UPDATE ID=’<\/span>\u00a0|| l_tab(i));<\/span><\/span><\/span><\/p>\n

END<\/span>\u00a0LOOP<\/span>;<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

COMMIT<\/span>;<\/span><\/span><\/p>\n

END<\/span>;<\/span><\/span><\/p>\n

\/<\/span><\/span><\/p>\n

UPDATE<\/span>\u00a0<\/span>ID<\/span>=<\/span>1<\/span><\/span><\/span><\/p>\n

UPDATE<\/span>\u00a0<\/span>ID<\/span>=<\/span>2<\/span><\/span><\/span><\/p>\n

UPDATE<\/span>\u00a0<\/span>ID<\/span>=<\/span>3<\/span><\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

5.We can also use the RETURNING INTO clause in combination with bulk binds.<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

SET<\/span>\u00a0SERVEROUTPUT\u00a0ON<\/span><\/span><\/span><\/p>\n

DECLARE<\/span><\/span><\/p>\n

TYPE<\/span>\u00a0t_desc_tab\u00a0IS<\/span>\u00a0TABLE<\/span>\u00a0OF<\/span>\u00a0t1.description<\/span>%TYPE<\/span>;<\/span><\/span><\/p>\n

TYPE<\/span>\u00a0t_tab\u00a0IS<\/span>\u00a0TABLE<\/span>\u00a0OF<\/span>\u00a0t1%ROWTYPE<\/span>;<\/span><\/span><\/p>\n

l_desc_tab t_desc_tab := t_desc_tab(<\/span>‘FIVE’<\/span>,\u00a0<\/span>‘SIX’<\/span>,\u00a0<\/span>‘SEVEN’<\/span>);<\/span><\/span><\/span><\/p>\n

l_tab t_tab;<\/span><\/span><\/p>\n

BEGIN<\/span><\/span><\/p>\n

FORALL<\/span>\u00a0i\u00a0IN<\/span>\u00a0l_desc_tab.first<\/span>\u00a0.. l_desc_tab.last<\/span><\/span><\/span><\/p>\n

INSERT<\/span>\u00a0INTO<\/span>\u00a0t1<\/span><\/span><\/p>\n

VALUES<\/span>\u00a0(t1_seq.nextval<\/span>, l_desc_tab(i))<\/span><\/span><\/p>\n

RETURNING<\/span>\u00a0id<\/span>,\u00a0description<\/span>\u00a0BULK<\/span>\u00a0COLLECT<\/span>\u00a0INTO<\/span>\u00a0l_tab;<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

FOR<\/span>\u00a0i\u00a0IN<\/span>\u00a0l_tab.first<\/span>\u00a0.. l_tab.last<\/span>\u00a0LOOP<\/span><\/span><\/span><\/p>\n

DBMS_OUTPUT.put_line(<\/span>‘INSERT ID=’<\/span>\u00a0|| l_tab(i).<\/span>id<\/span>\u00a0||\u00a0<\/span>‘ DESC=’<\/span>\u00a0|| l_tab(i).<\/span>description<\/span>);<\/span><\/span><\/span><\/p>\n

END<\/span>\u00a0LOOP<\/span>;<\/span><\/span><\/p>\n

COMMIT<\/span>;<\/span><\/span><\/p>\n

END<\/span>;<\/span><\/span><\/p>\n

\/<\/span><\/span><\/p>\n

INSERT<\/span>\u00a0<\/span>ID<\/span>=<\/span>5<\/span>\u00a0<\/span>DESC<\/span>=FIVE<\/span><\/span><\/span><\/p>\n

INSERT<\/span>\u00a0<\/span>ID<\/span>=<\/span>6<\/span>\u00a0<\/span>DESC<\/span>=SIX<\/span><\/span><\/span><\/p>\n

INSERT<\/span>\u00a0<\/span>ID<\/span>=<\/span>7<\/span>\u00a0<\/span>DESC<\/span>=SEVEN<\/span><\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

6.This functionality is also available from dymanic SQL.<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

SET<\/span>\u00a0SERVEROUTPUT\u00a0ON<\/span><\/span><\/span><\/p>\n

DECLARE<\/span><\/span><\/p>\n

TYPE<\/span>\u00a0t_tab\u00a0IS<\/span>\u00a0TABLE<\/span>\u00a0OF<\/span>\u00a0t1.id<\/span>%TYPE<\/span>;<\/span><\/span><\/p>\n

l_tab t_tab;<\/span><\/span><\/p>\n

BEGIN<\/span><\/span><\/p>\n

EXECUTE<\/span>\u00a0<\/span>IMMEDIATE<\/span>\u00a0<\/span>‘UPDATE t1<\/span><\/span><\/span><\/p>\n

SET description = description<\/span><\/span><\/p>\n

RETURNING id INTO :l_tab’<\/span><\/span><\/p>\n

RETURNING<\/span>\u00a0BULK<\/span>\u00a0COLLECT<\/span>\u00a0INTO<\/span>\u00a0l_tab;<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

FOR<\/span>\u00a0i\u00a0IN<\/span>\u00a0l_tab.first<\/span>\u00a0.. l_tab.last<\/span>\u00a0LOOP<\/span><\/span><\/span><\/p>\n

DBMS_OUTPUT.put_line(<\/span>‘UPDATE ID=’<\/span>\u00a0|| l_tab(i));<\/span><\/span><\/span><\/p>\n

END<\/span>\u00a0LOOP<\/span>;<\/span><\/span><\/p>\n

COMMIT<\/span>;<\/span><\/span><\/p>\n

END<\/span>;<\/span><\/span><\/p>\n

\/<\/span><\/span><\/p>\n

UPDATE<\/span>\u00a0<\/span>ID<\/span>=<\/span>1<\/span><\/span><\/span><\/p>\n

UPDATE<\/span>\u00a0<\/span>ID<\/span>=<\/span>2<\/span><\/span><\/span><\/p>\n

UPDATE<\/span>\u00a0<\/span>ID<\/span>=<\/span>3<\/span><\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

1.The RETURNING INTO clause allows us to return column […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[34,2],"class_list":["post-163","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-oracle","tag-oracle_basics"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/163","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=163"}],"version-history":[{"count":0,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/163\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=163"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=163"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=163"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}