SqlServer存储过程详解 1.创建存储过程的基本语法模板: if ( exists ( select * from sys.objects where name = ' pro_name ' )) drop proc pro_name go create proc pro_name @param_name param_type [ = default_value] as begin sql语句 end ps:[]表示非
SqlServer存储过程详解
1.创建存储过程的基本语法模板:
<span>if</span> (<span>exists</span> (<span>select</span> <span>*</span> <span>from</span> sys.objects <span>where</span> name <span>=</span> <span>'</span><span>pro_name</span><span>'</span><span>)) </span><span>drop</span> <span>proc</span><span> pro_name </span><span>go</span> <span>create</span> <span>proc</span><span> pro_name </span><span>@param_name</span> param_type [<span>=</span><span>default_value] </span><span>as</span> <span>begin</span><span> sql语句 </span><span>end</span>
ps:[]表示非必写内容。sys.objects存储的是本数据库中的信息,不仅仅存储表名,还有存储过程名 、视图名、触发器等等。
例如:
<span> 1</span> <span>if</span> (<span>exists</span> (<span>select</span> <span>*</span> <span>from</span> sys.objects <span>where</span> name <span>=</span> <span>'</span><span>USP_GetAllUser</span><span>'</span><span>)) </span><span> 2</span> <span>drop</span> <span>proc</span><span> USP_GetAllUser </span><span> 3</span> <span>go</span> <span> 4</span> <span>create</span> <span>proc</span><span> USP_GetAllUser </span><span> 5</span> <span>@UserId</span> <span>int</span> <span>=</span><span>1</span> <span> 6</span> <span>as</span> <span> 7</span> <span>set</span> nocount <span>on</span><span>; </span><span> 8</span> <span>begin</span> <span> 9</span> <span>select</span> <span>*</span> <span>from</span> UserInfo <span>where</span> Id<span>=</span><span>@UserId</span> <span>10</span> <span>end</span>
ps:SQL Server 实用工具将 GO 解释为应将当前的 Transact-SQL 批处理语句发送给 SQL Server 的信号。当前批处理语句是自上一 GO 命令后输入的所有语句,若是第一条 GO 命令,则是从特殊会话或脚本的开始处到这条 GO 命令之间的所有语句。
2.调用方法:
<span>exec</span> dbo.USP_GetAllUser <span>2</span>;
ps:一般在执行存储过程是,最好加上架构名称,例如 dbo.USP_GetAllUser 这样可以可以减少不必要的系统开销,提高性能。 因为如果在存储过程名称前面没有加上架构名称,SQL SERVER 首先会从当前数据库sys schema(系统架构)开始查找,如果没有找到,则会去其它schema查找,最后在dbo架构(系统管理员架构)里面查找。
3.查看本数据库中存在的存储过程
依次展开数据库、可编程性、存储过程,即可看到已创建的存储过程。
4.修改存储过程
<span>alter</span> <span>proc</span><span> proc_name </span><span>as</span> sql语句
5.存储过程中的输出参数的使用
<span> 1</span> <span>if</span> (<span>exists</span>(<span>select</span> <span>*</span> <span>from</span> sys.objects <span>where</span> name<span>=</span><span>'</span><span>GetUser</span><span>'</span><span>)) </span><span> 2</span> <span>drop</span> <span>proc</span><span> GetUser </span><span> 3</span> <span>go</span> <span> 4</span> <span>create</span> <span>proc</span><span> GetUser </span><span> 5</span> <span>@id</span> <span>int</span><span> output, </span><span> 6</span> <span>@name</span> <span>varchar</span>(<span>20</span><span>) out </span><span> 7</span> <span>as</span> <span> 8</span> <span>begin</span> <span> 9</span> <span>select</span> <span>@id</span><span>=</span>Id,<span>@name</span><span>=</span>Name <span>from</span> UserInfo <span>where</span> Id<span>=</span><span>@id</span> <span>10</span> <span>end</span> <span>11</span> <span>12</span> <span>go</span> <span>13</span> <span>declare</span> <span>14</span> <span>@name</span> <span>varchar</span>(<span>20</span><span>), </span><span>15</span> <span>@id</span> <span>int</span><span>; </span><span>16</span> <span>set</span> <span>@id</span><span>=</span><span>3</span><span>; </span><span>17</span> <span>exec</span> dbo.GetUser <span>@id</span>,<span>@name</span><span> out; </span><span>18</span> <span>select</span> <span>@id</span>,<span>@name</span><span>; </span><span>19</span> <span>print</span> <span>Cast</span>(<span>@id</span> <span>as</span> <span>varchar</span>(<span>10</span>))<span>+</span><span>'</span><span>-</span><span>'</span><span>+</span><span>@name</span>;
ps:参数output为该参数可以输出
6.分页获取数据的存储过程
<span> 1</span> <span>if</span> (<span>exists</span>(<span>select</span> <span>*</span> <span>from</span> sys.objects <span>where</span> name<span>=</span><span>'</span><span>GetUserByPage</span><span>'</span><span>)) </span><span> 2</span> <span>drop</span> <span>proc</span><span> GetUserByPage </span><span> 3</span> <span>go</span> <span> 4</span> <span>create</span> <span>proc</span><span> GetUserByPage </span><span> 5</span> <span>@pageIndex</span> <span>int</span><span>, </span><span> 6</span> <span>@pageSize</span> <span>int</span> <span> 7</span> <span>as</span> <span> 8</span> <span>declare</span> <span> 9</span> <span>@startIndex</span> <span>int</span><span>, </span><span>10</span> <span>@endIndex</span> <span>int</span><span>; </span><span>11</span> <span>set</span> <span>@startIndex</span> <span>=</span> (<span>@pageIndex</span><span>-</span><span>1</span>)<span>*</span><span>@pageSize</span><span>+</span><span>1</span><span>; </span><span>12</span> <span>set</span> <span>@endIndex</span> <span>=</span> <span>@startIndex</span> <span>+</span> <span>@pageSize</span> <span>-</span><span>1</span><span> ; </span><span>13</span> <span>begin</span> <span>14</span> <span>select</span> Id,Name <span>from</span> <span>15</span> <span> ( </span><span>16</span> <span>select</span> <span>*</span>,row_number()<span>over</span> (<span>order</span> <span>by</span> Id)<span>as</span> <span>number</span> <span>from</span><span> UserInfo </span><span>17</span> )t <span>where</span> t.<span>number</span><span>>=</span><span>@startIndex</span> <span>and</span> t.<span>number</span><span><span>@endIndex</span> <span>18</span> <span>end</span> <span>19</span> <span>20</span> <span>go</span> <span>21</span> <span>exec</span> dbo.GetUserByPage <span>2</span>,<span>4</span>;</span>
7.存储过程中事务的创建
<span>if</span> (<span>exists</span>(<span>select</span> <span>*</span> <span>from</span> sys.objects <span>where</span> name<span>=</span><span>'</span><span>JayJayToTest</span><span>'</span><span>)) </span><span>drop</span> <span>proc</span><span> JayJayToTest </span><span>go</span> <span>create</span> <span>proc</span><span> JayJayToTest </span><span>@GiveMoney</span> <span>int</span><span>, </span><span>@UserName</span> <span>nvarchar</span>(<span>20</span><span>) </span><span>as</span> <span>begin</span><span>set</span> nocount <span>on</span><span>; </span><span>begin</span> <span>tran</span><span>; </span><span>begin</span><span> try </span><span>update</span> BankTest <span>set</span> <span>Money</span> <span>=</span> <span>Money</span><span>-</span><span>@GiveMoney</span> <span>where</span> Name<span>=</span><span>@UserName</span><span>;</span> <span>update</span> BankTest <span>set</span> <span>Money</span> <span>=</span> <span>Money</span><span>+</span><span>@GiveMoney</span> <span>where</span> Name<span>=</span><span>'</span><span>test</span><span>'</span><span>; </span><span>commit</span><span>; </span><span>end</span><span> try </span><span>begin</span><span> catch </span><span>rollback</span> <span>tran</span><span>; </span><span>print</span> (<span>'</span><span>发生异常,事务进行回滚</span><span>'</span><span>); </span><span>end</span><span> catch </span><span>end</span> <span>go</span> <span>exec</span> JayJayToTest <span>10</span>,<span>'</span><span>jayjay</span><span>'</span>
8.了解存储过程的执行计划
<span>SELECT</span> <span>*</span> <span>FROM</span> sys.<span>[</span><span>syscacheobjects</span><span>]</span>查看当前缓存的执行计划
如果执行存储过程时成功通过解析阶段,则 Microsoft SQL Server 查询优化器将分析存储过程中的 Transact-SQL 语句并创建一个执行计划。执行计划描述执行存储过程的最快方法,所依据的信息包括:
1.表中的数据量。
2.表的索引的存在及特征,以及数据在索引列中的分布。
3.WHERE 子句条件所使用的比较运算符和比较值。
4.是否存在联接以及 UNION、GROUP BY 和 ORDER BY 关键字。
查询优化器在分析完存储过程中的这些因素后,将执行计划置于内存中。分析存储过程和创建执行计划的过程称为编译。优化的内存中的执行计划将用来执行该查询。执行计划将驻留在内存中,直到重新启动 SQL Server 或其他对象需要存储空间时为止。如果随后执行了存储过程,而现有执行计划仍留在内存中,则 SQL Server 将重用现有执行计划。如果执行计划不再位于内存中,则创建新的执行计划。
重新编译执行计划(create proc JayJayToTest with recompile)
创建存储过程时在其定义中指定 WITH RECOMPILE 选项,表明 SQL Server 将不对该存储过程计划进行高速缓存;该存储过程将在每次执行时都重新编译。当存储过程的参数值在各次执行间都有较大差异,导致每次均需创建不同的执行计划时,可使用 WITH RECOMPILE 选项。此选项并不常用,因为每次执行存储过程时都必须对其进行重新编译,这样会使存储过程的执行变慢。
由于数据库的新状态,数据库内的某些更改可能会导致执行计划效率低下或不再有效。SQL Server 检测这些使执行计划无效的更改,并将计划标记为无效。此后,必须为执行查询的下一个连接重新编译新的计划。导致计划无效的情况包括:
1.对查询所引用的表或视图进行任何结构更改(ALTER TABLE 和 ALTER VIEW)。
2.通过语句(如 UPDATE STATISTICS)显式生成或者自动生成新的分发内容统计。
3.除去执行计划所使用的索引。
4.显式调用 sp_recompile。
5.对键的大量更改(其他用户对由查询引用的表使用 INSERT 或 DELETE 语句所产生的修改)。
6.对于带触发器的表,inserted 或 deleted 表内的行数显著增长。

对于 SQL Server 数据库中已存在同名对象,需要采取以下步骤:确认对象类型(表、视图、存储过程)。如果对象为空,可使用 IF NOT EXISTS 跳过创建。如果对象有数据,使用不同名称或修改结构。使用 DROP 删除现有对象(谨慎操作,建议备份)。检查架构更改,确保没有引用删除或重命名的对象。

当 SQL Server 服务无法启动时,可采取以下步骤解决:检查错误日志以确定根本原因。确保服务帐户具有启动服务的权限。检查依赖项服务是否正在运行。禁用防病毒软件。修复 SQL Server 安装。如果修复不起作用,重新安装 SQL Server。

要查看 SQL Server 端口号:打开 SSMS,连接到服务器。在对象资源管理器中找到服务器名称,右键单击它,然后选择“属性”。在“连接”选项卡中,查看“TCP 端口”字段。

SQL Server 数据库文件通常存储在以下默认位置:Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data可通过修改数据库文件路径设置来自定义数据库文件位置。

问题发现这次使用的是SqlServer数据库,之前并没有使用过,但是问题不大,我按照需求文档的步骤连接好SqlServer之后,启动SpringBoot项目,发现了一个报错,如下:刚开始我以为是SqlServer连接问题呢,于是便去查看数据库,发现数据库一切正常,我首先第一时间问了我的同事,他们是否有这样的问题,发现他们并没有,于是我便开始了我最拿手的环节,面向百度编程。开始解决具体报错信息是这样,于是我便开始了百度报错:ERRORc.a.d.p.DruidDataSource$CreateCo

SQL Server 英文安装可通过以下步骤更改为中文:下载相应语言包;停止 SQL Server 服务;安装语言包;更改实例语言;更改用户界面语言;重启应用程序。

有网友反馈,在win11上无法安装sqlserver这款软件,不知道是怎么回事,根据目前的测试来看,win11存在硬盘问题,部分接口硬盘无法安装这款软件。win11为啥不能安装sqlserver:答:win11不能安装sqlserver是硬盘的问题。1、据了解,win11存在对于硬盘的检测bug。2、这导致sqlserver无法在“三星m.2接口”硬盘上安装。3、因此,如果我们要安装的话,需要准备一块其他硬盘。4、然后将该硬盘安装到电脑里,如果没有额外插槽的话就要换掉之前的硬盘。5、安装完成后,

可以通过以下步骤查询 SQL Server 数据库日志:1. 打开 SQL Server Management Studio,连接到数据库服务器;2. 展开“管理”节点,导航到“SQL Server 日志”;3. 选择要查询的日志文件,右键单击并选择“查看日志文件”;4. 浏览日志记录。其他查询日志方法:使用 Transact-SQL 查询、PowerShell Cmdlet。


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Dreamweaver Mac version
Visual web development tools

SublimeText3 Chinese version
Chinese version, very easy to use

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft
