'저장프로시져' 태그의 글 목록

1. 저장프로시저란?

저장 프로시저(Stored Procedure)는 일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합입니다. 저장프로시저를 잘 만들면 프로그램 내에서 복잡하게 코드를 구성하는 것을 막고 원하는 결과를 얻을 수 있습니다.

2. 저장프로시저 사용방법

저장프로시저를 사용하기 위해서는 먼저 MS SQL서버에 접속한 후 저장프로시저를 클릭해서 새 저장프로시저 추가를 누르면 저장프로시저가 생성됩니다.

저장프로시저 추가버튼을 누르면 다음과 같은 창이 생성됩니다. 여기서 CREATE PROCEDURE 라고 나오는 것은 프로시저를 새로 만들어야 하기 때문입니다. 한번 생성해서 저장한 후에는 ALTER PROCEDURE 라고 바뀌는데 이것은 저장프로시저를 수정하는 것을 뜻합니다. 첫 행 아래 부분에 괄호로 되어있는 곳에 매개변수를 넣어주는 곳인데요. 여기서 변수나 매개변수를 쓸 때는 @를 앞에 붙여 주셔야 합니다.

예를들면 @name varchar(20), @num int 이런식으로 사용할 수 있습니다. 그리고 AS 아래 부분에는 수행해야 할 쿼리문이나 다른 저장프로시저를 호출하는 코드를 작성 하는 곳입니다.

3. 저장프로시저 예제

- 회원가입에서 호출된 저장프로시저인 CheckID

ALTER PROCEDURE dbo.CheckID

(

@id varchar(10),

@check int output

)

AS

if Exists (select *from Member where m_id = @id)

Begin

set @check = 1

End

else

Begin

set @check = 0

End

RETURN

CheckID는 Member테이블 내에 매개변수로 넘어온 id와 같은 id가 있는지 존재여부를 판단해서 output으로 넘어온 변수에 값을 세팅해주는 역할을 합니다. 이 프로시저는 아래에 나오는 NewMember 에서 사용되는데요. 이 프로시저를 활용한 NewMember 저장프로시저에 대해서 알아보겠습니다.

- 계층화된 저장프로시저를 이용해서 회원가입.

ALTER PROCEDURE dbo.NewMember

(

@id varchar(10),

@pw varchar(10),

@name varchar(10)

)

AS

declare @check int

Exec CheckID @id,@check output

if @check != 1

Begin

insert into Member values(@id,@pw,@name,@check)

End

RETURN

NewMember 저장프로시저에서는 id와 pw, name을 매개변수로 받아와서 Member 테이블에 저장시키는 작업을 수행 하는 것입니다. 회원가입을 할 때 id가 중복되면 안되기 때문에 CheckID라는 저장프로시저를 호출합니다. Exec는 저장프로시저에서 다른 저장프로시저를 호출할 때 사용하는 키워드 입니다.

Declare는 AS 내부에 변수를 선언할 때 사용되는 키워드 입니다. 여기서 check라는 변수를 output 으로 전달하고 있습니다. output이란 C#에서 out과 같은 기능을 하는 것으로 호출된 저장프로시저 내부에서 check에 값을 넣어주면 값이 저장되서 사용할 수 있게 됩니다.

저장프로시저를 마우스 오른쪽 버튼 클릭한 후 실행을 하면 다음과 같은 창이 나오고

실행창에서 값을 입력해 저장프로시저가 제대로 작동하는지 확인 할 수 있습니다

제대로 실행되면 1개 행이 영향을 받았다고 출력됩니다.

중복아이디가 있어서 회원가입이 되지 않은 경우에는 0개 행이 영향을 받았다고 나옵니다.

그리고 저장프로시저에서 RETURN 반환 값을 지정할 경우 RETURN_VALUE에 반환되는 값이 출력되게 됩니다.

회원가입

ALTER PROCEDURE [dbo].[RegMember]

(

@m_id varchar(50),

@m_pw varchar(50),

@m_name varchar(50) ,

@m_age int,

@result int output

)

AS

declare @state int ,@existed int

set @state = 0

Exec ExistID @m_id, @result output

if @result = 0

begin

insert into MemberInfo values(@m_id,@m_pw,@m_name,@m_age,@state)

Exec NewMemberEachTable @m_id

set @result = 1

end

else

begin

set @result =0

end

RETURN

ID유효 확인

ALTER PROCEDURE dbo.ExistID

(

@m_id varchar(50),

@result int output

)

AS

if Exists (select *from MemberInfo where m_id = @m_id)

begin

set @result = 1

end

else

begin

set @result =0

end

RETURN

로그인

ALTER PROCEDURE dbo.LogMember

(

@m_id varchar(50),

@m_pw varchar(50),

@result int output

)

AS

declare @existed int

declare @state int

Exec AvailId @m_id, @m_pw, @existed output

If @existed = 0 /*ID 와PW가일치안한다면*/

begin

set @result = 0

end

else /*일치한다면*/

begin

set @state = ( select state from MemberInfo where m_id=@m_id ) /*state를조사한다. */

if @state = 0 /* 이미로그인중이라니라면상태를1로변환한다. */

begin

set @result = 1

update MemberInfo set state=1 where m_id=@m_id

update FriendList set state =1 where m_id = @m_id

/*state를1로변환시켜준다. */

end

else

begin

set @result = 0 /*아이디를있지만이미로그인상태이다.*/

end

RETURN

AvailId

아디가 유효한지 확인

ALTER PROCEDURE dbo.AvailId

(

@m_id varchar(50),

@m_pw varchar(50),

@result int output

)

AS

if Exists (select m_id from MemberInfo where m_id=@m_id and m_pw =@m_pw)

begin

set @result = 1

end

else

begin

set @result = 0

end

RETURN

로그아웃

ALTER PROCEDURE dbo.LogOut

(

@m_id varchar(50),

@result int OUTPUT

)

AS

set @result = 0

Exec LogOutExist @m_id,@result output

IF @result = 1

BEGIN

update MemberInfo set state = 0 where m_id = @m_id

update FriendList set state =0 where m_id = @m_id

END

RETURN

로그아웃 유효검사

ALTER PROCEDURE dbo.LogOutExist

(

@m_id varchar(50),

@result int OUTPUT

)

AS

Declare @buf varchar(50)

set @buf = (select m_id from MemberInfo where m_id = @m_id)

set @result = 0

IF @buf is not null

BEGIN

set @result = 1

END

RETURN

시청한 비디오추가

ALTER PROCEDURE dbo.AddVideo

(

@m_id varchar(50),

@m_videoname varchar(50),

@result int output

)

AS

declare @strSql nvarchar(1000)

declare @sql nvarchar(1000)

declare @param nvarchar(1000)

Exec ExistID @m_id, @result output

if @result = 1

begin

exec ExistVideoName @m_id,@m_videoname,@result output

end

else

begin

set @result = 0

end

set @sql = @m_id+'_wordInfo'

if @result = 1

begin

SET @strSql = 'INSERT INTO ' + @m_id + '_videoInfo

VALUES(@p_id, @p_videoname)'

SET @param = '@p_id varchar(50),

@p_videoname varchar(50)'

EXECUTE sp_executesql @strSql, @param,

@p_id = @m_id,

@p_videoname = @m_videoname

if NOT EXISTS (SELECT name FROM sys.sysobjects WHERE name = @sql AND (type = 'U'))

begin

set @result =1

Exec NewMemberEachWordTable @m_id

RETURN

end

else

begin

set @result =1

end

RETURN

영상이름 유효검사

ALTER PROCEDURE dbo.ExistVideoName

(

@m_id varchar(50),

@m_videoname varchar(50),

@result int output

)

AS

declare @strSql nvarchar(1000)

declare @params nvarchar(100)

declare @cnt int

SET @strSql = 'SELECT @p_value = count(m_videoname) from ' + @m_id +'_videoInfo

WHERE m_id = @p_id AND m_videoname =@p_videoname'

SET @params = '@p_id varchar(50),

@p_videoname varchar(50),

@p_value as int OUTPUT '

EXEC sp_executesql @strSql, @params,

@p_value = @result OUTPUT,

@p_id = @m_id,

@p_videoname = @m_videoname

if @result = 0

begin

set @result =1

end

else

begin

set @result = 0

end

RETURN

시청한 비디오에 단어 추가

ALTER PROCEDURE dbo.AddWord

(

@m_id varchar(50),

@m_videoname varchar(50),

@m_word varchar(50),

@m_mean varchar(50),

@result int output

)

AS

exec ExistVideoName @m_id,@m_videoname,@result output

declare @strSql nvarchar(1000)

declare @sql nvarchar(1000)

declare @param nvarchar(1000)

if @result = 0

begin

SET @strSql = 'INSERT INTO ' + @m_id + '_wordInfo

VALUES(@p_id,@p_videoname,@p_word,@p_mean)'

SET @param = '@p_id varchar(50),

@p_videoname varchar(50),

@p_word varchar(50),

@p_mean varchar(50)'

EXECUTE sp_executesql @strSql, @param,

@p_id = @m_id,

@p_videoname = @m_videoname,

@p_word = @m_word,

@p_mean = @m_mean

SET @result = 1

end

else

begin

SET @result = 0

end

RETURN

'Programing > 오라클&SQL' 카테고리의 다른 글

저장프로시져 (0)	2016.11.30
오라클 ODBC 연결 프로그램 (0)	2016.11.30
Oracle ODBC 설치 및 연결 (0)	2016.11.30

로그인

ALTER PROCEDURE dbo.LogMember

(

@m_id varchar(50),

@m_pw varchar(50),

@result int output

)

AS

declare @existed int

declare @state int

Exec AvailId @m_id, @m_pw, @existed output

If @existed = 0 /*ID 와PW가일치안한다면*/

begin

set @result = 0

end

else /*일치한다면*/

begin

set @state = ( select state from MemberInfo where m_id=@m_id ) /*state를조사한다. */

if @state = 0 /* 이미로그인중이라니라면상태를1로변환한다. */

begin

set @result = 1

update MemberInfo set state=1 where m_id=@m_id

update FriendList set state =1 where m_id = @m_id

/*state를1로변환시켜준다. */

end

else

begin

set @result = 0 /*아이디를있지만이미로그인상태이다.*/

end

RETURN

AvailId

아디가 유효한지 확인

ALTER PROCEDURE dbo.AvailId

(

@m_id varchar(50),

@m_pw varchar(50),

@result int output

)

AS

if Exists (select m_id from MemberInfo where m_id=@m_id and m_pw =@m_pw)

begin

set @result = 1

end

else

begin

set @result = 0

end

RETURN

시청 동영상 목록 요청

ALTER PROCEDURE dbo.ListVideo

(

@m_id varchar(50),

@count int output

)

AS

declare @sql nvarchar(1000)

set @count = 0

Exec GetVideoListCount @m_id,@count output

if @count !=0

begin

set @sql = 'select m_videoname from '+@m_id+'_videoInfo'

exec (@sql)

end

else

begin

set @count = 0

end

RETURN

해당 ID의 시청한 동영상의 개수

ALTER PROCEDURE dbo.GetVideoListCount

(

@m_id varchar(50),

@count int output

)

AS

declare @sql nvarchar(1000)

declare @p nvarchar(100)

declare @cnt int

set @sql = 'select @cnt = count(m_videoname) FROM '+@m_id+'_videoInfo'

set @p = '@cnt int output'

exec sp_executesql @sql,@p, @cnt=@count output

RETURN

시청 동영상별 저장 단어와 뜻 요청

ALTER PROCEDURE dbo.ListWord

(

@m_id varchar(50),

@m_videoname varchar(50),

@count int output

)

AS

declare @sql nvarchar(1000)

declare @param nvarchar(1000)

set @count = 0

Exec GetWordListCount @m_id,@m_videoname,@count output

if @count !=0

begin

set @sql = 'select m_word,m_mean from '+@m_id+'_wordInfo where m_videoname =@p_videoname'

set @param = '@p_videoname varchar(50)'

execute sp_executesql @sql,@param,

@p_videoname = @m_videoname

end

else

begin

set @count = 0

end

RETURN

영상의 단어 개수 확인

ALTER PROCEDURE dbo.GetWordListCount

(

@m_id varchar(50),

@m_videoname varchar(50),

@count int output

)

AS

declare @sql nvarchar(1000)

declare @p nvarchar(100)

declare @cnt int

set @sql = 'select @cnt = count(m_word) FROM '+@m_id+'_wordInfo where m_videoname = @p_videoname'

set @p = '@cnt int output,

@p_videoname varchar(50)'

exec sp_executesql @sql,@p,

@p_videoname = @m_videoname,

@cnt=@count output

RETURN

'Programing > 오라클&SQL' 카테고리의 다른 글

저장프로시져 (0)	2016.11.30
오라클 ODBC 연결 프로그램 (0)	2016.11.30
Oracle ODBC 설치 및 연결 (0)	2016.11.30

Hoit Studio

저장프로시져

저장프로시저

저장프로시져

'Programing > 오라클&SQL' 카테고리의 다른 글

저장프로시져

'Programing > 오라클&SQL' 카테고리의 다른 글

+ Recent posts

티스토리툴바