基础知识:
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_namego一般有上面这段加在前面。CREATE PROC[EDURE] procedure_name [;number][{@parameter data_type} ][VARYING] [= default] [OUTPUT]][,...n][WITH{RECOMPILE| ENCRYPTION| RECOMPILE, ENCRYPTION}][FOR REPLICATION]ASsql_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 elsebegin 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