您所在位置：主题：帮忙看下存储过程是什么意思
huangyu_00007
[专家分：0] 发布于 2007-11-28 16:36:00
CREATE PROCEDURE dbo.UP_simulatorDailyReport_new
(
@begintime varchar(10)='2003/11/02',
@beginHour varchar(2)='10',
@endtime varchar(10)='2003/11/02',
@endHour varchar(2)='10',
@portList varchar(5000)="1|2&1|1&1|3&1|45" --选择的站点列表
)
AS
--
SET NOCOUNT ON
--
--临时表，保存返回结果-----------------------------------------------------
CREATE TABLE #tmp_table(
station_code SMALLINT,
port_code SMALLINT,
chk_time int,
address VARCHAR(50),
prop_name VARCHAR(50),
prop_code smallint,
m_a_value NUMERIC(9,2) null,
a_a_value NUMERIC(9,2) null
)
--保存时间列表
CREATE TABLE #tmp_timeList(
chk_time INT
primary key (chk_time ))
--将时间段分片插入暂存时间列表中(小时)
INSERT INTO #tmp_timeList
select * from dbo.fn_timeTable_new (@begintime,@beginHour,@endtime,@endHour, 'dd')
--SELECT * FROM fn_timeTable('2003/11/02', '2003/11/03')
--select * from #tmp_timeList
DECLARE @station_code SMALLINT --分站号
DECLARE @port_code SMALLINT --测点号
DECLARE @c_int INT --记录数
DECLARE @address VARCHAR(50)
DECLARE @prop_name VARCHAR(50)
DECLARE @prop_code smallint
DECLARE @station_name VARCHAR(50)
declare @dtStartTm datetime--起始时间
declare @dtEndTm datetime--结束时间
set @dtStartTm = @begintime +' '+ @beginHour + ':00:00'
set @dtEndTm = @endtime + ' ' + @endHour + ':00:00'
set @dtEndTm = dateadd(hour, 1, @dtEndtm)
--逐个站点循环得到相应的记录
IF @portList<>''
DECLARE portList_cursor CURSOR FOR SELECT station_code, port_code FROM dbo.fn_selectedDot_byTM( @portList, 2, @begintime) ORDER BY station_code, port_code
ELSE
DECLARE portList_cursor CURSOR FOR SELECT station_code, port_code FROM v_station_port_hist where port_class_code=2 and tm=dbo.f_getTM(@begintime) ORDER BY station_code, port_code
OPEN portList_cursor
FETCH NEXT FROM portList_cursor INTO @station_code , @port_code
WHILE @@FETCH_STATUS = 0
BEGIN
SET @c_int =0
--PRINT " " + convert(char(4), @station_code) + ":" + convert(char(4), @port_code)
IF exists (SELECT top 1 station_code FROM v_chk_port_data WHERE station_code=@station_code and port_code=@port_code and chk_time >= @dtStartTm and chk_time <= @dtEndTm
and simulator_value is not null )
SET @c_int =1
--PRINT " @c_int: " + convert(char(4), @c_int)
--如果站点时间段中的记录数大于0
IF @c_int >0
BEGIN
SELECT @station_name=a.station_name, @address = a.address, @prop_name = b.prop_name ,@prop_code=a.prop_code FROM v_station_port_hist a, prop b WHERE
a.prop_code = b.prop_code and
a.port_class_code = b.prop_class_code and
a.tm = dbo.f_getTM(@begintime) and
a.station_code=@station_code and a.port_code=@port_code
--将某站点时间段中的记录分小时插入到暂存表中
INSERT INTO #tmp_table
SELECT @station_code, @port_code, b.chk_time, @address , @prop_name, @prop_code, a.max_value , a.avg_value
FROM
(SELECT datepart(month,chk_time)* 10000 + datepart(day, chk_time)*100 + datepart(Hour, chk_time) chk_time, max(simulator_value) max_value , avg(simulator_value) avg_value
FROM v_chk_port_data WHERE station_code=@station_code and port_code=@port_code
and chk_time >= @dtStartTm and chk_time <= @dtEndTm and simulator_value is not null
GROUP BY datepart(month,chk_time)* 10000 + datepart(day, chk_time)*100 + datepart(Hour, chk_time) ) a,
#tmp_timeList b
WHERE a.chk_time =* b.chk_time
END
-- Get the next port.
FETCH NEXT FROM portList_cursor INTO @station_code , @port_code
END
CLOSE portList_cursor
DEALLOCATE portList_cursor
SELECT station_code, port_code, chk_time,address, prop_name, prop_code, m_a_value, a_a_value FROM #tmp_table ORDER BY STATION_CODE, PORT_CODE,chk_time
drop table #tmp_table
drop table #tmp_timeList
GO
帮忙看下这存储是什么意思，谢了
(
@begintime varchar(10)='2003/11/02',
@beginHour varchar(2)='10',
@endtime varchar(10)='2003/11/02',
@endHour varchar(2)='10',
@portList varchar(5000)="1|2&1|1&1|3&1|45" --选择的站点列表
)
AS
--
SET NOCOUNT ON
--
--临时表，保存返回结果-----------------------------------------------------
CREATE TABLE #tmp_table(
station_code SMALLINT,
port_code SMALLINT,
chk_time int,
address VARCHAR(50),
prop_name VARCHAR(50),
prop_code smallint,
m_a_value NUMERIC(9,2) null,
a_a_value NUMERIC(9,2) null
)
--保存时间列表
CREATE TABLE #tmp_timeList(
chk_time INT
primary key (chk_time ))
--将时间段分片插入暂存时间列表中(小时)
INSERT INTO #tmp_timeList
select * from dbo.fn_timeTable_new (@begintime,@beginHour,@endtime,@endHour, 'dd')
--SELECT * FROM fn_timeTable('2003/11/02', '2003/11/03')
--select * from #tmp_timeList
DECLARE @station_code SMALLINT --分站号
DECLARE @port_code SMALLINT --测点号
DECLARE @c_int INT --记录数
DECLARE @address VARCHAR(50)
DECLARE @prop_name VARCHAR(50)
DECLARE @prop_code smallint
DECLARE @station_name VARCHAR(50)
declare @dtStartTm datetime--起始时间
declare @dtEndTm datetime--结束时间
set @dtStartTm = @begintime +' '+ @beginHour + ':00:00'
set @dtEndTm = @endtime + ' ' + @endHour + ':00:00'
set @dtEndTm = dateadd(hour, 1, @dtEndtm)
--逐个站点循环得到相应的记录
IF @portList<>''
DECLARE portList_cursor CURSOR FOR SELECT station_code, port_code FROM dbo.fn_selectedDot_byTM( @portList, 2, @begintime) ORDER BY station_code, port_code
ELSE
DECLARE portList_cursor CURSOR FOR SELECT station_code, port_code FROM v_station_port_hist where port_class_code=2 and tm=dbo.f_getTM(@begintime) ORDER BY station_code, port_code
OPEN portList_cursor
FETCH NEXT FROM portList_cursor INTO @station_code , @port_code
WHILE @@FETCH_STATUS = 0
BEGIN
SET @c_int =0
--PRINT " " + convert(char(4), @station_code) + ":" + convert(char(4), @port_code)
IF exists (SELECT top 1 station_code FROM v_chk_port_data WHERE station_code=@station_code and port_code=@port_code and chk_time >= @dtStartTm and chk_time <= @dtEndTm
and simulator_value is not null )
SET @c_int =1
--PRINT " @c_int: " + convert(char(4), @c_int)
--如果站点时间段中的记录数大于0
IF @c_int >0
BEGIN
SELECT @station_name=a.station_name, @address = a.address, @prop_name = b.prop_name ,@prop_code=a.prop_code FROM v_station_port_hist a, prop b WHERE
a.prop_code = b.prop_code and
a.port_class_code = b.prop_class_code and
a.tm = dbo.f_getTM(@begintime) and
a.station_code=@station_code and a.port_code=@port_code
--将某站点时间段中的记录分小时插入到暂存表中
INSERT INTO #tmp_table
SELECT @station_code, @port_code, b.chk_time, @address , @prop_name, @prop_code, a.max_value , a.avg_value
FROM
(SELECT datepart(month,chk_time)* 10000 + datepart(day, chk_time)*100 + datepart(Hour, chk_time) chk_time, max(simulator_value) max_value , avg(simulator_value) avg_value
FROM v_chk_port_data WHERE station_code=@station_code and port_code=@port_code
and chk_time >= @dtStartTm and chk_time <= @dtEndTm and simulator_value is not null
GROUP BY datepart(month,chk_time)* 10000 + datepart(day, chk_time)*100 + datepart(Hour, chk_time) ) a,
#tmp_timeList b
WHERE a.chk_time =* b.chk_time
END
-- Get the next port.
FETCH NEXT FROM portList_cursor INTO @station_code , @port_code
END
CLOSE portList_cursor
DEALLOCATE portList_cursor
SELECT station_code, port_code, chk_time,address, prop_name, prop_code, m_a_value, a_a_value FROM #tmp_table ORDER BY STATION_CODE, PORT_CODE,chk_time
drop table #tmp_table
drop table #tmp_timeList
GO
帮忙看下这存储是什么意思，谢了