CREATE PROCEDURE dbo.select_data
AS
BEGIN
--------------定义变量用于存储具体的数据信息------------------------------------------
declare @ge_date varchar(50)
declare @ge_organ varchar(50)
declare @ge_ccy varchar(50)
declare @ge_zhihang varchar(50)
declare @ge_busi varchar(50)
declare @ge_money decimal(38,6)
declare @ge_ccyU decimal(38,6)
declare @ge_ccyB decimal(38,6)
declare @ge_ccyC decimal(38,6)
declare @tablename varchar(50)
--------------定义变量用于进行初始化判断----------------------------------------------
declare @organ varchar(50)
declare @busi varchar(50)
declare @zhihang varchar(50)
select top 1 @organ=ge_organ,@busi=ge_busi,@zhihang=zhihang from ge_data
--------------建立游标cure,对整个表按照机构字段、要素字段、支行字段进行排序----------
declare cure cursor for select ge_data.ge_date, ge_data.ge_organ, ge_data.ge_ccy, ge_data.ge_busi, ge_data.ge_money, ge_data.tablename, ge_data.zhihang from ge_data order by ge_organ,ge_busi,zhihang
open cure
fetch cure into @ge_date,@ge_organ,@ge_ccy,@ge_busi,@ge_money,@tablename,@ge_zhihang
set @ge_ccyU=0,@ge_ccyB=0,@ge_ccyC=0
if @zhihang=@ge_zhihang
begin
-----------------判断机构和要素是否相同,相同则判断币种的余额存放到不同的变量中----------------------
if(@busi=@ge_busi and @organ=@ge_organ)
begin
if @ge_ccy='01'
begin
set @ge_ccyU=@ge_money
end
if @ge_ccy='91'
begin
set @ge_ccyB=@ge_money
end
if @ge_ccy='90'
begin
set @ge_ccyC=@ge_money
end
end
else
begin
-------------首先向支行表中插入该纪录,然后改变机构和要素的初始化值,再处理该条纪录的币种余额--------------------------
insert into ge_day (ge_date,ge_organ,ge_busi,ge_ccyC,ge_ccyU,ge_ccyB,tablename,ge_zhihang) values(@ge_date,@ge_organ,@ge_busi,@ge_ccyC,@ge_ccyU,@ge_ccyB,@tablename,@ge_zhihang)
set @busi=@ge_busi,@organ=@ge_organ
if @ge_ccy='01'
begin
set @ge_ccyU=@ge_money
end
if @ge_ccy='91'
begin
set @ge_ccyB=@ge_money
end
if @ge_ccy='90'
begin
set @ge_ccyC=@ge_money
end
end
end
else
begin
-----------首先改变初始化变量,然后处理该条数据纪录的币种余额------------------
set @zhihang=@ge_zhihang,@busi=@ge_busi,@organ=@ge_organ
if @ge_ccy='01'
begin
set @ge_ccyU=@ge_money
end
if @ge_ccy='91'
begin
set @ge_ccyB=@ge_money
end
if @ge_ccy='90'
begin
set @ge_ccyC=@ge_money
end
-----------一个支行导入完成调用存储过程处理数据-------------------------------
end
-------------------关闭游标释放游标-----------------------------------------------
close cure
deallocate cursor cure
END
AS
BEGIN
--------------定义变量用于存储具体的数据信息------------------------------------------
declare @ge_date varchar(50)
declare @ge_organ varchar(50)
declare @ge_ccy varchar(50)
declare @ge_zhihang varchar(50)
declare @ge_busi varchar(50)
declare @ge_money decimal(38,6)
declare @ge_ccyU decimal(38,6)
declare @ge_ccyB decimal(38,6)
declare @ge_ccyC decimal(38,6)
declare @tablename varchar(50)
--------------定义变量用于进行初始化判断----------------------------------------------
declare @organ varchar(50)
declare @busi varchar(50)
declare @zhihang varchar(50)
select top 1 @organ=ge_organ,@busi=ge_busi,@zhihang=zhihang from ge_data
--------------建立游标cure,对整个表按照机构字段、要素字段、支行字段进行排序----------
declare cure cursor for select ge_data.ge_date, ge_data.ge_organ, ge_data.ge_ccy, ge_data.ge_busi, ge_data.ge_money, ge_data.tablename, ge_data.zhihang from ge_data order by ge_organ,ge_busi,zhihang
open cure
fetch cure into @ge_date,@ge_organ,@ge_ccy,@ge_busi,@ge_money,@tablename,@ge_zhihang
set @ge_ccyU=0,@ge_ccyB=0,@ge_ccyC=0
if @zhihang=@ge_zhihang
begin
-----------------判断机构和要素是否相同,相同则判断币种的余额存放到不同的变量中----------------------
if(@busi=@ge_busi and @organ=@ge_organ)
begin
if @ge_ccy='01'
begin
set @ge_ccyU=@ge_money
end
if @ge_ccy='91'
begin
set @ge_ccyB=@ge_money
end
if @ge_ccy='90'
begin
set @ge_ccyC=@ge_money
end
end
else
begin
-------------首先向支行表中插入该纪录,然后改变机构和要素的初始化值,再处理该条纪录的币种余额--------------------------
insert into ge_day (ge_date,ge_organ,ge_busi,ge_ccyC,ge_ccyU,ge_ccyB,tablename,ge_zhihang) values(@ge_date,@ge_organ,@ge_busi,@ge_ccyC,@ge_ccyU,@ge_ccyB,@tablename,@ge_zhihang)
set @busi=@ge_busi,@organ=@ge_organ
if @ge_ccy='01'
begin
set @ge_ccyU=@ge_money
end
if @ge_ccy='91'
begin
set @ge_ccyB=@ge_money
end
if @ge_ccy='90'
begin
set @ge_ccyC=@ge_money
end
end
end
else
begin
-----------首先改变初始化变量,然后处理该条数据纪录的币种余额------------------
set @zhihang=@ge_zhihang,@busi=@ge_busi,@organ=@ge_organ
if @ge_ccy='01'
begin
set @ge_ccyU=@ge_money
end
if @ge_ccy='91'
begin
set @ge_ccyB=@ge_money
end
if @ge_ccy='90'
begin
set @ge_ccyC=@ge_money
end
-----------一个支行导入完成调用存储过程处理数据-------------------------------
end
-------------------关闭游标释放游标-----------------------------------------------
close cure
deallocate cursor cure
END