今天简单总结一下SQL中PROCEDUR,TRIGGER,FUNCTION的简单应用。
一.简单PROCEDURE
1.不带参数
create proc PROC_Test
as
select 字段名 from 表名
2.带输入,输出参数
create proc PROC_TESTParamer
@ID varchar(16),
@username varchar(20) output
as
select @username = USERNAME FROM 表名 where ID = @ID
二.简单TIRGGER
create trigger Trigger_Test
on 表名
for udpate (insert,delete)
as
declare @oldnum int, @num int, @productname varchar(20)
select @oldnum = num from deleted
select @productname = productname,@num = num from inserted
if @oldnum < @num
begin
print '产品数量不足'
rollback tran
end
else
update product set num = @oldnum - @num where productname = @productname
三.简单FUNCTION
1.不带参数
craete function f_Test()
returns @TempTable table(@ID varchar(16),@username,varchar(20))
as
begin
insert into @TempTable(@ID,@username) select id,username from TB_USER
return
end
2.带参数
create function f_Test1(@ID varchar(16))
returns varchar(20)
as
begin
declare @username varchar(20)
select @username = username from TB_USER where ID = @ID
return @username
end