sql个人总结4

说到sql，无疑存储进程很主要，这篇就和大家讨论贮存进程。

1：贮存进程

a：不带参数的贮存进程

USE test
GO
CREATE PROCEDURE WLY    --贮存进程，不带参数
AS
SELECT * FROM role
GO

USE test
GO
CREATE PROCEDURE WLY1 
@A INT
AS 
DECLARE @B INT
SET @B=1
SET @A=@B
PRINT @A
GO

USE test
GO
CREATE PROCEDURE WLY2 
@A INT OUTPUT    --输出参数值的参数
AS 
DECLARE @B INT
SET @B=1
select @A=@B
PRINT @A
GO

SP_HELP WLY1    --返回贮存进程的名称，时光，参数等
SP_HELPTEXT WLY1 --返回贮存进程的创立语句
SP_RENAME WLY,WLY2  --重命名贮存进程
GO

execute dbo.WLY
EXECUTE WLY1 1 --有参数的贮存进程
DECLARE @C INT    --带参数的输出
EXECUTE WLY2 @C OUTPUT
GO

DECLARE C_ROLE CURSOR SCROLL
FOR SELECT RID,RNAME FROM ROLE
FOR READ ONLY
DECLARE @COUNT INT,@VRID INT,@VRNAME VARCHAR(1)
OPEN C_ROLE
FETCH NEXT FROM C_ROLE
INTO @VRID,@VRNAME
SET @COUNT=0
WHILE @@FETCH_STATUS=0
BEGIN 
    PRINT @VRID
    PRINT @VRNAME
    PRINT @COUNT
    SET @COUNT=@COUNT+1
    FETCH NEXT FROM C_ROLE INTO @VRID,@VRNAME
END
CLOSE C_ROLE
DEALLOCATE C_ROLE
GO