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

@parameter 过程中的参数。在 Create PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。

OUTPUT 表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。

AS :指定过程要执行的操作

SQLSERVER: 变量的声明: 声明变量时必须在变量前加@符号 DECLARE @I INT

变量的赋值: 变量赋值时变量前必须加set SET @I = 30

声明多个变量: DECLARE @s varchar(10),@a INT

—————————————————————————————-

oracle的建表sql转成sqlserver的建表sql时的注意点 : 1.所有的comment语句需要删除。 2.clob类型转换为text类型。 3.blob类型转换为image类型。 4.number类型转换为int，number(16,2)等转换为decimal(16,2)，number(18)转换为bigint。 5.default sysdate改为default getDate()。 6.to_date(‘2009-12-18′,’yyyy-mm-dd’)改为cast(‘2009-12-18’ as datetime)

SQLSERVER: 变量的声明: 声明变量时必须在变量前加@符号 DECLARE @I INT

变量的赋值: 变量赋值时变量前必须加set SET @I = 30

声明多个变量: DECLARE @s varchar(10),@a INT

if语句:

Java代码

1. if ..
2. begin
3. …
4. end
5. else if ..
6. begin
7. …
8. end
9. else
10. begin
11. …
12. end

Example:

Sql代码

1. DECLARE @d INT
2. set @d = 1
3. IF @d = 1 BEGIN
4. PRINT ‘正确’
5. END
6. ELSE BEGIN
7. PRINT ‘错误’
8. END

多条件选择语句: Example:

Sql代码

1. declare @today int
2. declare @week nvarchar(3)
3. set @today=3
4. set @week= case
5. when @today=1 then ‘星期一’
6. when @today=2 then ‘星期二’
7. when @today=3 then ‘星期三’
8. when @today=4 then ‘星期四’
9. when @today=5 then ‘星期五’
10. when @today=6 then ‘星期六’
11. when @today=7 then ‘星期日’
12. else ‘值错误’
13. end
14. print @week

循环语句:

Java代码

1. WHILE 条件 BEGIN
2. 执行语句
3. END

Example:

Java代码

1. DECLARE @i INT
2. SET @i = 1
3. WHILE @i<1000000 BEGIN
4. set @i=@i+1
5. END

定义游标：

Sql代码

1. DECLARE @cur1 CURSOR FOR SELECT ………
3. OPEN @cur1
4. FETCH NEXT FROM @cur1 INTO 变量
5. WHILE(@@FETCH_STATUS=0)
6. BEGIN
7. 处理…..
8. FETCH NEXT FROM @cur1 INTO 变量
9. END
10. CLOSE @cur1
11. DEALLOCATE @cur1

Sql代码

1. AS
3. declare @CATEGORY_CI_TABLENAME VARCHAR(50) =”
4. declare @result VARCHAR(2000) = ”
5. declare @CI_ID DECIMAL = 0
6. declare @num int = 1
7. declare @countnum int = 1
9. BEGIN
10. select @countnum = count(ATTRIBUTE_CONFIG_ID) from T_ATTRIBUTE_CONFIG where CMDB_UPDATE_FLAG= ‘Y’ and CATEGORY_CODE =@CATEGORY_CODE
12. IF (@ATTRIBUTE2=’A’)
13. begin
14. DECLARE MyCursor CURSOR for select ATTRIBUTE_CONFIG_CODE from T_ATTRIBUTE_CONFIG where CMDB_UPDATE_FLAG= ‘Y’ and CATEGORY_CODE =@CATEGORY_CODE
15. OPEN MyCursor FETCH NEXT FROM MyCursor INTO @CONFIG_CODE
16. set @result = @result+@CONFIG_CODE+’,’
17. WHILE @@FETCH_STATUS = 0
18. BEGIN
19. FETCH NEXT FROM MyCursor INTO @CONFIG_CODE
20. set @num = @num+ 1
21. if(@num<@countnum)
22. begin
23. set @result = @result+@CONFIG_CODE+’,’
24. end
25. else if(@num=@countnum)
26. begin
27. set @result = @result +@CONFIG_CODE
28. end
29. END
30. CLOSE MyCursor
31. DEALLOCATE MyCursor
32. set @result = ‘insert into ‘ + @ATTRIBUTE1 + ‘(‘ + @result +’) select ‘+ @result +’ from ‘+@CATEGORY_CI_TABLENAME +’ where CI_ORDER_LINE_ID=’+@KEY_ID
33. end
34. else if((@ATTRIBUTE2=’U’))

临时表：

— Select INTO 从一个查询的计算结果中创建一个新表。 数据并不返回给客户端，这一点和普通的Select 不同。 新表的字段具有和 Select 的输出字段相关联（相同）的名字和数据类型。 select * into NewTable from Uname

— Insert INTO ABC Select — 表ABC必须存在 — 把表Uname里面的字段Username复制到表ABC Insert INTO ABC Select Username FROM Uname

— 创建临时表 Create TABLE #temp( UID int identity(1, 1) PRIMARY KEY, UserName varchar(16), Pwd varchar(50), Age smallint, Sex varchar(6) ) — 打开临时表 Select * from #temp

1、局部临时表（#开头）只对当前连接有效，当前连接断开时自动删除。 2、全局临时表（##开头）对其它连接也有效，在当前连接和其他访问过它的连接都断开时自动删除。 3、不管局部临时表还是全局临时表，只要连接有访问权限，都可以用drop table #Tmp(或者drop table ##Tmp)来显式删除临时表。

临时表对执行效率应该影响不大，只要不是太过份，相反可以提高效率特别是连接查询的地方，只要你的数据库临时表空间足够 游标多，会严重执行效率，能免则免！

临时表在不同数据库设计中的作用

SQLSERVER 存储过程 语法

===============================================================================

其他：

–有输入参数的存储过程–

create proc GetComment

(@commentid int)

as

select * from Comment where CommentID=@commentid

–有输入与输出参数的存储过程–

create proc GetCommentCount

@newsid int,

@count int output

as

select @count=count(*) from Comment where NewsID=@newsid

–返回单个值的函数–

create function MyFunction

(@newsid int)

returns int

as

begin

declare @count int

select @count=count(*) from Comment where NewsID=@newsid

return @count

end

–调用方法–

declare @count int

exec @count=MyFunction 2

print @count

–返回值为表的函数–

Create function GetFunctionTable

(@newsid int)

returns table

as

return

(select * from Comment where NewsID=@newsid)

–返回值为表的函数的调用–

select * from GetFunctionTable(2)

———————————————————————————————————————————–

SQLServer 存储过程中不拼接SQL字符串实现多条件查询

　以前拼接的写法 　　set @sql=’ select * from table where 1=1 ‘ 　　if (@addDate is not null) 　　 set @sql = @sql+’ and addDate = ‘+ @addDate + ‘ ‘ 　　if (@name <>” and is not null) 　　 set @sql = @sql+ ‘ and name = ‘ + @name + ‘ ‘ 　　exec(@sql) 下面是 不采用拼接SQL字符串实现多条件查询的解决方案 第一种写法是 感觉代码有些冗余 　　if (@addDate is not null) and (@name <> ”) 　　 select * from table where addDate = @addDate and name = @name 　　else if (@addDate is not null) and (@name =”) 　　 select * from table where addDate = @addDate 　　else if(@addDate is null) and (@name <> ”) 　　 select * from table where and name = @name 　　else if(@addDate is null) and (@name = ”) 　　select * from table 第二种写法是 　　select * from table where (addDate = @addDate or @addDate is null) and (name = @name or @name = ”) 第三种写法是 　　SELECT * FROM table where 　　addDate = CASE @addDate IS NULL THEN addDate ELSE @addDate END, 　　name = CASE @name WHEN ” THEN name ELSE @name END

———————————————————————————————————————————–

SQLSERVER存储过程基本语法

一、定义变量

二、表、临时表、表变量

三、循环

四、条件语句

五、游标

六、触发器

触发器中的临时表：

　　Inserted 　　存放进行insert和update 操作后的数据 　　Deleted 　　存放进行delete 和update操作前的数据

七、存储过程

八、自定义函数

　　函数的分类：

　　　　1）标量值函数

　　　　2）表值函数

　　　　　　　　a:内联表值函数

　　　　　　　　b:多语句表值函数

　　　　3）系统函数

谈谈自定义函数与存储过程的区别：

一、自定义函数：

　　1. 可以返回表变量

　　2. 限制颇多，包括

　　　　不能使用output参数；

　　　　不能用临时表；

　　　　函数内部的操作不能影响到外部环境；

　　　　不能通过select返回结果集；

　　　　不能update，delete，数据库表；

　　3. 必须return 一个标量值或表变量

　　自定义函数一般用在复用度高，功能简单单一，争对性强的地方。

二、存储过程

　　1. 不能返回表变量

　　2. 限制少，可以执行对数据库表的操作，可以返回数据集

　　3. 可以return一个标量值，也可以省略return

　　　存储过程一般用在实现复杂的功能，数据操纵方面。

———————————————————————————————————————————–

SqlServer存储过程–实例

实例1：只返回单一记录集的存储过程。

　　表银行存款表（bankMoney）的内容如下

要求1：查询表bankMoney的内容的存储过程

create procedure sp_query_bankMoney as select * from bankMoney go exec sp_query_bankMoney

注* 在使用过程中只需要把T-Sql中的SQL语句替换为存储过程名，就可以了很方便吧！

实例2（向存储过程中传递参数）：

加入一笔记录到表bankMoney，并查询此表中userID= Zhangsan的所有存款的总金额。

Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output with encryption ———加密 as insert into bankMoney (id,userID,sex,Money) Values(@param1,@param2,@param3, @param4) select @param5=sum(Money) from bankMoney where userID=’Zhangsan’ go 在SQL Server查询分析器中执行该存储过程的方法是： declare @total_price int exec insert_bank ‘004’,’Zhangsan’,’男’,100,@total_price output print ‘总余额为’+convert(varchar,@total_price) go

在这里再啰嗦一下存储过程的3种传回值（方便正在看这个例子的朋友不用再去查看语法内容）:

1.以Return传回整数 2.以output格式传回参数 3.Recordset

传回值的区别:

output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中。

实例3：使用带有复杂 SELECT 语句的简单过程

　　下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。

　　USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = ‘au_info_all’ AND type = ‘P’) DROP PROCEDURE au_info_all GO CREATE PROCEDURE au_info_all AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id GO

　　au_info_all 存储过程可以通过以下方法执行：

　　EXECUTE au_info_all — Or EXEC au_info_all

　　如果该过程是批处理中的第一条语句，则可使用：

　　au_info_all

实例4：使用带有参数的简单过程

　　CREATE PROCEDURE au_info @lastname varchar(40), @firstname varchar(20) AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname = @firstname AND au_lname = @lastname GO

　　au_info 存储过程可以通过以下方法执行：

　　EXECUTE au_info ‘Dull’, ‘Ann’ — Or EXECUTE au_info @lastname = ‘Dull’, @firstname = ‘Ann’ — Or EXECUTE au_info @firstname = ‘Ann’, @lastname = ‘Dull’ — Or EXEC au_info ‘Dull’, ‘Ann’ — Or EXEC au_info @lastname = ‘Dull’, @firstname = ‘Ann’ — Or EXEC au_info @firstname = ‘Ann’, @lastname = ‘Dull’

　　如果该过程是批处理中的第一条语句，则可使用：

　　au_info ‘Dull’, ‘Ann’ — Or au_info @lastname = ‘Dull’, @firstname = ‘Ann’ — Or au_info @firstname = ‘Ann’, @lastname = ‘Dull’

存储过程，其中@case作为执行update的选择依据，用if…else实现执行时根据传入的参数执行不同的修改. –下面是if……else的存储过程： if exists (select 1 from sysobjects where name = ‘Student’ and type =’u’ ) drop table Student go

if exists (select 1 from sysobjects where name = ‘spUpdateStudent’ and type =’p’ ) drop proc spUpdateStudent go

create table Student ( fName nvarchar (10), fAge

smallint , fDiqu varchar (50), fTel int ) go

insert into Student values (‘X.X.Y’ , 28, ‘Tesing’ , 888888) go

create proc spUpdateStudent ( @fCase int , @fName nvarchar (10), @fAge smallint , @fDiqu varchar (50), @fTel int ) as update Student set fAge = @fAge, — 传 1,2,3 都要更新 fAge 不需要用 case fDiqu = (case when @fCase = 2 or @fCase = 3 then @fDiqu else fDiqu end ), fTel = (case when @fCase = 3 then @fTel else fTel end ) where fName = @fName select * from Student go

— 只改 Age exec spUpdateStudent @fCase = 1, @fName = N’X.X.Y’ , @fAge = 80, @fDiqu = N’Update’ , @fTel = 1010101

— 改 Age 和 Diqu exec spUpdateStudent @fCase = 2, @fName = N’X.X.Y’ , @fAge = 80, @fDiqu = N’Update’ , @fTel = 1010101

— 全改 exec spUpdateStudent @fCase = 3, @fName = N’X.X.Y’ , @fAge = 80, @fDiqu = N’Update’ , @fTel = 1010101

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