SQL存储过程全面实例讲解(10页).doc

-SQL存储过程全面实例讲解-第 10 页SQL实例讲解一、 创建存储过程结构CREATE PROCEDURE创建存储过程，存储过程是保存起来的可以接受和返回用户提供的参数的  Transact-SQL 语句的集合。可以创建一个过程供永久使用，或在一个会话中临时 使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。也可以创建 在 Microsoft SQL Server启动时自动运行的存储过程。语法CREATE PROC  EDURE  procedure_name   number       parameter data_type          VARYING   = default   OUTPUT       ,.n   WITH     RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION    FOR REPLICATION  AS sql_statement  .n  二、 存储过程实例讲解1. 使用带有复杂 SELECT 语句的简单过程下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及 出版社。该存储过程不使用任何参数。USE pubsIF EXISTS (SELECT name FROM sysobjects          WHERE name = 'au_info_all' AND type = 'P')   DROP PROCEDURE au_info_allGOCREATE PROCEDURE au_info_allASSELECT au_lname, au_fname, title, pub_name   FROM authors a INNER JOIN titleauthor ta      ON  =  INNER JOIN titles t      ON  =  INNER JOIN publishers p      ON  = GOau_info_all 存储过程可以通过以下方法执行：EXECUTE au_info_all- OrEXEC au_info_all如果该过程是批处理中的第一条语句，则可使用：au_info_all2. 使用带有参数的简单过程下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍 以及出版社。该存储过程接受与传递的参数精确匹配的值。USE pubsIF EXISTS (SELECT name FROM sysobjects          WHERE name = 'au_info' AND type = 'P')   DROP PROCEDURE au_infoGOUSE pubsGOCREATE 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  =  INNER JOIN titles t      ON  =  INNER JOIN publishers p      ON  =    WHERE  au_fname = firstname      AND au_lname = lastnameGOau_info 存储过程可以通过以下方法执行：EXECUTE au_info 'Dull', 'Ann'- OrEXECUTE au_info lastname = 'Dull', firstname = 'Ann'- OrEXECUTE au_info firstname = 'Ann', lastname = 'Dull'- OrEXEC au_info 'Dull', 'Ann'- OrEXEC au_info lastname = 'Dull', firstname = 'Ann'- OrEXEC au_info firstname = 'Ann', lastname = 'Dull'如果该过程是批处理中的第一条语句，则可使用：au_info 'Dull', 'Ann'- Orau_info lastname = 'Dull', firstname = 'Ann'- Orau_info firstname = 'Ann', lastname = 'Dull'3. 使用带有通配符参数的简单过程下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍 以及出版社。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则使用 预设的默认值。USE pubsIF EXISTS (SELECT name FROM sysobjects       WHERE name = 'au_info2' AND type = 'P')   DROP PROCEDURE au_info2GOUSE pubsGOCREATE PROCEDURE au_info2   lastname varchar(30) = 'D*',   firstname varchar(18) = '*'AS SELECT au_lname, au_fname, title, pub_nameFROM authors a INNER JOIN titleauthor ta   ON  =  INNER JOIN titles t   ON  =  INNER JOIN publishers p   ON  = WHERE au_fname LIKE firstname   AND au_lname LIKE lastnameGOau_info2 存储过程可以用多种组合执行。下面只列出了部分组合：EXECUTE au_info2- OrEXECUTE au_info2 'Wh*'- OrEXECUTE au_info2 firstname = 'A*'- OrEXECUTE au_info2 'CKarsOEn'- OrEXECUTE au_info2 'Hunter', 'Sheryl'- OrEXECUTE au_info2 'H*', 'S*'4. 使用 OUTPUT 参数OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期 间设置的某个值。下面的示例创建一个存储过程  (titles_sum)，并使用一个可选 的输入参数和一个输出参数。首先，创建过程：USE pubsGOIF EXISTS(SELECT name FROM sysobjects      WHERE name = 'titles_sum' AND type = 'P')   DROP PROCEDURE titles_sumGOUSE pubsGOCREATE PROCEDURE titles_sum TITLE varchar(40) = '*', SUM money OUTPUTASSELECT 'Title Name' = titleFROM titles WHERE title LIKE TITLE SELECT SUM = SUM(price)FROM titlesWHERE title LIKE TITLEGO接下来，将该 OUTPUT 参数用于控制流语言。 说明  OUTPUT 变量必须在创建表和使用该变量时都进行定义。参数名和变量名不一定要匹配，不过数据类型和参数位置必须匹配（除非使用  SUM = variable 形式）。 DECLARE TOTALCOST moneyEXECUTE titles_sum 'The*', TOTALCOST OUTPUTIF TOTALCOST < 200 BEGIN   PRINT ' '   PRINT 'All of these titles can be purchased for less than $200.'ENDELSE   SELECT 'The total cost of these titles is $'          + RTRIM(CAST(TOTALCOST AS varchar(20)下面是结果集：Title Name                                                               - The Busy Executive's Database GuideThe Gourmet MicrowaveThe Psychology of Computer Cooking(3 row(s) affected)Warning, null value eliminated from aggregate.All of these titles can be purchased for less than $200.5. 使用 OUTPUT 游标参数OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。首先，创建以下过程，在 titles 表上声明并打开一个游标：USE pubsIF EXISTS (SELECT name FROM sysobjects       WHERE name = 'titles_cursor' and type = 'P')DROP PROCEDURE titles_cursorGOCREATE PROCEDURE titles_cursor titles_cursor CURSOR VARYING OUTPUTASSET titles_cursor = CURSORFORWARD_ONLY STATIC FORSELECT *FROM titlesOPEN titles_cursorGO接下来，执行一个批处理，声明一个局部游标变量，执行上述过程以将游标赋值给 局部变量，然后从该游标提取行。USE pubsGODECLARE MyCursor CURSOREXEC titles_cursor titles_cursor = MyCursor OUTPUTWHILE (FETCH_STATUS = 0)BEGIN   FETCH NEXT FROM MyCursorENDCLOSE MyCursorDEALLOCATE MyCursorGO6. 使用 WITH RECOMPILE 选项如果为过程提供的参数不是典型的参数，并且新的执行计划不应高速缓存或存储在 内存中，WITH RECOMPILE 子句会很有帮助。USE pubsIF EXISTS (SELECT name FROM sysobjects      WHERE name = 'titles_by_author' AND type = 'P')   DROP PROCEDURE titles_by_authorGOCREATE PROCEDURE titles_by_author LNAME_PATTERN varchar(30) = '*'WITH RECOMPILEASSELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name',   title AS TitleFROM authors a INNER JOIN titleauthor ta    ON  =  INNER JOIN titles t   ON  = WHERE au_lname LIKE LNAME_PATTERNGO7. 使用 WITH ENCRYPTION 选项WITH ENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程，使用  sp_helptext 系统存储过程获取关于加密过程的信息，然后尝试直接从  syscomments 表中获取关于该过程的信息。IF EXISTS (SELECT name FROM sysobjects      WHERE name = 'encrypt_this' AND type = 'P')   DROP PROCEDURE encrypt_thisGOUSE pubsGOCREATE PROCEDURE encrypt_thisWITH ENCRYPTIONASSELECT * FROM authorsGOEXEC sp_helptext encrypt_this下面是结果集：The object's comments have been encrypted.接下来，选择加密存储过程内容的标识号和文本。SELECT c.id,  FROM syscomments c INNER JOIN sysobjects oWHERE o.name = 'encrypt_this'下面是结果集：说明  text 列的输出显示在单独一行中。执行时，该信息将与 id 列信息出现在 同一行中。id         text                                                        - -1413580074 ?e?(1 row(s) affected)H. 创建用户定义的系统存储过程下面的示例创建一个过程，显示表名以 emp 开头的所有表及其对应的索引。如果 没有指定参数，该过程将返回表名以 sys 开头的所有表（及索引）。IF EXISTS (SELECT name FROM sysobjects      WHERE name = 'sp_showindexes' AND type = 'P')   DROP PROCEDURE sp_showindexesGOUSE masterGOCREATE PROCEDURE sp_showindexes   TABLE varchar(30) = 'sys*'AS SELECT o.name AS TABLE_NAME,   i.name AS INDEX_NAME,    indid AS INDEX_IDFROM sysindexes i INNER JOIN sysobjects o   ON o.id = i.id WHERE o.name LIKE TABLEGO         USE pubsEXEC sp_showindexes 'emp*'GO下面是结果集：TABLE_NAME       INDEX_NAME       INDEX_ID - - -employee         employee_ind     1employee         PK_emp_id        2(2 row(s) affected)8. 使用延迟名称解析下面的示例显示四个过程以及延迟名称解析的各种可能使用方式。尽管引用的表或 列在编译时不存在，但每个存储过程都可创建。IF EXISTS (SELECT name FROM sysobjects      WHERE name = 'proc1' AND type = 'P')   DROP PROCEDURE proc1GO- Creating a procedure on a nonexistent table.USE pubsGOCREATE PROCEDURE proc1AS   SELECT *   FROM does_not_existGO  - Here is the statement to actually see the text of the procedure.SELECT o.id, FROM sysobjects o INNER JOIN syscomments c WHERE  = 'P' AND o.name = 'proc1'GOUSE masterGOIF EXISTS (SELECT name FROM sysobjects      WHERE name = 'proc2' AND type = 'P')   DROP PROCEDURE proc2GO- Creating a procedure that attempts to retrieve information from a- nonexistent column in an existing table.USE pubsGOCREATE PROCEDURE proc2AS   DECLARE middle_init char(1)   SET middle_init = NULL   SELECT au_id, middle_initial = middle_init   FROM authorsGO  - Here is the statement to actually see the text of the procedure.SELECT o.id, FROM sysobjects o INNER JOIN syscomments c WHERE  = 'P' and o.name = 'proc2'