今天簡單總結一下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

?

?

轉載于:https://www.cnblogs.com/lhstrong/archive/2009/06/27/1512119.html

總結

以上是生活随笔為你收集整理的SQL 中 PROCEDURE,TRIGGER,FUNCTION简单应用的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。