ID 중복체크 프로시져
- exists 로 존재여부 판단
  * 아이디를 찾아서 넘겨줄 필요 없이 DB에서 존재여부만 판단

create proc prc_uidChk
 @uid varchar(20)
as
  if exists(                    -- 존재여부를 판단
              select uid
                 from tb_member
               where uid = @uid)
    select 'X' as uid                 -- 존재하면 X를 반환, as uid는 결과값의 컬럼명을 지정
  else
    select 'O' as uid                 -- 존재하지 않으면 O를 반환

-- 실행문
exec prc_uidChk 'uid'


로그인 체크 프로시져
- exists로 존재여부 판단

* 아이디와 비번을 DB에서 판단 존재여부 체크
* 보통 실행문

select uid, upw
   from tb_member
  where uid = 'user01' and upw='1111'

* 프로시저

create proc prc_MemberLoginChk
@uid varchar(20),
@upw varchar(20)
as
if exists(
  select uid
    from tb_member
  where uid = @uid
      and upw = @upw )
   select  'O'
else ( select 'X' )

-- 실행문
exec prc_MemberLoginChk 'user01', '1111'

방명록 검색
- 테이블명, 컬럼명 같은 객체형은 프로시저의 매개변수로 사용불가.
- declare 이용문장을 변수로 담는다.

create proc prc_GuestSrch
@field varchar(50),
@word varchar(50)
as
  select *
     from tb_guest
   where @field like '%'+@word+'%'
go
  Exec prc_GuestSrch 'gsubject', '오랜만'    -- 실행오류발생

go
  select *
     from tb_guest
go 
  drop proc prc_GuestSrch

- 해결방안
create proc prc_GuestSrch
@field varchar(50),
@word varchar(50)
as
declare @str varchar(255)
set @str = 'select * from tb_guest'
set @str = @str + 'where '+@field+' like ''%'+@word+'%'''
set @str = @str + 'order by reg_dt desc'

exec(@str)
go
Exec prc_GuestSrch 'gsubject', '오랜만'              -- 정상실행


공지사항 검색구현
create proc prc_NoticeSrch
@field varchar(50) ,@word varchar(50)
as

declare @str varchar(255)

  set @str='select * from tb_notice '

  set @str=@str + 'where '+@field+' like ''%'+@word+'%'''

  set @str=@str + 'order by reg_dt desc'

exec(@str)
go
  Exec prc_NoticeSrch 'nsubject', '테'   --정상실행

공지사항 다중검색 구현 (글제목 + 글내용)
--다중검색(or)

create proc prc_NoticeSrch2

 @s_field varchar(50)

 ,@c_field varchar(50)

 ,@word varchar(50)

as

 declare @str varchar(255)

 set @str='select * from tb_notice '

 set @str=@str + 'where '+@s_field+' like ''%'+@word+'%'' '

 set @str=@str + 'or ' +@c_field+' like ''%'+@word+'%'' '

 set @str=@str + 'order by nregdt desc'

 exec(@str)

go

Exec sp_NoticeSrch2 'nsubject','ncontent','ㅎ'  --정상실행


공지사항 단일검색 + 다중검색 구현 (글제목, 글내용, 글제목+글내용)

-- if 문으로 조건

create proc sp_NoticeSrch
@field varchar(50)

, @word varchar(50)

as

declare @str varchar(255)

if @field = 'nsubject'

   set @str ='쿼리문'

else if @field = 'ncontent'

   set @str ='쿼리문' 

else

   set @str ='쿼리문'

Stored Procedure 저장프로시저

프로시저 = 메서드 = 인스턴스 = 함수 = function 같은개념
1, 방명록 리스트

create proc sp_GuestSelect

as

 select *

 from tb_guest

 order by gregdt desc

go

exec sp_GuestSelect      -- 프로시져실행 : exec 생략가능

2, 공지사항 리스트
create proc sp_NoticeSelect

as

 select *

 from tb_notice

 order by nregdt desc
go

exec sp_NoticeSelect               -- 프로시져실행

글쓰기 구현

매개변수를 이용한 저장프로시져
1,방명록 글쓰기
insert into tb_guest(gsubject) value('매개변수')

go

create proc sp_GuestWrite

 @str varchar(255)

as

 insert into tb_guest(gsubject)

  values(@str)
  go

exec sp_GuestWrite '대한민국'     -- 프로시져실행

1,방명록 글쓰기
insert into tb_guest(gsubject) value('매개변수')

go
create proc sp_GuestWrite

 @str varchar(255)

as

 insert into tb_guest(gsubject)

 values(@str)
go

exec sp_GuestWrite '대한민국' --프로시져실행

2,공지사항 글쓰기
create proc sp_NoticeWrite

 @str varchar(255),

 @str1 text

as

 insert into tb_notice(nsubject,ncontent)

 values(@str,@str1)
go

exec sp_NoticeWrite '글제목','글내용' --프로시져실행

 

3,회원가입

sp_help tb_member

go

insert into tb_member(uid,upw,uname,jumin1,jumin2,zipcode,addr,phone,email) values('uid','upw','uname','jumin1','jumin2','zipcode','addr','phone','email')

go

create proc sp_MemberJoin

 @uid   varchar(20)

 ,@upw   varchar(20)

 ,@uname varchar(10)

 ,@jumin1 char(6)

 ,@jumin2 char(7)

 ,@zipcode char(7)

 ,@addr  varchar(255)

 ,@phone varchar(30)

 ,@email  varchar(50)

as

 insert into tb_member(uid,upw,uname,jumin1,jumin2,zipcode,addr,phone,email)

 values(@uid,@upw,@uname,@jumin1,@jumin2,@zipcode,@addr,@phone,@email)
go

exec sp_MemberJoin 'uid','upw','uname','jumin1','jumin2','zipcode','addr','phone','email' --프로시져실행

go

select * from tb_member

 

 

글보기 구현

 

1,공지사항 글보기

sp_help tb_notice

go

select * from tb_notice where notice_idx='idx'

go

create proc sp_NoticeView

 @str varchar(255)

as

 select * from tb_notice

 where notice_idx = @str

 go

exec sp_NoticeView 3 --프로시져실행