一个诡异的问题，关于调用oracle函数

Question

select REALTYRIGHT_STORAGE.FUNC_CHECKHOUSESTATUS(-5021026,6792) from dual我直接在SI Object Browser里的命令窗口执行此语句返回的是0然而我直接执行这个函数却返回1，而且我单步执行过，也是返回1。觉得这个问题很诡异，希望大家为我解惑。下面是所说的函数CREATE OR REPLACE FUNCTION REALTYRIGHT_STORAGE.FUNC_CHECKHOUSESTATUS 
(M_HOUSEID IN NUMBER,M_REALTYPROINSTID IN NUMBER)
----------------------检测房屋是否还有其他限制状态
RETURN number-- 返回值的数据类型
IS
  M_COUNT NUMBER(4); --统计
  HOUSE_LIMITSTATE NUMBER(4); --房屋状态
  E_MESSAGE2        EXCEPTION; --异常提示
BEGIN
  SELECT COUNT(*) INTO M_COUNT FROM REALTYRIGHT_STORAGE.T_REALTYOBJECT WHERE REALTYPROINST_ID<>M_REALTYPROINSTID AND HOUSE_ID=M_HOUSEID AND ACT_TYPE IN (8020,8022,8024);
      IF(M_COUNT>0) THEN 
         HOUSE_LIMITSTATE:=1030;
      ELSE
          SELECT COUNT(*) INTO M_COUNT FROM REALTYRIGHT_STORAGE.T_REALTYOBJECT WHERE REALTYPROINST_ID<>M_REALTYPROINSTID AND HOUSE_ID=M_HOUSEID AND ACT_TYPE IN (8030); 
          IF (M_COUNT>0) THEN 
             HOUSE_LIMITSTATE:=1020;
          ELSE 
             SELECT COUNT(*) INTO M_COUNT FROM REALTYRIGHT_STORAGE.T_REALTYOBJECT WHERE REALTYPROINST_ID<>M_REALTYPROINSTID AND HOUSE_ID=M_HOUSEID AND ACT_TYPE IN (8070);
             IF (M_COUNT>0) THEN
      HOUSE_LIMITSTATE:=1050;
             ELSE
SELECT COUNT(*) INTO M_COUNT FROM REALTYRIGHT_STORAGE.T_REALTYOBJECT WHERE REALTYPROINST_ID<>M_REALTYPROINSTID AND HOUSE_ID=M_HOUSEID AND ACT_TYPE IN (8090);
                IF(M_COUNT>0) THEN
   HOUSE_LIMITSTATE:=1060;
                ELSE
                   HOUSE_LIMITSTATE:=1010;
END IF;
             END IF;
          END IF;
      END IF;
   --如果没有则设置HOUSE表中的对应房屋限制状态为无限制
   UPDATE REALTYRIGHT_STORAGE.T_HOUSE SET LIMIT_STATE=HOUSE_LIMITSTATE WHERE HOUSE_ID=M_HOUSEID;
   RETURN 1;
EXCEPTION 
WHEN E_MESSAGE2 THEN
RAISE_APPLICATION_ERROR(-20000,'项目实例'||M_REALTYPROINSTID||'更新房屋状态出错');
                RETURN 0;
        WHEN OTHERS THEN
                RETURN 0;
END ;
/
我在代码里是这样调用的
#region [非自动生成]->CheckHouseStatus 检测房屋其他限制状态
[WebMethod]
public Boolean CheckHouseStatus(Int32 House_ID,Int32 RealtyProinst_ID)
{
string _ConnectString = ConnectStringSetting.GetOracleConnectString("RealtyRightStorage");
OracleConnection conn = new OracleConnection(_ConnectString);
OracleCommand comm= new OracleCommand("select REALTYRIGHT_STORAGE.FUNC_CHECKHOUSESTATUS(:M_HOUSEID,:M_REALTYPROINSTID) from dual", conn); //关键是这句
OracleParameter p0 = new OracleParameter("M_HOUSEID", OracleDbType.Int32,4);

p0.Direction = ParameterDirection.Input;
p0.Value = House_ID;

OracleParameter p1 = new OracleParameter("M_REALTYPROINSTID",OracleDbType.Int32,4);
p1.Direction = ParameterDirection.Input;
p1.Value = RealtyProinst_ID;  comm.Parameters.Add(p0);
comm.Parameters.Add(p1);
      
comm.CommandType = CommandType.Text;
  
Boolean  result;
try
{
if (conn.State == ConnectionState.Closed)
conn.Open();   
result = Convert.ToBoolean(comm.ExecuteScalar());
return result;
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
}
}
#endregion

Answer 1

我的目的是，判断房屋是否还有其他限制记录，如果没有就设置房屋的LIMIT_STATE为1010，即无限制，可惜调用方法CheckHouseStatus却没起作用，直接在oracle函数里执行就起作用，是不是与这种select REALTYRIGHT_STORAGE.FUNC_CHECKHOUSESTATUS(-5021026,6792) from dual函数调用方式有关呢？还有没有其他调用oracle函数的方式？

Answer 2

跟你测试调用的方式没关系的，我觉得应该是你逻辑判断的问题
  其余访问的方式有：
      begin
        dbms_output.put_lin(FUNC_CHECKHOUSESTATUS(-5021026,6792));
     end;
或者
     begin
        dbms_output.put_lin(FUNC_CHECKHOUSESTATUS(House_ID =>-5021026,RealtyProinst_ID=>6792));
     end;或者
     begin
        dbms_output.put_lin(FUNC_CHECKHOUSESTATUS(-5021026,RealtyProinst_ID=>6792));
     end;

Answer 3

我在函数里单步执行过，那句更新HOUSE表的语句确定执行过的，可是这样调用select REALTYRIGHT_STORAGE.FUNC_CHECKHOUSESTATUS(-5021026,6792) from dual
却不起作用，所以我觉得逻辑判断应该没错的吧

Answer 4

貌似是因为Select 语句中调用包含DML语句的函数时，会出错，函数一般不包含DML操作，这样也太限制了吧。最后只能改成存储过程了

Answer 5

函数一般不用于dml操作，可以使用过程，定义一个传出参数来返回值
如果一定要在函数中实现，可以使用自治事务

Answer 6

原代码中函数执行到update的时候出发异常，进入exception的when others 异常处理部分，返回0

Answer 7

你的函数体执行的时候传入的参数M_REALTYPROINSTID=6792 有问题所以抛出异常 返回0然而我直接执行这个函数却返回1，而且我单步执行过，也是返回1。觉得这个问题很诡异，希望大家为我解惑。直接执行的话默认为真没有进入异常 所以直接执行的是   RETURN 1;

Answer 8

执行update也会出异常么？不是最多update 0行？
还是参数跟字段类型不匹配？

Answer 9

我copy你的去运行，不是1哦。SQL> select REALTYRIGHT_STORAGE.FUNC_CHECKHOUSESTATUS(-5021026,6792) from dual;
select REALTYRIGHT_STORAGE.FUNC_CHECKHOUSESTATUS(-5021026,6792) from dual
       *
第 1 行出现错误:
ORA-00904: "REALTYRIGHT_STORAGE"."FUNC_CHECKHOUSESTATUS": 标识符无效

Answer 10

在函数的IS最后BEGIN之前加上下面这句看看：
  PRAGMA AUTONOMOUS_TRANSACTION;
不声明为自治事务，单独使用没问题，在SQL语句中使用会报异常

Answer 11

声明为自治事务，然后用select REALTYRIGHT_STORAGE.FUNC_CHECKHOUSESTATUS(-5021026,6792) from dual调用，还是返回0的，有兴趣可以试一下，当我到函数里进行单步调试时，到UPDATE语句时是没有异常产生的，到RETURN1时直接到END，然而诡异的事情又出现了，到END后没退出，而是跳到EXCEPTION，然后执行异常里的OTHERS，这就返回0。我觉得应该是，执行到UPDATE语句时，整个事务还没有提交，所以还没有异常产生，直到提交事务才产生异常。
声明自治事务也没起作用啊后来我改成存储过程了，不过想知道声明自治事务为什么没起作用，有兴趣的一起探讨一下。

Answer 12

终于可以了，我在RETURN1前面加了COMMIT;在RETURN 0 前加ROLLBACK；之后就可以了，要显式提交才行。看来得研究一下自治事务呀