sql存储过程语法详解

article/2025/9/16 23:27:02

一、定义变量

使用关键字declare申明变量：

declare @变量名 变量类型

/*简单赋值*/declare @a intset     @a=5print   @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
beginset @d+=1set @sum+=@d
end
print @sum

这里写图片描述

四、条件语句

if-else

if（1+1=2）
beginprintf（"对"）
end
else
beginprint（"错"）
end

when -then

declare @today int
declare @week nvarchar（3）
set @week=case
set@today=1when @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 '错误值'
endprint @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_nameON 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
BEGINinsert 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
)')
endEND

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

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


http://chatgpt.dhexx.cn/article/tjaYBIYh.shtml

相关文章

MySQL常用操作之创建存储过程语法详解

MySQL常用操作之创建存储过程语法详解

MySQL常用操作之创建存储过程语法详解 前言简介语法创建结构变量结构入参变量和出参变量流程控制判断(IF 语句)判断(CASE 语句)循环(LOOP 语句)循环(WHILE 语句)循环(REPEAT 语句)再次循环(ITERATE 语句) 总结参考链接 前言 场景介绍 作为一名Java搬运工&#xff0c;实际开发中…
阅读更多...
Oracle 存储过程语法

Oracle 存储过程语法

Oracle 存储过程语法 1. 创建表&#xff08;测试数据准备&#xff09; -- 创建用户表 create table TT_USER (USERID NUMBER(10),USERNAME VARCHAR2(255),PASSWORD VARCHAR2(255),SEX VARCHAR2(1) );INSERT INTO TT_USER VALUES (101, zhang, 111, 1); INSERT INTO TT…
阅读更多...
mysql存储过程基本语法

mysql存储过程基本语法

本文来说下mysql存储过程基本语法 文章目录 基本语法使用实例变量的使用变量定义declare语句变量赋值用户变量 存储过程的参数in 输入参数out 输出参数inout输入输出参数 本文小结 基本语法 存储过程就是具有名字的一段代码&#xff0c;用来完成一个特定的功能。创建的存储过程…
阅读更多...
Oracle存储过程基本语法

Oracle存储过程基本语法

后来者居上&#xff0c;不是easy的事情 尤其技术类work&#xff0c;更加如此。都是have one 投入的过程的。尤其开发&#xff0c;还是属于技术壁垒挺高的行业。 创建基本的存储过程 1 CREATE OR REPLACE PROCEDURE MyProName IS 2 BEGIN 3 NULL; 4 END; 行1:CREATE OR REPL…
阅读更多...
存储过程常见语法

存储过程常见语法

存储过程常见语法 一、存储过程的概念: 1、存储过程Procedure是一组为了完成特定功能的SQL语句集合&#xff0c;经编译后存储在数据库中&#xff0c;用户通过指定存储过程的名称并给出参数来执行 2、存储过程中可以包含逻辑控制语句和数据操纵语句&#xff0c;它可以接受参数…
阅读更多...
存储过程的语法讲解

存储过程的语法讲解

在上一篇文章&#xff1a;别再说不知道什么是存储过程和存储函数了 中简单的介绍了存储过程和存储函数以及其使用。其实存储过程是可以进行编程的&#xff0c;所以可以和其他的编程语言一样使用变量、表达式以及控制结构进行编程&#xff0c;从而实现一些复杂和有用的功能。这篇…
阅读更多...
子网掩码的作用

子网掩码的作用

IP地址由网络和主机两部分标识组成 IP地址由“网络标识&#xff08;网络地址&#xff09;”和“主机标识&#xff08;主机地址&#xff09;”两部分组成。在局域网内相互间通信的网络必须具有相同网络地址&#xff0c;也叫相同的网段&#xff0c;在同一个网段内每个设备的主机…
阅读更多...
子网掩码使用详解

子网掩码使用详解

一、子网掩码 IP地址是以网络号和主机号来标示网络上的主机的&#xff0c;我们把网络号相同的主机称之为本地网络&#xff0c;网络号不相同的主机称之为远程网络主机&#xff0c;本地网络中的主机可以直接相互通信&#xff1b;远程网络中的主机要相互通信必须通过本地网关&…
阅读更多...
什么是子网掩码 子网掩码的作用是什么？

什么是子网掩码 子网掩码的作用是什么？

什么是子网掩码 子网掩码的作用是什么&#xff1f; 网络工作人员经常需要与ip和子网掩码等打交道&#xff0c;相信绝大数的朋友都知道IP的意思&#xff0c;但是还不理解子网掩码的意思&#xff0c;下面装机之家小编来为大家介绍下关于子网掩码的相关知识&#xff0c;希望能够对…
阅读更多...
ip、子网掩码、网关、默认网关

ip、子网掩码、网关、默认网关

这里写目录标题 ip网络地址主机地址 子网子网掩码子网掩码的表示方法为什么要使用子网掩码&#xff1f;子网掩码的分类 网关默认网关 ip ip地址 网络地址 主机地址&#xff08;又称&#xff1a;网络号和主机号&#xff09;&#xff0c;我们把网络号相同的主机称之为本地网络…
阅读更多...
子网划分和子网掩码

子网划分和子网掩码

目录 前言 1、IP地址 1.1 IP地址的内容 1.2 IP地址的分类 2、子网掩码的作用 2.1 主机间的通信 2.2 子网掩码 3.子网划分 3.1 子网划分的原因 3.2 子网划分的原理 3.3 IP地址汇总 总结 前言 知道IP地址的分类和基本使用&#xff0c;如果公司拥有300台计算机&#xf…
阅读更多...
子网掩码详解

子网掩码详解

IP地址 IP地址被用来给Internet上的电脑一个编号。大家日常见到的情况是每台联网的PC上都需要有IP地址&#xff0c;才能正常通信。我们可以把“个人电脑”比作“一台电话”&#xff0c;那么“IP地址”就相当于“电话号码”&#xff0c;而Internet中的路由器&#xff0c;就相当于…
阅读更多...
子网掩码的两种计算方式

子网掩码的两种计算方式

&#xff08;尊重劳动成果&#xff0c;转载请注明出处&#xff1a;http://blog.csdn.net/qq_25827845/article/details/70946041冷血之心的博客&#xff09; 关注微信公众号&#xff08;文强的技术小屋&#xff09;&#xff0c;学习更多技术知识&#xff0c;一起遨游知识海洋~ …
阅读更多...
子网掩码的划分和计算详解

子网掩码的划分和计算详解

一、子网掩码的计算 TCP/IP网间网技术产生于大型主流机环境中&#xff0c;它能发展到今天的规模是当初的设计者们始料未及的。网间网规模的迅速扩展对IP地址模式的威胁并不是它不能保证主机地址的唯一性&#xff0c;而是会带来两方面的负担&#xff1a;第一&#xff0c;巨大的…
阅读更多...
一文带你了解什么是子网掩码

一文带你了解什么是子网掩码

什么是子网掩码 子网掩码代表了“网络号子网号”与主机号之间的分割方案。 很晦涩&#xff1f; 说子网掩码&#xff0c;我们还得先说说IP地址。 什么是IP地址 因特网上的每台主机或路由器端口都必须有一个唯一的IP地址。因为IP地址&#xff0c;在网络上我们才能互相识别&a…
阅读更多...
制作QQ登录界面（UI版）

制作QQ登录界面（UI版）

工具&#xff1a;Android Studio activity_main.xml <?xml version"1.0" encoding"utf-8"?> <androidx.constraintlayout.widget.ConstraintLayout xmlns:android"http://schemas.android.com/apk/res/android"xmlns:app"http:…
阅读更多...
html登录界面

html登录界面

今天是2019年11月14日我第一次注册博客&#xff0c;我把我做的一个登陆界面分享给大家看希望各位大佬指教。 首先是html文件。 1.登录界面html 登录界面 用户名: 密 码: 注册 2.注册界面html。 注册界面 用户名: 输入密码: 确认密码: 立刻注册返回登录 **3.效果图**! 欢迎…
阅读更多...
Android仿QQ微信开场导航以及登陆界面

Android仿QQ微信开场导航以及登陆界面

相信大家对于微信等社交应用的UI界面已经都很熟悉了&#xff0c;该UI最值得借鉴的莫过于第一次使用的时候一些列产品介绍的图片&#xff0c;可以左右滑动浏览&#xff0c;最后进入应用&#xff0c;这一效果适用于多种项目中&#xff0c;相信今后开发应用一定会用得到。网路上也…
阅读更多...
[练习]QQ登陆界面-测试用例的编写

[练习]QQ登陆界面-测试用例的编写

&#xff08;Test Case&#xff09;是为了实施测试而向被测试系统提供的一组集合&#xff0c; 包括&#xff1a;测试环境、操作步骤、测试数据、预期结果等要素。 一条测试用例最终只有一个结果。 一个功能点至少有一个测试用例。 测试用例数/功能点数 测试的覆盖率&#xf…
阅读更多...
web之qq邮箱登录界面

web之qq邮箱登录界面

我们可以用css来做这个登录的表格 具体的完整代码实现如下&#xff1a; <!DOCTYPE html> <html> <meta charset"utf-8"> <title>登录qq邮箱</title> <style type"text/css">* {padding: 0;margin: 0;}.content {wid…
阅读更多...
推荐文章

Copyright @ 2022~2023