/**********************************************************************************************
-- Title : [SQL2k5] 함수(scholar, inline table, multi table, clr) 작성 예
-- Reference : hanbitbook.co.kr bol
-- Key word : create function scholar inline table multi table CLR 함수 작성 함수 생성
**********************************************************************************************/
/*
-- Scholar Funcation
*/
use sqldb;
go

drop function dbo.ufn_getage;
go

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(1979); -- 호출시 스키마명을 붙여줘야 함
declare @retval int;
exec @retval = dbo.ufn_getage 1979;
print @retval;
go

-- sample 2.
select userid, name, dbo.ufn_getage(birthyear) as [만 나이] from usertbl;
alter function ufn_getage(@byear int)
 returns int
as
 begin
  declare @age int
  set @age = year(getdate()) - @byear + 1
  return(@age)
 end
go

select dbo.ufn_getage(1979);
drop function dbo.ufn_getage;


/*
-- Inline Table Funcation
*/
create function ufn_getuser(@ht int)
 returns table
as
 return
    (
  select userid as [아이디], name as [이름], height as [키]
  from usertbl
  where height > @ht
 )
go

select * from dbo.ufn_getuser(180);


/*
-- Multi Table Funcation
*/
use sqldb;
go

create function ufn_usergrade(@byear int)
-- 리턴할 테이블의 정의(@rettable은 begin..end에서 사용될 테이블변수임)
returns @rettable table 
  ( userid nvarchar(10),
    name  nvarchar(10),
    grade  nvarchar(5) )
as
begin
 declare @rowcnt int;
 -- 행의 개수를 카운트
 select @rowcnt = count(*) from usertbl where birthyear >= @byear;
 -- 행이 하나도 없으면 '없음'이라고 입력하고 테이블을 리턴함.
 if @rowcnt <= 0
 begin
  insert into @rettable values('없음','없음','없음');
  return;
 end;
 -- 행이 1개이상 있다면 아래를 수행하게됨
 insert into @rettable
  select u.userid, u.name,
   case
    when (sum(price*amount) >= 1500) then N'최우수고객'
    when (sum(price*amount) >= 1000) then N'우수고객'
    when (sum(price*amount) >= 1 ) then N'일반고객'
    else N'유령고객'
    end
  from buytbl b
     right outer join usertbl u
     on b.userid = u.userid
  where birthyear >= @byear
  group by u.userid, u.name;
 return;
end;
go

select * from dbo.ufn_usergrade(1980);


/*
-- CLR Function(from BOL)
*/
/*
예에서는 SQL Server Database Engine samples를 로컬 컴퓨터의 기본 위치에 설치하고
StringManipulate.csproj 예제 응용 프로그램을 컴파일했다고 가정합니다.
자세한 내용은 보충 문자 인식 문자열 조작을 참조하십시오.
이 예에서는 CLR 함수 len_s를 만듭니다. 함수를 만들기 전에 SurrogateStringFunction.dll
어셈블리가 로컬 데이터베이스에 등록됩니다.
*/
DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of the this variable if you have
--installed the sample someplace other than the default location.
SELECT @SamplesPath = REPLACE( physical_name
                             , 'Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf'
                             , 'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\'
                             )
FROM master.sys.database_files
WHERE name = 'master';
CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

신고

+ Recent posts

티스토리 툴바