17,382
社区成员




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;
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);
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;