存储过程(Stored Procedure)
发布时间：2020-12-12 15:21:27 所属栏目：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 ? 下面是一個例子 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中 ????临时表会被系统隐式地丢弃 --------------------------------------------------------- 五、存储过程(**) ????一、简介： ???? 存储过程（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里面声明变量时必须在变量前加@符号 ???? 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 , ' 男 ' （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |