oracle 两表查询

[tiandi_030]

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同样的功能，这里怎么写呢？

[lisong770818]

对你的代码修改了一下，下面的代码复制到你的环境下测试下结果。
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;

[tiandi_030]

我编译了下报：return语句无法包含表达式
另外两表查询oracle存储过程怎么实现呢？