Question

sqlserver的存储过程转化成oracle的存储过程

drop proc up_tongji_generateElct_1
go
create proc up_tongji_generateElct_1
@beginDate datetime=null,
@endDate datetime=null
as
if @beginDate is null and @endDate is null
begin
--统计上月发电考核明细
set @endDate=CONVERT(varchar(8),getdate(),120)+'01'
set @beginDate=CONVERT(varchar(8),dateadd(month,-1,getdate()),120)+'01'
end
print @beginDate
print @endDate
select * into #tmp_发电汇总 from troubleGenElctRecord
where generateElctBegin between @beginDate and @endDate
alter table #tmp_发电汇总 add bsno900_1 varchar(20) null
alter table #tmp_发电汇总 add bsno900_2 varchar(20) null
alter table #tmp_发电汇总 add bsno1800_1 varchar(20) null
alter table #tmp_发电汇总 add bsno1800_2 varchar(20) null
alter table #tmp_发电汇总 add bsno3G varchar(20) null
alter table #tmp_发电汇总 add 是否超时 varchar(20) null
alter table #tmp_发电汇总 add roomId varchar(20) null
--select * from #tmp_发电汇总 where roomId is null
update #tmp_发电汇总 set bsno3G=(select top 1 bsNo from room_basestation where room_basestation.roomId=#tmp_发电汇总.roomId and bsNo like 'CDW%')
where roomId is not null and bsno3G is null
and exists(select 1 from room_basestation where room_basestation.roomId=#tmp_发电汇总.roomId and bsNo like 'CDW%')
on #tmp_发电汇总.BsRoomNo=room_basestation.bsNo

Answer 1

CREATE OR REPLACE PROCEDURE UP_TONGJI_GENERATEELCT_1(BEGINDATE IN VARCHAR,
ENDDATE IN VARCHAR) IS
BEGIN
IF BEGINDATE IS NULL AND ENDDATE IS NULL THEN
--开始日期2014-01-01
select to_char(add_months(trunc(sysdate),-1),'YYYY-MM')||'-01' INTO BEGINDATE from dual;
--结束日期2014-02-01
select to_char(add_months(trunc(sysdate),0),'YYYY-MM')||'-01' INTO ENDDATE from dual;
END IF;
INSERT INTO TMP_发电汇总 SELECT * FROM TROUBLEGENELCTRECORD WHERE GENERATEELCTBEGIN >=BEGINDATE AND GENERATEELCTBEGIN<= ENDDATE;
commit;
--select * from #tmp_发电汇总 where roomId is null
UPDATE TMP_发电汇总 A
SET BSNO3G =
(SELECT TOP 1 BSNO
FROM ROOM_BASESTATION
WHERE ROOM_BASESTATION.ROOMID = A.ROOMID
AND BSNO LIKE 'CDW%')
WHERE ROOMID IS NOT NULL
AND BSNO3G IS NULL
AND EXISTS (SELECT 1
FROM ROOM_BASESTATION
WHERE ROOM_BASESTATION.ROOMID = A.ROOMID
AND BSNO LIKE 'CDW%') ON
A.BSROOMNO = ROOM_BASESTATION.BSNO;
COMMIT;
END UP_TONGJI_GENERATEELCT_1;
有疑问再说说！~

追问

你这种方法的话,TMP_发电汇总这张表是一开始就建好的临时表还是正式表呢?

追答

不知道你原来的要求，可以直接更新正式表中去啊，不需要临时表。

Answer 2

这个没有什么好的办法，

只能自己改写。