1. 全栈程序员必看首页

sql存储过程语法详解

全栈程序员-站长 • • 未分类 • 阅读 15

sql存储过程语法详解一、定义变量使用关键字declare申明变量：declare@变量名变量类型/*简单赋值*/declare@aintset@a=5print@a/*select赋值*/declare@bnvarchar(10)select@b=stu_namefromdbo.studentwherestu_id=6print@b/…

大家好，又见面了，我是你们的朋友全栈君。

一、定义变量

使用关键字declare申明变量：

declare @变量名 变量类型

/*简单赋值*/

 declare @a int
 set     @a=5
 print   @a

/*select赋值*/

declare @b nvarchar(10)
select @b= stu_name from dbo.student where stu_id=6
print @b

/*update赋值*/

declare @c  nvarchar(10)
update dbo.student set @c=stu_name where stu_id=5
print @c

运行结果：
这里写图片描述
ps：使用set赋值时，等式右边不可以用函数表达式。

二、临时表、表变量

临时表：
临时表的创建是在Tempdb中，在一个数据库连接结束后或者由SQL命令DROP掉，才会消失，否则就会一直存在。临时表在创建的时候都会产生SQL Server的系统日志。在Tempdb中体现，在内存中分配，它们也支持物理的磁盘，但用户在指定的磁盘里看不到文件。 

/*创建本地临时表#*/
create table #tablename()

/*创建全局临时表##*/
create table ##tablename()
/*创建一个临时表#student*/
create table #student( id int, username nvarchar(20), sex nvarchar(2), age int ) /*向临时表中插入一条数据*/ insert into #student values(1,'Susan','女',18) /*从student表查询数据，填充至新生成的临时表*/ 1.select * into #student2 from student where stu_age>30 2.insert into #student select * from student where stu_age>30

这里写图片描述

由于手误，执行了两次，表中出现了重复项，且每一列值都相同
怎么删除完全重复的项的呢？

delete T from (select ROW_NUMBER() over(partition by username order by id) as rownumber, * from #student) T WHERE T.rownumber>1

语法：ROW_NUMBER( ) OVER ( PARTITION BY 列1 ORDER BY 列2 )
根据COLUMN分组，在分组内部根据 COLUMN排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)
函数“Row_Number”必须有 OVER 子句。OVER 子句必须有包含 ORDER BY

这里写图片描述

/*查询并联合两临时表*/
select * from #student where id >2 union select * from #student2 /*删除两临时表*/ drop table #student drop table #student2
/*添加一列为int型自增长列*/
alter table #student add innerid int not null identity(1,1)

ps：identity（1,1）自增列，从1开始递增，每次加1
这里写图片描述

/*增加一列，默认填充全球唯一标识*/
alter table #student add myid uniqueidentifier not null default(newid())

表变量：
表变量是变量的一种，表变量也分为本地及全局的两种，本地表变量的名称都是以“@”为前缀，只有在本地当前的用户连接中才可以访问。全局的表变量的名称都是以“@@”为前缀，一般都是系统的全局变量。

declare @local_variable table()

declare @t table(
  id int not null,
  msg nvarchar(50) not null
)

insert into @t values(1,'1')
insert into @t values(2,'2')
select * from @t

临时表与表变量的区别：
这里写图片描述

ps：
非聚集索引：数据行的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。
聚集索引：与非聚集索引相反。
更多关于聚集索引与非聚集索引：
1.https://www.cnblogs.com/s-b-b/p/8334593.html
2.https://www.cnblogs.com/Jessy/p/3543063.html
更多关于表变量和临时表：
1.https://www.jb51.net/article/23952.htm
2.https://www.cnblogs.com/xinaixia/p/5821548.html

三、循环

while循环计算1到100的和：

declare @d int
declare @sum int
set @d=1
set @sum=0
while @d<100
begin
  set @d+=1
  set @sum+=@d
end
print @sum

这里写图片描述

四、条件语句

if-else

if（1+1=2）
begin
  printf（"对"）
end
else
begin
  print（"错"）
end

when -then

declare @today int
declare @week nvarchar（3）
set @week=case
set@today=1
   when @today=1 then '星期一'
   when @today=2 then '星期二'
   when @today=3 then '星期三'
   when @today=4 then '星期四'
   when @today=5 then '星期五'
   when @today=6 then '星期六'
   when @today=7 then '星期天'
   else '错误值'
end
   print @week

例子：导入前一天的数据

DECLARE @y varchar(4) 
DECLARE @m varchar(2) 
DECLARE @d varchar(2)

select  @y=DateName(year,GetDate())
select  @d=DateName(day,dateadd(day,-1,GetDate())) 

if (DateName(day,GetDate())='1')/*如果当天是每月1号*/
select  @m=DateName(month,dateadd(MONTH,-1,GetDate()))/*月份-1*/
else
select  @m=DateName(month,GetDate())

EXEC    [dbo].[InsertQYGPSData2]

        @year = @y,

        @month = @m,

        @day = @d

五、游标

1.定位到结果集中的某一行；
2.对当前位置的数据进行读写；
3.可以对结果集中的数据单独操作，而不是整行执行相同操作。
4.是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。

分类：
1.静态游标：不反应结果集中的所有更改
2.动态游标：反应结果集中所有更改
3.只进游标：不支持滚动，只支持从头到尾顺序提取数据
4.键集驱动游标:该游标中的各个成员顺序是固定的的，被标识的列做删改时，用户滚动游标是可见的，未标识则删改不可见。

游标的生命周期：
1.声明游标

declare cursor_name CURSOR [local | global][forward_only | scroll]
[static|keyset|dynamic|fast_forward]
[read_only|scroll_locks|optimistic]
[type_warning]
for select_statement
[for update[of column_name[,...n]]]

注释：

cursor_name：游标名称。
Local：作用域为局部，只在定义它的批处理，存储过程或触发器中有效。
Global：作用域为全局，由连接执行的任何存储过程或批处理中，都可以引用该游标。
[Local | Global]:默认为local。
Forward_Only:指定游标智能从第一行滚到最后一行。Fetch Next是唯一支持的提取选项。如果在指定Forward_Only是不指定Static、KeySet、Dynamic关键字，默认为Dynamic游标。如果Forward_Only和Scroll没有指定，Static、KeySet、Dynamic游标默认为Scroll，Fast_Forward默认为Forward_Only
Static:静态游标
KeySet：键集游标
Dynamic：动态游标，不支持Absolute提取选项
Fast_Forward：指定启用了性能优化的Forward_Only、Read_Only游标。如果指定啦Scroll或For_Update，就不能指定他啦。
Read_Only:不能通过游标对数据进行删改。
Scroll_Locks：将行读入游标是，锁定这些行，确保删除或更新一定会成功。如果指定啦Fast_Forward或Static，就不能指定他啦。
Optimistic：指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。当将行读入游标时，sqlserver不锁定行，它改用timestamp列值的比较结果来确定行读入游标后是否发生了修改，如果表不行timestamp列，它改用校验和值进行确定。如果已修改改行，则尝试进行的定位更新或删除将失败。如果指定啦Fast_Forward,则不能指定他。
Type_Warning:指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。
For Update[of column_name ,....] :定义游标中可更新的列。

2.声明一个动态游标：

declare OrderNum_cursor sursor scoll
for select OrderID from bigOrder where OrderNum='xxxx'

3.打开游标

open [Global] cursor_name | cursor_variable_name
              /*游标名 游标变量名*/

4.提取数据

Fetch
[ [Next|prior|Frist|Last|Absoute n|Relative n ]
from ]
[Global] cursor_name
[into @variable_name[,….]]

注释：

Frist：结果集的第一行
Prior：当前位置的上一行
Next：当前位置的下一行
Last：最后一行
Absoute n：从游标的第一行开始数，第n行。
Relative n：从当前位置数，第n行。
Into @variable_name[,...] : 将提取到的数据存放到变量variable_name中。

这里写图片描述
5.利用游标更新删除数据

/*①游标修改当前数据语法*/
Update 基表名 Set 列名=值[,...] Where Current of 游标名 /*②游标删除当前数据语法*/ Delete 基表名 Where Current of 游标名

6.关闭游标

/*关闭游标语法*/
close [ Global ] cursor_name | cursor_variable_name
/*关闭游标*/
close orderNum_03_cursor

7.删除游标

/*释放游标语法*/
deallocate  [ Global ] cursor_name | cursor_variable_name
/*释放游标*/
deallocate orderNum_03_cursor

六、触发器

原理，优点，作用参考：https://www.cnblogs.com/wangprince2017/p/7827091.html

分类：
1.DML：数据操纵语言
insert，delete，update触发器
2.DDl：数据定义语言
create，drop，alter触发器

语法:

CREATE TRIGGER trigger_name ON table_name [WITH ENCRYPTION] FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE] AS T-SQL语句 GO --with encryption 表示加密触发器定义的sql文本 --delete,insert,update指定触发器的类型
/*创建触发器 */
Create trigger User_OnUpdate On ST_User for Update As declare @msg nvarchar(50) /*@msg记录修改情况*/ select @msg = N'姓名从“' + Deleted.Name + N'”修改为“' + Inserted.Name + '”' from Inserted,Deleted /*插入日志表*/ insert into [LOG](MSG)values(@msg) /*删除触发器*/ drop trigger User_OnUpdate

七、自定义函数

1.标量函数：返回的结果是一个标量
例：
这里写图片描述

2.内联表值函数：返回表数据,内联表函数返回的表结构由函数体内的SELECT语句来决定。
例：

create function FUN_Sum1 ( @myid int ) RETURNS table AS RETURN ( select * from St_user where ID<@myid ) GO

3.多语句表值函数：已经定义好要返回的表中的字段

create function FUNC_UserTab_2 ( @myId int ) RETURNS @t table ( [ID] [int] NOT NULL, [Oid] [int] NOT NULL, [Login] [nvarchar](50) NOT NULL, [Rtx] [nvarchar](4) NOT NULL, [Name] [nvarchar](5) NOT NULL, [Password] [nvarchar](max) NULL, [State] [nvarchar](8) NOT NULL ) AS BEGIN insert into @t select * from ST_User where ID<@myId RETURN END GO

4.调用表值函数

select * from dbo.FUNC_UserTab_1(15)

5.调用标量函数

declare @s int
set @s=dbo.FUNC_Sum1(100,50) 
print @s

6.删除标量值函数

drop function FUNC_Sum1

尝试写了一个简单的存储过程：

USE [test]
GO
/****** Object: StoredProcedure [dbo].[InsertQYGPSData2] Script Date: 09/05/2018 15:51:27 ******/
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[InsertQYGPSData2] @year varchar(4), @month varchar(2), @day varchar(2) AS BEGIN declare @tablename varchar(20) set @day = right('00'+@day,2) set @tablename='QYGPSData_'+@year+'_'+ @month+'_'+@day IF NOT EXISTS(SELECT NAME FROM sys.objects WHERE NAME=@tablename and TYPE='u') begin exec('create Table ' +@tablename+ ' ( [Create_Date] [datetime] NULL, [CarNum] [varchar](50) NULL, [CarNo] [varchar](50) NULL, [FrontDoorUp] [int] NULL, [FrontDoorDown] [int] NULL, [BackDoorUp] [int] NULL, [BackDoorDown] [int] NULL, [lng] [varchar](50) NULL, [lat] [varchar](50) NULL, [State] [varchar](10) NULL, [CarPassenger] [int] NULL, [Line_Name] [varchar](100) NULL )') end END

ps：语法：LEFT(ARG,LENGTH)、RIGHT(ARG,LENGTH)

LEFT、RIGHT函数返回ARG最左边、右边的LENGTH个字符串，ARG可以是CHAR或BINARY STRING。

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。

发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/160803.html原文链接：https://javaforall.net

赞 (0)
0 0

关于作者

全栈程序员-站长的头像

全栈程序员-站长

133.5K 文章
3 粉丝
本网站汇聚当前互联网主流语音，持续更新，欢迎关注公众号“全栈程序员社区”
上一篇 2022年7月17日 上午11:16
下一篇 2022年7月17日 上午11:16


相关推荐

  • 阈值分割python实现

    阈值分割python实现

    阈值分割python实现1 直方图技术法 importcv2imp image rows cols image shapegrayHis np zeros 256 np uint64 forrinrange rows

    全栈程序员-站长的头像 全栈程序员-站长
    2026年3月17日
    2
  • 我的一人OpenClaw公司架构v1.0，把公司的会计、合规、运维全交给了AI openclaw

    我的一人OpenClaw公司架构v1.0，把公司的会计、合规、运维全交给了AI

    我的一人OpenClaw公司架构v1.0，把公司的会计、合规、运维全交给了AI

    全栈程序员-站长的头像 全栈程序员-站长
    2026年3月13日
    3
  • 虚拟机配置IIS服务

    虚拟机配置IIS服务

    虚拟机配置IIS服务虚拟机配置 IIS 服务器 IIS 简介配置 windows2003 的相关服务查看 XP 客户端服务端配置默认网页 XP 客户端访问 IIS 简介 IIS 互联网信息服务 英语 InternetInfo 简称 IIS 是由微软公司提供的基于运行 MicrosoftWin 的互联网基本服务 即发布网站的服务器 在此处我们用 windows2003 作为 IIS 服务器 windowsXP 作为一个客户端 windows2003 的 ip 设置为 10 1 1 1 24 windowsXP 的 ip 设置为 10

    全栈程序员-站长的头像 全栈程序员-站长
    2026年3月18日
    2
  • ARM汇编指令集汇总

    ARM汇编指令集汇总

    ARM汇编指令集汇总ARM 汇编指令集汇总作者 毛茏玮 Saint 掘金 https juejin im user 5aa1f89b6fb9 微博 https weibo com profile topnav 1 amp wvr 6 amp is all 1GitHub github com saint 000CSDN https me csdn

    全栈程序员-站长的头像 全栈程序员-站长
    2026年3月26日
    3
  • c语言求一个数的补码_反码补码原码怎么转换

    c语言求一个数的补码_反码补码原码怎么转换

    c语言求一个数的补码_反码补码原码怎么转换原码、反码和补码1).数据在内存中存储的时候都是以二进制的形式存储的.intnum=10;原码、反码、补码都是二进制.只不过是二进制的不同的表现形式.数据是以补码的二进制存储的.2).1个int类型的变量.在内存中占据4个字节,32位.00000000000000000000000000000000在不考虑正负的情况下.1个int类型的变量可以表示接近43e种数据.为了可以表示正负…

    全栈程序员-站长的头像 全栈程序员-站长
    2025年11月23日
    5
  • 数据挖掘-层次聚类

    数据挖掘-层次聚类

    数据挖掘-层次聚类微信搜索 二十同学 公众号 欢迎关注一条不一样的成长之路层次聚类 hierarchical 算法极为简单 有 N 多节点 最开始认为每个节点为一类 然后找到距离最近的节点 两两合并 合并后的两个节点的平均值作为新的节点 继续两两合并的过程 直到最后都合并成一类 下图表明了聚类的过程 距离最近的节点合并 第一步中 如果有两对节点距离一样 那就同时合并 层次聚类过程如果用数据挖掘工具来做 如 SPSS 一般会生成一个树形图 那么我们可以根据分析问题的具体情况 选

    全栈程序员-站长的头像 全栈程序员-站长
    2026年1月15日
    2

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

关注全栈程序员社区公众号