带有参数和返回值的存储过程
获取最大编号的使用
create or replace procedure Pro_GetMaxNo(GNoType in nvarchar2, GNO out nvarchar2) is
V_NoValue number;
V_NoFormatText nvarchar2(50);
V_NoNumLength number;
begin
select NoValue into V_NoValue from TAB_MAXNO where NoType = GNoType;
select NoFormatText into V_NoFormatText from TAB_MAXNO where NoType = GNoType;
select NoNumLength into V_NoNumLength from TAB_MAXNO where NoType = GNoType;
update TAB_MAXNO set NoValue = V_NoValue +1 where NoType = GNoType ;
commit;
GNO := CONCAT( V_NoFormatText,lpad(V_NoValue,V_NoNumLength,‘0‘));
exception
when others then
rollback;
end Pro_GetMaxNo;
Oracle中存储过程的调用
---调用 存储过程
declare
MaxNo nvarchar2(10);
begin
Pro_GetMaxNo(‘TaskNo‘, MaxNo);
end;
C#中调用存储过程
string cmdText = @"call Pro_GetMaxNo(:NoType,:GNO)";
OracleParameter[] oracleParameter = new OracleParameter[]
{
new OracleParameter(":NoType", OracleType.NVarChar, 10),
new OracleParameter(":GNO", OracleType.NVarChar,30)
};
oracleParameter[0].Value = NoType;
oracleParameter[1].Direction = ParameterDirection.Output;
OracleHelper.ExecuteReader(CommandType.Text, cmdText, oracleParameter);
string MaxNo = oracleParameter[1].Value.ToString();
return MaxNo;