함수

 

 

변수를 받아 값을 리턴하는 함수

create function ufn_getAge(@bYear int)
	returns int
as
	begin
		declare @age int
		set @age = year(GETDATE()) - @bYear
		return (@age)
	end
go


select dbo.ufn_getAge(1980)

-- 위 내용을 테이블에 적용하기 

 

select custId, custName, dbo.ufn_getAge(year(birthDate)) as[나이]
from customersTbl

 

 

 

 

 

-- table형태 반환하기 == inline table 반환하기

create function ufn_getVip(@amount int)
	returns table
as
	return(
		select distinct S.custId, C.custName
		from salesTbl S inner join customersTbl C
			on S.custId = C.custId
		where sales > @amount
	)
go

 

-- table형태이므로 from 절에 쓸 수 있다

select * from dbo.ufn_getVip(5000)

 

 

 

 

 

 

 

-- 구매 금액별 회원의 등급 나누기

create function ufn_userGrade(@bDate date)
	returns @retTable table
	(
		userId char(8),
		userName nvarchar(10),
		grade nvarchar(5)
	)
as
begin
	declare @rowCnt int;
	select @rowCnt = count(*) from customersTbl where birthDate >= @bDate

	if @rowCnt <= 0
	begin 
		insert into @retTable values('없음','없음','없음')
		return;
	end

	insert into @retTable
		select C.custId, C.custName,
			case
				when ( sum(sales) >= 5000 ) then N'최우수고객'
				when ( sum(sales) >= 3000 ) then N'우수고객'
				when ( sum(sales) >= 1 ) then N'일반고객'
				else N'휴먼고객'
			end
		from salesTbl S
		right outer join customersTbl C ON S.custId = C.custId
		where birthDate >= @bDate
		group by C.custId, C.custName
	return;
end

 

--실행

select * from dbo.ufn_userGrade('1970-01-01')