SQLServer存储过程学习

发布时间：2020-12-12 13:56:06 所属栏目：MsSql教程来源：网络整理

导读：什么是存储过程呢？ ? 定义：将常用的或很复杂的工作，预先用 SQL 语句写好并用一个指定的名称存储起来 ,? 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时 , 只需调用 execute, 即可自动完成命令。存储过程具有以下优点 ????1. 存储过

什么是存储过程呢？

?　　定义：

　　将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来,?那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。

　存储过程具有以下优点
????1.存储过程允许标准组件式编程(模块化设计)
????存储过程在被创建以后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句，而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响。因为应用程序源代码只包含存储过程的调用语句，从而极大地提高了程序的可移植性。
????????????
????2.存储过程能够实现快速的执行速度
　　　如果某一操作包含大量的Transaction-SQL?代码,，或分别被多次执行，那么存储过程要比批处理的执行速度快很多，因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析优?化，并给出最终被存在系统表中的执行计划，而批处理的Transaction-SQL?语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。
????????????????
????3.存储过程能够减少网络流量
　　　对于同一个针对数据数据库对象的操作，如查询修改，如果这一操作所涉及到的Transaction-SQL?语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，否?则将是多条SQL?语句从而大大增加了网络流量降低网络负载。
????????????
????4.存储过程可被作为一种安全机制来充分利用系统管理员通过，对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制。

?二、变量

????@I

????三、流程控制语句(if?else?|?select?case?|?while?)
????Select?...?CASE?实例
????DECLARE?@iRet?INT,?@PKDisp?VARCHAR(20)
????SET?@iRet?=?'1'
????Select?@iRet?=?
????CASE
????????WHEN?@PKDisp?=?'一'?THEN?1
????????WHEN?@PKDisp?=?'二'?THEN?2
????????WHEN?@PKDisp?=?'三'?THEN?3
????????WHEN?@PKDisp?=?'四'?THEN?4
????????WHEN?@PKDisp?=?'五'?THEN?5
????????ELSE?100
????END

????四、存储过程格式
????????
????创建存储过程
????Create?Proc?dbo.存储过程名
????存储过程参数
????AS
????执行语句
????RETURN
????执行存储过程
????GO
*********************************************************/

SQLSERVER存儲過程的寫法格式規格 *****************************************************

*** ?author：Susan

*** ?date: 2005 / 08 / 05

*** ?expliation:如何寫存儲過程的格式及例子，有游標的用法！

*** ?本版:SQL?SERVER?版！

******************************************************/

在存儲過程中的格式規格：

CREATE? PROCEDURE ?XXX

/*

列舉傳入參數

1：名稱，2：類型，包括長度

Eg:@strUNIT_CODE?varCHAR(3)

*/

參數1，

參數2……………

As

/*

定義內部參數

1：名稱，2：類型，包括長度

Eg:@strUNIT_CODE?varCHAR(3)

*/

Declare

參數1，

參數2……………

/*

初始化內部參數

Eg:SET?@strUNIT_CODE=’’

*/

Set參數1的初始值

Set參數2的初始值…………

/*

過程的主內容區

Trascation：這裡起到的作用是，如果他中間的任何一個執行錯誤，就全部執行都返回，這裡sql?sever?7.0以前一定要寫入，以後的就可以省略

Return：結束這支sp

*/

Begin ?trascation

???? /*

????????1:可以取得需要的值以存在內部參數中

?????Eg:SELECT?@strUNIT_CODE=UNIT_CODE?FROM?UNIT?WHERE?…….

2:可以用取到的或傳入的參數進行判斷，來進行update,insert,delete?等等操作

eg:?IF?@strUNIT_CODE=’’

?????????BEGIN

?????//具體的操作

End

Else

Begin

????//具體的操作

End

?3：有關游標的問題

?????Eg:

????????declare?db?cursor?for???????//聲明一個游標(db為其名稱)

????????SELECT?UNIT_NAME?FROM?UNIT?WHERE?LEFT(UNIT_CODE,2)=LEFT(@strTO,2)//記錄集

????????open?db?????????????????//打開游標

????????????fetch?next?from?db?into?@strUNIT_NAME?//將第一個值放入一個參數中

????????while?@@fetch_status?=?0?---存在本筆值向下循環

(0：順利執行;-1：失敗，或資料列超出結果集;-2：擷取的資料列已遺漏)

????????BEGIN?????????????----開始循環

????????????????????????//個體操作

????????End????????????????----結束循環

????????Close?db????????????---關閉游標

deallocate?db?????????//移除資料指標參考

*/

Commit ?trascation

Return

?

下面是一個例子

CREATE? PROCEDURE ?TEST_2

@strTO? VARCHAR ( 3 )

AS

DECLARE

? @strUNIT_NAME? VARCHAR ( 800 ),

? @strSQL? VARCHAR ( 8000 ),

? @Link? VARCHAR ( 1 ),

? @Link1? VARCHAR ( 1 )

?

SET? @strUNIT_NAME = ''

SET? @strSQL = ''

SET? @Link = ''

SET? @Link1 = ''

?

/*

處理update?的部分

EXEC?TEST_2?'011'

EXEC?TEST_2?''

SELECT?UNIT_NAME?FROM?UNIT?WHERE?UNIT_CODE='011'

*/

BEGIN? TRANSACTION ???????????????????????????????????????

????????? IF? @strTO <> ''

???????? BEGIN

???????????????? UPDATE ?UNIT? SET ?UNIT_NAME = REPLACE (UNIT_NAME, ' * ' , '' )? WHERE ?UNIT_CODE = @strTO

???????? END

????????????? ELSE

???????? BEGIN

???????????????? UPDATE ?UNIT? SET ?UNIT_NAME = UNIT_NAME + ' * '? WHERE ?UNIT_CODE = ' 011 '

???????? END

/*

EXEC?TEST_2?'011'

功能說明：本sp用於處理cursor問題

*/

???????? IF ??? @strTO <> ''

???????????? BEGIN

???????????????????????? declare ?db? cursor? for ???????????????????????????????????????????????? -- 必需聲明在查詢的前面

???????????????? SELECT ?UNIT_NAME? FROM ?UNIT? WHERE? LEFT (UNIT_CODE, 2 ) = LEFT ( @strTO , 2 ) -- -取到相關信息

???????????? END

???????? ELSE

???????????? BEGIN

????????????????????? declare ?db? cursor? for ?????????????????????????????????????????????? -- 必需聲明在查詢的前面

????????????????????? SELECT ?UNIT_NAME? FROM ?UNIT? WHERE? LEFT (UNIT_CODE, 2 ) = LEFT ( ' 011 ' , 2 ) -- -取到相關信息

???????????? END

???????? open ?db???????????????????????????????????????????????????? -- -開起取到的信息

???????? fetch? next? from ?db? into? @strUNIT_NAME ???????? -- -把第一筆放入@strUNIT_NAME中

???????? while? @@fetch_status? =? 0 ??????????????????????????????????????????? -- -表示存在本筆資料

???????? BEGIN ???????????????????????????????????????????????????? -- --開始循環

???????????????? set? @strSQL? = @strSQL + @Link1 + @Link +? @strUNIT_NAME? -- --設定保存的值

???????????????? fetch? next? from ?db? into? @strUNIT_NAME ??????????????????????? -- --進行下次循環

???????????????? SET? @Link = CHAR ( 13 )? + CHAR ( 10 )

???????????????? SET? @Link1 = ' , '

???????? END ???????????????????????????????????????????????????????? -- --結束循環

???????? close ?db??????????????????????????????????????????????????? -- -關閉信息

???????? deallocate ?db???????????????????????????????????????????? -- -移除資料指標參考

SELECT? @strSQL

COMMIT? TRANSACTION

RETURN 如果循环insert的例子
DECLARE? @strLoginID? VARCHAR ( 16 )
BEGIN
declare ?db? cursor? for
SELECT ?LoginID? FROM ?dbo.s_Users? WHERE? len (UnitCoding)? in ( 9 , 12 )
END
open ?db
fetch? next? from ?db? into? @strLoginID
while? @@fetch_status? =? 0? BEGIN
insert? into ?s_P_User
select? @strLoginID ,LevelID? from ?s_P_User? where ?LoginID? =? ' aa '
fetch? next? from ?db? into? @strLoginID
END
close ?db
deallocate ?db 一、TRUNCATE

二、Select INTO 建表
????把一个表中的数据复制到另外一个表中。

三、Insert INTO Select

四、补充：临时表
????临时表存储在系统数据库tempdb中
????临时表会被系统隐式地丢弃

---------------------------------------------------------

*********************************************************/

-- ?变量的声明,sql里面声明变量时必须在变量前加@符号
???? DECLARE? @I? INT

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

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

-- ?Sql?里if语句
???? IF ?条件? BEGIN
????????执行语句
???? END
???? ELSE? BEGIN
????????执行语句
???? END
????????????
???? DECLARE? @d? INT
???? set? @d? =? 1

???? IF? @d? =? 1? BEGIN

???? -- ?打印
???????? PRINT? ' 正确 '
???? END
???? ELSE? BEGIN
???????? PRINT? ' 错误 '
???? END

-- ?Sql?里的多条件选择语句.
???? DECLARE? @iRet? INT ,? @PKDisp? VARCHAR ( 20 )
???? SET? @iRet? =? 1
???? Select? @iRet? =
???? CASE
???????? WHEN? @PKDisp? =? ' 一 '? THEN? 1
???????? WHEN? @PKDisp? =? ' 二 '? THEN? 2
???????? WHEN? @PKDisp? =? ' 三 '? THEN? 3
???????? WHEN? @PKDisp? =? ' 四 '? THEN? 4
???????? WHEN? @PKDisp? =? ' 五 '? THEN? 5
???????? ELSE? 100
???? END

-- ?循环语句
???? WHILE ?条件? BEGIN ????
????????执行语句
???? END

???? DECLARE? @i? INT
???? SET? @i? =? 1
???? WHILE? @i < 1000000? BEGIN
???????? set? @i = @i + 1
???? END
???? -- ?打印
???? PRINT? @i

-- ?TRUNCATE?删除表中的所有行，而不记录单个行删除操作，不能带条件

???? /*
????TRUNCATE?TABLE?在功能上与不带?Where?子句的?Delete?语句相同：二者均删除表中的全部行

。但?TRUNCATE?TABLE?比?Delete?速度快，且使用的系统和事务日志资源少。
????Delete?语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE?TABLE?通过

释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
????TRUNCATE?TABLE?删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用

的计数值重置为该列的种子。如果想保留标识计数值，请改用?Delete。如果要删除表定义及其数据，请

使用?Drop?TABLE?语句。
????对于由?FOREIGN?KEY?约束引用的表，不能使用?TRUNCATE?TABLE，而应使用不带?Where?子句的

Delete?语句。由于?TRUNCATE?TABLE?不记录在日志中，所以它不能激活触发器。
????TRUNCATE?TABLE?不能用于参与了索引视图的表。
????示例
????????下例删除?authors?表中的所有数据。 */
????????
???????? TRUNCATE? TABLE ?authors
????????????????

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

-- ?Insert?INTO?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

-- ?存储过程
???????? -- ?要创建存储过程的数据库
???????? Use ?Test
???????? -- ?判断要创建的存储过程名是否存在
???????????? if? Exists ( Select ?name? From ?sysobjects? Where ?name = ' csp_AddInfo '? And

type = ' P ' )
???????????? -- ?删除存储过程
???????????? Drop? Procedure ?dbo.csp_AddInfo
???????? Go
????????????????
????????????????
???????? -- ?创建存储过程
???????? Create? Proc ?dbo.csp_AddInfo
???????? -- ?存储过程参数
???????? @UserName? varchar ( 16 ),
???????? @Pwd? varchar ( 50 ),
???????? @Age? smallint ,
???????? @Sex? varchar ( 6 )
???????? AS
???????? -- ?存储过程语句体
???????? insert? into ?Uname?(UserName,Pwd,Age,Sex)
???????????? values ?( @UserName , @Pwd , @Age , @Sex )
???????? RETURN
???????? -- ?执行
???????? GO
????????????????
???????? -- ?执行存储过程
???????? EXEC ?csp_AddInfo? ' Junn.A ' , ' 123456 ' , 20 , ' 男 '

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!