1. 全栈程序员必看首页

sql存储过程语法详解

全栈程序员-用户IM • • 未分类

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。

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

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

【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛

【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

赞 (0)
全栈程序员-用户IM全栈程序员-用户IM
0 0


相关推荐

  • C语言中的strstr函数的用法「建议收藏」

    C语言中的strstr函数的用法「建议收藏」

    C语言中的strstr函数的用法「建议收藏」strstr(str1,str2)函数用于判断字符串str2是否是str1的子串。如果是，则该函数返回str2在str1中首次出现的地址；否则，返回NULL。PHP语言函数编辑strstr()函数搜索一个字符串在另一个字符串中的第一次出现。该函数返回字符串的其余部分（从匹配点）。如果未找到所搜索的字符串，则返回false。语法

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年6月25日
  • js动画效果大全_jquery 动画

    js动画效果大全_jquery 动画

    js动画效果大全_jquery 动画动画基础(1)位置(2)时间(3)设置时间增量(4)抽象用动画增强网页效果(1)设置动画(2)变量作用域(3)改进(4)生成HTML标记添加安全检查

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年8月31日
  • Windows上使用Vagrant打造Laravel Homestead可协同跨平台开发环境

    Windows上使用Vagrant打造Laravel Homestead可协同跨平台开发环境

    Windows上使用Vagrant打造Laravel Homestead可协同跨平台开发环境

    全栈程序员-用户IM 全栈程序员-用户IM
    2021年10月22日
  • GSLB调度服务原理

    GSLB调度服务原理

    GSLB调度服务原理GSLB，全局负载均衡（GlobalServerLoadBalancing），主要的目的是在整个网络范围内将用户的请求定向到最近的节点（或者区域）。是对物理集群的负载均衡，不止是简单的流量均匀分配，还会根据应用场景的不同来制定不同的策略。本文将讨论GSLB的几种实现，并介绍调度服务实现的大体情况。

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年6月11日
  • double 转 BigDecimal 精度问题

    double 转 BigDecimal 精度问题

    double 转 BigDecimal 精度问题请看图：//double转BigDecimal精度测试@Testpublicvoida(){DoubleDou=5.56;BigDecimalbigDou1=newBigDecimal(Dou);BigDecimalbigDou2=BigDecimal.valueOf(Dou);…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年6月4日
  • Java关键字(50个)（超详细！）[通俗易懂]

    Java关键字(50个)（超详细！）[通俗易懂]

    Java关键字(50个)（超详细！）[通俗易懂]关键字大致含义abstract表明类或者成员方法具有抽象属性assert断言，用来进行程序调试boolean基本数据类型之一，声明布尔类型的关键字break提前跳出一个块byte基本数据类型之一，字节类型case用在switch语句之中，表示其中的一个分支…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年7月8日

发表回复

您的电子邮箱地址不会被公开。

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