我在SQL Server 7.0中建立了一个存储过程如下：
CREATE PROCEDURE AddStorageItem @ID id, @Unit smallint, @Place Name
AS
if exists (select * from storage where ProductID = @ID and Place = @Place)
update storage
set
Unit = Unit + @Unit
where ProductID = @ID and Place = @Place
else
insert into storage(ProductID, Unit, Place) values(@ID, @Unit, @Place)
功能是：在向Storage表中添加数据时，如果在需要添加的位置处存在要添加的产品，则只更新产品数量，否则插入一条新纪录。
现将其移植到InterBase数据库中：
SET TERM ^ ;
CREATE PROCEDURE UP_ADD_STORAGE_ITEM(ID INTEGER, UNIT SMALLINT, PLACE VARCHAR(20))
AS
BEGIN
IF EXISTS (SELECT * FROM STORAGE WHERE PRODUCT_ID = :ID
AND PLACE= :PLACE) THEN
UPDATE STORAGE
SET UNIT = UNIT + :UNIT
WHERE PRODUCT_ID = :ID AND PLACE = :PLACE;
ELSE
INSERT INTO STORAGE(PRODUCT_ID, UNIT, PLACE)
VALUES(:ID, :UNIT, :PLACE);
END ^
SET TERM ; ^
但运行时提示EXISTS处有错误，请各位高手帮忙更正错误。或若InterBase不支持EXISTS语句,请指教如何用InterBase实现此存储过程。
---------------------------------------------------------------
现将其移植到InterBase数据库中：
SET TERM ^ ;
CREATE PROCEDURE UP_ADD_STORAGE_ITEM(ID INTEGER, UNIT SMALLINT, PLACE VARCHAR(20))
productid integer;
AS
BEGIN
SELECT PRODUCT_ID into productid
FROM STORAGE WHERE PRODUCT_ID = :ID
AND PLACE= :PLACE
if (productid <> null) then
begin
UPDATE STORAGE
SET UNIT = UNIT + :UNIT
WHERE PRODUCT_ID = :ID AND PLACE = :PLACE;
end
ELSE
begin
INSERT INTO STORAGE(PRODUCT_ID, UNIT, PLACE)
VALUES(:ID, :UNIT, :PLACE);
end;
END ^
SET TERM ; ^