【澳门金沙vip】SQL Server 中 自定义函数 和 游标 应用的经典案例

/* 步骤1

问题:
 

/*sql 语法学习*/

删除本地及海关单证待分派表、报关单表中的数据
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,

获取当前时间(时/分/秒):select convert(varchar(10),getdate(),8)
获取当前年月日:select convert(varchar(10),getdate(),120)
获取当前年月:select convert(varchar(7),getdate(),120)
获取当前年月:select convert(varchar(10),year(getdate())) + ‘-‘ +
convert(varchar(10),month(getDate()))

*/

表中数据:      1       秦云        10102800 13500000

select cast(b as integer) as bb from table1 where b = ’11’

/* 步骤2

                2       在路上      10378    13600000

select a,case b when ’11’ then ‘细细’ when ’22’ then ‘呵呵’ else ‘哈哈’
end as 转换,c from table1

调用带输入参数的存储过程
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

select a,b,case when c = ‘111’ then ‘细细’ when c = ‘222’ then ‘呵呵’
else ‘哈哈’ end as 转换1 from table1

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,  上机时间,管理员,

获取当前时间:print current_timestamp

*/

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

/*---------------------------------------------*/

/* 步骤3

               2   秦云    2005-1-1  马化腾

—————–将sql查询输出到txt文本文件中——————————————-
EXEC master..xp_cmdshell ‘bcp 数据库名.dbo.表名 out d:/1.txt -c -q
-U”sa” -P”password”‘

匹配生成单证规则的数量
条件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  李大伟

—————————round的用法beigin——————————
declare @s float
set @s = 0.1566134
print round(@s,3)
—————————round的用法end———————————

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—————————–

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

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

EXEC [master]..sp_dboption [Database Name], ‘autoshrink’, ‘TRUE’

*/

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

——————————–自动收缩数据库end—————————–

/* 步骤4

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

——————————-去除首尾无效的字符begin————————–
declare @s varchar(20)
set @s=’,,,1->1,’
while(left(@s,1)=’,’)
set @s=stuff(@s,1,1,”)
while(right(@s,1)=’,’)
set @s=stuff(reverse(@s),1,1,”)
select @s
——————————-去除首尾无效的字符end————————–

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

               在路上           2            马化腾,李大伟

————删除数据库中的重复记录(且仅保留一条有效记录)示例—————–
create table A
(
 userID int identity(1,1),
 userName varchar(20),
 userPwd varchar(20),
 userEmail varchar(50)
)
insert into A(userName,userpwd) select ‘qin’,’qin’ union all select
‘qin’,’qin1′ union all select ‘qin’,’qin1′
select * from A

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      

–method one
delete from A where userid not in(select min(userid) as userid from A
group by username ,userpwd)

*/

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

–method two
delete from A where exists (select * from A b where a.username =
b.username and a.userpwd = b.userpwd and a.userid < b.userid)

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

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

–method three
delete from a where userid not in(select min(userid) from A b where
a.username = b.username and a.userpwd = b.userpwd and a.userid >
b.userID)

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

select * from A
drop table A

*/

      表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 t
 (st varchar(20),ed varchar(20),km int)
go
insert t values (‘A’,’B’,1000)
insert t values (‘A’,’C’,1100)
insert t values (‘A’,’D’,900)
insert t values (‘A’,’E’,400)
insert t values (‘B’,’D’,300)
insert t values (‘D’,’F’,600)
insert t values (‘E’,’A’,400)
insert t values (‘F’,’G’,1000)
insert t values (‘C’,’B’,600)
go
–显示插入值
select * from t
go

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

create table 表1( –drop table 表1

–创建函数
–函数返回一个表,根据实际情况的不同一层一层的插入,可以充分利用生成的表
create function f_go(@col varchar(10))
 returns @t table(col varchar(30),st varchar(20),ed varchar(20),km
int,level int)
as
begin
 declare @i int
 set @i=1
 insert @t select st+’-‘+ed,*,@i from t where st=@col
 while exists (select * from t a,@t b where
  b.ed=a.st and b.level=@i and b.ed<>@col )
 begin
  set @i=@i+1
  insert @t
  select b.col+’-‘+a.ed,a.st,a.ed,b.km+a.km,@i from t a,@t b
   where b.level=@i-1 and b.ed=a.st and b.ed<>@col
 end
 return
end
go

select count(*) from W_DOCUMENTS;

ID     int,

–调用
–select * from dbo.f_go(‘A’)
select col,km from dbo.f_go(‘a’)

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

NAME   varchar(10),

–删除环境
drop function f_go
drop table t

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

QQ     varchar(10),

——————————-迭归的应用(找起点和终点之间的路径—————————–