如何在Oracle存储过程发生异常时获取out类型参数的值

Oracle存储过程关于在出现（自定义/自带）异常下out类型参数的获取问题的分析

✈️ 场景:

有一些关于金额和时间的精确且量大的计算需要在存储过程中完成。存储过程中有一些自定义的异常。并且将在RAISE前通过out类型的参数将详细的异常原因返回。

但是在通过Csharp的调用中,发现了很多的问题。需要书面总结。

💥问题点1：存储过程自身的问题

存储过程中发生异常时，在存储过程中发生异常无法获取out类型参数。

例表：

procedure:

create or replace procedure SP_TEST(EXC_MSG out varchar2)
as
    ExcOne EXCEPTION;    --自定义异常
    ExcTwo EXCEPTION;
    item varchar2(10);
    
    cursor curOne is select ABBREVIATION from TB_CM_CPNAME;
begin
    open curOne;
    
    loop
    
    fetch curOne into item;
    exit when curOne%NOTFOUND;
        
        IF(item='云久鴻') then
            exc_msg := 'XXX错误';    --在异常raise前给out参数赋值
            RAISE ExcOne;            --raise异常
        end if;
        
        IF(item='海浪') then
            exc_msg := 'YYY错误';
            RAISE ExcTwo;            --raise异常
        end if;
    end loop;
end;

测试执行语句：

SET SERVEROUTPUT ON;
declare
    V_msg varchar2(20);
    
begin
    SP_TEST(V_msg);
    
Exception              --外部执行时尝试获取out参数
    when others then
        DBMS_OUTPUT.put_line('COUT<<'||v_msg);
end;

结果：

当在存储过程中去处理异常

修改存储过程:

create or replace procedure SP_TEST(EXC_MSG out varchar2)
as
    ExcOne EXCEPTION;    --自定义异常
    ExcTwo EXCEPTION;
    item varchar2(10);
    
    cursor curOne is select ABBREVIATION from TB_CM_CPNAME;
begin
    open curOne;
    loop
    
    fetch curOne into item;
    exit when curOne%NOTFOUND;
        IF(item='云久鴻') then
            exc_msg := 'XXX错误';
            RAISE ExcOne;            --raise异常
        end if;
        
        IF(item='海浪') then
            exc_msg := 'YYY错误';
            RAISE ExcTwo;            --raise异常
        end if;
    end loop;
    
    EXCEPTION                        --在SP内处理异常
        when ExcOne then
        ROLLBACK;
        --RAISE  注意不在再向上抛异常，不然还是获取不到out参数
        when ExcTwo then
        ROLLBACK;
end;

测试：

💥问题点2 ：在外部调用中获取out参数

关于：

一般不会写了个存储过程只通过sql去调用，大部分情况是通过其他的外部语言来调用。所以对于外部调用来获取out参数又是一个需要注意的问题。

🌀使用C#调用oracle存储过程

Dapper调用存储过程

namespace UnitTest
{
    [TestFixture]
    public class TestDual
    {
        private IServiceProvider provider;

        [SetUp]
        public void SetUp()
        {
            var collection = new ServiceCollection();
			//这是一个Dapper实现的执行sql工具
            collection.AddSingleton(new SqlDapperService("XXXX", Db_Type.DbName.Oracle,
                "XXXX","XXXX" ));

            provider =  collection.BuildServiceProvider();
        }

        [Test]
        public void RealTest()
        {
            SqlDapperService dapper = provider.GetService<SqlDapperService>();

            string EXC_MSG = $@"传参前";

            int res = dapper.ExcuteNonQuery("sp_test", new { EXC_MSG }, CommandType.StoredProcedure);

            Console.WriteLine($@"COUT<<"+EXC_MSG);
        }
    }
}

测试结果:并没有获取到out类型参数的值

ADO.NET 原生方式获取

/// <summary>
/// ADO.NET 测试调用存储过程
/// </summary>
[TestFixture]
public class ADO_test
{
    [Test]
    public void TestOne()
    {
        
        using(var conn = new OracleConnection(Encrypter.DecryptAES("XXXXX",
            "xxxx", "xxxxx")))
        {
            conn.Open();

            OracleCommand command = conn.CreateCommand();

            command.CommandText = "SP_TEST";
            command.CommandType = System.Data.CommandType.StoredProcedure;
		   //设置out参数
            OracleParameter outparm = command.Parameters.Add("EXC_MSG", OracleDbType.Varchar2, ParameterDirection.Output);
            outparm.Size = 200; //参数的长度

            command.ExecuteNonQuery();

            Console.WriteLine(outparm.Value) ;输出
        }
    }
}

小节

如果想要在Oracle的存储过程发生异常时获取out类型的参数，需要

• 在SP中处理异常，且不要再向上抛出（再次抛出会导致out类型参数的值被清除）
• 在数据库外部调用时，尽量选择原生的调用方式