《第10章存储过程与触发器例题源代码.doc》由会员分享，可在线阅读，更多相关《第10章存储过程与触发器例题源代码.doc（10页珍藏版）》请在金锄头文库上搜索。
1、第10章存储过程与触发器例题源代码第九章存储过程与触发器例题源代码:例9.1CREATE PROCEDURE ST_CHAXUN_01AS SELECT 姓名,性别,出生日期 FROM 学生 WHERE 系部代码=01例9.2USE studentGOCREATE PROCEDURE ST_PRO_BJ ASSELECT 班级名称FROM 班级,系部WHERE 系部.系部代码=班级.系部代码 and 系部.系部名称=计算机系GO例9.3USE StudentEXECUTE ST_PRO_BJGO例9.4USE studentGOEXEC SP_HELPTEXT ST_PRO_BJEXEC SP
2、_DEPENDS ST_PRO_BJEXEC SP_HELP ST_PRO_BJGO例9.5USE studentGOALTER PROC DBO.ST_PRO_BJ ASSELECT 班级名称FROM 班级,系部WHERE 系部.系部代码=班级.系部代码 and 系部.系部名称=经济管理系GO例9.6USE studentGODROP PROCEDURE ST_CHAXUN_01GO例9.7USE studentGOCREATE PROCEDURE XIBU_INFOR系部代码 CHAR (2)ASSELECT 系部名称, 系主任FROM 系部WHERE 系部代码=系部代码GO执行存储过程：
3、EXEC XIBU_INFOR 01返回结果如下：系部名称系主任 计算机徐才智 (所影响的行数为 1 行)例9.8EXEC sp_recompile ST_PRO_BJGO例9.9USE masterGOEXEC sp_addgroup user_group例9.10USE masterGOEXEC sp_addlogin user01例9.11EXEC sp_addtypeuser_date, datetime例9.12USE masterGOEXEC SP_monitorGO例9.13EXECsp_addextendedprocxp_userprint,xp_userprint.dll例9
4、.14EXEC xp_dirtreeC:winnt例9.15EXEC master .xp_cmdshell dir *.exeGO例9.16EXECxp_cmdshellnet share baktest=e:baktestGOMaster.xp_cmdshellnet usejkxbaktest123/use:domainsaGOBackup database student to disk=jkxbakteststudent.bakGO EXECxp_cmdshellnet share baktest/deleteGO-案例USE studentGO-如果存储过程TEACHER存在，将其
5、删除IF EXISTS(SELECT NAME FROM SYS.OBJECTS WHERE NAME=TEACHER AND TYPE=P)DROP PROCEDURE TEACHERGO-建立一个查询存储过程CREATE PROCEDURE TEACHER -查询选项WITH ENCRYPTIONASSELECT 姓名,职称FROM 教师,系部WHERE 系部.系部代码=教师.系部代码 and 系部.系部名称=计算机系GO-执行TEACHER EXEC TEACHERGOUSE studentGO-如果存储过程教师查询存在，将其删除IF EXISTS(SELECT NAME FROM SY
6、S.OBJECTS WHERE NAME=教师查询 AND TYPE=P)DROP PROCEDURE 教师查询GO-创建一个带参数的存储过程 教师查询CREATE PROCEDURE 教师查询XIBIE CHAR(8)-查询选项WITH ENCRYPTIONASSELECT 教师.姓名,教师.职称,教师.职务FROM 教师,系部WHERE 系部.系部代码=教师.系部代码 and 系部.系部名称=XIBIEORDER BY 教师.教师编号GO-执行存储过程，并向存储过程传递参数。 EXEC 教师查询 计算机系GOUSE StudentGO-如果存储过程单科成绩分析存在，将其删除IF EXIST
7、S(SELECT NAME FROM SYS.OBJECTS WHERE NAME=单科成绩分析 AND TYPE=P)DROP PROCEDURE 单科成绩分析GO-创建存储过程 单科成绩分析-定义一个输入参数KECHENGMING-定义三个输出参数 AVGCHENGJI,MAXCHENGJI和MINCHENGJI,用于接受平均成绩，最高成绩和最低成绩CREATE PROCEDURE 单科成绩分析KECHENGMING VARCHAR(20),AVGCHENGJI tinyint OUTPUT, MAXCHENGJI tinyint OUTPUT, MINCHENGJI tinyint OU
8、TPUTASSELECT AVGCHENGJI=AVG(成绩),MAXCHENGJI=MAX(成绩),MINCHENGJI=MIN(成绩)FROM 课程注册WHERE 课程号 in (SELECT 课程号 FROM 课程 WHERE 课程名=KECHENGMING)GOUSE STUDENT-声明四个变量，用于保存输入和输出参数DECLARE KECHENGMING VARCHAR(20)DECLARE AVGCHENGJI1 tinyintDECLARE MAXCHENGJI1 tinyintDECLARE MINCHENGJI1 tinyint-为输出参数赋值SELECT KECHENGM
9、ING=计算机基础-执行存储过程EXEC 单科成绩分析 KECHENGMING,AVGCHENGJI1 OUTPUT, MAXCHENGJI1 OUTPUT, MINCHENGJI1 OUTPUT -显示结果SELECT KECHENGMING AS 课程名, AVGCHENGJI1 AS 平均成绩, MAXCHENGJI1 AS 最高成绩, MINCHENGJI1 AS 最低成绩GO例9.17CREATE TRIGGER xiaoshou ON dbo.产品 FOR INSERT ASDECLARE AA CHAR(4)SELECT AA=产品编号 FROM 产品 INSERT INTO 产
10、品销售VALUES (AA,0)例9.18USE StudentGOCREATE TRIGGER del_xiaoshouON dbo.产品FOR DELETEAS DELETE 产品销售 WHERE 产品编号 IN (SELECT 产品编号 FROM DELETED) GO例9.19USE studentGOEXEC SP_helptrigger 产品GO例9.20USE StudentGO ALTER TRIGGER del_xiaoshou ON dbo.产品 FOR DELETE ASBEGINDELETE 产品销售 WHERE 产品编号 IN (SELECT 产品编号 FROM DE
11、LETED)PRINT 产品销售表中相应记录也被删除END GO例9.21USE studentGODELETE FROM 产品 WHERE 产品编号=0001GOUSE StudentGOIF EXISTS(SELECT name FROM sys.objectsWHERE name=Ninsert_xibu AND type=tr)DROP TRIGGER insert_xibuGOCREATE TRIGGER insert_xibu ON dbo.教师 FOR INSERT ASDECLARE XIBU CHAR(2)SELECT XIBU =教师.系部代码FROM 教师, insert
12、edWHERE 教师.系部代码= inserted.系部代码IF XIBUPRINT(记录插入成功)ELSEBEGINPRINT(系部代码不存在教师表中，不能插入记录，插入将终止!)ROLLBACK TRANSACTIONENDGOUSE StudentGOCREATE TRIGGER del_zhuanye ON 专业 FOR DELETE ASIF (SELECT COUNT(*) FROM 班级 INNER JOIN DELETEDON 班级.专业代码=DELETED.专业代码)0BEGINPRINT(该专业被班级表引用,你不可以删除此条记录,删除将终止)ROLLBACK TRANSACTIONENDELSEPRINT(记录已删除)GOUSE StudentGOCREATE TRIGGER updatel_xibu ON dbo.教师 FOR UPDATE ASIF UPDATE(姓名)BEGINPRINT(不能修改系部名称)ROLLBACK TRANSACTIONENDGO
