SQL{常用}基础知识到存储过程简单整理

基础知识：

sql：Structured Query Language

1.增删改查

	INSERT INTO <表名> [（<字段名1> [,<字段名2>, ...])] VALUES (<常量1> [,<常量2>, ...])

	DELETE FROM〈表名〉[WHERE <条件表达式> [AND|OR <条件表达式>...]]

	UPDATE 〈表名〉 SET 列名1 = 常量表达式1[,列名2 = 常量表达式2 ...] WHERE <条件表达式> [AND|OR <条件表达式>...]

	SELECT [ALL|DISTINCT] <目标列表达式> [AS 列名] [,<目标列表达式> [AS 列名] ...] 

	FROM <表名> [，<表名>„] 

	[WHERE <条件表达式> [AND|OR <条件表达式>...] 

	[GROUP BY 列名 [HAVING <条件表达式>]] 

	[ORDER BY 列名 [ASC | DESC]]  

存储过程：Stored Procedure

	一组为了完成特定功能的SQL 语句集。

		
 
 

  
  语法如下：
 
 
 
 

  
  if exists (select * from sysobjects where name='
  
  procedure_name ')
 
 
 
 

  
  	drop procedure procedure_name
 
 
 
 

  
  go
 
 
 
 

  
  一般有上面这段加在前面。
 
 
 
 

  
  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、 procedure_name  
 
  新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。  
  要创建局部临时过程，可以在 procedure_name 前面加一个编号
符 (#procedure_name)，要创建全局临时过程，可以在 procedure_name 前面加两个编号符 (##procedure_name)。完整的名称（包括 # 或 ##）不能超过 128 个字符。指定过程所有者的名称是可选的。  
 2、;number 
 
  是可选的整数，用来对同名的过程分组，以便用一条 Drop PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。Drop PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。   3、@parameter 
 
  过程中的参数。在 Create PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2100 个参数。  
  使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。  
4、data_type 
 
  参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。  
  说明：对于可以是cursor 数据类型的输出参数，没有最大数目的限制。  
5、VARYING 
 
  指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。  
6、default 
 
  参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。  
7、OUTPUT 
 
  表明参数是返回参数。该选项的值可以返回给 EXEC[UTE]。使用 OUTPUT 参数可将信息返回给调用过程。Text、ntext 和 image 参数可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以是游标占位符。  
8、n 
 
  表示最多可以指定 2100 个参数的占位符。  
9、{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} 
 
  RECOMPILE 表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用 RECOMPILE 选项。  
  ENCRYPTION 表示 SQL Server 加密 syscomments 表中包
含 Create PROCEDURE 语句文本的条目。使用 ENCRYPTION 可防止将过程作为 SQL Server 复制的一部分发布。  
  说明：在升级过程中，SQL Server 利用存储在 syscomments 中的加密注释来重新创建加密过程。  
10、FOR REPLICATION 
 
  指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。  
11、AS 
 
  指定过程要执行的操作。  
12、sql_statement 
 
  过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。 
13、 n 
 
  是表示此过程可以包含多条 Transact-SQL 语句的占位符。  
14、注释 
 
  /*和*/之间的为注释，可以包含一行和多行的说明文字。

 
 
 
 

  
  
 SQL Server

 
 
 
 

  
  关的管理任务或其它的系统管理任务。  
    系统存储过程可以在任意一个数据库中执行。创建并存放于系统数据库master中，并且名称以sp_或者xp_开头。  
    部分系统存储过程： 
        sp_addtype：用于定义一个用户定义数据类型。  
       sp_configure：用于管理服务器配置选项设置。  
       xp_sendmail：用于发送电子邮件或寻呼信息。  
       sp_stored_procedures：用于返回当前数据库中的存储过程的清单。  
       sp_help：用于显示参数清单和其数据类型。  
       sp_helptext：用于显示存储过程的定义文本。  
       sp_rename：用于修改当前数据库中用户对象的名称。  
       Sp_who:用于显示使用数据库的当前用户  
       sp_help：用于显示参数清单和其数据类型。  
       sp_depends：用于显示存储过程依据的对象或者依据存储过程的对象  
       sp_helptext：用于显示存储过程的定义文本
 
 
 
 


 
 
 
 

  
  存储过程中游标的使用 
1、需要游标的数据操作 
 
    当select语句的结果中包含多个元组时，使用游标可以逐个存取这些元组  
    活动集：select语句返回的元组的集合  
    当前行：活动集中当前处理的那一行。游标即是指向当前行的指针。  
2、游标分类 
 
    滚动游标：游标的位置可以来回移动，可在活动集中取任意元组。  
    非滚动游标：只能在活动集中顺序地取下一个元组。  
    更新游标：数据库对游标指向的当前行加锁，当程序读下一行数据时，本行数据解锁，下一行数据加锁。  
3、定义与使用游标的语句 
 
    declare ：  
        declare  游标名[scroll]  cursor  for select语句[for update [of列表名]] 定义一个游标，使之对应一个select语句  
       for update任选项，表示该游标可用于对当前行的修改与删除  
    open  
       打开一个游标，执行游标对应的查询，结果集合为该游标的活动集  
       open  游标名  
    fetch  
       在活动集中将游标移到特定的行，并取出该行数据放到相应的变量中  
       fetch [next | prior | first | last | current | relative n | absolute m] 游标名into  [变量表]  
    close  
       关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行open语句  
       close  游标名  
    deallocate  
       删除游标，以后不能再对该游标执行open语句  
       deallocate 游标名  
    @@FETCH_STATUS  
        返回被FETCH 语句执行的最后游标的状态.  
       0 fetch语句成功  
        -1 fetch语句失败  
        -2 被提取的行不存在 
4、游标实例 
     例:查询电子商务系学生信息,性别为女输出为female,否则输出为male？  
       declare c1 cursor for select sno,sname,ssex from student where sdept='ec'  
       declare @sno char(10),@sname char(10),@ssex char(2)  
       Open c1  
       Fetch c1 into @sno,@sname,@ssex  
       While @@fetch_status==0  
       Begin  
       if @ssex='女'  
           begin  set @ssex='female' end  
       else 
 
 
 
 

  
  begin set @ssex='male' end  
       Select @sno,@sname ,@ssex  
           Fetch c1 into @sno,@sname,@ssex  
       end 例： 
    ALTER PROC [dbo].[dnt_UserRecoveryByUserName]     @username    NVARCHAR(50) AS  
BEGIN  
    DECLARE    @uid INT;     DECLARE    @tid INT;     DECLARE    @replies INT; 
    DECLARE    @temp varchar(50);  
    SET @uid        = (SELECT TOP(1) uid FROM dnt_users WHERE username = '@username'); 
    SET @tid        = 0;     SET @replies    = 0;  
    UPDATE dnt_users SET accessmasks = 0 WHERE uid = @uid; 
    UPDATE dnt_userforum SET groupid = 5 WHERE groupid = 4 AND uid = @uid;     UPDATE dnt_posts SET invisible = 0 WHERE invisible = -1 AND posterid = @uid;  
    -- 定义一游标 
    DECLARE Ctemp CURSOR FOR SELECT tid FROM dnt_topics WHERE posterid = @uid FOR READ only -- FOR UPDATE  
    OPEN Ctemp   
        FETCH next FROM Ctemp INTO @tid;         WHILE (@@fetch_status = 0)         BEGIN 
            SET @replies = (SELECT COUNT(1) FROM dnt_posts WHERE tid = @tid AND layer > 0); 
            UPDATE dnt_topics SET replies = @replies WHERE posterid = @uid AND tid = @tid; 
            FETCH next FROM Ctemp INTO @tid;         END 
    CLOSE Ctemp; 
    DEALLOCATE Ctemp; 
begin set @ssex='male' end  
       Select @sno,@sname ,@ssex  
           Fetch c1 into @sno,@sname,@ssex  
       end 例： 
    ALTER PROC [dbo].[dnt_UserRecoveryByUserName]     @username    NVARCHAR(50) AS  
BEGIN  
    DECLARE    @uid INT;     DECLARE    @tid INT;     DECLARE    @replies INT; 
    DECLARE    @temp varchar(50);  
    SET @uid        = (SELECT TOP(1) uid FROM dnt_users WHERE username = '@username'); 
    SET @tid        = 0;     SET @replies    = 0;  
    UPDATE dnt_users SET accessmasks = 0 WHERE uid = @uid; 
    UPDATE dnt_userforum SET groupid = 5 WHERE groupid = 4 AND uid = @uid;     UPDATE dnt_posts SET invisible = 0 WHERE invisible = -1 AND posterid = @uid;  
    -- 定义一游标 
    DECLARE Ctemp CURSOR FOR SELECT tid FROM dnt_topics WHERE posterid = @uid FOR READ only -- FOR UPDATE  
    OPEN Ctemp   
        FETCH next FROM Ctemp INTO @tid;         WHILE (@@fetch_status = 0)         BEGIN 
            SET @replies = (SELECT COUNT(1) FROM dnt_posts WHERE tid = @tid AND layer > 0); 
            UPDATE dnt_topics SET replies = @replies WHERE posterid = @uid AND tid = @tid; 
            FETCH next FROM Ctemp INTO @tid;         END 
    CLOSE Ctemp; 
    DEALLOCATE Ctemp; 
END