SQL 문법(FROM 과 rank()함수 그리고 별칭, INSERT DECLARE 지역변수)

CREATE PROCEDURE [dbo].[USP_RankTotal]

@Start bigint,

@Count bigint

AS

--출력을 하는데..FROM으로부터인데..

--FROM은 Rank()함수를 쓰고 order by Point를 기준으로 desc 내림차순 Rank를 출력한다 *FROM TB_User 테이블의 전체 항목을..

--a는 위의 결과를 담는 별칭으로..a로부터 a의 비교문을 통해서 start랭크를 기준으로 start부터 count만큼 랭킹은 반환(출력 SELECT)하게 된다.

SELECT * FROM(SELECT Rank() over (order by Point desc) as Rank, * FROM TB_User) a WHERE a.Rank >= @Start AND a.Rank <= @Start + @Count - 1;

RETURN 0

---

CREATE PROCEDURE [dbo].[USP_StageUpdateRecord]

@UserID bigint,

@Point bigint

AS

--  DECLARE @변수명 데이타형  /* 변수 선언 */ 여기서는 지역변수정도되겠네..

DECLARE @UserPoint bigint

DECLARE @ExpBeforeGame int

DECLARE @ExpAfterGame int

SELECT @UserPoint = Point, @ExpBeforeGame = Experience FROM TB_User WHERE UserID = @UserID

--회원데이터가 없다면

IF(@UserPoint IS NULL)

BEGIN

SELECT 'UserID is invalid' as Message, 1002 as ResultCode

RETURN 0

END

--  INSERT INTO 테이블명 (칼럼1, 칼럼2, ...) VALUES (값1, 값2, ...)   /* 특정 칼럼에만 데이타를 넣을 때 */

INSERT INTO TB_StageRecord(UserID, Point, RecordTime)

VALUES (@UserID, @Point, GETDATE())

IF(@UserPoint < @Point)

BEGIN

UPDATE TB_User SET Point = @Point WHERE UserID = @UserID

END

UPDATE TB_User SET Experience = Experience + @Point WHERE UserID = @UserID

DECLARE @LevelBeforeGame int

DECLARE @LevelAfterGame int

SET @ExpAfterGame = @ExpBeforeGame + @Point

--아래 이해가 안감 dbo.FNC??

SET @LevelBeforeGame = dbo.FNC_LevelFromExp(@ExpBeforeGame)

SET @LevelAfterGame = dbo.FNC_LevelFromExp(@ExpAfterGame)

--in ??

DECLARE @IsLevelUp int

IF @LevelBeforeGame != @LevelAfterGame

BEGIN

SET @IsLevelUp = 1

UPDATE TB_User SET Level = @LevelAfterGame WHERE UserID = @UserID

END

ELSE SET @IsLevelUp = 0

SELECT 'Success' as Message, 1 as ResultCode, @IsLevelUp as IsLevelUp, @LevelBeforeGame as LevelBeforeGame, @LevelAfterGame as LevelAfterGame,

@ExpBeforeGame as ExpBeforeGame, @ExpAfterGame as ExpAfterGame

RETURN 0