存储过程:已经在pl/sql里面调试通过了并且测试了
create or replace procedure getSerialNumber(PtabName BASE_TABLESERIALNUMBER.TABNAME%Type,
SerialNumber out varchar2) as
tabcount number;
VcurrSerialNumber number;
VserialNumberLen number;
len number;
chars varchar2(20);
Vprefix varchar2(20);
begin SET TRANSACTION READ ONLY;
---首先检查表明是否存在
select count(*)
into tabcount
from BASE_TABLESERIALNUMBER
where TABNAME = PtabName;
if tabcount < 1 then
---如果表名不存在
SerialNumber := to_char(-1);
else
---取出表的当前流水号,最大长度
select CURRSERIALNUMBER
into VcurrSerialNumber
from BASE_TABLESERIALNUMBER
where TABNAME = PtabName;
select SERIALNUMBERLEN
into VserialNumberLen
from BASE_TABLESERIALNUMBER
where TABNAME = PtabName;
----流水号加1
VcurrSerialNumber := VcurrSerialNumber + 1;
if length(to_char(VcurrSerialNumber)) > VserialNumberLen then
--判断流水号的长度是否已经大于了指定长度
SerialNumber := to_char(-2);
else
SerialNumber := to_char(VcurrSerialNumber);
len := VserialNumberLen - length(to_char(VcurrSerialNumber)); ---补零操作
while len > 0 loop
chars := '0' || chars;
len := len - 1;
end loop;
SerialNumber := chars || SerialNumber;
select Prefix into Vprefix from BASE_TABLESERIALNUMBER where TABNAME = PtabName;
SerialNumber :=Vprefix||SerialNumber;
end if;
end if;
commit;
---dbms_output.PUT_LINE('执行成功');
exception
when others then
rollback;
----dbms_output.PUT_LINE('异常');
end getSerialNumber;
C#代码:
OracleConnection oledbConnection = new OracleConnection(connectString);
OracleCommand comm = new OracleCommand();
comm.Connection = oledbConnection;
comm.CommandText = "getSerialNumber";
comm.CommandType = CommandType.StoredProcedure; OracleParameter[] pas = { new OracleParameter("PtabName", OracleType.VarChar), new OracleParameter("SerialNumber", OracleType.VarChar) };
pas[0].Value = tabName;
pas[0].Direction = ParameterDirection.Input;
pas[1].Direction = ParameterDirection.Output;
oledbConnection.Open();
comm.ExecuteNonQuery();
oledbConnection.Close();
return pas[1].Value.ToString();
create or replace procedure getSerialNumber(PtabName BASE_TABLESERIALNUMBER.TABNAME%Type,
SerialNumber out varchar2) as
tabcount number;
VcurrSerialNumber number;
VserialNumberLen number;
len number;
chars varchar2(20);
Vprefix varchar2(20);
begin SET TRANSACTION READ ONLY;
---首先检查表明是否存在
select count(*)
into tabcount
from BASE_TABLESERIALNUMBER
where TABNAME = PtabName;
if tabcount < 1 then
---如果表名不存在
SerialNumber := to_char(-1);
else
---取出表的当前流水号,最大长度
select CURRSERIALNUMBER
into VcurrSerialNumber
from BASE_TABLESERIALNUMBER
where TABNAME = PtabName;
select SERIALNUMBERLEN
into VserialNumberLen
from BASE_TABLESERIALNUMBER
where TABNAME = PtabName;
----流水号加1
VcurrSerialNumber := VcurrSerialNumber + 1;
if length(to_char(VcurrSerialNumber)) > VserialNumberLen then
--判断流水号的长度是否已经大于了指定长度
SerialNumber := to_char(-2);
else
SerialNumber := to_char(VcurrSerialNumber);
len := VserialNumberLen - length(to_char(VcurrSerialNumber)); ---补零操作
while len > 0 loop
chars := '0' || chars;
len := len - 1;
end loop;
SerialNumber := chars || SerialNumber;
select Prefix into Vprefix from BASE_TABLESERIALNUMBER where TABNAME = PtabName;
SerialNumber :=Vprefix||SerialNumber;
end if;
end if;
commit;
---dbms_output.PUT_LINE('执行成功');
exception
when others then
rollback;
----dbms_output.PUT_LINE('异常');
end getSerialNumber;
C#代码:
OracleConnection oledbConnection = new OracleConnection(connectString);
OracleCommand comm = new OracleCommand();
comm.Connection = oledbConnection;
comm.CommandText = "getSerialNumber";
comm.CommandType = CommandType.StoredProcedure; OracleParameter[] pas = { new OracleParameter("PtabName", OracleType.VarChar), new OracleParameter("SerialNumber", OracleType.VarChar) };
pas[0].Value = tabName;
pas[0].Direction = ParameterDirection.Input;
pas[1].Direction = ParameterDirection.Output;
oledbConnection.Open();
comm.ExecuteNonQuery();
oledbConnection.Close();
return pas[1].Value.ToString();
解决方案 »
- Oracle 11g 默认安装带来的用户名/密码有哪些?
- 如何在ORACLE中设置小数的格式化
- oracle 11g 卸载的问题
- 求把该函数转化为Oracle
- 如何用一条SQL语句实现,克隆一张表。
- 用pro*c编成的程序,如何独立运行?
- oracle9 for red advance server3.0
- 现在在ORACLE官方网站有一个Oracle9i Release2 for windows server 2003(64-bit) 的版本,是什么意思
- 一个PL/sql的简单问题,对者有分阿!!!
- 请教一下有没有update 表 set=变量 的具体例子
- 一个table有20列,我要select除第13列外的所有列,怎么做
- 产生大量 defunct 进程 怎么排查原因 并处理
---取出表的当前流水号,最大长度
select CURRSERIALNUMBER
into VcurrSerialNumber
from BASE_TABLESERIALNUMBER
where TABNAME = PtabName;
select SERIALNUMBERLEN
into VserialNumberLen
from BASE_TABLESERIALNUMBER
where TABNAME = PtabName;
--这个可以这样
select CURRSERIALNUMBER,SERIALNUMBERLEN
into VcurrSerialNumber,VserialNumberLen
from BASE_TABLESERIALNUMBER
where TABNAME = PtabName;
new OracleParameter("PtabName", OracleType.VarChar,20), new OracleParameter("SerialNumber", OracleType.VarChar,20)
是在这一行报错的 执行的时候报错的 这些代码 我在网上也查了 c#确实是这么调用的