SQL%ROWCOUNT 和 RETURNING INTO

本文主要是让大家了解这两个语法的功能，同时达到更灵活运用的目的。
首先让我们来看一下这两个语法的介绍：

SQL%ROWCOUNT:How Many Rows Were Affected.
(1)NULL if no SELECT or DML statement has run
(2)Otherwise, the number of rows returned by a SELECT statement or affected by a DML

statement (a PLS_INTEGER)
使用此语法要注意几点：
1、如果使用不带bulk collect的select into 语句，返回多行的话，则会报TOO_MANY_ROWS错误，

并且SQL%ROWCOUNT返回1。
2、如果使用了bulk collect的select into语句，则返回实际影响的行数。
3、如果事务回滚了，则SQL%ROWCOUNT并不是开始事务时的值。
4、如果使用了自主事务，则SQL%ROWCOUNT不会保存原事务值。

The RETURNING INTO clause specifies the variables in which to store the values returned

by the statement to which the clause belongs. The variables can be either individual

variables or collections. If the statement affects no rows, then the values of the

variables are undefined.

关键点：
1.SQL%ROWCOUNT只能够返回影响行数.
2.returning into 可以返回所影响行数的集（字段值、记录值或常量值）.

下面我们来做一些测试，以便更好的理解。

准备环节:创建一张测试表

create table test(
id int,
name varchar2(100)
);

 

创建一个类型

create or replace 
type id_type is table of number;

1、对单行记录，判断影响行数

declare
  v_id int;
begin
  insert into test values(2, 'AA')  returning id into v_id;
  dbms_output.put_line(v_id);
  dbms_output.put_line(SQL%ROWCOUNT);
end;

在这里，我可以使用SQL%ROWCOUNT来判断是否插入成功，也可以根据返回值v_id来判断。returning

后面的字段，既可以使用表的字段，如id,name，也可以使用常量如1,'test'等.这点，对于我们插

入数据时尤为重要，比如，我们插入sys_guid,sysdate时，可以使用此方法获取其值，而不必使用

select into 预定义。
（对于更新、删除和插入是一样的操作）
2、对于多行记录，判断影响行数

declare
  ids id_type;
begin
  insert into test values(1, 'AA') ;
  insert into test values(2, 'BB');
  --update test set name = name || '_1' returning id into v_id; -- 这里会报错，影响多行，但返回单一值
  update test set name = name || '_1' returning id bulk collect  into ids;
  for i in ids.first..ids.last loop
    dbms_output.put_line('record ' || i || '=' ||ids(i));
  end loop;
  dbms_output.put_line(SQL%ROWCOUNT);
end;

在多行的情况下，我们可以看到不能够直接使用returning into到一个值，必须先声明结果集，然

后再返回。同时也体现了returning into的一个优点，能够返所需要的记录，而不必再查询。而

SQL%ROWCOUNT只能够返回影响的行数。

下面再举一下单行记录返回多个值的例子：

declare
  type testRecord is record (id test.id%type,name test.name%type);
  v_r testRecord;
begin
  insert into test values(1, 'AA') returning id,name into v_r;
  dbms_output.put_line('id=' || v_r.id ||',name=' || v_r.name);
  dbms_output.put_line(SQL%ROWCOUNT);
end;

 

总结：有效的使用这两个语法，会给我们的编写程序和逻辑带来更多的灵活性。