/*
사용자 지역
*/
sp_helptext fn_GetUser_Area
ALTER Function dbo.fn_GetUser_Area
(
@SEQ CHAR(1)
)
RETURNS NVARCHAR(5)
BEGIN
DECLARE @RX VARCHAR(5)
IF @SEQ ='A' SET @RX = '서울'
IF @SEQ ='B' SET @RX = '경기'
IF @SEQ ='C' SET @RX = '인천'
IF @SEQ ='D' SET @RX = '부산'
IF @SEQ ='E' SET @RX = '대전'
IF @SEQ ='F' SET @RX = '대구'
IF @SEQ ='G' SET @RX = '광주'
IF @SEQ ='H' SET @RX = '울산'
IF @SEQ ='I' SET @RX = '강원'
IF @SEQ ='J' SET @RX = '충북'
IF @SEQ ='K' SET @RX = '충남'
IF @SEQ ='L' SET @RX = '경남'
IF @SEQ ='M' SET @RX = '경북'
IF @SEQ ='N' SET @RX = '전북'
IF @SEQ ='O' SET @RX = '전남'
IF @SEQ ='P' SET @RX = '제주'
IF @SEQ ='Q' SET @RX = '해외'
RETURN (@RX)
END
/*
접속 의도
*/
CREATE Function dbo.fn_GetUser_Purpose
(
@SEQ CHAR(1)
)
RETURNS NVARCHAR(10)
BEGIN
DECLARE @RX NVARCHAR(10)
IF @SEQ ='A' SET @RX = '번개팅'
IF @SEQ ='B' SET @RX = '편한만남'
IF @SEQ ='C' SET @RX = '새친구사귀기'
IF @SEQ ='D' SET @RX = '애인만들기'
IF @SEQ ='E' SET @RX = '술한잔하기'
IF @SEQ ='F' SET @RX = '공연/영화보기'
IF @SEQ ='G' SET @RX = '드라이브가기'
IF @SEQ ='H' SET @RX = '커피한잔하기'
IF @SEQ ='I' SET @RX = '게임친구만들기'
IF @SEQ ='J' SET @RX = '기타'
RETURN (@RX)
END
/*
외모
*/
CREATE Function dbo.fn_GetUser_appearance
(
@Gender INT
, @SEQ CHAR(1)
)
RETURNS NVARCHAR(10)
BEGIN
DECLARE @RX NVARCHAR(10)
IF @Gender = 0 Begin
IF @SEQ ='A' SET @RX = '귀여움'
IF @SEQ ='B' SET @RX = '깜찍'
IF @SEQ ='C' SET @RX = '세련'
IF @SEQ ='D' SET @RX = '섹시'
IF @SEQ ='E' SET @RX = '지적'
IF @SEQ ='F' SET @RX = '청순'
IF @SEQ ='G' SET @RX = '순진'
IF @SEQ ='H' SET @RX = '여우'
IF @SEQ ='I' SET @RX = '평범'
IF @SEQ ='J' SET @RX = '기타'
End
IF @Gender = 1 Begin
IF @SEQ ='A' SET @RX = '미소년'
IF @SEQ ='B' SET @RX = '터프'
IF @SEQ ='C' SET @RX = '세련'
IF @SEQ ='D' SET @RX = '샤프'
IF @SEQ ='E' SET @RX = '지적'
IF @SEQ ='F' SET @RX = '순진'
IF @SEQ ='G' SET @RX = '듬직'
IF @SEQ ='H' SET @RX = '평범'
IF @SEQ ='I' SET @RX = '기타'
End
RETURN (@RX)
END
/*
신장
*/
CREATE Function dbo.fn_GetUser_height
(
@SEQ CHAR(1)
)
RETURNS NVARCHAR(10)
BEGIN
DECLARE @RX NVARCHAR(10)
IF @SEQ ='A' SET @RX = '140~145'
IF @SEQ ='B' SET @RX = '145~150'
IF @SEQ ='C' SET @RX = '150~155'
IF @SEQ ='D' SET @RX = '155~160'
IF @SEQ ='E' SET @RX = '160~165'
IF @SEQ ='F' SET @RX = '165~170'
IF @SEQ ='G' SET @RX = '170~175'
IF @SEQ ='H' SET @RX = '175~180'
IF @SEQ ='I' SET @RX = '180~185'
IF @SEQ ='J' SET @RX = '185~190'
IF @SEQ ='K' SET @RX = '190 이상'
IF @SEQ ='L' SET @RX = '상관없음'
RETURN (@RX)
END
/*
체중
*/
CREATE Function dbo.fn_GetUser_weight
(
@SEQ CHAR(1)
)
RETURNS NVARCHAR(10)
BEGIN
DECLARE @RX NVARCHAR(10)
IF @SEQ ='A' SET @RX = '40kg 미만'
IF @SEQ ='B' SET @RX = '40~44'
IF @SEQ ='C' SET @RX = '45~49'
IF @SEQ ='D' SET @RX = '50~54'
IF @SEQ ='E' SET @RX = '55~59'
IF @SEQ ='F' SET @RX = '60~64'
IF @SEQ ='G' SET @RX = '65~69'
IF @SEQ ='H' SET @RX = '70~74'
IF @SEQ ='I' SET @RX = '75~79'
IF @SEQ ='J' SET @RX = '80~84'
IF @SEQ ='K' SET @RX = '85~89'
IF @SEQ ='L' SET @RX = '90 이상'
IF @SEQ ='M' SET @RX = '100kg 이상'
IF @SEQ ='N' SET @RX = '공개못함'
RETURN (@RX)
END
/*
학력
*/
CREATE Function dbo.fn_GetUser_schooling
(
@SEQ CHAR(1)
)
RETURNS NVARCHAR(10)
BEGIN
DECLARE @RX NVARCHAR(10)
IF @SEQ ='A' SET @RX = '대학원'
IF @SEQ ='B' SET @RX = '대졸이상'
IF @SEQ ='C' SET @RX = '대재'
IF @SEQ ='D' SET @RX = '초대졸'
IF @SEQ ='E' SET @RX = '고졸이상'
IF @SEQ ='F' SET @RX = '고졸이하'
IF @SEQ ='G' SET @RX = '해외유학파'
IF @SEQ ='H' SET @RX = '기타'
RETURN (@RX)
END
/*
직업
*/
CREATE Function dbo.fn_GetUser_JOB
(
@Gender INT
, @SEQ CHAR(1)
)
RETURNS NVARCHAR(10)
BEGIN
DECLARE @RX NVARCHAR(10)
IF @Gender = 0 Begin
IF @SEQ ='A' SET @RX = '대학생'
IF @SEQ ='B' SET @RX = '모델'
IF @SEQ ='C' SET @RX = '연예인'
IF @SEQ ='D' SET @RX = '전문직'
IF @SEQ ='E' SET @RX = '회사원'
IF @SEQ ='F' SET @RX = '프리랜서'
IF @SEQ ='G' SET @RX = '자영업'
IF @SEQ ='H' SET @RX = '주부'
IF @SEQ ='I' SET @RX = '무직'
IF @SEQ ='J' SET @RX = '기타'
End
IF @Gender = 1 Begin
IF @SEQ ='A' SET @RX = '대학생'
IF @SEQ ='B' SET @RX = '회사원'
IF @SEQ ='C' SET @RX = '전문직'
IF @SEQ ='D' SET @RX = '예술인'
IF @SEQ ='E' SET @RX = '전문직'
IF @SEQ ='F' SET @RX = '연예인'
IF @SEQ ='G' SET @RX = '공무원'
IF @SEQ ='H' SET @RX = '자영업'
IF @SEQ ='I' SET @RX = '무직'
IF @SEQ ='J' SET @RX = '기타'
End
RETURN (@RX)
END
/*
성격
*/
CREATE Function dbo.fn_GetUser_Character
(
@SEQ CHAR(1)
)
RETURNS NVARCHAR(10)
BEGIN
DECLARE @RX NVARCHAR(10)
IF @SEQ ='A' SET @RX = '명랑'
IF @SEQ ='B' SET @RX = '화끈'
IF @SEQ ='C' SET @RX = '터프'
IF @SEQ ='D' SET @RX = '온순'
IF @SEQ ='E' SET @RX = '내성'
IF @SEQ ='F' SET @RX = '털털함'
IF @SEQ ='G' SET @RX = '카리스마'
IF @SEQ ='H' SET @RX = '얌전'
IF @SEQ ='I' SET @RX = '섬세함'
IF @SEQ ='J' SET @RX = '개방적'
IF @SEQ ='K' SET @RX = '보수적'
IF @SEQ ='L' SET @RX = '표현할수없음'
RETURN (@RX)
END
/*
체형
*/
CREATE Function dbo.fn_GetUser_BodyLine
(
@Gender INT
, @SEQ CHAR(1)
)
RETURNS NVARCHAR(10)
BEGIN
DECLARE @RX NVARCHAR(10)
IF @Gender = 0 Begin
IF @SEQ ='A' SET @RX = '글래머'
IF @SEQ ='B' SET @RX = '날씬빵빵'
IF @SEQ ='C' SET @RX = '쭉죽빵빵'
IF @SEQ ='D' SET @RX = '뚱뚱함'
IF @SEQ ='E' SET @RX = '통통함'
IF @SEQ ='F' SET @RX = '홀쭉함'
IF @SEQ ='G' SET @RX = '아담형'
IF @SEQ ='H' SET @RX = '적당함'
IF @SEQ ='I' SET @RX = '기타'
End
IF @Gender = 1 Begin
IF @SEQ ='A' SET @RX = '호리호리'
IF @SEQ ='B' SET @RX = '뽀대남'
IF @SEQ ='C' SET @RX = '뚱뚱함'
IF @SEQ ='D' SET @RX = '통통함'
IF @SEQ ='E' SET @RX = '홀쭉함'
IF @SEQ ='F' SET @RX = '우람함'
IF @SEQ ='G' SET @RX = '건장함'
IF @SEQ ='H' SET @RX = '아담형'
IF @SEQ ='I' SET @RX = '적당함'
IF @SEQ ='J' SET @RX = '기타'
End
RETURN (@RX)
END
/*
흡연
*/
CREATE Function dbo.fn_GetUser_Tabaco
(
@SEQ CHAR(1)
)
RETURNS NVARCHAR(10)
BEGIN
DECLARE @RX NVARCHAR(10)
IF @SEQ ='A' SET @RX = '비흡연'
IF @SEQ ='B' SET @RX = '흡연'
IF @SEQ ='C' SET @RX = '금연중'
RETURN (@RX)
END
/*
주량
*/
CREATE Function dbo.fn_GetUser_Drink
(
@SEQ CHAR(1)
)
RETURNS NVARCHAR(10)
BEGIN
DECLARE @RX NVARCHAR(10)
IF @SEQ ='A' SET @RX = '전혀안마심'
IF @SEQ ='B' SET @RX = '필요시마심'
IF @SEQ ='C' SET @RX = '분위기에따라'
IF @SEQ ='D' SET @RX = '가끔즐김'
IF @SEQ ='E' SET @RX = '매우즐김'
IF @SEQ ='F' SET @RX = '술은내인생'
IF @SEQ ='G' SET @RX = '기타'
RETURN (@RX)
END
/*
관심사
*/
CREATE Function dbo.fn_GetUser_interest
(
@SEQ CHAR(1)
)
RETURNS NVARCHAR(10)
BEGIN
DECLARE @RX NVARCHAR(10)
IF @SEQ ='A' SET @RX = '온라인게임'
IF @SEQ ='B' SET @RX = '컴퓨터'
IF @SEQ ='C' SET @RX = '영화/공연'
IF @SEQ ='D' SET @RX = '애인상대'
IF @SEQ ='E' SET @RX = '결혼상대'
IF @SEQ ='F' SET @RX = '여행'
IF @SEQ ='G' SET @RX = '독서'
IF @SEQ ='H' SET @RX = '레져'
IF @SEQ ='I' SET @RX = '기타'
RETURN (@RX)
END
/*
결혼여부
*/
CREATE Function dbo.fn_GetUser_Marry
(
@SEQ CHAR(1)
)
RETURNS NVARCHAR(10)
BEGIN
DECLARE @RX NVARCHAR(10)
IF @SEQ ='A' SET @RX = '미혼'
IF @SEQ ='B' SET @RX = '기혼'
IF @SEQ ='C' SET @RX = '이혼'
RETURN (@RX)
END
아이디
나이 : 23
지역 : 서울
외모 : nice
키/체중 : 178Cm /65kg
체형 : nice
직업 : 대학생
성격 : nice
접속의도 : 번개팅
최근 접속일 : 2003/10/8
인사말 20
/*
작성자 : 한영민
작성일 : 2003-10-23
설 명 : 사용자 기본 정보(사용자정의함수 호출로 직접 값을 세팅)
*/
ALTER View dbo.music_PopProFile_v
AS
Select Pro_ID, Pro_Name, Pro_Age
, dbo.fn_GetGender(Pro_Gender) AS Pro_Gender
, dbo.fn_GetUser_Area(SubString(Pro_Profile, 2, 1)) AS AREA
, dbo.fn_GetUser_appearance(Pro_Gender, SubString(Pro_Profile, 4, 1)) AS Appearance
, dbo.fn_GetUser_height(SubString(Pro_Profile, 5, 1)) AS Height
, dbo.fn_GetUser_weight(SubString(Pro_Profile, 6, 1)) AS Weight
, dbo.fn_GetUser_BodyLine(Pro_Gender, SubString(Pro_Profile, 10, 1)) AS BodyLine
, dbo.fn_GetUser_JOB(Pro_Gender, SubString(Pro_Profile, 8, 1)) AS JOB
, dbo.fn_GetUser_Character(SubString(Pro_Profile, 9, 1)) AS [Character]
, dbo.fn_GetUser_Purpose(SubString(Pro_Profile, 3, 1)) AS Purpose
, Pro_Profile18, Pro_Profile20, Pro_ArsID,Pro_Photo,Pro_ViewCnt
from Meet_Profile
/*
작성자 : 한영민
작성일 : 2003-10-23
설 명 : 사용자 이상형 정보(사용자정의함수 호출로 직접 값을 세팅)
*/
ALTER View dbo.music_PopProFile_Dar_v
AS
Select Pro_ID, Pro_Name, Pro_DarAge1, Pro_DarAge2
, dbo.fn_GetUser_height(SubString(Pro_DarProFile, 5, 1)) AS Height
, dbo.fn_GetUser_appearance(dbo.fn_CGender(Pro_Gender), SubString(Pro_DarProFile, 4, 1)) AS Appearance
, dbo.fn_GetUser_BodyLine(dbo.fn_CGender(Pro_Gender), SubString(Pro_DarProFile, 10, 1)) AS BodyLine
, dbo.fn_GetUser_Character(SubString(Pro_DarProFile, 9, 1)) AS [Character]
, dbo.fn_GetUser_Area(SubString(Pro_DarProFile, 2, 1)) AS AREA
, dbo.fn_GetUser_Purpose(SubString(Pro_DarProFile, 3, 1)) AS Purpose
, dbo.fn_GetUser_interest(SubString(Pro_DarProFile, 13, 1)) AS interest
, Pro_Profile19
from Meet_Profile
CREATE Function dbo.fn_CGender
(
@Gender TinyInt
)
RETURNS TinyInt
AS
Begin
Declare @RX TinyINT
IF @Gender =0 Set @RX = 1
IF @Gender =1 Set @RX = 0
Return @RX
End
/*
작성자 : 한영민
작성일 : 2003-10-23
설 명 : 사용자 기본 정보(뷰를 찹조 하고 있음)
*/
ALTER Procedure dbo.music_getPopupProFile_sp
@UserID NVARCHAR(12)
AS
Select Pro_ID, Pro_Name, Pro_Age, Pro_Gender, AREA, Appearance, Height, Weight, BodyLine, JOB, [Character], Purpose, Pro_Profile18 , Pro_Profile20, Pro_ArsID, Pro_Photo, Pro_ViewCnt
from music_PopProFile_v Where Pro_ID = @UserID
/*
작성자 : 한영민
작성일 : 2003-10-23
설 명 : 사용자 이상형 정보(뷰를 찹조 하고 있음)
*/
AlTER Procedure dbo.music_getPopupProFileDar_sp
@UserID NVARCHAR(12)
AS
SELECT Pro_ID, Pro_Name, Pro_DarAge1, Pro_DarAge2, Height, Appearance, BodyLine, [Character], AREA, Purpose, Interest, Pro_Profile19
FROM music_PopProFile_Dar_v Where Pro_ID=@UserID
music_getPopupProFileDar_sp 'icdtl'
music_getPopupProFileDar_sp 'wjddms1093'
/*
작성자 : 한영민
작성일 : 2003-10-23
설 명 : 사용자 추가 정보(사용자정의함수 호출로 직접 값을 세팅)
*/
Alter View dbo.music_PopProFile2_v
AS
Select Pro_ID, Pro_Name, Pro_Age
,dbo.fn_GetUser_schooling(SubString(Pro_Profile, 4, 1)) AS schooling
,dbo.fn_GetUser_interest(SubString(Pro_Profile, 13, 1)) AS interest
,dbo.fn_GetUser_Tabaco(SubString(Pro_Profile, 11, 1)) AS Tabaco
,dbo.fn_GetUser_Drink(SubString(Pro_Profile, 12, 1)) AS Drink
,Pro_Profile17
,Pro_Profile20
,dbo.fn_GetUser_weight(SubString(Pro_Profile, 6, 1)) AS Weight
,Pro_ArsID
from Meet_Profile
/*
작성자 : 한영민
작성일 : 2003-10-24
설 명 : 사용자 추가 정보 저장프로시져
*/
ALTER Procedure dbo.music_getPopupProFileDetail_sp
@UserID NVARCHAR(12)
AS
Select Pro_ID, Pro_Name, Pro_Age, schooling, interest, Tabaco, Drink, Pro_Profile17, Pro_Profile20, Weight, NULL, NULL, NULL, NULL, Pro_ArsID
from music_PopProFile2_v where Pro_ID = @UserID
music_getPopupProFileDetail_sp 'ICDTL'
