《SQLServer数据库应用与维护》-第31讲 综合实训5

复杂查询04在这一环节中，要求学生根据金蝶软件温州分公司提供的项目要求，建立多张表之间的各种查询。并结合视图，提高数据库编程技术，并能模仿金蝶K3系统的查询功能实现对采购订单信息的复杂查询以及职员表的视图建立。上节回顾第30讲课程内容：案例引入上节回顾第30讲：分析金蝶K3系统中已有的职员视图：创建职员信息视图上节回顾第30讲问题1：职员表（t_Emp）来源于哪些基本表？问题2：采购订单视图（vwICBill_26）的信息来源于哪些基本表？存储过程和触发器05在这一环节中，要求学生根据金蝶软件温州分公司提供的项目要求，建立存储过程和触发器，从而提高数据库编程技术。综合实训（五）第31讲综合实训（五）第31讲创建存储过程2.1创建触发器2.2任务1创建存储过程（1）创建一个存储过程p1，要求根据该采购订单号显示该采购订单的供应商名称、采购日期。（2）创建一个存储过程p2，要求当修改某一订单的订单编号时，同时修改该采购订单分录的所有分录的订单编号。（3）创建一个存储过程p３，要求根据供应商名称显示该供应商供应的采购订单号，采购日期，物料名称，数量、含税单价，含税金额。

CREATEVIEW[schema_name.]view_name

[(column[,...n])]

[WITHENCRYPTION]

ASSELECT_statement

[WITHCHECKOPTION]

参数说明如下。

schema_name：视图所属架构名

view_name：视图名

column：视图中所使用的列名。

WITHENCRYPTION：加密视图。

WITHCHECKOPTION：指出在视图上所进行的修改都要符合查询语句所指定的限制条件，这样可以确保数据修改后仍可通过视图看到修改的数据。复习视图创建存储过程

Create

Proc[edure]

存储过程名

@参数1

数据类型=默认值OUTPUT,

@参数n

数据类型=默认值OUTPUT

AS

SQL语句执行存储过程

Exec[ute]<存储过程名>[<@参数名1>…]复习存储过程【例】创建一个没有参数的存储过程

createproctest1

as

begin

--SQL代码省略

end

执行该存储过程

exectest1

1：没有参数的存储过程复习存储过程【例】创建一个有参数的存储过程

createproctest2 @value1

int, @value2

int

as

begin

--SQL代码省略

end执行该存储过程exectest21000，2000或：exectest2@value1=1000，@value2=20002：有输入参数的存储过程复习存储过程【例】设置参数的默认值

CreateProctest3

@value1int=100，@value2int=400

asbegin

--SQL代码省略

end执行该存储过程exectest3或：exectest3300，500exectest3300exectest2@value2=5003：有参数默认值的存储过程复习存储过程如果调用批命令将变量作为参数传入存储过程，而参数在存储过程中被修改，则修改不会传给调用该存储过程的命令，除非生成和执行存储过程时对参数指定OUTPUT关键字【例】有输出参数的存储过程创建：Createproctest4 @valuevarchar(50),@value_out

varchar(50)

OUTPUT as select@value_out

=valueXfromtable1 wherevalue=@value

return

4：有输出参数的存储过程

调用：

Declare

@value_save

varchar(50)

Exectest4@value=‘myValue',@value_out=@value_save

OUTPUT

Print@value_save

复习存储过程触发器（trigger）是SQLserver提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（insert，delete，update）时就会激活它执行。DML触发器Insert触发器Delete触发器Update触发器

复习触发器不能直接调用，只有在对触发器表的数据进行更改时，才自动执行不能传递和接受参数触发器可以实施更为复杂的数据完整性约束修改操作inserted表deleted表增加(INSERT)记录存放新增的记录------删除(DELETE)记录-----存放被删除的记录修改(UPDATE)记录存放更新后的记录存放更新前的记录复习触发器例：设计一个触发器（

SC_update

），当向课程表中修改课程名称时触发该触发器，如果该课程正在被选修，则课程名不能进行修改。CREATETRIGGERSC_updateON课程FORUPDATEAS

beginIF(SELECTCOUNT(*)FROMinserted,选课

WHERE选课.课程号=inserted.课程号)>0

ROLLBACKTRANSACTION

end--指定所监控的表--指定触发事件类型--执行代码复习触发器（1）创建一个触发器d1，要求当删除某一个供应商时，如果该供应商已有采购订单，则提示‘该供应商不能进行删除’，并拒绝删除，否则就可以删除。（2）创建一个触发器