存储过程代码如下:
create or replace procedure dsTwoMtBillIsEqu(
i_BillIDA MatTranPN.BillID%type,
i_BillIDB MatTranPN.BillID%type,
o_Result OUT char --返回出错信息,成功返回空
) is
/*
名称: 对比两张MatTranPn,MatTranCn单据,是否一致.用于决断导入单据与原单据是否一致
流程:修改记录:
调用过程:
涉及表:
MatTranPN, MatTranCN
调试语句:
declare
begin
dsTwoMtBillIsEqu();
end;
*/ v_BillAMatCount integer;
v_BillBMatCount integer;
v_MatIDA Medicine.Numcode%type;
v_MatIDB MatTranCN.MatID%type;
v_SellPriceA MatTranCN.SellPrice%type;
v_SellPriceB MatTranCN.SellPrice%type;
v_QtyA MatTranCN.Qty%type;
v_QtyB MatTranCN.Qty%type;
v_loop integer;begin o_Result := '';
create global temporary table BillAData
as
select B.NumCode, A.SellPrice, sum(A.Qty) Qty
from MatTranCN A, Medicine B
where A.BillID=i_BillIDA
and A.MatID=B.MedID
group by NumCode, SellPrice
order by v, SellPrice;
create global temporary table BillBData
as
select B.NumCode, A.SellPrice, sum(A.Qty) Qty
from MatTranCN A, Medicine B
where A.BillID=i_BillIDB
and A.MatID=B.MedID
group by NumCode, SellPrice
order by v, SellPrice;
select count(*) into v_BillAMatCount from BillAData;
select count(*) into v_BillBMatCount from BillBData;
if v_BillAMatCount <> v_BillBMatCount then
o_Result := '导入的单据和将要确认的单据物资数量不一致!';
else
for v_loop in 1..v_BillAMatCount loop
select NumCode, SellPrice, Qty into v_MatIDA, v_SellPriceA, v_QtyA
from BillAData where rownum = v_loop;
select NumCode, SellPrice, Qty into v_MatIDB, v_SellPriceB, v_QtyB
from BillBData where rownum = v_loop;
if v_MatIDA <> v_MatIDB then
o_Result := '导入的单据和将要确认的单据物资不一致!';
break;
end if;
if v_SellPriceA <> v_SellPriceB then
o_Result := '导入的单据和将要确认的单据物资零售价格不一致!物资编码为:'+to_char(v_MatIDA);
break;
end if;
if v_QtyA <> v_QtyB then
o_Result := '导入的单据和将要确认的单据物资数量不一致!物资编码为:'+to_char(v_MatIDA);
break;
end if;
end loop;
end if; drop table BillAData;
drop table BillBData;
end;
出错信息如下:
PROCEDURE DSTWOMTBILLISEQU 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
35/3 PLS-00103: Encountered the symbol "CREATE" when expecting one of
the following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
请问各位怎样解决?在线等候。
create or replace procedure dsTwoMtBillIsEqu(
i_BillIDA MatTranPN.BillID%type,
i_BillIDB MatTranPN.BillID%type,
o_Result OUT char --返回出错信息,成功返回空
) is
/*
名称: 对比两张MatTranPn,MatTranCn单据,是否一致.用于决断导入单据与原单据是否一致
流程:修改记录:
调用过程:
涉及表:
MatTranPN, MatTranCN
调试语句:
declare
begin
dsTwoMtBillIsEqu();
end;
*/ v_BillAMatCount integer;
v_BillBMatCount integer;
v_MatIDA Medicine.Numcode%type;
v_MatIDB MatTranCN.MatID%type;
v_SellPriceA MatTranCN.SellPrice%type;
v_SellPriceB MatTranCN.SellPrice%type;
v_QtyA MatTranCN.Qty%type;
v_QtyB MatTranCN.Qty%type;
v_loop integer;begin o_Result := '';
create global temporary table BillAData
as
select B.NumCode, A.SellPrice, sum(A.Qty) Qty
from MatTranCN A, Medicine B
where A.BillID=i_BillIDA
and A.MatID=B.MedID
group by NumCode, SellPrice
order by v, SellPrice;
create global temporary table BillBData
as
select B.NumCode, A.SellPrice, sum(A.Qty) Qty
from MatTranCN A, Medicine B
where A.BillID=i_BillIDB
and A.MatID=B.MedID
group by NumCode, SellPrice
order by v, SellPrice;
select count(*) into v_BillAMatCount from BillAData;
select count(*) into v_BillBMatCount from BillBData;
if v_BillAMatCount <> v_BillBMatCount then
o_Result := '导入的单据和将要确认的单据物资数量不一致!';
else
for v_loop in 1..v_BillAMatCount loop
select NumCode, SellPrice, Qty into v_MatIDA, v_SellPriceA, v_QtyA
from BillAData where rownum = v_loop;
select NumCode, SellPrice, Qty into v_MatIDB, v_SellPriceB, v_QtyB
from BillBData where rownum = v_loop;
if v_MatIDA <> v_MatIDB then
o_Result := '导入的单据和将要确认的单据物资不一致!';
break;
end if;
if v_SellPriceA <> v_SellPriceB then
o_Result := '导入的单据和将要确认的单据物资零售价格不一致!物资编码为:'+to_char(v_MatIDA);
break;
end if;
if v_QtyA <> v_QtyB then
o_Result := '导入的单据和将要确认的单据物资数量不一致!物资编码为:'+to_char(v_MatIDA);
break;
end if;
end loop;
end if; drop table BillAData;
drop table BillBData;
end;
出错信息如下:
PROCEDURE DSTWOMTBILLISEQU 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
35/3 PLS-00103: Encountered the symbol "CREATE" when expecting one of
the following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
请问各位怎样解决?在线等候。
解决方案 »
- 由于oracle数据库达到最大连接数。sqlplus登陆会报错。但是要获取当前的连接数。
- Oracle数据库中SYS.XMLTYPE类型子内容查询的问题
- oracle 9i数据库移到oracle 8i后的问题
- 高手请看:这两个SQL语句效率上有什么区别?
- 字符和时间函数问题
- 请教sql的写法
- 求教“无法分配 4096 字节的共享内存”问题出现后增加共享池的大小后仍然出现该问题?
- ****走过路过的高手请进来看一下****
- 初次接触ORACLE,问一个初级的问题.请各位高手帮忙!
- 请各位发表看法:如何进行数据库开发中的版本控制问题?
- 一个字段要把重复的名称从第二个开始在名称后面加数字,这个SQL如何写,,,多谢
- 求一简单高效的sql语句,谢谢大家。
改为:
execute immediate 'create global temporary table....' 这样的形式.
比如下:
create global temporary table BillAData
as
select B.NumCode, A.SellPrice, sum(A.Qty) Qty
from MatTranCN A, Medicine B
where A.BillID=i_BillIDA
and A.MatID=B.MedID
group by NumCode, SellPrice
order by v, SellPrice; 要修改为以下:
exec immediate 'create global temporary table BillAData
as
select B.NumCode, A.SellPrice, sum(A.Qty) Qty
from MatTranCN A, Medicine B
where A.BillID=:1
and A.MatID=B.MedID
group by NumCode, SellPrice
order by v, SellPrice' using i_BillIDA
使用了您的方法修改,还是出错,能再帮忙看看吗?谢谢了!
贴上出错信息:
PROCEDURE DSTWOMTBILLISEQU 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
53/3 PLS-00103: Encountered the symbol "SELECT" when expecting one of
the following:
. ( , * @ % & = - + ; < / > at in is mod not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like between ||
The symbol ";" was substituted for "SELECT" to continue.78/3 PLS-00103: Encountered the symbol "DROP" when expecting one of
the following:
begin case declare end exception exit for goto if loop mod
null pragma raise return select update while withLINE/COL ERROR
-------- -----------------------------------------------------------------
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
select NumCode, SellPrice, Qty into v_MatIDA, v_SellPriceA, v_QtyA
from BillAData where rownum = v_loop;
select NumCode, SellPrice, Qty into v_MatIDB, v_SellPriceB, v_QtyB
from BillBData where rownum = v_loop; 因为rownum不可以这样用,这不是表的一个字段,而是随机产生的行号
如果你要用,只能这样写:
select *
from (select rownum as rn,NumCode, SellPrice, Qty into v_MatIDA, v_SellPriceA, v_QtyA
from BillAData )A
where rn = v_loop;