SQL Server 中 自定义函数 和 游标 应用的经典案例

/* 步骤1

问题:  

删除本地及海关单证待分派表、报关单表中的数据
delete from W_DOCUMENTS;
delete from W_DOCUMENTS_TEST;
delete from W_DECLARATIONS_TEST;

假设环境如下:

统计本地及海关单证待分派表、报关单表中的数据总条数
select count(*) from W_DOCUMENTS;
select count(*) from W_DOCUMENTS_TEST;
select count(*) from W_DECLARATIONS_TEST;

    表1:      ID, NAME,      QQ,     PHONE,

*/

表中数据:      1       秦云        10102800 13500000

/* 步骤2

                2       在路上      10378    13600000

调用带输入参数的存储过程
GenBGDH
条件2 I_E_FLAG,
条件1 TRAF_MODE,
固定前4位的单证号 TMP_ENTRY_ID
航次 VOYAGE_NO,
船名 TRAF_NAME,
提单号 BILL_NO,
是否拼箱、联单(1:是|0:是) @flag,
条数(数字范围:0~32767) @count

                3       LEO         10000    13900000

create procedure GenBGDH @I_E_FLAG varchar(10),@TRAF_MODE varchar(10),@TMP_ENTRY_ID bigint,
@VOYAGE_NO varchar(100),@TRAF_NAME varchar(100),@BILL_NO varchar(100),@flag tinyint,@count smallint
with encryption
as
declare @ENTRY_ID bigint;
while(@count>0)
begin
SET @ENTRY_ID =convert(bigint,ceiling(rand()*100000000000000+@TMP_ENTRY_ID));
if @flag=1
begin
insert into W_DECLARATIONS_TEST(I_E_FLAG,TRAF_MODE,ENTRY_ID,VOYAGE_NO,TRAF_NAME,BILL_NO)
values(@I_E_FLAG,@TRAF_MODE,@ENTRY_ID,@VOYAGE_NO,@TRAF_NAME,@BILL_NO);
end
else if @flag=0
begin
insert into W_DECLARATIONS_TEST(I_E_FLAG,TRAF_MODE,ENTRY_ID)
values(@I_E_FLAG,@TRAF_MODE,@ENTRY_ID);
end
else print'不执行插入数据操作'
set @count=@count-1;
end

    表2:      ID, NAME,  上机时间,管理员,

*/

表中数据:     1   秦云    2004-1-1  李大伟

/* 步骤3

               2   秦云    2005-1-1  马化腾

匹配生成单证规则的数量
条件1 条件2
g o
b w
a f
v x
g d
j f
船名 航次 提单号 箱号
cm hc bn cn
TRAF_NAME VOYAGE_NO BILL_NO CONTAINER_NO
CM1 HC1 BN01 CN1
CM1 HC1 BN02 CN2

               3    在路上  2005-1-1  马化腾

匹配生成单证规则
declare @p1 varchar(10),@p2 varchar(10),@p3 bigint,@p4 varchar(100);
declare @p5 varchar(100),@p6 varchar(100),@p7 tinyint,@p8 smallint;

               4    秦云   2005-1-1  李大伟

set @p1='g';
set @p2='o';
set @p3=534500000000000000;
set @p4='HC1';
set @p5='CM1';
set @p6='BN01';
set @p7=0;
set @p8=100;

               5   在路上 2005-1-1  李大伟

begin
exec GenBGDH @p2,@p1,@p3,@p4,@p5,@p6,@p7,@p8
end

实现目的:从表1中取所有人员列表,从表2中取上机次数和管理员.

*/

             上机人员名单    上机次数   管理员(上这几次机的每个管理员都列出来)

/* 步骤4

               秦云             3             李大伟,马化腾,李大伟

选择报关单表中的单证号、条件1、条件2列中数据,插入到本地单证待分派表

               在路上           2            马化腾,李大伟

insert into W_DOCUMENTS_TEST(id,I_E_FLAG,TRADE_MODE,ENTRY_ID)
select id,I_E_FLAG,TRAF_MODE,ENTRY_ID from W_DECLARATIONS_TEST;

               LEO              0      

*/

如果不算管理员那一列的话,我是这样写的。

/*
步骤5
更新本地单证待分派表中其他列数据
将OP_DATE字段的时间改成与当前系统时间相差为1个小时以内的时间

SELECT  表1.NAME AS 姓名, COUNT(表2.ID) AS 上机次数

UPDATE W_DOCUMENTS_TEST
SET [CREATE_DATE] =getdate()
,[DECL_PORT] =convert(int,ceiling(rand()*1000+1000))
,[RANDOM_FLAG] =convert(int,ceiling(rand()*10))
,[OP_DATE] =dateadd(hh,-0.5,getdate())
,[ENTRY_TYPE] =1
,[D_DATE] =getdate()
,[CERT_FLAG] =1
,[TAX_FLAG] =1;

FROM  表1 LEFT OUTER JOIN

*/

      表2 ON 表1.NAME = 表2.NAME

/*
其他
2个时间之间相差的秒数

GROUP BY 表1.名称

select datediff(mi,'2013-10-11 16:13:08','2013-10-11 17:18:39');

解答:
测试用例

查询已成功分派了多少条单证

create table 表1( --drop table 表1

select count(*) from W_DOCUMENTS;

ID     int,

查询报关单表中是否有重复生成的单证号数据

NAME   varchar(10),

select count(*) from W_DECLARATIONS_TEST group by entry_id having count(id)<>1

QQ     varchar(10),

查询海关单证待分派表中是否有重复生成的单证号数据

PHONE  varchar(20)

select count(*) from W_DOCUMENTS_TEST group by entry_id having count(id)<>1

)

*/

insert into 表1 values(1   ,'秦云'    ,'10102800'     ,'13500000')

insert into 表1 values(2   ,'在路上'  ,'10378'        ,'13600000')

insert into 表1 values(3   ,'LEO'     ,'10000'        ,'13900000')

create table 表2( --drop table 表2

ID        int,

NAME    varchar(10) ,

上机时间  datetime,

管理员    varchar(10)

)

insert into 表2  values(1,'秦云'   ,cast('2004-1-1' as datetime),'李大伟')

insert into 表2  values(2,'秦云'   ,cast('2005-1-1' as datetime),'马化腾')

insert into 表2  values (3,'在路上' ,cast('2005-1-1' as datetime),'马化腾')

本文由金沙官网线上发布于数据库,转载请注明出处:SQL Server 中 自定义函数 和 游标 应用的经典案例

您可能还会对下面的文章感兴趣: