chp5数据库触发器,存储过程,函数教学ppt.ppt

1、An Introduction to Database System,数据库原理 An Introduction to Database System 第五章 数据库编程,An Introduction to Database System,SqlServer编程基础,declare a int set a=5 print a,变量的定义、赋值、输出,An Introduction to Database System,SqlServer编程基础,使用select语句赋值 declare user1 nvarchar(50) Select user1=张三 Declare user2 nvar

2、char(50) Select user2 = Name from ST_User where ID=1,变量的定义、赋值、输出,An Introduction to Database System,SqlServer编程基础,使用update语句赋值 declare user3 nvarchar(50) update ST_User set user3 = Name where ID=1,变量的定义、赋值、输出,An Introduction to Database System,SqlServer编程基础,while循环计算1到100的和 declare a int declare sum

3、 int set a=1 set sum=0 while a=100 begin set sum= sum + a set a= a + 1 end print sum,循环,An Introduction to Database System,SqlServer编程基础,if,else条件分支 if(1+1=2) begin print 对 end else begin print 错 end,条件语句,An Introduction to Database System,SqlServer编程基础,when then条件分支 declare today int declare week n

4、varchar(3) set today=3 set week=case when today=1 then 星期一 when today=2 then 星期二 when today=3 then 星期三 when today=4 then 星期四 when today=5 then 星期五 when today=6 then 星期六 when today=7 then 星期日 else 值错误 end print week,An Introduction to Database System,SqlServer编程基础,游标 ?declare ID int declare Oid int d

5、eclare Login varchar(50) -定义一个游标 declare user_cur cursor for select ID,Oid,Login from ST_User -打开游标 open user_cur while fetch_status=0 begin -读取游标 fetch next from user_cur into ID,Oid,Login print ID -print Login end close user_cur -摧毁游标 deallocate user_cur,An Introduction to Database System,触发器,触发器（

6、Trigger）是定义在基本表上的一类由事件驱动的特殊程序 事件：insert、update、delete,An Introduction to Database System,5.6 触发器,5.6.1 定义触发器 5.6.2 激活触发器 5.6.3 删除触发器,An Introduction to Database System,5.6.1 定义触发器,CREATE TRIGGER语法格式 CREATE TRIGGER ON FOR insert|update|delete AS begin 触发器程序； end;,An Introduction to Database System,定义

7、触发器(续),触发器程序中可以使用的两个记录型变量：inserted和deleted 当发生insert时，inserted有值 当发生delete时，deleted有值 当发生update时，都有值,An Introduction to Database System,商品入库表,商品出库表,商品库存表,应用实例,An Introduction to Database System,create table tab_rk (spbh int,rkrq datetime,rksl int); create table tab_ck (spbh int,ckrq datetime,cksl in

8、t); create table tab_kc (spbh int, kcsl int);,An Introduction to Database System,-入库表的插入触发器 create trigger tg_rkinsert on tab_rk for insert As declare spbh int declare rksl int set spbh=(select spbh from inserted) set rksl=(select rksl from inserted) if not exists(select * from tab_kc where spbh=spb

9、h) begin insert into tab_kc values(spbh,rksl); commit; return; end update tab_kc set kcsl=kcsl+rksl where spbh=spbh; commit;,An Introduction to Database System,insert into tab_rk values(1001,2005-6-1,80); insert into tab_rk values(1001, 2005-6-25,100); Select * from tab_rk; Select * from tab_kc;,An

10、Introduction to Database System,存储过程,1. 创建存储过程 2. 删除存储过程,An Introduction to Database System,创建带output参数的存储过程 CREATE PROCEDURE PR_Sum a int, b int, sum int output AS BEGIN set sum=a+b END,创建存储过程,An Introduction to Database System,执行存储过程获取output型返回值 declare mysum int execute PR_Sum 1,2,mysum output pr

11、int mysum,创建存储过程,An Introduction to Database System,创建Return返回值存储过程 CREATE PROCEDURE PR_Sum2 a int, b int AS BEGIN Return a+b END,创建存储过程,An Introduction to Database System,执行存储过程获取Return型返回值 declare mysum2 int execute mysum2= PR_Sum2 1,2 print mysum2,创建存储过程,An Introduction to Database System,Create

12、PROCEDURE delete_student sno int AS begin if exists (select * from student where sno= sno) delete from student where sno= sno; else print 没有这个学生 end,创建存储过程,An Introduction to Database System,exec delete_student 200215123,执行存储过程,An Introduction to Database System,create procedure ret_name ename varch

13、ar(14) output,empno int as if exists (select * from emp where empno= empno) select ename=ename from emp where empno= empno; else print 该员工号不存在!,创建存储过程,An Introduction to Database System,删除存储过程,declare name varchar(14) exec ret_name name output,7788 select name,An Introduction to Database System,删除存储

14、过程,Drop PROCEDURE delete_student；,An Introduction to Database System,函数,1. 创建函数 2. 使用函数,An Introduction to Database System,标量值函数 create function FUNC_Sum1 ( a int, b int ) returns int as begin return a+b end,创建函数,An Introduction to Database System,调用标量值函数 declare s int set s=dbo.FUNC_Sum1(100,50) pr

15、int s,创建函数,An Introduction to Database System,内联表值函数 create function FUNC_UserTab_1 ( myId int ) returns table as return (select * from ST_User where IDmyId),创建函数,An Introduction to Database System,调用表值函数 select * from dbo.FUNC_UserTab_1(15),创建函数,An Introduction to Database System,多语句表值函数 create fun

16、ction FUNC_UserTab_2 ( myId int ) returns t table ( ID int NOT NULL, Oid int NOT NULL, Login nvarchar(50) NOT NULL, Rtx nvarchar(4) NOT NULL, Name nvarchar(5) NOT NULL, Password nvarchar(max) NULL, State nvarchar(8) NOT NULL ) as begin insert into t select * from ST_User where IDmyId return end,创建函数

17、,An Introduction to Database System,create function emp_name(empno int) returns varchar(14) as begin declare ename varchar(14) if not exists (select * from emp where empno= empno) set ename=无此员工 else select ename=ename from emp where empno= empno return ename end,创建函数,An Introduction to Database System,使用函数,select dbo.emp_name(7788) select dbo.emp_name(7799),An Introduction to Database System,函数与存储过程的区别,函数： 1. 可以返回表变量 2. 限制颇多，包括