mssql 过程:
CREATE procedure dbo.PTR_add_commit (@mid VARCHAR(50)
,@UserID VARCHAR(50)
,@code VARCHAR(50)
,@Barcode VARCHAR(50)
,@Qty int
,@Msg VARCHAR(100) output) as
BEGIN
if not exists(select * from pt_wh_goods where Barcode=@Barcode)
begin
set @Msg = '条码(' + ltrim(rtrim(@Barcode)) + ')不存在!'
select @Msg[Msg]
return -1
end if exists(select * from pt_wh_add_items where Order_code=@code and Barcode=@Barcode)
update pt_wh_add_items set order_qty=order_qty+@Qty where Order_code=@code and Barcode=@Barcode
else
insert into pt_wh_add_items(order_code,barcode,order_Qty) values(@code, @Barcode, @Qty) select a.Barcode 商品条码, b.Name 商品名称,a.order_qty 数量 from pt_wh_add_items a left join pt_wh_goods b on b.Barcode=a.Barcode
where order_code=@code
RETURN 0 END
我下的相同功能的oracle 过程:
CREATE OR REPLACE PROCEDURE "SYSTEM"."PTR_ADD_COMMIT" (mid
VARCHAR
,UserID varchar2
,code varchar2
,Barcode varchar2
,Qty number
,Msg out varchar2 ) as
jil number;
name varchar2 (50);
Package1 varchar2 (50);
BEGIN
select count(*) into jil from "SYSTEM"."pt_wh_goods" where BARCODE=Barcode;
if jil=0 then
Msg := '条码(' + ltrim(rtrim(Barcode)) + ')不存在!';
end IF; select count(*) into jil from "SYSTEM"."pt_wh_add_items" where ORDER_CODE=code and BARCODE=Barcode;
if jil>0 THEN
update "SYSTEM"."pt_wh_add_items" set ORDER_QTY=order_qty+Qty where ORDER_CODE=code and BARCODE=Barcode;
else
insert into "SYSTEM"."pt_wh_add_items"(ORDER_CODE,BARCODE,ORDER_QTY) values(code, Barcode, Qty);
END IF;
select a.BARCODE as 商品条码, b.NAME as 商品名称,a.ORDER_QTY as 数量 from "SYSTEM"."pt_wh_add_items" a left join "SYSTEM"."pt_wh_goods" b on b.BARCODE=a.BARCODE
where ORDER_CODE=code;
END; select a.BARCODE as 商品条码, b.NAME as 商品名称,a.ORDER_QTY as 数量 from "SYSTEM"."pt_wh_add_items" a left join "SYSTEM"."pt_wh_goods" b on b.BARCODE=a.BARCODE
where ORDER_CODE=code;
这里报select 缺少into子句错误,请问这是怎么回事呢?要实现mssql同样的功能,这里怎么写呢?
CREATE procedure dbo.PTR_add_commit (@mid VARCHAR(50)
,@UserID VARCHAR(50)
,@code VARCHAR(50)
,@Barcode VARCHAR(50)
,@Qty int
,@Msg VARCHAR(100) output) as
BEGIN
if not exists(select * from pt_wh_goods where Barcode=@Barcode)
begin
set @Msg = '条码(' + ltrim(rtrim(@Barcode)) + ')不存在!'
select @Msg[Msg]
return -1
end if exists(select * from pt_wh_add_items where Order_code=@code and Barcode=@Barcode)
update pt_wh_add_items set order_qty=order_qty+@Qty where Order_code=@code and Barcode=@Barcode
else
insert into pt_wh_add_items(order_code,barcode,order_Qty) values(@code, @Barcode, @Qty) select a.Barcode 商品条码, b.Name 商品名称,a.order_qty 数量 from pt_wh_add_items a left join pt_wh_goods b on b.Barcode=a.Barcode
where order_code=@code
RETURN 0 END
我下的相同功能的oracle 过程:
CREATE OR REPLACE PROCEDURE "SYSTEM"."PTR_ADD_COMMIT" (mid
VARCHAR
,UserID varchar2
,code varchar2
,Barcode varchar2
,Qty number
,Msg out varchar2 ) as
jil number;
name varchar2 (50);
Package1 varchar2 (50);
BEGIN
select count(*) into jil from "SYSTEM"."pt_wh_goods" where BARCODE=Barcode;
if jil=0 then
Msg := '条码(' + ltrim(rtrim(Barcode)) + ')不存在!';
end IF; select count(*) into jil from "SYSTEM"."pt_wh_add_items" where ORDER_CODE=code and BARCODE=Barcode;
if jil>0 THEN
update "SYSTEM"."pt_wh_add_items" set ORDER_QTY=order_qty+Qty where ORDER_CODE=code and BARCODE=Barcode;
else
insert into "SYSTEM"."pt_wh_add_items"(ORDER_CODE,BARCODE,ORDER_QTY) values(code, Barcode, Qty);
END IF;
select a.BARCODE as 商品条码, b.NAME as 商品名称,a.ORDER_QTY as 数量 from "SYSTEM"."pt_wh_add_items" a left join "SYSTEM"."pt_wh_goods" b on b.BARCODE=a.BARCODE
where ORDER_CODE=code;
END; select a.BARCODE as 商品条码, b.NAME as 商品名称,a.ORDER_QTY as 数量 from "SYSTEM"."pt_wh_add_items" a left join "SYSTEM"."pt_wh_goods" b on b.BARCODE=a.BARCODE
where ORDER_CODE=code;
这里报select 缺少into子句错误,请问这是怎么回事呢?要实现mssql同样的功能,这里怎么写呢?
CREATE OR REPLACE PROCEDURE "SYSTEM"."PTR_ADD_COMMIT"(mid VARCHAR,
UserID varchar2,
code varchar2,
Barcode varchar2,
Qty number,
Msg out varchar2) as
jil number;
name varchar2(50);
Package1 varchar2(50);BEGIN
select count(1)
into jil
from "SYSTEM"."pt_wh_goods"
where BARCODE = Barcode;
if jil = 0 then
Msg := '条码(' + ltrim(rtrim(Barcode)) + ')不存在!';
return - 1;
end IF; select count(1)
into jil
from "SYSTEM"."pt_wh_add_items"
where ORDER_CODE = code
and BARCODE = Barcode;
if jil > 0 THEN
update "SYSTEM"."pt_wh_add_items"
set ORDER_QTY = order_qty + Qty
where ORDER_CODE = code
and BARCODE = Barcode;
else
insert into "SYSTEM"."pt_wh_add_items"
(ORDER_CODE, BARCODE, ORDER_QTY)
values
(code, Barcode, Qty);
END IF; /*select a.BARCODE as 商品条码, b.NAME as 商品名称, a.ORDER_QTY as 数量
from "SYSTEM"."pt_wh_add_items" a
left join "SYSTEM"."pt_wh_goods" b
on b.BARCODE = a.BARCODE
where ORDER_CODE = code;*/
return 0;
end;
另外两表查询oracle存储过程怎么实现呢?