使用的为sqlserver2017版本
一、xp_cmdshell组件不能用
报错提示:
“ 消息 15281,级别 16,状态 1,过程 xp_cmdshell,第 1 行 SQL Server 阻止了对组件
‘xp_cmdshell’ 的 过程’sys.xp_cmdshell’
的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用
‘xp_cmdshell’。有关启用 ‘xp_cmdshell’ 的详细信息,请参阅 SQL Server 联机丛书中的
“外围应用配置器”. ”
解决方法:
--执行以下语句
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
二、判断某个路径下的某个文件是否存在
--判断文件是否存在
declare @errCode varchar(2)
EXEC @errCode = master..xp_cmdshell 'dir D:\If_paths.txt'
select @errCode --存在的话返回0 不存在返回1
三、判断临时表是否存在
--判断临时表是否存在
declare @sss varchar(10)
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#表名') and type='U')
begin
set @sss = '存在'
drop table #表名 --删除临时表
end
else
begin
set @sss = '不存在'
end
四、创建临时表并读取执行文件中的数据插入到临时表中(逐行读取)
①只有一列数据
【文档内容】
【SQL语句】
--创建临时表
declare @cmd varchar(1000)
create table #TEST_TABLE
(
file_value char(100)
)
--从txt文件中按行读取数据,插入到临时表中
set @cmd ='bulk insert #TEST_TABLE from ''D:\1234.txt'' WITH( FIELDTERMINATOR = ''\t'', ROWTERMINATOR = ''\n'')'
exec (@cmd)
--查询临时表
select * from #TEST_TABLE
【执行结果】
PS:遇到个问题就是我的 txt文本格式是UTF-8的时候,读取插入表中是乱码:
【文档内容】
执行结果:
文本格式改为ANSI的时候读入正常。
②插入多列
文档内容:
【SQL语句】
--判断临时表是否存在,存在就删除
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#table') and type='U')
begin
drop table #table
end
--创建存储过程--后面挪到主存储过程中
create table #table
(
one varchar(50),
two varchar(50),
three varchar(50)
)
--定义需要用到的变量
declare @path varchar(255) = '',
@地址 varchar(100),
@文件名 varchar(100),
@cmd varchar(255)
--变量赋值
set @地址 = 'E:'
set @文件名 = 'wendang.csv'
set @path =@地址 + '\' + @文件名
--执行读取插入
set @cmd ='bulk insert #table from '''+@path + ''' WITH( FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'',FIRSTROW = 2,KEEPNULLS)'
exec (@cmd)
select * from #table
【执行结果】
五、取指定行的数据
--取第一行数据,并赋值
select top 1 @one = file_value from TABLE
--取第二行数据,并赋值
select top 1 @two = file_value from TABLE where file_value not in (select top 1* from TABLE )
--取第三行数据,并赋值
select top 1 @three = file_value from TABLE where file_value not in (select top 2* from TABLE )
六、关于日期
①取当前年份
declare @year_month_day varchar(20)
set @year_month_day = year(getdate())
七、截取字符串
--取前四位
LEFT('这是举例用的一句话',4)
--截取第五位到第八位
SUBSTRING('这是举例用的一句话',5,4)
八、判断文件是否存在并删除
①删除指定文件
--判断文件是否存在并删除
declare @file_path varchar(255)
set @file_path = 'E:\1234.txt'
declare @result int
exec master.dbo.xp_fileexist @file_path,@result out
if @result = 1 --1存在该文件,0不存在
begin
--如果路径有空格,在执行cmdshell前必须替换空格字符,用双引号括住
set @file_path = 'del ' + replace(@file_path,' ','" "')
exec master.dbo.xp_cmdshell @file_path
end
②删除指定地址下的所有指定后缀的文件
DECLARE @FILE_PATH varchar(1000),
@path varchar(255)
set @path = 'D:\'
--只删除一个后缀(不删除路径下子文件夹内的文件)
SET @FILE_PATH = 'DEL /f /q ' + @path +'\*.LOG '
--只删除一个后缀(删除路径下子文件夹内的文件)
SET @FILE_PATH = 'DEL /f /s /q ' + @path +'\*.LOG '
--删除多个后缀
SET @FILE_PATH = 'DEL /f /q ' + @path +'\*.LOG '
+'/f /q ' + @path +'\*.CSV '
+'/f /q ' + @path +'\*.JEF '
EXEC master.dbo.xp_cmdshell @FILE_PATH
九、移动文件
--移动文件
declare @需要移动的文件地址 varchar(255)
set @需要移动的文件地址 = 'E:\1234.txt'
declare @将文件移动到的地址 varchar(255)
set @将文件移动到的地址 = 'D:\1234.txt'
declare @cmd_path varchar(255)
set @cmd_path = 'move ' + @需要移动的文件地址 + ' ' + @将文件移动到的地址
--执行cmd命令进行文件移动
exec sp_configure 'xp_cmdshell',1
exec master.dbo.xp_cmdshell @cmd_path
十、遍历表数据
【游标遍历】
--判断临时表是否存在
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#table') and type='U')
begin
--如果存在就删除临时表
drop table #table
end
--创建第一个临时表
create table #table
(
one varchar(255),
two varchar(255),
three int,
four int,
)
--给第一个表造点数据
insert into #table values(1,1,1,1);
insert into #table values(2,2,2,2);
insert into #table values(3,3,3,3);
insert into #table values(4,4,4,4);
select * from #table
--判断临时表是否存在
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#table2') and type='U')
begin
--如果存在就删除临时表
drop table #table2
end
--创建第二个临时表
create table #table2
(
one varchar(255),
two varchar(255),
three int,
four int,
)
--声明游标使用的变量
declare
@one varchar(255),
@two varchar(255),
@three int,
@four int
--创建游标
DECLARE C_table CURSOR FOR SELECT * FROM #table
--打开游标
OPEN C_table
---- 取第一条记录
FETCH NEXT FROM C_table INTO
@one,
@two,
@three,
@four
--while循环
WHILE @@FETCH_STATUS=0
BEGIN
--将第一个表的数据插入到第二个表中
insert into #table2 values(@one,@two,@three,@four);
-- 取下一条记录
FETCH NEXT FROM C_table INTO @one,@two,@three,@four
END
-- 关闭游标
CLOSE C_table;
-- 释放游标
DEALLOCATE C_table;
select * from #table2
游标一般格式:
【创建游标】
DECLARE 游标名称 CURSOR FOR SELECT 字段1,字段2,字段3,... FROM 表名 WHERE ...
【开启游标】
OPEN 游标名称
【取第一条记录】
FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...
【使用while循环游标】
WHILE @@FETCH_STATUS=0
BEGIN
SQL语句执行过程... ...(业务处理)
【取下一条记录】
FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...
END
【关闭游标】
CLOSE 游标名称;
【释放游标】
DEALLOCATE 游标名称;
十一、事务
对表进行增删改的时候,如果出现异常,事务可以保证回滚到操作前的状态
--开启事务
begin transaction
set @Flg_Trn = 1
--结束事务
if @@error <> 0
begin
--回滚事务
rollback transaction
end
else
begin
--提交事务
commit transaction
@Flg_Err = 0
end
十二、判断数字长度,并在前面补位
示例:判断是否为数字并且如果只有一位数字的话,前面补一个0
declare @R_工程 varchar(10)
set @R_工程 = '1'
--判断是否为数字 ISNUMERIC:如果为数字返回:1,如果不为数字返回:0
--DATALENGTH 判断字节长度,一位数字返回:1 一个汉字返回:2 null值返回null
if ISNUMERIC(@R_工程) = 1 and DATALENGTH(@R_工程) = 1
begin
--进行位数补充 right('①需要补充道前面的字符' + cast(字段 as varchar),②一共补充的位数)
--一般来说 ①的长度+字段的长度 >= ②
--如果两边不相等,比如:@R_工程 = '11' set @R_工程 = right('00' + cast(@R_工程 as varchar),9) 输出结果为:0011
--如果①的长度+字段长度 = ②,比如: set @R_工程 = '1' set @R_工程 = right('00' + cast(@R_工程 as varchar),3) 输出结果为:001
set @R_工程 = right('00' + cast(@R_工程 as varchar),2)
select @R_工程
end
else
begin
select @R_工程
end
十三、执行拼接的sql语句
①执行正常的sql语句
declare
@SQL语句 nvarchar(255)
set @SQL语句 = N'select 1'
--sp_executesql 后面的变量类型只能是 nvarchar类型,否则会报错:プロシージャでは型 'ntext/nchar/nvarchar' のパラメーター '@statement' を想定しています。
exec sp_executesql @SQL语句
②需要定义一个变量接收SQL语句查询到的值
表数据:
SQL语句:
declare
@SQL语句 nvarchar(255),
@接收字段 int
set @SQL语句 = N'select @接收字段 = id from test_t where one = ''3'''
exec sp_executesql @SQL语句 ,N'@接收字段 int out' , @接收字段 OUTPUT
select @接收字段
结果:
十四、获取其他存储过程的return值
创建测试存储过程
USE TEST
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create PROCEDURE [dbo].[PR_存储过程]
(
@id int
)
as
--返回11111
return 11111
获取return值
declare @return_value int
exec @return_value = PR_存储过程 1
select @return_value
十五、截取首位字符
截取数据库中某一列中的双引号:
①.REPLACE
--REPLACE(字段,需要替换的符号,替换成什么符号)
REPLACE(字段,'"','')
PS:REPLACE会替换掉这个字段中的所有符号,比如:“111"222”,如果将"替换为’'(空字符串),得出的结果就会是11122,但是有些需求是保留字段中的字符只去除首位字符(111"222),所以这个并不是适用于所有的需求
②.SubString
简单粗暴,但是比较灵活
--去除第一个字符
SUBSTRING(字段,2,len(字段))
--去除最后一个字符
SUBSTRING(字段,1,len(字段)-1)
--从第二位开始取到字段倒数第二位
SUBSTRING(字段,2,len(字段)-2)
十六、创建并写入文件
declare @cmd varchar(200),
@Path VARCHAR(50), --保存路径
@Name VARCHAR(50), --文件名
@Content VARCHAR(MAX) --文件的内容
set @Content ='this is log'
set @Name = '日志.log'
set @Path = 'D:\'
--内容后加一个>会覆盖原内容
set @cmd ='echo '+@Content+ '> '+ @Path+ @Name
--两个>>会在原内容后面(下一行)追加内容
set @cmd ='echo '+@Content+ '>> '+ @Path + @Name
--如果想一次追加两行内容
set @cmd ='echo '+@Content+ '>> '+ @Path + @Name +
' &echo '+@Content+ '>> '+ @Path + @Name
--如果有明显的换行标识符,比如:
--这是一段文字\r\n这是第二行的一段文字
set @cmd='echo '+REPLACE(@Content, '\r\n', '>> ' + @Path + @Name +' &echo ') +'>> '+ @Path + @Name
--如果想添加空行
set @cmd ='echo= >> '+ @Path + @Name
exec master..xp_cmdshell @cmd
十七、echo中特殊字符当做文本输出
需要在特殊字符前加上^
--使用 REPLACE 将特殊字符 替换为 ^特殊字符
set @Content = REPLACE(@Content,'^','^^')
set @Content = REPLACE(@Content,'<','^<')
set @Content = REPLACE(@Content,'>','^>')
set @Content = REPLACE(@Content,'|','^|')
set @Content = REPLACE(@Content,'&','^&')
set @Content = REPLACE(@Content,'%','%%')
十八、根据标识符分割字符串
【SQL语句】
declare @VALUE varchar(255)
--set @VALUE = '12345678910'
set @VALUE = '笑笑笑刀笑笑笑笑笑'
--分割标识符所在的index
declare @Index int,
--分割标识符
@flag varchar(100),
--分割标识符的长度
@flag_len int
--指定分割标识符
set @flag = '刀'
--指定分割标识符的长度
set @flag_len = len(@flag)
--获取分割标识符的index
set @Index = CHARINDEX(@flag,@VALUE)
--如果字符串中没有指定的分割标识符返回的是0
if @Index > 0
begin
--分割标识符前面的字符串
select SUBSTRING(@VALUE,1,@Index-1)
--分割标识符后面的字符串
select SUBSTRING(@VALUE,@Index+@flag_len,LEN(@VALUE))
end
【输出结果:】
十九、导出一个表中所有的数据到指定路径
declare @logName varchar(200),
@cmd varchar(255)
--文件名
set @logName = 'LogFile.log'
set @cmd = 'BCP "select * from 総合原価計算.dbo.T_仕掛情報_有機" queryout d:\' + @logName + ' -c -U"sa" -P"Fnst.1234"'
EXEC master..xp_cmdshell @cmd