SQLSERVER 存储过程 语法
发布时间：2020-12-12 16:07:11 所属栏目：MsSql教程 来源：网络整理
导读：SQLSERVER存儲過程的寫法格式規格 ***************************************************** *** ?author：Susan *** ?date: 2005 / 08 05 *** ?expliation:如何寫存儲過程的格式及例子，有游標的用法！ *** ?本版:SQL?SERVER?版！ *************************
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 ? 下面是一個例子 PROCEDURE?TEST_2 @strTO? VARCHAR( 3) AS DECLARE ? @strUNIT_NAME? VARCHAR( 800), ? @strSQL? VARCHAR( 8000),0)">@Link? VARCHAR( 1),0)">@Link1? VARCHAR( 1) ? SET? @strUNIT_NAME = '' @strSQL @Link @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,' * ',0)">'')? WHERE?UNIT_CODE = @strTO ???????? END ????????????? ELSE ???????? =UNIT_NAME + '? 011 ' ???????? END EXEC?TEST_2?'011' 功能說明：本sp用於處理cursor問題 */ ???????? IF??? '' ???????????? BEGIN ???????????????????????? declare?db? cursor? for???????????????????????????????????????????????? -- 必需聲明在查詢的前面 ???????????????? SELECT?UNIT_NAME? FROM?UNIT? WHERE? LEFT(UNIT_CODE,2) LEFT( @strTO,0)">2) -取到相關信息 ???????????? END ???????? ELSE ???????????? BEGIN ????????????????????? for?????????????????????????????????????????????? ????????????????????? LEFT( open?db???????????????????????????????????????????????????? -開起取到的信息 ???????? fetch? next? from?db? into? @strUNIT_NAME???????? -把第一筆放入@strUNIT_NAME中 while? @@fetch_status? =? 0??????????????????????????????????????????? -表示存在本筆資料 BEGIN???????????????????????????????????????????????????? --開始循環 set? @strSQL? + +? @strUNIT_NAME? --設定保存的值 @strUNIT_NAME??????????????????????? --進行下次循環 = CHAR( 13)? + CHAR( 10) ???????????????? , END???????????????????????????????????????????????????????? --結束循環 close?db??????????????????????????????????????????????????? -關閉信息 deallocate?db???????????????????????????????????????????? -移除資料指標參考 SELECT? @strSQL COMMIT? TRANSACTION RETURN 如果循环insert的例子? DECLARE? @strLoginID? VARCHAR( 16) BEGIN for SELECT?LoginID? FROM?dbo.s_Users? len(UnitCoding)? in( 9,0)">12) END open?db @strLoginID 0? insert? into?s_P_User select? @strLoginID,LevelID? from?s_P_User? where?LoginID? =? aa ' close?db deallocate?db 一、TRUNCATE 二、Select INTO 建表 ????把一个表中的数据复制到另外一个表中。 三、Insert INTO Select 四、补充：临时表 ????临时表存储在系统数据库tempdb中 ????临时表会被系统隐式地丢弃 --------------------------------------------------------- 五、存储过程(**) ????一、简介： ???? 存储过程（Stored Procedure）， 是一组为了完成特定功能的SQL 语句，集经编译后 ????存储在数据库中，用户通过指定存储过程的名字并给出参数，如果该存储过程带有参数来执行 它， ????在SQL Server 的系列版本中，存储过程分为两类：系统提供的存储过程和用户自定义存储过程 。 ????系统SP，主要存储master 数据库中，并以sp_为前缀并且系统存储过程主要是从系统表中获取 ????信息，从而为系统管理员管理SQL Server。 用户自定义存储过程是由用户创建，并能完成 ????某一特定功能，如：查询用户所需数据信息的存储过程。 ???? ???? 存储过程具有以下优点 ????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 *********************************************************/? ?变量的声明,sql里面声明变量时必须在变量前加@符号 ???? @I? INT ?变量的赋值，变量赋值时变量前必须加set @I? 30 ?声明多个变量 @s? varchar( 10),@a? INT ?Sql?里if语句 IF?条件? BEGIN ????????执行语句 ???? END ???? ELSE? END ???????????? ???? @d? INT ???? @d? 1 ???? 1? BEGIN ???? ?打印 ???????? PRINT? 正确 ' ???? BEGIN ???????? 错误 END ?Sql?里的多条件选择语句. @iRet? INT,? @PKDisp? VARCHAR( 20) ???? @iRet? 1 ???? Select? = ???? CASE ???????? WHEN? @PKDisp? 一 THEN? 1 ???????? 二 2 ???????? 三 3 ???????? 四 4 ???????? 五 5 ???????? ELSE? 100 ???? ?循环语句 WHILE?条件? BEGIN???? ????????执行语句 ???? END ???? @i? INT ???? @i? WHILE? @i < 1000000? + 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 ???????????????? ???????????????? ???????? ?创建存储过程 Proc?dbo.csp_AddInfo ???????? ?存储过程参数 @UserName? varchar( @Pwd? varchar( @Age? smallint,0)">@Sex? varchar( 6) ???????? AS ???????? ?存储过程语句体 into?Uname?(UserName,Pwd,Age,Sex) ???????????? values?( @UserName,0)">@Pwd,0)">@Age,0)">@Sex) ???????? RETURN ???????? ?执行 GO ???????????????? ???????? ?执行存储过程 EXEC?csp_AddInfo? Junn.A 123456 20,0)">男 ' （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |