SQL 中 PROCEDURE,TRIGGER,FUNCTION简单应用

今天简单总结一下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