最近sql数据库的使用总结

Sql学习

 

 

•1、             带游标的存储过程：

存储过程的定义规范，我们可以参照sql的联机帮助，可以详细的得到。

CREATE PROCEDURE copyData AS

declare @qid int,

    @orgAddr varchar(25),

       @destaddr varchar(25),

       @answers varchar(256),

       @receivedate datetime,

       @serviceID varchar(10)

DECLARE MutiInsert_Cursor CURSOR FOR

select substring(DestAddr,14,17) as QID,OrgAddr,DestAddr,SM_Content,RecvTime,ServiceID from tbl_SMReceived

where Reserve2 is NULL

 

OPEN MutiInsert_Cursor

 

FETCH NEXT FROM MutiInsert_Cursor

into @qid,@orgAddr,@destaddr,@answers,@receivedate,@serviceID

WHILE @@FETCH_STATUS = 0

BEGIN

    insert into tbl_Answers (QID,OrgAddr,DestAddr,Anwsers,ReceiveDate,ServiceID)

       values(@qid,@orgAddr,@destaddr,','+@answers+',',@receivedate,@serviceID)

    FETCH NEXT FROM MutiInsert_Cursor

    into @qid,@orgAddr,@destaddr,@answers,@receivedate,@serviceID

END

CLOSE MutiInsert_Cursor

DEALLOCATE MutiInsert_Cursor

GO

上述存储过程用了游标，在使用存储过程时可能会出现查出多条记录插入一个表中的情况，当出现这种情况的时候sql会报错，错误大致意思就是不能同时插入处理多行。这个时候游标的作用就凸现出来啦。

游标顾名思义就像一个可以游动的指针，它指向结果的"一条"记录，这个一条很重要，这样就可以有了解决多行处理的方法。

定义方法：DECLARE MutiInsert_Cursor CURSOR FOR ........

For后的内容就是一张表，可以使select语句查询出来的表，也可以是其他。

在看后面的代码，你会发现，这跟asp读取数据库的recordset很相似，应该就是一个东西吧，游标嘛。

定义好游标后，先打开它，这时就可以读出它这个时候指向的记录内容。读取方法为：

FETCH NEXT FROM MutiInsert_Cursor

into @qid,@orgAddr,@destaddr,@answers,@receivedate,@serviceID

into后面的东西就是存储过程一开始定义的变量。

对于内容遍历，可以做一个循环（如下：）：

WHILE @@FETCH_STATUS = 0

BEGIN

    insert into tbl_Answers (QID,OrgAddr,DestAddr,Anwsers,ReceiveDate,ServiceID)

       values(@qid,@orgAddr,@destaddr,','+@answers+',',@receivedate,@serviceID)

    FETCH NEXT FROM MutiInsert_Cursor

    into @qid,@orgAddr,@destaddr,@answers,@receivedate,@serviceID

END

完成所有之后记住要关闭游标，释放之。

•2、             自定义函数

自定义的函数可以减少很多工作量，是我们编程者京城用到的，在sql中也一样不例外。

 

打开上图中的内容，即为该数据库中存在的用户自定义函数。

简单语法

CREATE FUNCTION [ owner_name.] function_name
    ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS scalar_return_data_type

[ WITH < function_option> [ [,] ...n] ]

[ AS ]

BEGIN
    function_body
    RETURN scalar_expression
END

owner_name

拥有该用户定义函数的用户 ID 的名称。owner_name 必须是现有的用户 ID。

function_name

用户定义函数的名称。函数名称必须符合标识符的规则，对其所有者来说，该名称在数据库中必须是唯一的。

@parameter_name

用户定义函数的参数。CREATE FUNCTION 语句中可以声明一个或多个参数。函数最多可以有 1,024 个参数。函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。 如果函数的参数有默认值，在调用该函数时必须指定"default"关键字才能获得默认值。这种行为不同于存储过程中有默认值的参数，在存储过程中省略参数也意味着使用默认值。

使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个函数的参数仅用于该函数本身；相同的参数名称可以用在其它函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。

scalar_parameter_data_type

参数的数据类型。所有标量数据类型（包括 bigint 和 sql_variant）都可用作用户定义函数的参数。不支持 timestamp 数据类型和用户定义数据类型。不能指定非标量类型（例如 cursor 和 table）。

scalar_return_data_type

是标量用户定义函数的返回值。scalar_return_data_type 可以是 SQL Server 支持的任何标量数据类型（text、ntext、image 和 timestamp 除外）。

scalar_expression

指定标量函数返回的标量值。

TABLE

指定表值函数的返回值为表。

在内嵌表值函数中，通过单个 SELECT 语句定义 TABLE 返回值。内嵌函数没有相关联的返回变量。

在多语句表值函数中，@return_variable 是 TABLE 变量，用于存储和累积应作为函数值返回的行。

function_body

指定一系列 Transact-SQL 语句定义函数的值，这些语句合在一起不会产生副作用。function_body 只用于标量函数和多语句表值函数。

在标量函数中，function_body 是一系列合起来求得标量值的 Transact-SQL 语句。

详见sql的联机帮助！！！

 

 

一下介绍一个函数使用例子：

此例子的功能是判断输入字符串中是否含有数字和逗号之外的字符，有就返回0，没有就返回1：

CREATE  FUNCTION PanDuan  (@PanduanStr as varchar(200)) 

RETURNS int  AS 

BEGIN

declare @ComPare varchar(100)

declare @index int

set @index=1

set @ComPare='1,2,3,4,5,6,7,8,9,0'

while(@index<=len(@PanduanStr))

BEGIN

if(CHARINDEX(substring(@PanduanStr,@index,1),@ComPare,1)=0)

begin

return 0

end

set @index=@index+1

END

return 1

END

上述的函数对字符串做了一个遍历，使用substring和循环获取字符串中的单个字符，使用charindex判断字符串是否符合要求。

 

函数的调用：

函数的调用方法规范为：数据库名称.dbo.函数名称（参数）

注意同一数据库中的数据库名称可以省略，但是dbo不可省略。不加dbo，系统会认为函数是系统函数而非用户自定义函数。

•3、             定义作业

作业也是sql的又一重要的功能,使用者可以将周期执行的过程让作业来完成，定义作业的方法为如下：

 

上图就是作业定义的位置，在上面右键就可以新建作业，出现新建作业对话框，然后按照要求一步一步的就可以完成作业的定义：

 

步骤的定义：

步骤就是这个作业的具体内容，可以定义多个步骤，步骤中的内容就是sql语句，其可以exec存储过程等等。

 

 

调度的设置：调度中可以设置作业的执行周期等。一般可以设置的最短时间间隔是一分钟，但是我们可以定义多个调度来彼此之间设定一定得执行间隔来完成更短时间的间隔。比如你想每半分钟执行一次作业，那麽你就可以定义两个调度，二者之间执行的时间间隔设成30秒，这个时候就可以构成30秒执行一次的作业。

 

 

•4、             触发器编写

触发器在简化前台程序的时候显得非常有用。常常用来处理很多插入删除更新的操作。

触发器，顾名思义就是触发的枢纽，比如插入，删除，更新记录的时候就会使这个枢纽启动起来，这个枢纽也是很多sql语句。

在插入触发器中常使用的一个inserted，其意思就是当前插入的这行记录。

注意：触发器有三种类型：

• AFTER 触发器在触发操作（INSERT、UPDATE 或 DELETE）后和处理完任何约束后激发。可通过指定 AFTER 或 FOR 关键字来请求 AFTER 触发器。因为 FOR 关键字与 AFTER 的效果相同，所以具有 FOR 关键字的触发器也归类为 AFTER 触发器。
• INSTEAD OF 触发器代替触发动作进行激发，并在处理约束之前激发。

对于每个触发操作（UPDATE、DELETE 和 INSERT），每个表或视图只能有一个 INSTEAD OF 触发器。而一个表对于每个触发操作可以有多个 AFTER 触发器。

示例

A. 用 INSTEAD OF 触发器代替标准触发动作

CREATE TRIGGER TableAInsertTrig ON TableA

INSTEAD OF INSERT

AS ...

B. 用 AFTER 触发器增加标准触发动作

CREATE TRIGGER TableBDeleteTrig ON TableB

AFTER DELETE

AS ...

C. 用 FOR 触发器增加标准触发动作

-- This statement uses the FOR keyword to generate an AFTER trigger.

CREATE TRIGGER TableCUpdateTrig ON TableC

FOR UPDATE

AS ...

 

for触发器是在操作的动作完成后才触发的，比如：插入触发器，其执行是在插入进去了一条记录才执行，而非插入这一动作触发，等触发器完成后在插入记录，也就是说，执行触发器内容是所属表已经做了更新，这一点很重要。

一个触发器的例子，很长，没做简化，以显示触发器的重要性，其中也有很多有用的sql语句以备以后使用。

CREATE TRIGGER [triAnswers] ON [dbo].[tbl_Answers]

FOR INSERT

AS

if ((select OrgAddr from inserted) not  in (select memberMobile from tbl_Member))

BEGIN

insert into tbl_SMSendTask (CreatorID,ServiceID,OperationType,sm_Content,OrgAddr,DestAddr,SendType,TaskStatus,FeeType,FeeCode,MsgID)

select '0000',ServiceID,'WAS','对不起，没有你的信息，无法进行投票',DestAddr,OrgAddr,'4','0','01','0','0'from inserted

update tbl_Answers set Useable=0 where id=(select id from inserted)

END

else

if((select OrgAddr from inserted) not in (

select memberMobile from tbl_GroupMemberRelation where groupid=(select

groupid from tbl_title where id=(select titleid from tbl_option where id=(select id from

tbl_middle where QID=(select qid from inserted))))))

BEGIN

insert into tbl_SMSendTask  (CreatorID,ServiceID,OperationType,sm_Content,OrgAddr,DestAddr,SendType,TaskStatus,FeeType,FeeCode,MsgID)

select '0000',ServiceID,'WAS','对不起，你不是该小区的业主，无权进行投票',DestAddr,OrgAddr,'4','0','01','0','0' from inserted

update tbl_Answers set Useable=0 where id=(select id from inserted)

END

else

if(EXISTS

    (

             select qid from inserted,(

         select [id],enddate from tbl_Title

         ) b where b.[id]=(select titleid from tbl_option where id=(select id from tbl_middle where QID=(select qid from inserted))) and receivedate>enddate

    )

 )

BEGIN

insert into tbl_SMSendTask  (CreatorID,ServiceID,OperationType,sm_Content,OrgAddr,DestAddr,SendType,TaskStatus,FeeType,FeeCode,MsgID)

select '0000',ServiceID,'WAS', '对不起，该投票已结束',DestAddr,OrgAddr,'4','0','01','0','0'from inserted

update tbl_Answers set Useable=0 where id=(select id from inserted)

END

else

if(((select count(QID) from tbl_Answers where OrgAddr=(select OrgAddr from inserted) and qid=(select qid from inserted) and useable=1)>1) )

BEGIN

insert into tbl_SMSendTask  (CreatorID,ServiceID,OperationType,sm_Content,OrgAddr,DestAddr,SendType,TaskStatus,FeeType,FeeCode,MsgID)

select '0000',ServiceID,'WAS', '对不起，该项议题您已经投过票了，请不要重复投票',inserted.DestAddr,inserted.OrgAddr,'4','0','01','0','0' from inserted

update tbl_Answers set Useable=0 where id=(select id from inserted)

END

else

if(EXISTS

(

 select Option_Type from tbl_option

 where id=(select id from tbl_middle where QID=(select qid from inserted)) and Option_Type<9

) and dbo.PanDuan((select Anwsers from inserted))=0

)

BEGIN

insert into tbl_SMSendTask  (CreatorID,ServiceID,OperationType,sm_Content,OrgAddr,DestAddr,SendType,TaskStatus,FeeType,FeeCode,MsgID)

select '0000',ServiceID,'WAS', '对不起，投票失败，单选或多选请不要出现逗号和数字之外的字符！',inserted.DestAddr,inserted.OrgAddr,'4','0','01','0','0' from inserted

update tbl_Answers set Useable=0 where id=(select id from inserted)

END

else

BEGIN

Insert into tbl_SMSendTask  (CreatorID,ServiceID,OperationType,sm_Content,OrgAddr,DestAddr,SendType,TaskStatus,FeeType,FeeCode,MsgID)

select '0000',ServiceID,'WAS', '恭喜您，投票成功，谢谢参与！',inserted.DestAddr,inserted.OrgAddr,'4','0','01','0','0' from inserted

END