SQL第六章

/*----------------------------系  统  存  储  过  程-------------------------------*/
    EXEC sp_databases        --列出当前系统中的数据库
    EXEC sp_renamedb 'student','students'    --修改数据库的名称(单用户访问)
    USE students    --修改当前数据库
    GO
    EXEC sp_tables        --当前数据库中查询的对象的列表
    EXEC sp_columns stuInfo        --返回某个表的列的信息
    EXEC sp_help stuInfo        --查看表stuInfo的信息
    EXEC sp_helpconstraint stuInfo        --查看表stuInfo的约束
    EXEC sp_helpindex stuMarks            --查看表stuMarks的索引
    EXEC sp_helptext 'view_stuInfo_stuMarks'    --查看视图的语句文本
    EXEC sp_stored_procedures        --查看当前数据库中的存储过程

    --解决错误(2008数据库cmd不能执行时候解决方案)
    EXEC sp_configure 'show advanced options', 1;RECONFIGURE;
    EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;

    EXEC xp_cmdshell 'mkdir d:\Back',NO_OUTPUT        --创建文件夹
    EXEC xp_cmdshell 'dir d:\Back'                    --显示文件夹信息
    EXEC xp_cmdshell 'del d:\Back'                --删除一个或数个文件
    

/*----------------------------自  定  义  存  储  过  程-------------------------------*/
/*--基本语法--*/
    --GO
    --CREATE  PROC[EDURE]  存储过程名
    --    [@参数1  数据类型 [= 默认值 | OUTPUT],
    --    @参数n  数据类型 [= 默认值 | OUTPUT],...]
    --AS
    --    T-SQL语句
    --GO    
    
/*--不带参数的存储过程--*/
    --获取所有的学生成绩信息
    
    --视图引入
        ----如果存在该视图，先将其删除掉
        --IF EXISTS (    SELECT * FROM sys.views WHERE NAME = 'view_stuInfo_stuMarks')
        --    DROP VIEW view_stuInfo_stuMarks
        --GO
        ----创建名为view_stuInfo_stuMarks的视图
        --CREATE VIEW view_stuInfo_stuMarks
        --AS
        --    SELECT stuInfo.StuID,stuName,subject,score FROM stuInfo LEFT JOIN stuMarks
        --    ON stuInfo.StuID = stuMarks.StuID
        --GO
        --select * from view_stuInfo_stuMarks;
        
    IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_GetAllStuMark')
        DROP PROC Proc_GetAllStuMark
    GO
        
    CREATE PROC Proc_GetAllStuMark
    AS
        SELECT a.stuid,a.StuName, b.Subject, b.Score
        FROM StuInfo a, StuMarks b
        WHERE a.StuID=b.StuID
    GO
    
    --执行存储过程
    EXEC Proc_GetAllStuMark
    EXECUTE Proc_GetAllStuMark    
    
/*--创建带有参数的存储过程--*/
    --根据学生姓名,获得学生成绩
    IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_GetStuMarkByStuName')
        DROP PROC Proc_GetStuMarkByStuName
    GO
    
    CREATE PROC Proc_GetStuMarkByStuName @stuname VARCHAR(20)
    AS
        SELECT a.StuName, b.Subject, b.Score
        FROM StuInfo a, StuMarks b
        WHERE a.StuID=b.StuID
        AND a.StuName=@stuname
    GO

    EXEC Proc_GetStuMarkByStuName '李四'
    
/*--创建参数有默认值的存储过程--*/
    --添加学生信息表的数据
    IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_InsertStuInfo')
        DROP PROC  Proc_InsertStuInfo
    GO
    
    CREATE PROC Proc_InsertStuInfo @stuname varchar(20),@stusex char(2) = '男'
    AS
        INSERT INTO StuInfo (StuName, StuSex)
        VALUES (@stuname, @stusex)
    GO
    
    --调用参数有默认值的存储过程
    EXEC Proc_InsertStuInfo '唐僧'
    EXEC Proc_InsertStuInfo '白骨精','女'
    
    
    --如果传入姓名，则查询执行学生；否则查询全部
    IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_SelectByName')
        DROP PROC  Proc_SelectByName
    GO
    
    CREATE PROC Proc_SelectByName (@name varchar(20)=null)
    AS
        --if @name is null
        --begin
        --    select StuInfo.stuid,stuName,subject,score
        --    from stuinfo,stumarks
        --    where stuinfo.stuid=stumarks.stuid
        --end
        --else
        --begin
        --    select StuInfo.stuid,stuName,subject,score
        --    from stuinfo,stumarks
        --    where stuinfo.stuid=stumarks.stuid and stuName=@name
        --end
        
        declare @sql varchar(max)
        set @sql=' select StuInfo.stuid,stuName,subject,score
                    from stuinfo,stumarks
                    where stuinfo.stuid=stumarks.stuid '
        if @name is not null
            set @sql=@sql + ' and stuName='''+@name +''''
            
        exec (@sql)
    GO
    
    EXEC Proc_SelectByName
    EXEC Proc_SelectByName '张三'
    
/*--带输出类型参数的存储过程--*/
    --根据学生姓名查找学员SQL分数
    IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_GetCMarkByStuName')
        DROP PROC Proc_GetCMarkByStuName
    GO
    
    CREATE PROC Proc_GetCMarkByStuName @stuname VARCHAR(20),@cmark int OUTPUT
    AS
        SELECT @cmark = b.Score FROM StuInfo a, StuMarks b
        WHERE a.StuID = b.StuID
        AND b.Subject = 'SQL' AND a.StuName = @stuname
    GO
    
    --调用该存储过程
    DECLARE @cmark INT
    EXEC Proc_GetCMarkByStuName '李四', @cmark OUTPUT
    PRINT '李四的SQL分数为:' + CONVERT(VARCHAR, @cmark)

    
/*----------------------------存  储  过  程  异  常-------------------------------*/
/*--使用RAISERROR语句抛出错误信息--*/
    --RAISERROR (错误消息, 严重级别, 状态)
    --自定义错误
    IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_Devide')
        DROP PROC Proc_Devide
    GO
    
    CREATE PROC Proc_Devide @a int, @b int
    AS
        DECLARE @c INT
        IF (@b = 0)
            BEGIN
                RAISERROR ('以零作除数错误', 15, 2)
                RETURN
            END
        SET @c = @a / @b
    GO

    EXEC Proc_Devide 10, 0
    print '错误编号' + CONVERT(VARCHAR, @@ERROR)