SQLSERVER存储过程开发笔记----零碎问题以及关于操作文件的操作

使用的为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