中文SQL_Server_2005数据库管理与开发源代码

o RETURN GradeEND例 6 USE basetestGOCREATE FUNCTION info_sdeptsdept nchar10RETURNS TABLEASRETURN SELECT snosnamessex FROM student WHERE sdeptsdeptUSE basetestGOSELECT FROM dbo.info_sdeptMA例 7 USE basetestGOCREATE FUNCTION higher_gradehighergrade FLOATRETURNS higher_grade TABLE sno char10sname char20grade FLOATASBEGIN INSERT higher_grade SELECT student.snosnamegrade FROM studentsc WHERE student.snosc.sno AND gradehighergrade RETURNENDGO例 8 USE basetestGODECLARE GRADE FLOATMESSAGE varchar200SET GRADE98IF EXISTSSELECT FROM sc WHERE gradeGRADE BEGIN SELECT DISTINCT student.snosnamegrade FROM studentsc WHERE student.snosc.sno AND gradeGRADE ENDELSE SET MESSAGE不存在选修课成绩高于 98 的学生 PRINT MESSAGE例 9 USE basetestGOSELECT cnocname课程说明 CASE cpni WHEN 4THEN 该课程有先行课数据结构 ELSE 该课程没有先行课数据结构 ENDFROM courseORDER BY cnocname例 10 DECLARE r inttimes intSET r1SET times1label1: SET rr times SET timestimes1 IF times 0 BEGIN SET i i 1 IF i 100 BEGIN SELECT 1 到 100 的和 sum BREAK END ELSE SET sum sum i END例 12 USE basetestBEGIN WAITFOR DELAY00:00:10 SELECT FROM courseEND例 13 USE basetestGODECLARE student_cursor CURSORFOR SELECT FROM studentWHERE ssex女ORDER BY snoFOR READ ONLY例 14 USE basetestGOOPEN student_cursorFETCH NEXT FROM student_cursorWHILE FETCH_STATUS 0BEGIN FETCH NEXT FROM student_cursorEND例 15 IF EXISTSSELECT name FROM sysobjects WHERE namestu_pro and typep DROP PROCEDURE stu_proGOCREATE PROCEDURE stu_pro AS SELECT FROM student WHERE ssex女GOEXECUTE stu_proGO例16 USE basetestGOIF EXISTS SELECT name FROM sysobjects WHERE namesum_pro AND typep DROP PROCEDURE sum_proGOCREATE PROC sum_proa intb intresult int OUTPUTASSET resultab 成功执行后就创建了 sum_pro 存储过程。

     然后在 SQL Server management studio query 窗口中输入下面命令：USE basetestGODECLARE a intb intresult intSET a789SET b123EXEC sum_pro abresult outputSELECT aabbab 和为result例17 USE basetestGOEXEC sp_helptext stu_pro例18 USE basetestGOEXEC sp_depends stu_pro例19 USE basetestGOEXEC sp_help stu_pro例20 USE basetestGOEXEC sp_rename stu_pro，查询女生信息过程第8章例 1 USE basetestIF EXISTS SELECT name FROM sysobjects WHERE name stu_tr AND type TR DROP TRIGGER TR1GOCREATE TRIGGER stu_trON studentWITH ENCRYPTIONFOR INSERTUPDATEASPRINT 新加入一个学生GO例 2 USE basetestGOEXEC sp_helptrigger student例3 USE basetestGOEXEC sp_helptext stu_tr例 4 USE basetestGOEXEC sp_help stu_tr例5 USE basetestIF EXISTS SELECT name FROM sysobjects WHERE name sc_tr AND type TR DROP TRIGGER sc_trGOCREATE TRIGGER sc_trON scFOR INSERTASIF EXISTS SELECT sno FROM inserted WHERE sno NOT IN SELECT sno FROM student WHERE sdeptIS BEGIN PRINT 该学生不是ＩＳ系的学生 ROLLBACK tranENDGOINSERT sc sno VALUES 05001例6 USE basetestGOIF EXISTS SELECT name FROM sysobjects WHERE name course_tr AND type TR DROP TRIGGER course_trGOCREATE TRIGGER course_trON courseFOR DELETEASIF EXISTS SELECT cno FROM deleted WHERE cno IN SELECT cno FROM sc BEGIN PRINT 课程有学生选修不能被删除 ROLLBACK tranENDGODELETE FROM course例7 USE basetestGOIF EXISTS SELECT name FROM sysobjects WHERE name sc1_tr AND type TR DROP TRIGGER sc1_trGOCREATE TRIGGER sc1_trON scFOR UPDATEASIF EXISTS SELECT cno FROM inserted WHERE cno NOT IN SELECT cno FROM course BEGIN PRINT 没有该课程的记录