帮我转换一下，谢谢

Question

  我写了一个SQL server存储过程，但我不会ORACLE，现在需要把这个存储过程改成ORACLE，谢谢各位了
create procedure pr_ModifyHQSchedule--生成总部计划
@scheduleName varchar(50),
@scheduleType varchar(1),
@scheduleCreater varchar(38),
@yearMonth varchar(10),
@scheduleData varchar(100)
as
DECLARE @PointerPrev int 
DECLARE @PointerCurr int 
DECLARE @TId varchar(10) 
declare @guid varchar(100)
declare @scheduleDay int
declare @tdate datetime
Set @PointerPrev=1 
SET @scheduleDay=0--将具体日期初始化为0
---先处理主表T_HQ_Schedule的数据
set @guid=newid()
       if((select count(1) from T_HQ_Schedule 
where HQ_Schedule_Name=@yearMonth)>0)
begin
delete from T_HQ_Schedule 
   where HQ_Schedule_Date=@yearMonth 
end
if((select count(1) from T_HQ_Schedule_Date_Record 
where cast(year(Schedule_Date) as varchar(10))+cast(month(Schedule_Date) as varchar(10))=@yearMonth
)>0)
begin
delete from T_HQ_Schedule_Date_Record 
where cast(year(Schedule_Date) as varchar(10))+cast(month(Schedule_Date) as varchar(10))=@yearMonth
end
insert into T_HQ_Schedule(HQ_Schedule_Guid,HQ_Schedule_Name,Schedule_Class,Record_Creator,
Is_Published,Record_CreatorDateTime,HQ_Schedule_Date)
values(@guid,@scheduleName,@scheduleType,@scheduleCreater,'N',getdate(),@yearMonth)
 ---处理明细表T_HQ_Schedule_Date_Record的数据
while (@PointerPrev < LEN(@scheduleData)) 
Begin 
    Set @PointerCurr=CharIndex(',',@scheduleData,@PointerPrev) 
    if(@PointerCurr>0) 
    Begin 
        set @TId=SUBSTRING(@scheduleData,@PointerPrev,@PointerCurr-@PointerPrev)
        ------
set @scheduleDay=@scheduleDay+1
set @tdate = cast( substring(@yearMonth,1,4)+'-'+substring(@yearMonth,5,len(@yearMonth) - 4)+'-'
+ cast(@scheduleDay as varchar(10) as datetime)
insert into T_HQ_Schedule_Date_Record(HQ_Schedule_Date_Guid,HQ_Schedule_Guid,Schedule_Date,
Schedule_Content,Record_Creator,Record_Creator,Record_CreatorDateTime)
values(newid(),@guid,@tdate,@TId,@scheduleCreater,getdate())
        SET @PointerPrev = @PointerCurr+1 
    End 
    else 
        Break 
End 
--处理最后一个,因为最后一个后面没有逗号,所以在循环中跳出,需另外再处理 
set @scheduleDay=@scheduleDay+1
set @TId=SUBSTRING(@scheduleData,@PointerPrev,LEN(@scheduleData)-@PointerPrev+1)
set @tdate = cast( substring(@yearMonth,1,4)+'-'+substring(@yearMonth,5,len(@yearMonth) - 4)+'-'
+ cast(@scheduleDay as varchar(10) as datetime)
insert into T_HQ_Schedule_Date_Record(HQ_Schedule_Date_Guid,HQ_Schedule_Guid,Schedule_Date,
Schedule_Content,Record_Creator,Record_Creator,Record_CreatorDateTime)
values(newid(),@guid,@tdate,@TId,@scheduleCreater,getdate())
 

Answer 1

会ORACLE 但不会MS SQL 呵呵

Answer 2

@scheduleName varchar(50), 
@scheduleType varchar(1), 
@scheduleCreater varchar(38), 
@yearMonth varchar(10), 
@scheduleData varchar(100) 
是输入参数？

Answer 3

create or replace procedure pr_ModifyHQSchedule(
   v_scheduleName       in varchar2(50), 
   v_scheduleType       in varchar2(1), 
   v_scheduleCreater    in varchar2(38), 
   v_yearMonth          in varchar2(10), 
   v_scheduleData       in varchar(100)
) 
as 
   v_PointerPrev     number         := 1;
   v_PointerCurr     number         := 0; 
   v_TId             varchar2(10) ;
   v_guid            varchar2(100)  := sys_guid();
   v_scheduleDay     number         := 0;
   v_tdate           date;---先处理主表T_HQ_Schedule的数据 
begin   delete from T_HQ_Schedule 
   where HQ_Schedule_Date   = v_yearMonth;   delete from T_HQ_Schedule_Date_Record 
   where to_char(Schedule_Date,'yyyymm')   = v_yearMonth;   insert into T_HQ_Schedule(HQ_Schedule_Guid,HQ_Schedule_Name,Schedule_Class,
                             Record_Creator,Is_Published,
                             Record_CreatorDateTime,HQ_Schedule_Date) 
   values(v_guid,v_scheduleName,v_scheduleType,v_scheduleCreater,'N',
          sysdate,v_yearMonth);---处理明细表T_HQ_Schedule_Date_Record的数据 
   while v_PointerPrev < length(v_scheduleData) loop 
      v_PointerCurr  := INSTR(v_scheduleData,',',v_PointerPrev); 
      if v_PointerCurr > 0 then
         v_TId          := SUBSTR(v_scheduleData,v_PointerPrev,v_PointerCurr-v_PointerPrev); 
         v_scheduleDay  := v_scheduleDay + 1; 
         v_tdate        := to_date(v_yearMonth||lpad(v_scheduleDay,2,'0'),'yyyymmdd');
         insert into T_HQ_Schedule_Date_Record(HQ_Schedule_Date_Guid,HQ_Schedule_Guid,
                                               Schedule_Date, Schedule_Content,
                                               Record_Creator,Record_Creator,
                                               Record_CreatorDateTime)
         values(sys_guid(),v_guid,v_tdate,v_TId,v_scheduleCreater,sysdate);          v_PointerPrev  := v_PointerCurr + 1;
      end if;
   end loop;--处理最后一个,因为最后一个后面没有逗号,所以在循环中跳出,需另外再处理 
   v_scheduleDay  := v_scheduleDay + 1;
   v_TId          := SUBSTR(v_scheduleData,v_PointerPrev,v_PointerCurr-v_PointerPrev+1); 
   v_tdate        := to_date(v_yearMonth||lpad(v_scheduleDay,2,'0'),'yyyymmdd');   insert into T_HQ_Schedule_Date_Record(HQ_Schedule_Date_Guid,HQ_Schedule_Guid,
                                               Schedule_Date, Schedule_Content,
                                               Record_Creator,Record_Creator,
                                               Record_CreatorDateTime)
         values(sys_guid(),v_guid,v_tdate,v_TId,v_scheduleCreater,sysdate); exception
   when others then
      raise;
end pr_ModifyHQSchedule;

Answer 4

语法其本上差不多，oracle参数前不用加@
  给个简单的例子希望对lz有帮助：
 PROCEDURE sp_u_provideMaterialHead(smainbillheadid in out varchar2) is
  begin
    update t_drm_providematerialhead
       set MainBillState = '3'
     where Trim(mainbillheadid) = Trim(smainbillheadid);
  exception
    when others then
      smainbillheadid := sqlerrm;
      rollback;
  end sp_u_provideMaterialHead;

Answer 5

..慢了一步``
把我的结果也发出来吧
那个newid()不知道是干什么用的函数，所以没转换
create procedure pr_ModifyHQSchedule--生成总部计划 
(p_scheduleName varchar2, 
 p_scheduleType varchar2, 
 p_scheduleCreater varchar2, 
 p_yearMonth varchar2, 
 p_scheduleData varchar2 )
as 
DECLARE 
 v_PointerPrev number ;
 v_PointerCurr number; 
 v_TId varchar2(10) ;
 v_guid varchar2(100) ;
 v_scheduleDay number;
 v_tdate date;
 v_count number;
begin
 v_PointerPrev:=1; 
 scheduleDay:=0;--将具体日期初始化为0 
---先处理主表T_HQ_Schedule的数据 
v_guid:=newid() 
 select count(1) into v_count from T_HQ_Schedule 
where HQ_Schedule_Name=p_yearMonth;
if v_count>0 then 
  delete from T_HQ_Schedule 
    where HQ_Schedule_Date=p_yearMonth ;
end if;
 select count(1) into v_count from T_HQ_Schedule_Date_Record 
    where to_char(Schedule_Date,'yyyymm')=p_yearMonth ;
if v_count>0 then  
delete from T_HQ_Schedule_Date_Record 
where to_char(Schedule_Date,'yyyymm')=p_yearMonth ;
end if;
insert into T_HQ_Schedule(HQ_Schedule_Guid,HQ_Schedule_Name,Schedule_Class,Record_Creator, 
Is_Published,Record_CreatorDateTime,HQ_Schedule_Date) 
values(v_guid,p_scheduleName,p_scheduleType,p_scheduleCreater,'N',sysdate,p_yearMonth) ;
---处理明细表T_HQ_Schedule_Date_Record的数据 
while (v_PointerPrev < LENGTH(p_scheduleData)) 
loop
    v_PointerCurr:=instr(p_scheduleData,',',v_PointerPrev) 
    if(v_PointerCurr>0) 
    then
        v_TId:=SUBSTR(p_scheduleData,v_PointerPrev,v_PointerCurr-v_PointerPrev) ;
        ------ 
 v_scheduleDay:=v_scheduleDay+1 ;
 tdate = cast( p_yearmonth||'-'||scheduleDay,'yyyymm-dd'); 
insert into T_HQ_Schedule_Date_Record(HQ_Schedule_Date_Guid,HQ_Schedule_Guid,Schedule_Date, 
Schedule_Content,Record_Creator,Record_Creator,Record_CreatorDateTime) 
values(newid(),v_guid,v_tdate,v_TId,p_scheduleCreater,sysdate); 
     v_PointerPrev := v_PointerCurr+1 ;
    
    else 
        exit; end if;
End loop;
--处理最后一个,因为最后一个后面没有逗号,所以在循环中跳出,需另外再处理 
v_scheduleDay:=v_scheduleDay+1 ;
v_TId:=SUBSTR(p_scheduleData,v_PointerPrev,LENGTH(p_scheduleData)-v_PointerPrev+1) ;
v_tdate = to_date( p_yearMonth||'-'||scheduleDay ,'yyyymm-dd') ;
insert into T_HQ_Schedule_Date_Record(HQ_Schedule_Date_Guid,HQ_Schedule_Guid,Schedule_Date, 
Schedule_Content,Record_Creator,Record_Creator,Record_CreatorDateTime) 
values(newid(),v_guid,v_tdate,v_TId,p_scheduleCreater,sysdate); 

Answer 6

过程头部as后的declare忘了去掉了

Answer 7

 create sequence gid
minvalue 1
maxvalue 99999999999999999999999999
start with 1
increment by 1
cache 20;create or replace procedure pr_ModifyHQSchedule--生成总部计划 
(     scheduleName varchar2, 
      scheduleType varchar2, 
      scheduleCreater varchar2, 
      yearMonth varchar2, 
      scheduleData varchar2
) 
as 
PointerPrev int(100); 
PointerCurr int(100); 
TId varchar2(10); 
guid varchar2(100);--该处理解为取序列号，用之前先定义一个序列gid 
scheduleDay int(100); 
tdate date
PointerPrev number(10) :=1 
scheduleDay number(10) :=0--将具体日期初始化为0 
v_count number(10);
---先处理主表T_HQ_Schedule的数据 
begin
     select gid.nextval into guid from dual;
     select count(1) into v_count from T_HQ_Schedule 
     where HQ_Schedule_Name= yearMonth;
     
      if v_count >0 then
         begin 
               delete from T_HQ_Schedule 
               where HQ_Schedule_Date=@yearMonth 
         exception
                  when others then
                       dbms_output.put_line(sqlcode||':'||sqlerrm);
         end;
      end if;
      
      select count(1) into v_count from T_HQ_Schedule_Date_Record 
      where extract(year from Schedule_Date)||extract(month from Schedule_Date)= yearMonth
      
      if v_count >0 then 
         begin 
               delete from T_HQ_Schedule_Date_Record 
               where extract(year from Schedule_Date)||extract(month from Schedule_Date)= yearMonth 
         end;
      end if;
      
      insert into T_HQ_Schedule
      (   HQ_Schedule_Guid,
          HQ_Schedule_Name,
          Schedule_Class,
          Record_Creator, 
          Is_Published,
          Record_CreatorDateTime,
          HQ_Schedule_Date
      ) 
      values(guid,scheduleName,scheduleType,scheduleCreater,'N',sysdate,yearMonth);
      
      
      ---处理明细表T_HQ_Schedule_Date_Record的数据 
    while PointerPrev < length(scheduleData) loop 
          Begin 
                PointerCurr := instr(scheduleData,',',PointerPrev);
                if PointerCurr>0 then 
                   Begin 
                        TId := substr(scheduleData,PointerPrev,PointerCurr-PointerPrev);
                    ------ 
                        scheduleDay := scheduleDay+1;
                         
                        tdate := to_date(substr(yearMonth,1,4)||'-'||substr(yearMonth,5,len(yearMonth) - 4)||'-' 
                                 ||extract(day from scheduleDay),'yyyy-mm-dd'); 
                                 
                         insert into T_HQ_Schedule_Date_Record
                         (HQ_Schedule_Date_Guid,HQ_Schedule_Guid,Schedule_Date, 
                         Schedule_Content,Record_Creator,Record_Creator,Record_CreatorDateTime) 
                         values(gid.nextval,guid,tdate,TId,scheduleCreater,sysdate);
                         
                         PointerPrev := PointerCurr+1 ;
                   End;
                end if; 
          end;
     end loop;
exception
         when others then
              dbms_output.put_line(sqlcode||':'||sqlerrm);
End;