Question

求高手帮我把SQL存储过程转换成oracle的~~

USE [dms1011]
GO
/****** Object: StoredProcedure [dbo].[pr_TyConfPriceTimeAdd] Script Date: 10/20/2014 10:04:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[pr_TyConfPriceTimeAdd]
@StructureCode varchar(255),
@price float,
@begindate datetime,
@enddate datetime,
@checkor varchar(200),
@creator varchar(255),
@product varchar(50),
@remark varchar(2000),
@useofstate int,
@info int output
AS
BEGIN
set @info=0
DECLARE @count int
set @count=(select count(oid) from TyConfPricetime where StructureCode=@StructureCode
and ((DATEADD(DAY,1,@BEGINDATE) BETWEEN BEGINDATE and ENDDATE)
or (DATEADD(DAY,-1,@ENDDATE) BETWEEN BEGINDATE and ENDDATE)
or (BEGINDATE BETWEEN DATEADD(DAY,1,@BEGINDATE) and DATEADD(DAY,-1,@ENDDATE))
or (ENDDATE BETWEEN DATEADD(DAY,1,@BEGINDATE) and DATEADD(DAY,-1,@ENDDATE))))
if @count=0
BEGIN
insert into TyConfPriceTime(CREATED,CREATOR,MODIFIER,UPDATED,StructureCode,price,begindate,enddate,[state],checkor,product,REMARK,UseOfState)
values(GETDATE(),@creator,@creator,GETDATE(),@StructureCode,@price,@begindate,@enddate,0,@checkor,@product,@remark,@useofstate)
END
else
BEGIN
set @info=@count
END
END

Answer 1

create or replace PROCEDURE pr_TyConfPriceTimeAdd(p_StructureCode varchar2,

                                                  p_price float,

                                                  p_begindate date,

                                                  p_enddate date,

                                                  p_checkor varchar2,

                                                  p_creator varchar2,

                                                  p_product varchar2,

                                                  p_remark varchar2,

                                                  p_useofstate int,

                                                  p_info out int) is

  p_count int;

BEGIN

  p_info := 0;

  select count(oid)

    into p_count

    from TyConfPricetime

   where StructureCode = p_StructureCode

     and (p_BEGINDATE + 1 BETWEEN BEGINDATE and ENDDATE or

         p_ENDDATE - 1 BETWEEN BEGINDATE and ENDDATE or

         BEGINDATE BETWEEN p_BEGINDATE + 1 and p_ENDDATE - 1 or

         ENDDATE BETWEEN p_BEGINDATE + 1 and p_ENDDATE - 1);

  if p_count = 0 then

    insert into TyConfPriceTime

      (CREATED, CREATOR, MODIFIER, UPDATED, StructureCode, price, begindate,

       enddate, [ state ], checkor, product, REMARK, UseOfState)

    values

      (sysdate, p_creator, p_creator, sysdate, p_StructureCode, p_price,

       p_begindate, p_enddate, 0, p_checkor, p_product, p_remark,

       p_useofstate);

  else

    p_info := p_count;

  end if;

  commit;

END