Oracle returning 详解

文章目录

• 1、使用场景
• • 1.2、基础数据
• 2、实例分析
• • 2.1 insert、update、delete 修改单行记录
  • • 2.1.1 + 动态sql
  • 2.2 record + table 修改多行记录
  • • 2.2.1 + 动态sql
    • • 2.2.1.1 returning 支持 table
      • 2.2.1.2 returning 不支持 record

1、使用场景

在存储过程、PL/SQL 块里需要 返回 insert、delete、update 等 dml 语句后的信息时使用，合理使用 returning 能够简化程序逻辑（少一次 select into）、提高程序性能。

1.2、基础数据

DROP TABLE stu; -- if exists
CREATE TABLE stu (
  s_id NUMBER,
  s_xm VARCHAR2(30)
);
INSERT INTO stu(s_id, s_xm) VALUES(1, '小游子');
INSERT INTO stu(s_id, s_xm) VALUES(2, '小优子');
INSERT INTO stu(s_id, s_xm) VALUES(3, '小倩子');
COMMIT;

 
 

2、实例分析

2.1 insert、update、delete 修改单行记录

• insert、update 是提取 操作后 的数据
• delete 是提取 操作前 的数据

DROP TABLE stu1; -- if exists
CREATE TABLE stu1 AS SELECT * FROM stu WHERE 1 = 2;
DECLARE
   v_xm system.stu.s_xm%TYPE;
BEGIN
   INSERT INTO stu(s_id, s_xm) VALUES (1, '小游子') RETURNING s_xm INTO v_xm;
   dbms_output.put_line('插入后的 s_xm ：' || v_xm);
   v_xm:= NULL;
   UPDATE stu SET s_xm = '小优子' WHERE s_id = 1 RETURNING s_xm INTO v_xm;
   dbms_output.put_line('修改后的 s_xm ：' || v_xm);
   v_xm:= NULL;   
   DELETE stu WHERE s_id = 1 RETURNING s_xm INTO v_xm;
   dbms_output.put_line('删除前的 s_xm ：' || v_xm);
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

 
 

测试结果：

2.1.1 + 动态sql

returning into 在 动态sql内部和外面都要写，且外面的 returning 后面不加字段直接 into。

TRUNCATE TABLE stu;
INSERT INTO stu(s_id, s_xm) VALUES(1, '小游子');
INSERT INTO stu(s_id, s_xm) VALUES(2, '小优子');
INSERT INTO stu(s_id, s_xm) VALUES(3, '小倩子');
COMMIT;
-------- 以上为：数据准备 --------
DECLARE
   v_id system.stu.s_id%TYPE; 
   v_xm system.stu.s_xm%TYPE;
   v_sql VARCHAR(200);
BEGIN
   v_sql := 'UPDATE stu SET s_xm = ''a'' WHERE s_id = :b1 RETURNING s_id, s_xm INTO :b2, :b3';   
   EXECUTE IMMEDIATE v_sql USING 3 RETURNING INTO v_id, v_xm; -- 内外 returning 都要写   
   ROLLBACK;
   dbms_output.put_line(v_id||' : '||v_xm);
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

 
 

2.2 record + table 修改多行记录

TRUNCATE TABLE stu;
INSERT INTO stu(s_id, s_xm) VALUES(1, '小游子');
INSERT INTO stu(s_id, s_xm) VALUES(2, '小优子');
INSERT INTO stu(s_id, s_xm) VALUES(3, '小倩子');
COMMIT;
-------- 以上为：数据准备 --------
DECLARE
   TYPE stu_record IS RECORD(
      v_id system.stu.s_id%TYPE,
      v_xm system.stu.s_xm%TYPE);
   TYPE stu_table IS TABLE OF stu_record INDEX BY PLS_INTEGER;
   v_stu_table stu_table;   
BEGIN
   UPDATE stu t SET t.s_xm = 'a' WHERE 1 = 1 RETURNING t.s_id, t.s_xm BULK COLLECT INTO v_stu_table;
   ROLLBACK; 
   FOR i IN v_stu_table.first .. v_stu_table.last LOOP
      dbms_output.put_line(v_stu_table(i).v_id||' : '||v_stu_table(i).v_xm);
   END LOOP;
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

 
 

测试结果：

2.2.1 + 动态sql

动态sql 内部仍然是 returning into 而不是 returning bulk collect into

2.2.1.1 returning 支持 table

TRUNCATE TABLE stu;
INSERT INTO stu(s_id, s_xm) VALUES(1, '小游子');
INSERT INTO stu(s_id, s_xm) VALUES(2, '小优子');
INSERT INTO stu(s_id, s_xm) VALUES(3, '小倩子');
COMMIT;
-------- 以上为：数据准备 --------
DECLARE
   TYPE stu_id_table IS TABLE OF system.stu.s_id%TYPE;
   TYPE stu_xm_table IS TABLE OF system.stu.s_xm%TYPE;   
   v_id stu_id_table;
   v_xm stu_xm_table;   
   v_sql VARCHAR2(2000);
BEGIN
   v_sql := 'UPDATE stu t SET t.s_xm = ''a'' WHERE t.s_id <= :b1 
               RETURNING t.s_id, t.s_xm INTO :b2, :b3';   
   EXECUTE IMMEDIATE v_sql USING 3 RETURNING BULK COLLECT INTO v_id, v_xm;
   ROLLBACK;   
   FOR i IN v_id.first .. v_id.last LOOP
      dbms_output.put_line(v_id(i));
   END LOOP;   
   dbms_output.new_line();   
   FOR i IN v_xm.first .. v_xm.last LOOP
      dbms_output.put_line(v_xm(i));
   END LOOP;   
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

 
 

测试结果：

2.2.1.2 returning 不支持 record

TRUNCATE TABLE stu;
INSERT INTO stu(s_id, s_xm) VALUES(1, '小游子');
INSERT INTO stu(s_id, s_xm) VALUES(2, '小优子');
INSERT INTO stu(s_id, s_xm) VALUES(3, '小倩子');
COMMIT;
-------- 以上为：数据准备 --------
DECLARE
   TYPE stu_record IS RECORD(
      v_id system.stu.s_id%TYPE,
      v_xm system.stu.s_xm%TYPE);
   TYPE stu_table IS TABLE OF stu_record INDEX BY PLS_INTEGER;
   v_stu_table stu_table;
   v_sql VARCHAR2(30);
BEGIN
   v_sql := 'UPDATE stu t SET t.s_xm = ''a'' WHERE t.s_id := b1
               RETURNING t.s_id, t.s_xm INTO :b2';               
   EXECUTE IMMEDIATE v_sql USING 1  RETURNING BULK COLLECT INTO v_stu_table;          
   ROLLBACK;    
   FOR i IN v_stu_table.first .. v_stu_table.last LOOP
      dbms_output.put_line(v_stu_table(i).v_id||' : '||v_stu_table(i).v_xm);
   END LOOP;   
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
END;

 
 

测试结果：