数据库基础（六）：T-SQL语言程序设计、游标定义与使用及存储过程创建与信息查找

DECLARE @SUM SMALLINT, @I SMALLINT,@NUMS SMALLINT
SET @SUM=0
SET @I=1
SET @NUMS=0
WHILE (@I<=100)
BEGIN
IF (@I% 3=0)
BEGIN 
SET @SUM=@SUM+@I
SET @NUMS=@NUMS+1
END
SET @I=@I+1
END
PRINT'总和是'+STR(@SUM)
PRINT'个数是'+STR(@NUMS)

USE jxsk
GO
SELECT SNO AS 学号, SN AS 姓名,
性别=
CASE SEX
WHEN '男' THEN 'M'
WHEN '女' THEN 'F'
END
FROM S
GO

USE jxsk
GO
SELECT SN AS 姓名, CN AS 课程名,
成绩=
CASE
WHEN SCORE IS NULL THEN '未考'
WHEN SCORE<60 THEN '不及格'
WHEN SCORE>=60 AND SCORE<70 THEN '及格'
WHEN SCORE>=70 AND SCORE<80 THEN '中'
WHEN SCORE>=80 AND SCORE<90 THEN '良好'
WHEN SCORE>=90 THEN '优'
END 
FROM SC,S,C
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO
ORDER BY S.SNO,C.CNO,SCORE DESC
GO

USE jxsk
UPDATE T SET SAL=SAL+
CASE
WHEN T.TNO IN (
SELECT TC.TNO FROM T,TC
WHERE T.TNO=TC.TNO AND (SAL+COMM)>=4000
GROUP BY TC.TNO HAVING COUNT(*)>=2) THEN 300
WHEN T.TNO IN (
SELECT TC.TNO FROM T,TC
WHERE T.TNO=TC.TNO AND (SAL+COMM)>=3000 AND (SAL+COMM)<4000
GROUP BY TC.TNO HAVING COUNT(*)>=2) THEN 200
WHEN T.TNO IN (
SELECT TC.TNO FROM T,TC
WHERE T.TNO=TC.TNO AND (T.SAL+T.COMM<3000)
GROUP BY TC.TNO HAVING COUNT(*)>=2) THEN 100
WHEN T.TNO IN (
SELECT TC.TNO FROM T,TC
WHERE T.TNO=TC.TNO
GROUP BY TC.TNO HAVING COUNT(*)=1) THEN 50
ELSE 0
END
GO

DECLARE @SNO CHAR(6),@SNAME CHAR(8),@SEX CHAR(2),
@AGE TINYINT,@DEPT CHAR(10)
DECLARE CURSOR_FAMALE CURSOR
FOR SELECT SNO,SN,SEX,AGE,DEPT FROM S
OPEN CURSOR_FAMALE
FETCH NEXT FROM CURSOR_FAMALE
INTO @SNO,@SNAME,@SEX,@AGE,@DEPT
WHILE @@FETCH_STATUS=0
BEGIN
IF @SNAME='牛莉'
BEGIN
PRINT '找到牛莉的信息如下'
PRINT @SNO+''+@SNAME+''+@SEX+''+
CONVERT (CHAR(2),@AGE)+''+@DEPT
BREAK
END
FETCH NEXT FROM CURSOR_FAMALE
INTO @SNO,@SNAME,@SEX,@AGE,@DEPT
END
IF @@FETCH_STATUS !=0
PRINT '很抱歉，没有找到牛莉的信息！'
CLOSE CURSOR_FAMALE
DEALLOCATE CURSOR_FAMALE

CREATE PROCEDURE PRO_C @C_CURSOR CURSOR VARYING OUTPUT
AS
SET @C_CURSOR = CURSOR
FOR
SELECT SNAME,SCORE FROM STUDENT,SC,COURSE
WHERE STUDENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO AND COURSE.CNAME='数据库'
OPEN @C_CURSOR

DECLARE @MYCURSOR CURSOR
DECLARE @NAME VARCHAR(30)
DECLARE @IN_NAME CHAR(8)
DECLARE @SCORE INT
SELECT @IN_NAME = '王一山'
EXECUTE PRO_C @C_CURSOR = @MYCURSOR OUTPUT
FETCH NEXT FROM @MYCURSOR INTO @NAME,@SCORE
WHILE (@@FETCH_STATUS=0)
BEGIN
IF @NAME=@IN_NAME
BEGIN
PRINT @NAME+'选修了数据库课程，成绩是：'+CONVERT(CHAR(2), @SCORE)
BREAK
END
FETCH NEXT FROM @MYCURSOR INTO @NAME, @SCORE
END
IF (@@FETCH_STATUS!=0)
PRINT @IN_NAME+'没有选修数据库课程。'
CLOSE @MYCURSOR
DEALLOCATE @MYCURSOR

CREATE PROCEDURE PRO_C @C_CURSOR CURSOR VARYING OUTPUT
AS
SET @C_CURSOR = CURSOR
FOR
SELECT SN,SCORE FROM S,SC,C
WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND C.CN='数据库'
OPEN @C_CURSOR