CREATE PROCEDURE music_udtDefaultInfo
@mc_Subject NVarchar(255),
@mc_Artist NVarchar(255),
@file_Code INT,
@genre NVarchar(32)
AS
Declare @IsCnt INT, @GenreIDX INT
SET XACT_ABORT ON
BEGIN TRANSACTION
Select @IsCnt = Count(*) , @GenreIDX = MAX(m_CD) From musicGenre_tbl Where m_GroupDesc = @genre
IF @IsCnt = 0 Begin
Insert musicGenre_tbl(m_GroupDesc, doID)
Values(@genre, 'system')
SET @GenreIDX = @@IDENTITY
End
IF @@Error <> 0 Begin
Goto Err
End
SET @IsCnt = 0
Select @IsCnt = Count(*) From musicContents_tbl Where file_code=@file_Code
IF @IsCnt = 1 Begin
UPDATE musicContents_tbl SET mc_Subject =@mc_Subject , mc_Artist =@mc_Artist , m_CD = @GenreIDX WHERE file_Code = @file_Code
IF @@Error <> 0 Begin
Goto Err
End
End
COMMIT TRANSACTION
Return 1
Err:
ROLLBACK TRANSACTION
Return 0
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*
작성자 : 한영민
작성일 : 2003-10-14
설 명 : 초기 musicCityContents 테이블에 데이터가 삽입 될때 사용되는 우리측 기본 정보 DB 생성
*/
CREATE PROCEDURE dbo.[music_setDefaultInfo]
@mc_Subject NVarchar(255),
@mc_Artist NVarchar(255),
@file_Code INT,
@genre NVarchar(32)
AS
Declare @IsCnt INT, @GenreIDX INT
SET XACT_ABORT ON
BEGIN TRANSACTION
Select @IsCnt = Count(*) , @GenreIDX = MAX(m_CD) From musicGenre_tbl Where m_GroupDesc = @genre
IF @IsCnt = 0 Begin
Insert musicGenre_tbl(m_GroupDesc, doID)
Values(@genre, 'system')
SET @GenreIDX = @@IDENTITY
End
IF @@Error <> 0 Begin
Goto Err
End
SET @IsCnt = 0
Select @IsCnt = Count(*) From musicContents_tbl Where file_code=@file_Code
IF @IsCnt = 0 Begin
Insert musicContents_tbl(mc_Subject, mc_Artist, m_CD, Rank, BuyCnt, giftCnt, ProposeCnt, askToBuyCnt, file_Code)
Values(@mc_Subject, @mc_Artist, @GenreIDX, 0, 0, 0, 0, 0, @file_Code)
IF @@Error <> 0 Begin
Goto Err
End
End
COMMIT TRANSACTION
Return 1
Err:
ROLLBACK TRANSACTION
Return 0
CREATE PROCEDURE music_delDefaultInfo
@file_Code INT
AS
DELETE FROM musicContents_tbl WHERE file_Code = @file_Code
SELECT * FROM MUSICCONTENTS_TBL WHERE FILE_CODE = 54498
--랜덤으로 구매수, 랭킹 데이터 조작
--커서 선언
DECLARE cur_music CURSOR
FOR
SELECT mc_CD FROM MUSICCONTENTS_TBL
--커서 오픈
OPEN cur_music
--변수 선언
DECLARE @mc_CD INT
--첫 로우 FETCH
FETCH NEXT FROM cur_music INTO @mc_CD
WHILE @@FETCH_STATUS = 0
BEGIN
--FETCH
UPDATE MUSICCONTENTS_TBL
SET rank = @mc_CD, buyCnt = Convert(INT, Rand() * 1000), giftCnt = Convert(INT, Rand() * 1000), proposeCnt = Convert(INT, Rand() * 1000), asktobuyCnt = Convert(INT, Rand() * 1000)
WHERE mc_CD = @mc_CD
--다음 로우 FEETCH - 루프
FETCH NEXT FROM cur_music INTO @mc_CD
END
--커서 CLOSE
CLOSE cur_music
--커서 DEALLOCATE
DEALLOCATE cur_music
GO
Select * from musicGenre_tbl
---------------------------------------------------------------------------------------------
INSERT MUSICCONTENTS_TBL
(mc_Subject, mc_Artist, m_CD, rank, buyCnt, giftCnt, proposeCnt, askTobuyCnt, file_code)
SELECT mc_Subject, mc_Artist, m_CD, Convert(INT, Rand() * 1000),Convert(INT, Rand() * 1000),Convert(INT, Rand() * 1000),Convert(INT, Rand() * 1000),Convert(INT, Rand() * 1000), file_code + 100000 FROM MUSICCONTENTS_TBL
INSERT musicAlbumRecord_tbl(ma_CD, mc_CD, UserID)
Select 1, mc_CD, 'icdtl' from musicContents_tbl
------------------------------------------------------------------------------
DECLARE @strSQL NVARCHAR(1000)
DECLARE CUR_WorkCode CURSOR
FOR
Select WWH_CD, WWH_CheckDay, WWH_WC_CD
from dbo.WH_WorkHistory Where WWH_WC_CD=18
OPEN CUR_WorkCode
DECLARE @ii INT
DECLARE @WWH_CD INT
DECLARE @WWH_CheckDay INT
DECLARE @WWH_WC_CD NVarchar(1000)
SET @ii =1
--첫 로우 FETCH
FETCH NEXT FROM CUR_WorkCode INTO @WWH_CD, @WWH_CheckDay, @WWH_WC_CD
WHILE @@FETCH_STATUS = 0
BEGIN
Update WH_WorkHistory SET WWH_CheckDay = WWH_CheckDay + @ii Where WWH_CD = @WWH_CD
SET @ii = @ii + 1
FETCH NEXT FROM CUR_WorkCode INTO @WWH_CD, @WWH_CheckDay, @WWH_WC_CD
END
CLOSE CUR_WorkCode
DEALLOCATE CUR_WorkCode
