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.
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
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
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
分享到:
相关推荐
主要介绍了oracle RETURNING 子句使用方法,需要的朋友可以参考下
在本文中,我们介绍了Spring Boot AOP中的@AfterReturning注解,它用于定义在目标方法成功返回后执行的切面逻辑。我们通过一个图书管理系统的实例演示了@AfterReturning的使用方式,并在成功查询图书时记录了查询的...
采用bulk collect可以将查询结果一次性地加载到collections中。...可以在select into,fetch into,returning into语句使用bulk collect。注意在使用bulk collect时,所有的into变量都必须是collections.
NULL 博文链接:https://futeng.iteye.com/blog/1833449
python库。 资源全名:django_pg_returning-1.0.0-py2.py3-none-any.whl
bulk collect在 select ... into、fetch ... into、returning ... into中的应用。
NULL 博文链接:https://caohong286.iteye.com/blog/1486738
字符型函数返回字符值(Character Functions Returning Character Values) (三).字符型函数返回数字值(Character Functions Returning Number Values) (四).日期函数(Datetime Functions) (五).转换函数(Conversion ...
字符型函数返回字符值(Character Functions Returning Character Values) (三).字符型函数返回数字值(Character Functions Returning Number Values) (四).日期函数(Datetime Functions) (五).转换函数(Conversion ...
3.3、@AfterReturning,后置【try】通知,放在方法头上,使用returning来引用方法返回值。 3.4、@AfterThrowing,后置【catch】通知,放在方法头上,使用throwing来引用抛出的异常。 3.5、@Around,环绕通知,放在...
attr_copy: copy an attribute list, returning the size in bytes.
主要介绍了Spring AOP定义AfterReturning增加,结合实例形式分析了Spring面相切面AOP定义AfterReturning增加相关操作技巧与使用注意事项,需要的朋友可以参考下
可解决HP B320i (FLUSH、CMPLT、BKGND)这三个进程cpu使用率一直很高的问题。 描述如下: 1、/var/log/message显示 Dec 17 13:59:26 localhost kernel: PAL Shim AllocRequest returning NULL Dec 17 13:59:26 ...
An Eco-tour of returning to nature.zip
returning-DOM-elements-javascript-源码.rar
/*departments.location_id//部门地址编号*/ ...returning salary,first_name,hire_date into v_rec; dbms_output.put_line(v_rec.dd_sal||v_rec.dd_name||to_char(v_rec.dd_hiredate,'yyyymmdd')); end;
May expose internal representation by returning reference to mutable object 描述:调用get方法,获得对象属性,获得的对象属性是一个可变的对象; b) 建议处理 Dead store to local variable 描述:对一个局部...
10.2 引用类变量和类方法(REFERRING TO CLASS VARIABLES AND METHODS) 20 10.3 常量(CONSTANTS) 21 10.4 变量赋值(VARIABLE ASSIGNMENTS) 21 10.5 其它惯例(MISCELLANEOUS PRACTICES) 22 10.5.1 圆括号(Parentheses)...