회원가입
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 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
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 |
오라오라오라클.zip
