ASP.NET Core调用Oracle存储过程无法返回完整员工列表问题

算力阿峰

2026-7-26

问题描述

为实现经理审批功能，我在Oracle中创建了存储过程MANGERLIST用于获取员工ID和姓名列表，但在ASP.NET Core控制器调用时，要么仅返回最后一名员工，要么以游标形式调用时无任何结果。

存储过程代码

create or replace PROCEDURE MANGERLIST
(
  EMPNO IN NUMBER 
, DG IN NUMBER 
, DEPT IN NUMBER 
, DESGN IN NUMBER 
,l_name OUT sys_refcursor
) AS 

  l_sql VARCHAR2(800);

BEGIN
  l_sql := 'select TRIM(emp_no) , TRIM(EMP_NAME_E) FROM hr."VempDtls" where DESG_TYPE <>99 ';

if DESGN in (4,5,6,99) then
    if EMPNO = 213 then
      l_sql := l_sql || 'and emp_no =85 ' ;    
    ELSif EMPNO in (341,215,278) then
        l_sql := l_sql||' and emp_no in(213,215,219,58) ';    
    ELSif EMPNO in (336,323) then
      l_sql := l_sql||' and emp_no in(277,219,58) ';    
    ELSE  
     l_sql := l_sql||' and (dg_code = DG and DESG_TYPE in (2,3,0,1) and emp_no <>EMPNO) OR (dg_code = DG  and dept_code = DEPT
            and DESG_TYPE in (4,5,6) and emp_no <>EMPNO and DESG_TYPE < DESGN) ';    
    end if;
 ELSif DESGN in (2,3) and EMPNO != 58 then 
       l_sql := l_sql||' and emp_no=120 ';
 else
      l_sql := l_sql;
end if;

   OPEN  l_name FOR l_sql;

END MANGERLIST;

C#实体类

public class MangerProc
{
    [NotMapped]
    public int inEMPNO { get; set; }
    [NotMapped]
    public int inDG { get; set; }
    [NotMapped]
    public int inDEPT { get; set; }
    [NotMapped]
    public int inDESGN { get; set; }

    [NotMapped]
    public int? Outl_id { get; set; }
    
    [StringLength(200)]
    [NotMapped]
    public string? Outl_name { get; set; }
}

调用存储过程的C#函数

public virtual string  GetManagerList(int? empId, int? dg, int? dept, int? desgnation)
{
        try
        {
            using (var ctx = new hrmsContext())
            using (var cmd = ctx.Database.GetDbConnection().CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "MANGERLIST";

                var EMPNOParam = new OracleParameter("EMP_NO", OracleDbType.Int32, empId, ParameterDirection.Input);
                var DGParam = new OracleParameter("inDG", OracleDbType.Int32, dg, ParameterDirection.Input);
                var DEPTParam = new OracleParameter("inDEPT", OracleDbType.Int32, dept, ParameterDirection.Input);
                var DESGNParam = new OracleParameter("inDESGN", OracleDbType.Int32, desgnation, ParameterDirection.Input);
                var l_idParam = new OracleParameter("Outl_id", OracleDbType.RefCursor, ParameterDirection.Output);

                cmd.Parameters.AddRange(new[] { EMPNOParam, DGParam, DEPTParam, DESGNParam, l_idParam });

                cmd.Connection.Open();
                var result = cmd.ExecuteNonQuery();
                cmd.Connection.Close();

                var assetRegistered = l_idParam.Value;
                return assetRegistered.ToString();
            }
        }
        catch (Exception x)
        {
            return null;
        }
}

测试数据

CREATE TABLE "HR"."VEMP_DTLS"
(
"EMP_NO" NUMBER,
"EMP_NAME_A" VARCHAR2(109 BYTE),
"DG_CODE" NUMBER,
"DEPT_CODE" NUMBER,
"DESG_TYPE" NUMBER
);

INSERT INTO "HR"."VEMP_DTLS" (EMP_NO, EMP_NAME_A, DG_CODE, DEPT_CODE, DESG_TYPE) VALUES ('278', 'Asma', '11', '11', '99')
INSERT INTO "HR"."VEMP_DTLS" (EMP_NO, EMP_NAME_A, DG_CODE, DEPT_CODE, DESG_TYPE) VALUES ('35', 'Ali', '11', '11', '2')
INSERT INTO "HR"."VEMP_DTLS" (EMP_NO, EMP_NAME_A, DG_CODE, DEPT_CODE, DESG_TYPE) VALUES ('127', 'Ahmed', '8', '4', '4')
INSERT INTO "HR"."VEMP_DTLS" (EMP_NO, EMP_NAME_A, DG_CODE, DEPT_CODE, DESG_TYPE) VALUES ('314', 'Mohammed', '11', '2', '4')
INSERT INTO "HR"."VEMP_DTLS" (EMP_NO, EMP_NAME_A, DG_CODE, DEPT_CODE, DESG_TYPE) VALUES ('215', 'Abudallah', '11', '11', '2')
INSERT INTO "HR"."VEMP_DTLS" (EMP_NO, EMP_NAME_A, DG_CODE, DEPT_CODE, DESG_TYPE) VALUES ('127', 'Saud', '9', '3', '4')

问题修复步骤

1. 修复存储过程的参数绑定问题

原存储过程直接拼接参数到SQL字符串，会导致参数值无法正确传递，同时存在SQL注入风险，改用绑定变量：

create or replace PROCEDURE MANGERLIST
(
  EMPNO IN NUMBER 
, DG IN NUMBER 
, DEPT IN NUMBER 
, DESGN IN NUMBER 
,l_name OUT sys_refcursor
) AS 

  l_sql VARCHAR2(1000);

BEGIN
  l_sql := 'select TRIM(emp_no) emp_no, TRIM(EMP_NAME_A) emp_name FROM hr."VEMP_DTLS" where DESG_TYPE <> 99 ';

if DESGN in (4,5,6,99) then
    if EMPNO = 213 then
      l_sql := l_sql || 'and emp_no = :p_target_emp ';    
    ELSif EMPNO in (341,215,278) then
        l_sql := l_sql||' and emp_no in(213,215,219,58) ';    
    ELSif EMPNO in (336,323) then
      l_sql := l_sql||' and emp_no in(277,219,58) ';    
    ELSE  
     l_sql := l_sql||' and (dg_code = :p_dg and DESG_TYPE in (2,3,0,1) and emp_no <> :p_current_emp) OR (dg_code = :p_dg  and dept_code = :p_dept
            and DESG_TYPE in (4,5,6) and emp_no <> :p_current_emp and DESG_TYPE < :p_desgn) ';    
    end if;
 ELSif DESGN in (2,3) and EMPNO != 58 then 
       l_sql := l_sql||' and emp_no=120 ';
 else
      l_sql := l_sql;
end if;

   -- 根据条件绑定对应参数
   if DESGN in (4,5,6,99) then
        if EMPNO = 213 then
            OPEN l_name FOR l_sql USING 85;
        elsif EMPNO not in (341,215,278,336,323) then
            OPEN l_name FOR l_sql USING DG, EMPNO, DG, DEPT, EMPNO, DESGN;
        else
            OPEN l_name FOR l_sql;
        end if;
   else
        OPEN l_name FOR l_sql;
   end if;

END MANGERLIST;

2. 修复C#调用代码

原代码参数名称与存储过程不匹配，且用ExecuteNonQuery()无法读取游标结果，修改后：

public virtual List<MangerProc> GetManagerList(int? empId, int? dg, int? dept, int? desgnation)
{
    var managerList = new List<MangerProc>();
    try
    {
        using (var ctx = new hrmsContext())
        using (var cmd = ctx.Database.GetDbConnection().CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "MANGERLIST";

            // 严格匹配存储过程的参数名称
            var EMPNOParam = new OracleParameter("EMPNO", OracleDbType.Int32, empId ?? 0, ParameterDirection.Input);
            var DGParam = new OracleParameter("DG", OracleDbType.Int32, dg ?? 0, ParameterDirection.Input);
            var DEPTParam = new OracleParameter("DEPT", OracleDbType.Int32, dept ?? 0, ParameterDirection.Input);
            var DESGNParam = new OracleParameter("DESGN", OracleDbType.Int32, desgnation ?? 0, ParameterDirection.Input);
            var l_nameParam = new OracleParameter("l_name", OracleDbType.RefCursor, ParameterDirection.Output);

            cmd.Parameters.AddRange(new[] { EMPNOParam, DGParam, DEPTParam, DESGNParam, l_nameParam });

            cmd.Connection.Open();
            // 用ExecuteReader读取游标中的所有数据
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    managerList.Add(new MangerProc
                    {
                        Outl_id = reader.IsDBNull(0) ? null : (int?)reader.GetInt32(0),
                        Outl_name = reader.IsDBNull(1) ? null : reader.GetString(1)
                    });
                }
            }
            cmd.Connection.Close();
        }
    }
    catch (Exception)
    {
        // 建议添加日志记录，不要直接吞掉异常
        throw;
    }
    return managerList;
}

3. 额外注意事项

确保存储过程中查询的表名、字段名与测试数据完全匹配（原存储过程中用了VempDtls和EMP_NAME_E，测试数据是VEMP_DTLS和EMP_NAME_A，Oracle对双引号包裹的对象名大小写敏感）。

内容的提问来源于stack exchange，提问作者AL- MAHROUQI

ASP.NET Core调用Oracle存储过程无法返回完整员工列表问题

存储过程代码 #

C#实体类 #

调用存储过程的C#函数 #

测试数据 #

问题修复步骤 #