create procedure sp_data_jzxttl
astruncate table 航空事故和航空器事实表
---------------变量声明--------------
declare @公司编码 varchar(50)
declare @设备编码 varchar(50)
declare @事故原因编码 varchar(50)
declare @事故等级编码 varchar(50)
declare @航班编码 varchar(50)
declare @时间 smalldatetime
declare @人次 int
declare @损失金额 int ---------------随机数种子-------
declare @seed int
declare @seedtime smalldatetime
declare @seednum int
--------------------声明游标----------
declare cur_gs scroll cursor for select 公司编码 from 公司维度表
declare cur_SBBM scroll cursor for select 设备编码 from 设备维度表
declare cur_SGYY scroll cursor for select 事故原因编码 from 事故原因维度表
declare cur_SGDJ scroll cursor for select 事故等级编码 from 事故等级维度表
declare cur_HBBM scroll cursor for select 航班编码 from 航班维度表
------------------打开游标-----------------------
open cur_gs
open cur_SBBM
open cur_SGYY
open cur_SGDJ
open cur_HBBM------------------初始化随机种子----------------
set @seedtime='2000-1-1'
while @seedtime<'2004-1-1'
begin
set @seed=convert(int,@seedtime)+datepart(millisecond,getdate())
set @seednum=convert(int,rand(@seed)*10000)%33+1
----------------金额数据--------
set @人次=(@seednum+30)%9+@seed%3
set @损失金额=@seednum*200+@seed
----------------代码数据-------
fetch next from cur_gs into @公司编码
if @@fetch_status=-1 fetch first from cur_gs into @公司编码
fetch next from cur_SBBM into @设备编码
if @@fetch_status=-1 fetch first from cur_SBBM into @设备编码
fetch next from cur_SGYY into @事故原因编码
if @@fetch_status=-1 fetch first from cur_SGYY into @事故原因编码
fetch next from cur_SGDJ into @事故等级编码
if @@fetch_status=-1 fetch first from cur_SGDJ into @事故等级编码
fetch next from cur_HBBM into @航班编码
if @@fetch_status=-1 fetch first from cur_HBBM into @航班编码-----------------------模拟数据生成----------------------
set @时间=@seedtime insert into 航空事故和航空器事实表
(时间,设备编码,公司编码,事故原因编码,事故等级编码,航班编码,人次,损失金额)
values(@时间,@设备编码,@公司编码,@事故原因编码,@事故等级编码,@航班编码,@人次,@损失金额) set @seedtime=@seedtime+1
end----------------------数据生成结束,关闭游标------------
close cur_gs
close cur_SBBM
close cur_SGYY
close cur_SGDJ
close cur_HBBM open cur_gs
open cur_SBBM
open cur_SGYY
open cur_SGDJ
open cur_HBBM deallocate cur_gs
deallocate cur_SBBM
deallocate cur_SGYY
deallocate cur_SGDJ
deallocate cur_HBBM return
astruncate table 航空事故和航空器事实表
---------------变量声明--------------
declare @公司编码 varchar(50)
declare @设备编码 varchar(50)
declare @事故原因编码 varchar(50)
declare @事故等级编码 varchar(50)
declare @航班编码 varchar(50)
declare @时间 smalldatetime
declare @人次 int
declare @损失金额 int ---------------随机数种子-------
declare @seed int
declare @seedtime smalldatetime
declare @seednum int
--------------------声明游标----------
declare cur_gs scroll cursor for select 公司编码 from 公司维度表
declare cur_SBBM scroll cursor for select 设备编码 from 设备维度表
declare cur_SGYY scroll cursor for select 事故原因编码 from 事故原因维度表
declare cur_SGDJ scroll cursor for select 事故等级编码 from 事故等级维度表
declare cur_HBBM scroll cursor for select 航班编码 from 航班维度表
------------------打开游标-----------------------
open cur_gs
open cur_SBBM
open cur_SGYY
open cur_SGDJ
open cur_HBBM------------------初始化随机种子----------------
set @seedtime='2000-1-1'
while @seedtime<'2004-1-1'
begin
set @seed=convert(int,@seedtime)+datepart(millisecond,getdate())
set @seednum=convert(int,rand(@seed)*10000)%33+1
----------------金额数据--------
set @人次=(@seednum+30)%9+@seed%3
set @损失金额=@seednum*200+@seed
----------------代码数据-------
fetch next from cur_gs into @公司编码
if @@fetch_status=-1 fetch first from cur_gs into @公司编码
fetch next from cur_SBBM into @设备编码
if @@fetch_status=-1 fetch first from cur_SBBM into @设备编码
fetch next from cur_SGYY into @事故原因编码
if @@fetch_status=-1 fetch first from cur_SGYY into @事故原因编码
fetch next from cur_SGDJ into @事故等级编码
if @@fetch_status=-1 fetch first from cur_SGDJ into @事故等级编码
fetch next from cur_HBBM into @航班编码
if @@fetch_status=-1 fetch first from cur_HBBM into @航班编码-----------------------模拟数据生成----------------------
set @时间=@seedtime insert into 航空事故和航空器事实表
(时间,设备编码,公司编码,事故原因编码,事故等级编码,航班编码,人次,损失金额)
values(@时间,@设备编码,@公司编码,@事故原因编码,@事故等级编码,@航班编码,@人次,@损失金额) set @seedtime=@seedtime+1
end----------------------数据生成结束,关闭游标------------
close cur_gs
close cur_SBBM
close cur_SGYY
close cur_SGDJ
close cur_HBBM open cur_gs
open cur_SBBM
open cur_SGYY
open cur_SGDJ
open cur_HBBM deallocate cur_gs
deallocate cur_SBBM
deallocate cur_SGYY
deallocate cur_SGDJ
deallocate cur_HBBM return
解决方案 »
- 如何解决sql server express 数据文件限制大小的问题?
- 求助啊~~~~~~~~~~~~~~~~~~~~
- ASP+access转换MSSQL需要多少费用
- 怎么把查询到的结果连接起来?(在线等)
- 求一SQL语句或是存储过程??在线急!!回答马上结贴
- SQL 2000 用户角色 问题
- MSSQL2005,如果使用SNAPSHOT_READ_COMMITTED会不会导致Transactional Replication失败?
- 怎样改变一个database的名字
- 用 ADO 从数据库查找所有,如果记录很多(十万或更多),可不可以? ADO 是怎样处理的?
- sql语法错误
- 求SQL语句:如何将一个表中的某一列的数据全部复制到另一个表中,根据一个两张表里都有的ID列
- 字段類型轉換問題
astruncate table 航空事故和航空器事实表
---------------变量声明--------------
declare @公司编码 varchar(50)
declare @设备编码 varchar(50)
declare @事故原因编码 varchar(50)
declare @事故等级编码 varchar(50)
declare @航班编码 varchar(50)
declare @时间 smalldatetime
declare @人次 int
declare @损失金额 int ---------------随机数种子-------
declare @seed int
declare @seedtime smalldatetime
declare @seednum int
--------------------声明游标----------
declare cur_gs scroll cursor for select 公司编码 from 公司维度表
declare cur_SBBM scroll cursor for select 设备编码 from 设备维度表
declare cur_SGYY scroll cursor for select 事故原因编码 from 事故原因维度表
declare cur_SGDJ scroll cursor for select 事故等级编码 from 事故等级维度表
declare cur_HBBM scroll cursor for select 航班编码 from 航班维度表
------------------打开游标-----------------------
open cur_gs
open cur_SBBM
open cur_SGYY
open cur_SGDJ
open cur_HBBM------------------初始化随机种子----------------
set @seedtime='2000-1-1'
while @seedtime<'2004-1-1'
begin
set @seed=convert(int,@seedtime)+datepart(millisecond,getdate())
set @seednum=convert(int,rand(@seed)*10000)%33+1
----------------金额数据--------
set @人次=(@seednum+30)%9+@seed%3
set @损失金额=@seednum*200+@seed
----------------代码数据-------
fetch next from cur_gs into @公司编码
if @@fetch_status=-1 fetch first from cur_gs into @公司编码
fetch next from cur_SBBM into @设备编码
if @@fetch_status=-1 fetch first from cur_SBBM into @设备编码
fetch next from cur_SGYY into @事故原因编码
if @@fetch_status=-1 fetch first from cur_SGYY into @事故原因编码
fetch next from cur_SGDJ into @事故等级编码
if @@fetch_status=-1 fetch first from cur_SGDJ into @事故等级编码
fetch next from cur_HBBM into @航班编码
if @@fetch_status=-1 fetch first from cur_HBBM into @航班编码-----------------------模拟数据生成----------------------
set @时间=@seedtime insert into 航空事故和航空器事实表
(时间,设备编码,公司编码,事故原因编码,事故等级编码,航班编码,人次,损失金额)
values(@时间,@设备编码,@公司编码,@事故原因编码,@事故等级编码,@航班编码,@人次,@损失金额) set @seedtime=@seedtime+1
end----------------------数据生成结束,关闭游标------------
close cur_gs
close cur_SBBM
close cur_SGYY
close cur_SGDJ
close cur_HBBM open cur_gs
open cur_SBBM
open cur_SGYY
open cur_SGDJ
open cur_HBBM deallocate cur_gs
deallocate cur_SBBM
deallocate cur_SGYY
deallocate cur_SGDJ
deallocate cur_HBBM return
select '1','2'...
union
select '3','2'...
...
谢谢老兄就是满了一点!!