SQL语言简介（四）

十二、存储过程

        存储过程是一组欲编译的SQL语句的集合，它可以作为一个独立的数据库对象，也可以作为一个单元被用户的应用程序使

                         用，以实现某一功能。

        按定义的文体可分为三种：

                 1、系统存储过程：SQL SERVER内置的存储过程，以“sp_”开头，用于进行系统设置或执行相关操作。

                 2、扩展存储过程：多数由传统的设计语言编写，其内容不保存在SQL SERVER中，而是以DLL形式单独存在.

                                                   以“xp_”开头，用于扩展SQL Server服务器功能的存储过程。

                 3、用户自定义存储过程：用户通过SQL语句自己创建的存储过程，用于实现某一功能。

         创建存储过程：

                  CREATE PROCEDURE procedure_name [ ; number] [{@parameter data_type} [VARYING] [=default][OUTPUT]][ ,...N]

                                                            [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION }]

                                                            [ FOR REPLICATION] AS sql_statement

                        procedure_name：要创建的存储过程名称

                        number：对存储过程进行分组，以后可以用一条DROP PROCEDURE将同组的过程一起删除。

                        @parameter：存储过程的参数，可声明一个或多个，用户必须在执行过程时提供每个所声明参数的值。

                        data_type：参数的数据类型。

                        varying：指定作为输出参数支持的结果集，仅适用于游标参数。

                        default：为参数设置默认值。

                        output：表明参数是返回参数

                        n：表示可定义多个参数。

                        sql_statement：存储过程中的过程体。

                     例：

                           create procedure p_selectUser as select * from user

                                             exec p_selectUser

                           create procddure p_selectGoods  @goodsID int =1 as select * from goods where id=@goodsID

                                             exec p_selectGoods 13  或 exec p_selectGoods @goodsID=13

 

          在SQL Server中使用Execute语名招待存储过程： exec procedure_name

          修改存储过程：

                   ALTER PROCEDURE procedure_name [; number] [{@parameter data_type} [VARYING][=default][OUTPUT]][ ,...n]

                                                          [WITH {RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION }]

                                                          [FOR REPLICATION] AS sql_statement

          删除存储过程：

                   DROP PROCEDURE procedure_name [ , ...n]

十三、触发器

          1、它是一种特殊类型的存储过程，在插入、删除或修改特定的数据时触发执行。通常可以强制执行一定的业务规则，以

                 保持 数据完整性、检查数据有效性、实现数据库管理任务和一些附加的功能。

          2、它不能通过名称被直接调用，更不允许设置参数。

          3、分类：

                 1)AFTER触发器：会在触发INSERT , UPDATE, DELETE动作之后执行，它也可以在已插入一行或多行和所有约束已被

                                                 处理且通过后才触发。

                 2)INSTEAD OF触发器：它是在激活触发器的语句执行前，系统执行触发器中的内容，并取代激活触发器语句的执行

           4、创建

                 CREATE TRIGGER trigger_name ON {table | view} [ WITH ENCRYPTION ] {{{FOR | AFIER | INSTEAD OF}{[INSERT],

                                  [UPDATE]} [WITH APPEND] [NOT FOR REPLICATION] AS [{IF UPDATE (column)[{AND | OR}UPDATE

                                  (column)] [...n] | IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask){comparsion_operator}

                                    column_bitmask[...n]}] sql_statement[...n]}}

            5、注意事项

                  1）CREATE TRIGGER必须是批处理中的第一个语句，若该语句前有其他语句，应该使用批处理结束符GO隔开。

                  2）不能在临时表或系统表上创建触发器，但在触发器中可以引用临时表，不能引用系统表。

                  3）创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。

                  4）触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。

                  5）在含有用DELETE或UPDATE操作定义外键的表中，不能定义INSTEAD OF和INSTEAD OF UPDATE触发器。

                  6）WRITETEXT语句不会引发INSERT或UPDATE触发器。

                  7）TRUNCATE TABLE语句不能引发DELETE触发器，因为该语句没有记录。

           6、删除触发器

                 DROP TRIGGER {trigger}[,...n]

十四、游标

           1、游标是映射结果集并在结果集内的单行上建立一个位置的实例，使用它，用户可以访问结果集中任意行的数据。

           2、使用游标的步骤：

                 1）声明（DECLARE）：定义游标的名称、类型和属性。

                       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]]]

                 2）调用（OPEN）：打开和直充数据。

                       OPEN {{[GLOBAL] cursor_name} | cursor_variable_name}

                 3）提取（FETCH）：从结果集中读取数据并进行处理。

                       FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}] FROM ]

                                     {{[GLOBAL] cursor_name} | @cursor_variable_name} [ INTO @variable_name[,...n]]

                 4）关闭（CLOSE）：停止游标使用的查询，但并不删除游标定义，可以使用OPEN语句再次打开。

                       CLOSE {{[GLOBAL] cursor_name} | cursor_variable_name}

                 5）释放（DEALLOCATED）：删除游标并释放其占用的所有资源。

                       DEALLOCATE {{[GLOBAL] cursor_name | @cursor_variable_name}