C#调用oracle存储过程，返回的受影响行数总为1，数据库数据也没动，但是在plsql中可以执行该存储过程
0
悬赏园豆：10
[已解决问题]
解决于 2014-09-17 00:39
1 private string Cons() 2 { 3 _connectionString = "Data Source=" + _serverVo.Ip + ":1521/orcl;user=" + _serverVo.UserName + ";password=" + _serverVo.Password + ";"; 4 return _connectionString; 5 } 6 7 public int ExcuteProcedure(string procedureName) 8 { 9 Cons(); 10 11 OracleConnection conn = new OracleConnection(_connectionString); 12 conn.Open(); 13 OracleCommand cmd = conn.CreateCommand(); 14 cmd.CommandType = CommandType.StoredProcedure;//指明是执行存储过程 15 cmd.CommandText = procedureName; 16 int count = cmd.ExecuteNonQuery(); 17 conn.Close(); 18 return count; 19 20 }
count值总返回为1，不知在哪一部出错，请高手帮忙看看，谢谢
最佳答案
0
存储过程写错了吧?
收获园豆：10
如果存储过程写错了，为什么在PL/SQL中可以正确执行呢？
@天有日月星辰谓之文，地有山川陵故谓之理: Count 值返回1说明语句执行正确。也返回了具体的值，所以应该是你存储过程写的有问题。
@三十三块: 您好 能加您QQ 方便请教一下吗 关于这个存储过程的问题
@天有日月星辰谓之文，地有山川陵故谓之理: 不好意思，公司不能上QQ.
@天有日月星辰谓之文，地有山川陵故谓之理: 贴出来看看。
@三十三块: 我那段存储过程在数据库中可以正确执行，就是上面代码调用之后 只返回count=1，在库里面不是1
@三十三块:
1 create or replace procedure dealbsm 2 as 3 tabname varchar2(20); 4 objid number; 5 prebsm number; 6 currentbsm number; 7 maxbsm number; 8 cursor cur_bsm is 9 select 'DGX' as tabname,objectid,nvl(BSM,-999999) as bsm 10 from DGX 11 union all 12 select 'DLJX' as tabname,objectid,nvl(BSM,-999999) as bsm 13 from DLJX 14 union all 15 select 'DLTB' as tabname,objectid,nvl(BSM,-999999) as bsm 16 from DLTB 17 union all 18 select 'GCZJD' as tabname,objectid,nvl(BSM,-999999) as bsm 19 from GCZJD 20 union all 21 select 'JBNTBHPK' as tabname,objectid,nvl(BSM,-999999) as bsm 22 from JBNTBHPK 23 union all 24 select 'JBNTBHQ' as tabname,objectid,nvl(BSM,-999999) as bsm 25 from JBNTBHQ 26 union all 27 select 'JBNTBHTB' as tabname,objectid,nvl(BSM,-999999) as bsm 28 from JBNTBHTB 29 union all 30 select 'JZD' as tabname,objectid,nvl(BSM,-999999) as bsm 31 from JZD 32 union all 33 select 'JZKZD' as tabname,objectid,nvl(BSM,-999999) as bsm 34 from JZKZD 35 union all 36 select 'JZX' as tabname,objectid,nvl(BSM,-999999) as bsm 37 from JZX 38 union all 39 select 'KFYQ' as tabname,objectid,nvl(BSM,-999999) as bsm 40 from KFYQ 41 union all 42 select 'LXDW' as tabname,objectid,nvl(BSM,-999999) as bsm 43 from LXDW 44 union all 45 select 'PDT' as tabname,objectid,nvl(BSM,-999999) as bsm 46 from PDT 47 union all 48 select 'TOPO_CHAIN_UNK' as tabname,objectid,nvl(BSM,-999999) as bsm 49 from TOPO_CHAIN_UNK 50 union all 51 select 'XZDW' as tabname,objectid,nvl(BSM,-999999) as bsm 52 from XZDW 53 union all 54 select 'XZQ' as tabname,objectid,nvl(BSM,-999999) as bsm 55 from XZQ 56 union all 57 select 'XZQJX' as tabname,objectid,nvl(BSM,-999999) as bsm 58 from XZQJX 59 union all 60 select 'ZD' as tabname,objectid,nvl(BSM,-999999) as bsm 61 from ZD 62 union all 63 select 'ZJ' as tabname,objectid,nvl(BSM,-999999) as bsm 64 from ZJ 65 order by bsm; 66 begin 67 prebsm := -9999; 68 maxbsm := 0; 69 for rec in cur_bsm loop 70 tabname := rec.tabname; 71 currentbsm := rec.bsm; 72 objid := rec.objectid; 73 74 if currentbsm = -999999 then 75 prebsm := -999999; 76 end if; 77 78 if currentbsm = prebsm then 79 getmaxbsm(maxbsm); 80 if tabname = 'DGX' then 81 update DGX 82 set BSM = maxbsm+1 83 where objectid = objid; 84 elsif tabname='DLJX' then 85 update DLJX 86 set BSM = maxbsm+1 87 where objectid = objid; 88 elsif tabname='DLTB' then 89 update DLTB 90 set BSM = maxbsm+1 91 where objectid = objid; 92 elsif tabname='GCZJD' then 93 update GCZJD 94 set BSM = maxbsm+1 95 where objectid = objid; 96 elsif tabname='JBNTBHPK' then 97 update JBNTBHPK 98 set BSM = maxbsm+1 99 where objectid = objid; 100 elsif tabname='JBNTBHQ' then 101 update JBNTBHQ 102 set BSM = maxbsm+1 103 where objectid = objid; 104 elsif tabname='JBNTBHTB' then 105 update JBNTBHTB 106 set BSM = maxbsm+1 107 where objectid = objid; 108 elsif tabname='JZD' then 109 update JZD 110 set BSM = maxbsm+1 111 where objectid = objid; 112 elsif tabname='JZKZD' then 113 update JZKZD 114 set BSM = maxbsm+1 115 where objectid = objid; 116 elsif tabname='JZX' then 117 update JZX 118 set BSM = maxbsm+1 119 where objectid = objid; 120 elsif tabname='KFYQ' then 121 update KFYQ 122 set BSM = maxbsm+1 123 where objectid = objid; 124 elsif tabname='LXDW' then 125 update LXDW 126 set BSM = maxbsm+1 127 where objectid = objid; 128 elsif tabname='PDT' then 129 update PDT 130 set BSM = maxbsm+1 131 where objectid = objid; 132 elsif tabname='TOPO_CHAIN_UNK' then 133 update TOPO_CHAIN_UNK 134 set BSM = maxbsm+1 135 where objectid = objid; 136 elsif tabname='XZDW' then 137 update XZDW 138 set BSM = maxbsm+1 139 where objectid = objid; 140 elsif tabname='XZQ' then 141 update XZQ 142 set BSM = maxbsm+1 143 where objectid = objid; 144 elsif tabname='XZQJX' then 145 update XZQJX 146 set BSM = maxbsm+1 147 where objectid = objid; 148 elsif tabname='ZD' then 149 update ZD 150 set BSM = maxbsm+1 151 where objectid = objid; 152 elsif tabname='ZJ' then 153 update ZJ 154 set BSM = maxbsm+1 155 where objectid = objid; 156 end if; 157 commit; 158 end if; 159 prebsm := currentbsm; 160 end loop; 161 end;
这个就是那个存储过程
@三十三块: 下面这个是getmaxbsm 过程
1 create or replace procedure getmaxbsm(maxbsm out integer) 2 as 3 begin 4 select max(mb) into maxbsm 5 from ( 6 select max(BSM) as mb 7 from DGX 8 union 9 select max(BSM) as mb 10 from DLJX 11 union 12 select max(BSM) as mb 13 from DLTB 14 union 15 select max(BSM) as mb 16 from GCZJD 17 union 18 select max(BSM) as mb 19 from JBNTBHPK 20 union 21 select max(BSM) as mb 22 from JBNTBHQ 23 union 24 select max(BSM) as mb 25 from JBNTBHTB 26 union 27 select max(BSM) as mb 28 from JZD 29 union 30 select max(BSM) as mb 31 from JZKZD 32 union 33 select max(BSM) as mb 34 from JZX 35 union 36 select max(BSM) as mb 37 from KFYQ 38 union 39 select max(BSM) as mb 40 from LXDW 41 union 42 select max(BSM) as mb 43 from PDT 44 union 45 select max(BSM) as mb 46 from TOPO_CHAIN_UNK 47 union 48 select max(BSM) as mb 49 from XZDW 50 union 51 select max(BSM) as mb 52 from XZQ 53 union 54 select max(BSM) as mb 55 from XZQJX 56 union 57 select max(BSM) as mb 58 from ZD 59 union 60 select max(BSM) as mb 61 from ZJ 62 ) t; 63 end;
@天有日月星辰谓之文，地有山川陵故谓之理:
尝试一下输出返回结果，再对照一下数据库中数据：
cmd.CommandType = CommandType.StoredProcedure;//指明是执行存储过程
cmd.CommandText = procedureName;
cmd.Parameters.Add("return_value", OracleType.Number).Direction =ParameterDirection.ReturnValue; // 如果是dealbsm， 就换成表类型， 输出值来看看。
int count = cmd.ExecuteNonQuery();
Console.WriteLine(cmd.Parameters["return_value"].Value);
conn.Close();
return count;
其他回答(2)
0
好长
0
存储过程迷茫。。。
