Stored Procedure 저장프로시저
형식)
create proc 프로시저이름
as
구문입력
drop proc list_zipcode --삭제
create proc List_zipcode --생성
as
select * from w_zipcode
--실행
exec List_zipcode
*매개변수 이용한 저장 프로시저
create proc List1_zipcode
as
select * from w_zipcode where sido='서울'
exec list1_zipcode
declare @a int,@b int
set @a=3
set @b=4
select @a+@b
create proc list2_zip
@city varchar(10) --매개변수 선언
as
select * from w_zipcode where sido=@city
exec list2_zip '제주' --함수호출
exec list3_zip '서울', '영등포구' --프로시저 작성하시오
create proc list3_zip
@city1 varchar(10),@city2 varchar(10)
as
select * from w_zipcode
where sido=@city1 and gugun=@city2
select * from tb_mem
create table tb_mem
(
uid varchar(10),
uname varchar(10),
uphone varchar(10),
uaddr varchar(10)
)
create proc Ins_mem
@id varchar(10),@name varchar(10),
@phone varchar(10),@addr varchar(10)
as
insert into tb_mem(uid,uname,uphone,uaddr)
values(@id,@name,@phone,@addr)
exec Ins_mem 'kim5','홍길동','444-4444','제주'
insert tb_mem(uid,uname,uphone,uaddr)
values ('kim2','홍길동','제주','444-4444')
select * from tb_member
--회원테이블 insert 프로시저 작성
create proc Ins_member
@id varchar(20),
@pw varchar(10),
@name varchar(20),
@jumin1 char(6),
@jumin2 char(7),
@zip char(7),
@addr varchar(255),
@phone varchar(20),
@email varchar(50)
as
insert into tb_member
(uid,upw,uname,jumin1,jumin2,uzip,uaddr,uphone,uemail)
values(@id,@pw,@name,@jumin1,@jumin2,@zip,@addr,
@phone,@email)
select * from tb_member
[입력 stored procedure]
exec Ins_member 'lee','1234','홍길동',''.......
[상세보기 stored procedure]
exec View_member 'kim5','1234'
create proc View_member
@id varchar(20),@pw varchar(20)
as
select * from tb_member
where uid=@id and upw=@pw
[수정 stored procedure]
exec Edit_member 13,'kim5','1234' --아이디/비밀번호수정하기
create proc Edit_member
@idx int, @id varchar(20),@pw varchar(20)
as
update tb_member
set uid=@id,upw=@pw
where member_idx=@idx
[삭제 stored procedure]
exec Del_member 'kim5','1234'
create proc Del_member
@id varchar(20),@pw varchar(20)
as
delete from tb_member
where uid=@id and upw=@pw
select 'dfddd'+'DDD'
select ''''
create proc Search_zip
@dong varchar(20)
as
select * from w_zipcode
where dong like '%' + @dong + '%'
[동적SQL문장]
테이블명 같은 객체형값(object value)는 프로시저의 매개변수로
사용불가.
exec List_page 'w_zipcode','dong'
create proc List_page
@tb varchar(50),@col varchar(50)
as
select @col from @tb