获取结果状态

语法：

get_diagnostics ::=

get_diagnostics ::= GET DIAGNOSTICS { var_name { := | = } item } [,...n];

参数：

var_name变量名，用于接收系统变量值

item 关键字，指定要读取的系统变量（var_name应该与该系统变量的数据类型相同）。当前可用的 关键字有两个：

ROW_COUNT：最后一个发送给SQL引擎的SQL查询处理的行的数量；

RESULT_OID：最后一条SQL查询插入的最后一行的ROWID。（只有在INSERT查询后）

示例:获取结果状态

这条语句允许用户一次读取多个系统状态值。

--  清理环境
DROP TABLE authors CASCADE;
DROP PROCEDURE mydiagas;

create table authors
(
    title varchar(32), 
    name varchar(32), 
    city varchar(32), 
    age integer
);
insert into authors values('evergreen', 'rickyyang', 'hangzhou', 18);
insert into authors values('flybird', 'colinchen', 'shenzhen', 22);
insert into authors values('shyboy', 'bolinhou', 'xiamen', 19);
insert into authors values('coach', 'neoliu', 'chengdu', 20);

create or replace procedure proc as
    var1 int;
    var2 bigint;
begin
    select * from authors;
    get diagnostics var1=ROW_COUNT, var2=RESULT_OID;
    dbms_output.put_line('ROW_COUNT=' || var1 || '  LASTROW_OID=%' || var2);
end;

/
exec proc;
TITLE(varchar)      |NAME(varchar)      |CITY(varchar)      |AGE(int)      |
------------------------------------------------------------------------
evergreen           |rickyyang          |hangzhou           |18            |
------------------------------------------------------------------------
flybird             |colinchen          |shenzhen           |22            |
------------------------------------------------------------------------
shyboy              |bolinhou           |xiamen             |19            |
------------------------------------------------------------------------
coach               |neoliu             |chengdu            |20            |
总数目：4
ROW_COUNT=4  LASTROW_OID=%0


select * from authors;
TITLE(varchar)      |NAME(varchar)      |CITY(varchar)      |AGE(int)      |
------------------------------------------------------------------------
evergreen           |rickyyang          |hangzhou           |18            |
------------------------------------------------------------------------
flybird             |colinchen          |shenzhen           |22            |
------------------------------------------------------------------------
shyboy              |bolinhou           |xiamen             |19            |
------------------------------------------------------------------------
coach               |neoliu             |chengdu            |20            |
总数目：4

--删除过程
DROP PROCEDURE proc;
DROP TABLE authors CASCADE;