Oracle returning 详解

1 概述

1.1 基础数据准备

-- 学生信息表
create table scott.student (
  sno  number,
  name varchar2(30),
  sex  varchar2(30)
);

1.2 简化逻辑简述

insert into scott.student(sno, name, sex) values (1, '阿梦', '女')
  returning name, sex into v_name, v_sex;

-- 等同于以下两个步骤
insert into scott.student(sno, name, sex) values (1, '阿梦', '女');

select s.name,
       s.sex
  into v_name,
       v_sex
  from scott.student s
 where s.sno = 1; 

2 静态SQL示例

2.1 单行数据获取

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

示例：

declare
   v_name scott.student.name%type; -- 用于输出演示
   v_sex  scott.student.sex%type;
begin
   insert into scott.student(sno, name, sex) values (1, '阿梦', '女') 
     returning name, sex into v_name, v_sex;
   dbms_output.put_line('insert 后 name ：' || v_name || 
                                 ', sex : ' || v_sex);
   v_name:= null; -- 清空
   v_sex := null;

   update scott.student set name = '悠悠', sex = '男' where sno = 1 
     returning name, sex into v_name, v_sex;
   dbms_output.put_line('update 后 name ：' || v_name ||
                                 ', sex : ' || v_sex);
   v_name:= null;
   v_sex := null;
   
   delete scott.student where sno = 1 
     returning name into v_name;
   dbms_output.put_line('delete 前 name ：' || v_name);

   -- commit;
exception
   when others then
      dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

测试结果：

2.2 多行数据获取

基础数据：

truncate table scott.student;

insert into scott.student(sno, name, sex) values(1, '张三', '女');
insert into scott.student(sno, name, sex) values(2, '李四', '男');
insert into scott.student(sno, name, sex) values(3, '王五', '女');
commit;

示例：

declare
   type student_record is record(
      v_sno  scott.student.sno%type,
      v_name scott.student.name%type,
      v_sex  scott.student.sex%type);
   type student_table is table of student_record index by pls_integer;
   v_student_table student_table;
   
begin
   -- dml 演示
   update scott.student t 
      set t.name = 'a' 
    where 1 = 1 
    returning t.sno, t.name, t.sex
    bulk collect into v_student_table;
    
   -- 存在数据时,输出结果
   if v_student_table.count >= 1 then 
     for i in v_student_table.first .. v_student_table.last loop
        dbms_output.put_line(v_student_table(i).v_sno || ' : ' ||
                             v_student_table(i).v_name || ' : ' ||
                             v_student_table(i).v_sex);
     end loop;
   end if;
exception
   when others then
      dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

输出结果：

1 : a : 女
2 : a : 男
3 : a : 女

3 动态SQL示例

3.1 单行数据获取

基础数据：

truncate table scott.student;

insert into scott.student(sno, name, sex) values(3, '张三', '女');
commit;

示例：

declare
   v_name scott.student.name%type;
   v_sex  scott.student.sex%type;
   v_sql_update varchar(200);
begin
   v_sql_update := 'UPDATE scott.student 
                       SET name = ''a'' 
                     WHERE sno = :b1 
                    RETURNING name, sex INTO :b2, :b3';
   
   -- 注意参数位置的顺序一致
   execute immediate v_sql_update using 3 returning into v_name, v_sex;

   -- commit;
   dbms_output.put_line(v_name||' : '||v_sex);
exception
   when others then
      dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

输出结果：

a : 女

3.3 多行数据获取

3.3.1 returning 支持 table

基础数据：

truncate table scott.student;

insert into scott.student(sno, name, sex) values(1, '张三', '女');
insert into scott.student(sno, name, sex) values(2, '李四', '男');
insert into scott.student(sno, name, sex) values(3, '王五', '女');
commit;

示例：

declare
   type student_sno_table is table of scott.student.sno%type;
   type student_name_table is table of scott.student.name%type;
   
   v_sno_table student_sno_table;
   v_name_table student_name_table;
   
   v_sql_update varchar2(2000);
begin
   v_sql_update := 'UPDATE scott.student t 
                       SET t.name = ''a'' 
                     WHERE t.sno <= :b1 
                    RETURNING t.sno, t.name INTO :b2, :b3';
   
   execute immediate v_sql_update using 3 
     returning bulk collect into v_sno_table, v_name_table;
   
   -- 输出结果
   for i in v_sno_table.first .. v_sno_table.last loop
      dbms_output.put_line(v_sno_table(i) || ' : ' || v_name_table(i));
   end loop;
   
   -- commit;
exception
   when others then
      dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

测试结果：

1 : a
2 : a
3 : a

3.3.2 returning 不支持 record

基础数据：

truncate table scott.student;

insert into scott.student(sno, name, sex) values(1, '张三', '女');
insert into scott.student(sno, name, sex) values(2, '李四', '男');
insert into scott.student(sno, name, sex) values(3, '王五', '女');
commit;

示例：

declare
   type student_record is record(
      v_sno scott.student.sno%type,
      v_name scott.student.name%type);
   type student_table is table of student_record index by pls_integer;
   v_student_table student_table;
   v_sql_update varchar2(100);
begin
   v_sql_update := 'UPDATE scott.student t 
                       SET t.name = ''a'' 
                     WHERE t.sno := b1
                    RETURNING t.sno, t.name INTO :b2';
               
   execute immediate v_sql_update using 3
     returning bulk collect into v_student_table;          
   
   -- 输出结果
   for i in v_student_table.first .. v_student_table.last loop
      dbms_output.put_line(v_student_table(i).v_sno || ' : ' ||
                           v_student_table(i).v_name);
   end loop;
   
   -- commit;
exception
   when others then
      dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
      dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

测试结果：