T-SQL 学习随笔2

浏览数：21 / 时间：2015年06月12日

A.存储过程

1.创建存储过程(PROCEDURE)/*我的理解存储过程就是一个函数，存储过程创建必须在查询的第一行一开始声明的相当于输入的参数*/

CREATE PROCEDURE T_PROCEDURE
(@Sno VARCHAR(10)
)
AS
BEGIN
    SELECT * 
    FROM Student
    WHERE @Sno = Sno
END;

2.存储过程执行

EXECUTE T_PROCEDURE ‘1307020‘;/*执行*/

3.存储过程删除

DROP PROCEDURE T_PROCEDURE;

T-SQL 错误处理机制

BEGIN TRANSACTION
BEGIN TRY
    INSERT INTO Student VALUES(‘13070201‘,‘xiaohuang‘);
    INSERT INTO Student VALUES(‘13070202‘,‘xiaolv‘);
    COMMIT;
END TRY
BEGIN CATCH
    PRINT ERROR_NUMBER() + ERROR_SEVERITY()+ERROR_STATE()+ERROR_PROCEDURE()+ERROR_LINE()+ERROR_MESSAGE();/*打印错误信息*/
    ROLLBACK;
END CATCH

C 游标/**/

1.声明游标可以遍历所有的列名

declare @colName varchar(1000)
set @colName = ‘‘
select @colName = @colName + COLUMN_NAME + ‘ ‘
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME = ‘Student‘
print subString(@colName,1,len(@colName)-1);

2.把表的内容输出

DECLARE CUSTCURSOR CURSOR  /*创建游标*/
FOR SELECT Sno,Sname
FROM Student;
DECLARE @SNO VARCHAR(10),
        @SNAME VARCHAR(10)
OPEN CUSTCURSOR /*打开游标*/
FETCH NEXT FROM CUSTCURSOR /*每行遍历*/
    INTO @SNO,@SNAME
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @SNO + ‘ ‘ + @SNAME;
FETCH NEXT FROM CUSTCURSOR
    INTO @SNO,@SNAME
END
CLOSE CUSTCURSOR; /*关闭游标*/

3.删除游标

DEALLOCATE CUSTCURSOR;

D 触发器 /*触发器是特殊的存储过程，不过它是自发的执行*/

触发器分为AFTER,和INSTEAD OF 触发器

AFTER 触发器是在事件发生之后触发

INSTEAD OF 触发器是代替你要做的操作，所以没执行你的操作，你要继续进行操作的话需要做相应处理

数据更新(UPDATE) 、数据插入(INSERT)会产生INSERTED的表

数据删除(DELETE)会产生一个DELETED的表，可以去里面去找信息

但是那是一张表，单纯的写DELETED.(列名) 不能指定某一个对象需要用游标来访问信息

/*创建插入信息自动在姓名前加xiao的触发器*/
CREATE TRIGGER T_TRIGGER 
ON Student
AFTER INSERT
AS
DECLARE @SNO VARCHAR(10)
SELECT @SNO = Sno FROM INSERTED
UPDATE Student
SET Sname = ‘xiao‘ + Sname
WHERE @SNO = Student.Sno;
/*
--这个是错误的版本
CREATE TRIGGER T_TTRIGGER
ON Student
AFTER INSERT
AS
UPDATE Student
SET Sname = ‘xiao‘ + Sname
WHERE INSERTED.Sno = Student.Sno;
--INSERTED是一张表，无法绑定多个对象
*/

CREATE TRIGGER TT_TRIGGER /*修改信息，如果修改后的学生姓名不是xiao开头的，则不允许更新*/
ON Student
INSTEAD OF UPDATE
AS
DECLARE @SNO VARCHAR(10),
        @SNAME VARCHAR(10)
SELECT @SNO = Sno,@SNAME = Sname
FROM INSERTED
IF(@sNAME NOT LIKE ‘xiao%‘)
    PRINT ‘名字不符合规范‘;
ELSE
    UPDATE Student /*因为INSTEAD OF 是代替 所以 进行相应的手动更新*/
    SET Sname = @SNAME
    WHERE Sno = @SNO;

D 用户

1.创建用户

CREATE LOGIN STU WITH PASSWORD = ‘123456‘ /*创建登录账户*/
CREATE USER STU;/*创建用户*/
/*登陆账户是你能登录上SQL Management而使用需要相应的用户而用户需要相应的权限才能执行相应操作*/

2.给予权限

GRANT SELECT ON Student
TO STU;/*把查询权限给用户STU*/

3.删除权限

/*把权限移除*/
REVOKE SELECT
ON Student
FROM STU;

4.角色

角色是权限的集合

角色创建

CREATE ROLE STU1;

角色创建之后可以拿权限给角色

然后可以将角色的权限给用户，收回角色的权限，用户的权限也就相应的收回了

这样比较好管理

/*补充查询外键*/
SELECT NAME
FROM SYS.foreign_key_columns F JOIN SYS.objects O ON
F.constraint_object_id = O.object_id
WHERE F.parent_object_id = object_id(‘Student‘);