sql server 常用的扩展存储过程

时间:2024-04-07 20:49:42

参考资料:mssql数据库视频学习

目录

一:存储过程概述

二、sql server 常用的扩展存储过程

1、xp_cmdshell 

2、xp_logininfo

3、xp_msver

4、xp_sscanf


一:存储过程概述

简单来说,存储过程就是一条或者多条sql语句的集合,可视为批处理文件,但是其作用不仅限于批处理。本篇主要介绍变量的使用,存储过程和存储函数的创建,调用,查看,修改以及删除操作。

SQL Server中的存储过程是使用T_SQL编写的代码段。它的目的在于能够方便的从系统表中查询信息,或者完成与更新数据库表相关的管理任务和其他的系统管理任务.T_SQL语句是SQL Server数据库与应用程序之间的编程接口。在很多情况下,一些代码会被开发者重复编写多次,如果每次都编写相同功能的代码,不但繁琐,而且容易出错,而且由于SQL Server逐条的执行语句会降低系统的运行效率。

简而言之,存储过程就是SQL Server为了实现特定任务,而将一些需要多次调用的固定操作语句编写成程序段,这些程序段存储在服务器上,有数据库服务器通过程序来调用。

1、存储过程分类:

存储过程分类:系统存储过程、用户存储过程(自定义存储过程)、扩展存储过程。

参考资料:SQL Server基础之存储过程

 

(1)系统存储过程

  • 有系统定义,存放在masters数据库中;
  • 类似C语言中的系统函数;
  • 系统存储过程的名称都已“ sp_ ”开头或“ xp_ ”开头。

(2)用户自定义存储过程

  • 由用户在自己的数据库中创建的存储过程;
  • 类似C语言的用户自定义函数。

2、常用的系统存储过程

sql server 常用的扩展存储过程

sql server 常用的扩展存储过程

二、sql server 常用的扩展存储过程

sql server 里面提供了丰富的系统存储过程来辅助我们管理数据库以及开发。今天分享介绍一些常用的数据库扩展存储过程

1、xp_cmdshell 

这个大家都比较熟悉了,使用xp_cmdshell 可以在连接sql server 的时候可以执行doc 命令。

xp_cmdshell扩展存储过程:

  • 可以执行DOS命令下的一些操作;
  • 以文本行方式返回任何输出;
  • 调用语法:EXEC xp_cmdshell DOS命令 [NO_OUTPUT]

最简单一个例子EXEC master.sys.xp_cmdshell 'dir D:\' 获取D 盘下面的目录。当然这个D:\ 是服务器上面的,不是本地电脑的。

所以有时候可以使用 xp_cmdshell 配合作业都角度使用执行bcp等命令来导入导出数据,也是很方便的。

但是使用的过程中,有下面几点留意一下:

  1. 首次使用的时候,需要使用 EXEC sys.sp_configure @configname = 'xp_cmdshell',@configvalue = 1 来启用xp_cmdshell ,默认情况下,sql server 是禁用本功能的;
  2. 可以使用 no_output 来忽略输出结果;
  3. 注意权限问题,注意非管理员账号的用户使用xp_cmdshell 。

2、xp_logininfo

这个返回的是服务器windows 用户和windows 组的信息。

msdn上面写着这个存储过程具有3个参数 @acctname ,@option ,@privilege 

燃鹅不知道是我测试的问题还是本身这个存储过程只有1个参数,@option 和 @privilege 传入进去都是无效的。

使用方法

 EXEC master.sys.xp_logininfo

 或者 EXEC master.sys.xp_logininfo @acctname = 'GINLATOP\Gin' 加上个账号名还是ok的

3、xp_msver

这个扩展存储过程看起来就有点像入门写C语言的printf 函数了。燃鹅即时这样,我认为其实用到它的机会并不多。大部分使用的场景如下

DECLARE @i VARCHAR(20) = 1,
@j varchar(50) = 'ajofen',
@s VARCHAR(80)

EXEC master..xp_sprintf @s OUTPUT,'there are two varaint one is %s and another is %s ',@i,@j
PRINT @s

坑爹的是,这个存储过程的参数只支持字符型的……并且如果这样搞的话,使用 +来拼接字符串也ok了,所以也只是介绍一下╮(╯_╰)╭

4、xp_sscanf

这个扩展存储过程是对插入的字符串变量进行格式化取值。

DECLARE @i VARHAR(50) = 1,
@j varchar(50) ,
@s VARCHAR(80)

EXEC master..xp_sscanf '3 + 31 = 34','%s + %s =34',@i OUTPUT,@s OUTPUT
SELECT @i,@j,@s

参考资料:sql server 常用的扩展存储过程