SqlServer存儲過程示例

/* 步驟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;

*/

/* 步驟2

調用帶輸入參數的存儲過程
GenBGDH
條件2 I_E_FLAG,
條件1 TRAF_MODE,
固定前4位的單證號 TMP_ENTRY_ID
航次 VOYAGE_NO,
船名 TRAF_NAME,
提單號 BILL_NO,
是否拼箱、聯單(1：是|0：是) @flag,
條數(數字範圍：0~32767) @count

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

*/

/* 步驟3

匹配生成單證規則的數量
條件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

匹配生成單證規則
declare @p1 varchar(10),@p2 varchar(10),@p3 bigint,@p4 varchar(100);
declare @p5 varchar(100),@p6 varchar(100),@p7 tinyint,@p8 smallint;

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

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

*/

/* 步驟4

選擇報關單表中的單證號、條件1、條件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;

*/

/*
步驟5
更新本地單證待分派表中其他列數據
將OP_DATE欄位的時間改成與當前系統時間相差為1個小時以內的時間

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;

*/

/*
其他
2個時間之間相差的秒數

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

查詢已成功分派了多少條單證

select count(*) from W_DOCUMENTS;

查詢報關單表中是否有重覆生成的單證號數據

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

查詢海關單證待分派表中是否有重覆生成的單證號數據

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

*/