sql server存储过程的使用-阿里云开发者社区

sql server存储过程的使用
2016-05-09 1182
简介：
+关注继续查看
--SET NOCOUNT ON  
 
--sql server存储过程被创建之后，它的名字就存储在系统表sysobjects中；
 
--存储过程从创建到执行要经历语法分析、代码解析、编译和执行四个阶段；
--编译阶段是指分析存储过程和生成存储过程执行计划的过程
 
--查询教师的工资是否低于1200元，以便决定是否加薪

IF EXISTS (SELECT * FROM Teacher WHERE SAL < 1200)

    BEGIN

            SELECT NO,NAME,SAL FROM Teacher WHERE SAL <1200

            SELECT '以上教师工资<1200，需考虑加薪' 提示

    END
ELSE

    PRINT '所有教师工资在计以上，暂时不考虑加薪'

--判断数据库是否存在

IF DB_ID(N'Mytest')IS NOT NULL

    BEGIN

        PRINT '数据库存在' 

        --判断数据表是否存在   

        IF OBJECT_ID('Mytest..Teacher', 'U')IS NOT NULL

            PRINT '数据表存在'

        ELSE

            PRINT '数据表不存在'

    END
ELSE

    PRINT '数据库不存在'

--while循环语句的用法

DECLARE @I INT, @SUM INT

SELECT @I=1, @SUM=0
WHILE @I < 10

    SELECT @SUM = @SUM + @I, @I = @I + 1

PRINT @SUM
 
--创建无参数的存储过程
USE Mytest
GO

CREATE PROC TEST1

WITH RECOMPILE     --在每次执行时对存储过程进行冲编译处理

AS      --用于指定该存储过程要执行的操作

SELECT * FROM Teacher
 
--删除存储过程

DROP PROC TEST1
--执行存储过程

EXEC TEST1
--查看存储过程源代码（小写）

EXEC sp_helptext TEST1        
--查看存储过程的参数及其数据类型

EXEC sp_help TEST4
 
--创建带输入参数的存储过程
USE Mytest
GO

CREATE PROC TEST2

    @TNAME VARCHAR(12)

    --@TNAME VARCHAR(12) = '李'

    AS

        IF(@TNAME IS NULL)

            BEGIN

                RAISERROR('NULL VALUE ARE INVALID',5,5)

                RETURN      --立刻退出该过程,只能返回整数,默认返回值为0

            END

        ELSE

            BEGIN

                SELECT * FROM Teacher WHERE Tname = @TNAME

                RETURN

            END

DROP PROC TEST2

DECLARE @RETURN_VALUE INT

EXEC @RETURN_VALUE=TEST2 '李'

SELECT @RETURN_VALUE
/*return返回
0：过程成功执行
1：对象丢失
2：发生数据类型错误
3：处理过程被死锁
4：发生权限错误
5：发生语法错误
6：发生恶意用户错误
7：发生资源错误
*/
 
--创建带输出参数的存储过程
USE Mytest
GO

CREATE PROC TEST3

    @TNAME VARCHAR(12) OUTPUT
AS
BEGIN

    SELECT @TNAME = Tname FROM Teacher WHERE T# = 50050
END
 
DROP PROC TEST3

DECLARE @NAME VARCHAR(12)

EXEC TEST3 @NAME OUTPUT

SELECT @NAME
 
--创建带入参和出参的存储过程
USE Mytest
GO

CREATE PROC TEST4

    @TNAME VARCHAR(12),

    @NAME VARCHAR(12) OUTPUT
AS
BEGIN

    UPDATE Teacher SET Tname = @TNAME WHERE T# = 50050

    SELECT @NAME = Tname FROM Teacher WHERE Tname = @TNAME
END
 
DROP PROC TEST4

DECLARE @tNAME VARCHAR(12)

EXEC TEST4 '李',@tNAME OUTPUT

SELECT @tNAME
 
--建立创建sql server数据表的存储过程

CREATE PROC SP_CREATE_TABLE

    @TableName SYSNAME
AS

    EXEC('CREATE TABLE '+@TableName+' (column1 varchar(100), column2 varchar(100))');

    --EXEC('DROP TABLE '+@TableName);

    --EXEC('ALTER TABLE '+@TableName+ ...)
GO
 
DROP PROC SP_CREATE_TABLE

EXEC SP_CREATE_TABLE testT

DROP TABLE testT
 
--建立重命名sql server数据表的存储过程

CREATE PROC SP_RENAME_TABLE

    @OLD_TABLE_NAME SYSNAME,

    @NEW_TABLE_NAME SYSNAME
AS

    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES 

              WHERE TABLE_SCHEMA = 'dbo'

              AND TABLE_TYPE = 'BASE TABLE'

              AND TABLE_NAME = @OLD_TABLE_NAME)

    BEGIN

        EXEC sp_rename @OLD_TABLE_NAME, @NEW_TABLE_NAME

        --EXEC sp_renamedb @OldDataBaseName, @NewDataBaseName        --重命名数据库

    END

DROP PROC SP_RENAME_TABLE

EXEC SP_RENAME_TABLE 'testT','testTable'