MSSQL扫盲系列(6)-异常，事物，函数，存储过程
异常
View Code
BEGIN TRY --SQL异常和VB很像，需要很完整的开闭
DECLARE @N INT--这里做一个最简单的异常，除0异常
SET @N=0
SET @N=7/@N
PRINT @N --这个不会输出
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS MSG,@@ERROR CODE
END CATCH
--现在做个很实际的异常处理
--人员表，包含人名和出生日期，
--因为基本上不会有100多岁的用户来使用这个系统
--所以在注册的时候，要限制出厂日期的填写，加一个约束
CREATE TABLE # (NAME NVARCHAR(6) UNIQUE NOT NULL,
BIRTHDAY DATETIME CHECK(BIRTHDAY>'1900-1-1' AND BIRTHDAY<GETDATE()))
BEGIN TRY
--插入数据的时候很可能会出错
INSERT INTO # VALUES ('丹尼斯·里奇','1941-9-9')
--这条可以插进去
INSERT INTO # VALUES ('戴尔·卡耐基','1888-12-24')
--这个不行
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS MSG,@@ERROR CODE
END CATCH
SELECT * FROM #
DROP TABLE #
事物(更多，CLICK HERE)
View Code
--还是上面的表
CREATE TABLE # (NAME NVARCHAR(6) UNIQUE NOT NULL,
BIRTHDAY DATETIME CHECK(BIRTHDAY>'1900-1-1' AND BIRTHDAY<GETDATE()))
BEGIN TRANSACTION
INSERT INTO # VALUES ('丹尼斯·里奇','1941-9-9')
INSERT INTO # VALUES ('戴尔·卡耐基','1888-12-24')
IF @@ERROR>0
ROLLBACK
ELSE
COMMIT
BEGIN TRAN
BEGIN TRY
INSERT INTO # VALUES ('丹尼斯·里奇','1941-9-9')
INSERT INTO # VALUES ('戴尔·卡耐基','1888-12-24')
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
SELECT * FROM #
DROP TABLE #
函数（以斐波那契函数为例）
CREATE(创建)
View Code
---求斐波那契数
--递归法
--注意啊，存储过程、函数、触发器或视图的最大嵌套层数为32
--此法非常慢
CREATE FUNCTION FAB
(
@N INT
) RETURNS INT AS--此处是RETURNS
BEGIN
IF @N<0
RETURN 0
ELSE IF @N=1 OR @N=0
RETURN 1
RETURN [DBO].FAB(@N-2)+[DBO].FAB(@N-1)
END
--非递归
CREATE FUNCTION FASTFAB
(
@N INT
) RETURNS INT AS
BEGIN
IF @N<0
RETURN 0
ELSE IF @N=1 OR @N=0
RETURN 1
DECLARE @CN INT,@L INT,@P INT,@R INT
SET @CN=1
SET @L=1
SET @P=1
SET @R=0
WHILE @CN<@N
BEGIN
SET @R=@P+@L
SET @P=@L
SET @L=@R
SET @CN=@CN+1
END
RETURN @R
END
ALTER(修改)
View Code
ALTER FUNCTION [dbo].[FASTFAB]
(
@N INT
) RETURNS INT AS
BEGIN
IF @N<0
RETURN 0
--把这两句删掉
--ELSE IF @N=1 OR @N=0
-- RETURN 1
DECLARE @CN INT,@L INT,@P INT,@R INT
SET @CN=1
SET @L=1
SET @P=1
SET @R=0
WHILE @CN<@N
BEGIN
SET @R=@P+@L
SET @P=@L
SET @L=@R
SET @CN=@CN+1
END
RETURN @R
END
DROP(删除)
View Code
--删除
DROP FUNCTION [DBO].FAB
存储过程（以一个实际案例为准）
具体的业务规则
--一个CMS系统中，有一个文章分享功能
--为了节省资源，文章表和用户分享表分开存放
--要求在用户删除文章时，检测是否还有其他用户分享此文章
--如果有则只删除用户分享表中的记录，否则删除分享记录和文章
--要求传入删除分享主键值，就完成这些任务
先放业务表并插入部分数据，中间插入文章的SQL有点长（都是非常经典的东西），具体SQL如下：
View Code
--文章表(精简后)
CREATE TABLE ARTICLE ( ID INT IDENTITY(1,1) PRIMARY KEY,--文章主键
TITLE NVARCHAR(100) NOT NULL,
CONTENT NVARCHAR(MAX) CHECK(LEN(CONTENT)>1))
--分享表(精简后)
CREATE TABLE SHARE ( ID INT IDENTITY(1,1) PRIMARY KEY,
ARTICLEID INT REFERENCES ARTICLE(ID) NOT NULL,
NAME NVARCHAR(5) NOT NULL)
INSERT INTO ARTICLE VALUES(
'宣州谢朓楼饯别校书叔云',
N'弃我去者，昨日之日不可留；
乱我心者，今日之日多烦忧。
长风万里送秋雁，对此可以酣高楼。
蓬莱文章建安骨，中间小谢又清发。
俱怀逸兴壮思飞，欲上青天览明月。
抽刀断水水更流， 举杯销愁愁更愁。
人生在世不称意，明朝散发弄扁舟。')
INSERT INTO ARTICLE VALUES(
'滕王阁序',
N'豫章故郡，洪都新府,星分翼轸，地接衡庐。
襟三江而带五湖，控蛮荆而引瓯越。
物华天宝，龙光射牛斗之墟；人杰地灵，徐孺下陈蕃之榻。
雄州雾列，俊采星驰。台隍枕夷夏之交，宾主尽东南之美。
都督阎公之雅望，棨戟遥临；宇文新州之懿范，襜帷暂驻。
十旬休假，胜友如云；千里逢迎，高朋满座。
腾蛟起凤，孟学士之词宗；紫电清霜，王将军之武库。
家君作宰，路出名区；童子何知，躬逢胜饯。 ')
INSERT INTO ARTICLE VALUES(
'Zion(Morpheus)',
N'Zion! Hear me!
It is true, what many of you have heard.
The machines have gathered an army,
and as I speak that army is drawing nearer to our home.
Believe me when I say we have a difficult time ahead of us.
But if we are to be prepared for it,
we must first shed our fear of it!
I stand here before you now, truthfully unafraid.
Why?
Because I believe something you do not?
No!
I stand here without fear because I remember.
I remember that I am here not because of the path that lies before me,
but because of the path that lies behind me!
I remember that for 100 years we have fought these machines.
I remember that for 100 years they have sent their armies to destroy us.
And after a century of war,
I remember that which matters most.
We are still here!
Tonight let us send a message to that army.
Tonight let us shake this cave!
Tonight let us tremble these halls of earth, steel, and stone!
Let us be heard from red core to black sky.
Tonight, let us make them remember.
This is Zion! And we are not afraid! ')
SELECT * FROM ARTICLE
--查询
INSERT INTO SHARE VALUES(1,'韩菱纱')
INSERT INTO SHARE VALUES(2,'韩菱纱')
INSERT INTO SHARE VALUES(3,'韩菱纱')
INSERT INTO SHARE VALUES(1,'云天河')
INSERT INTO SHARE VALUES(2,'云天河')
INSERT INTO SHARE VALUES(1,'柳梦璃')
INSERT INTO SHARE VALUES(3,'柳梦璃')
INSERT INTO SHARE VALUES(2,'慕容紫英')
INSERT INTO SHARE VALUES(3,'慕容紫英')
SELECT * FROM SHARE
--查询
SELECT S.NAME,A.TITLE,A.CONTENT FROM ARTICLE A,SHARE S WHERE S.ARTICLEID=A.ID
--查询
CREATE(创建)
View Code
CREATE PROCEDURE DelShareByID
@SHAREID INT =NULL
AS
BEGIN
SET NOCOUNT ON;
IF ISNULL(@SHAREID,0)>0--不为空才进行处理
BEGIN
DECLARE @ARTICLEID INT --分享ID所对应的文章ID
SELECT @ARTICLEID= S.ARTICLEID FROM SHARE S WHERE S.ID=@SHAREID--设置ID
DELETE FROM SHARE WHERE ID=@SHAREID--删掉自己的记录
IF ISNULL(@ARTICLEID,0)>0--不为空才进行处理
BEGIN
DECLARE @C INT--SHARP表中文章的引用数
SELECT @C=COUNT(S.ID) FROM SHARE S WHERE S.ARTICLEID=@ARTICLEID--获取引用数
IF ISNULL(@C,0)<1--如果没有引用
DELETE FROM ARTICLE WHERE ID=@ARTICLEID--删掉无引用文章
END
END
END
--测试
--试着删除掉最长的Zion
EXEC DelShareByID 3--菱纱的引用
EXEC DelShareByID 7--梦璃的引用
EXEC DelShareByID 9--紫英的引用
ALTER(修改)
View Code
ALTER PROCEDURE [dbo].[DelShareByID]
@SHAREID INT =NULL
AS
BEGIN
SET NOCOUNT ON;
IF ISNULL(@SHAREID,0)>0--不为空才进行处理
BEGIN
DECLARE @ARTICLEID INT --分享ID所对应的文章ID
SELECT @ARTICLEID= S.ARTICLEID FROM SHARE S WHERE S.ID=@SHAREID--设置ID
DELETE FROM SHARE WHERE ID=@SHAREID--删掉自己的记录
IF NOT EXISTS (SELECT TOP 1 1 FROM SHARE S WHERE S.ARTICLEID=@ARTICLEID)--查看是否存在引用
DELETE FROM ARTICLE WHERE ID=@ARTICLEID--删掉无引用文章
END
END
--试用
--试着删除次长的滕王阁序
EXEC DelShareByID 2 --菱纱的引用
EXEC DelShareByID 5 --天河的引用
EXEC DelShareByID 8 --紫英的引用
DROP(删除)
View Code
DROP PROC DelShareByID
