刚开始学习存储过程,写了个MSSQL的,不太熟悉ORACLE的该怎么写。希望各位大侠帮忙set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[proc_getTotal]--<getTotal, gjjktemp, getTotal>
-- Add the parameters for the stored procedure here
--declare
@tableNamed nvarchar(20),
@start nvarchar(20),
@end nvarchar(20),
@centerid varchar(20),
@stationid varchar(20),
@itemid int,
@itemcode varchar(20),
@a int output
AS
BEGIN
--set @tableNamed = 'R_Hour_Data'
--set @start = '2009-1-1 0:00:00'
--set @end = '2010-12-31 0:00:00'
--set @centerid = 86
--set @stationid = 6
--set @itemid = 12516
--set @itemcode = '1CV'
declare @countOne int
declare @countTwo int
declare @s nvarchar(4000)
declare @startyear nvarchar(4) --开始年
declare @endyear nvarchar(4) --结束年
declare @tableName nvarchar(40) --表名
declare @colName nvarchar(40) --列名
set @startyear = substring(@start,1,5)
set @endyear = substring(@end,1,5) SET NOCOUNT ON;
if @tableNamed is not null
begin
set @s='select @i=count(*) from '+@tableNamed+'_'+@startyear+' as a where a.StaticDate >= '''+@start+''' and a.StaticDate<= '''+@end+''' and a.centerid = '+@centerid+' and a.stationid = '+@stationid+' and a.itemcode = '''+@itemcode+''''
print @s
exec sp_executesql @s,N'@i int output',@i=@countOne output --定义sql语句里参数
if @startyear != @endyear
begin
set @s='select @i=count(*) from '+@tableNamed+'_'+@endyear+' as a where a.StaticDate >= '''+@start+''' and a.StaticDate<= '''+@end+''' and a.centerid = '+@centerid+' and a.stationid = '+@stationid+' and a.itemcode = '''+@itemcode+''''
print @s
exec sp_executesql @s,N'@i int output',@i=@countTwo output --定义sql语句里参数
end
else
begin
set @countTwo = 0
end
end
else
begin
if @itemid != 0
begin
select @tableName = HisTableName, @colName = ColName from T_Item_Mapping where id = @itemid
end
set @s='select @i=count(*) from '+@tableName+'_'+@startyear+' as a where a.GetTime >= '''+@start+''' and a.gettime<= '''+@end+''''
exec sp_executesql @s,N'@i int output',@i=@countOne output --定义sql语句里参数
if @startyear != @endyear
begin
set @s='select @i=count(*) from '+@tableName+'_'+@endyear+' as a where a.GetTime >= '''+@start+''' and a.gettime<= '''+@end+''''
exec sp_executesql @s,N'@i int output',@i=@countTwo output --定义sql语句里参数
end
else
begin
set @countTwo = 0
end
end
set @a = @countOne+@countTwo
print @a
END
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[proc_getTotal]--<getTotal, gjjktemp, getTotal>
-- Add the parameters for the stored procedure here
--declare
@tableNamed nvarchar(20),
@start nvarchar(20),
@end nvarchar(20),
@centerid varchar(20),
@stationid varchar(20),
@itemid int,
@itemcode varchar(20),
@a int output
AS
BEGIN
--set @tableNamed = 'R_Hour_Data'
--set @start = '2009-1-1 0:00:00'
--set @end = '2010-12-31 0:00:00'
--set @centerid = 86
--set @stationid = 6
--set @itemid = 12516
--set @itemcode = '1CV'
declare @countOne int
declare @countTwo int
declare @s nvarchar(4000)
declare @startyear nvarchar(4) --开始年
declare @endyear nvarchar(4) --结束年
declare @tableName nvarchar(40) --表名
declare @colName nvarchar(40) --列名
set @startyear = substring(@start,1,5)
set @endyear = substring(@end,1,5) SET NOCOUNT ON;
if @tableNamed is not null
begin
set @s='select @i=count(*) from '+@tableNamed+'_'+@startyear+' as a where a.StaticDate >= '''+@start+''' and a.StaticDate<= '''+@end+''' and a.centerid = '+@centerid+' and a.stationid = '+@stationid+' and a.itemcode = '''+@itemcode+''''
print @s
exec sp_executesql @s,N'@i int output',@i=@countOne output --定义sql语句里参数
if @startyear != @endyear
begin
set @s='select @i=count(*) from '+@tableNamed+'_'+@endyear+' as a where a.StaticDate >= '''+@start+''' and a.StaticDate<= '''+@end+''' and a.centerid = '+@centerid+' and a.stationid = '+@stationid+' and a.itemcode = '''+@itemcode+''''
print @s
exec sp_executesql @s,N'@i int output',@i=@countTwo output --定义sql语句里参数
end
else
begin
set @countTwo = 0
end
end
else
begin
if @itemid != 0
begin
select @tableName = HisTableName, @colName = ColName from T_Item_Mapping where id = @itemid
end
set @s='select @i=count(*) from '+@tableName+'_'+@startyear+' as a where a.GetTime >= '''+@start+''' and a.gettime<= '''+@end+''''
exec sp_executesql @s,N'@i int output',@i=@countOne output --定义sql语句里参数
if @startyear != @endyear
begin
set @s='select @i=count(*) from '+@tableName+'_'+@endyear+' as a where a.GetTime >= '''+@start+''' and a.gettime<= '''+@end+''''
exec sp_executesql @s,N'@i int output',@i=@countTwo output --定义sql语句里参数
end
else
begin
set @countTwo = 0
end
end
set @a = @countOne+@countTwo
print @a
END
v_tableNamed varchar,
v_start varchar,
v_end varchar,
v_centerid varchar,
v_stationid varchar,
i_itemid int,
v_itemcode varchar,
i_a int output
)
iS
i_countOne int;
i_countTwo int;
v_s varchar(4000);
v_startyear varchar(4); --开始年
v_endyear varchar(4); --结束年
v_tableName varchar(40); --表名
v_colName varchar(40); --列名
BEGIN
v_startyear := substr(v_start,1,5);
v_endyear := substr(v_end,1,5); if v_tableNamed is not null
......
else
....
end if;
END proc_getTotal;
看一下PL/Sql