컴퓨터/Web_DB
저장 프로시저 연습 문제
CIY
2011. 11. 2. 12:03
테이블 생성 : member
num 자동증가
id 기본키
pwd
email
hphone
저장 프로시저 생성
1. insert_m
입력 프로시저
4개 데이터 입력
2. select_m
출력 프로시저
3. select_m2
상세 프로시저
id로 검색
4. edit_m
id로 검색
이메일과 핸드폰 번호 변경 설정
5. delete_m
id로 검색 삭제
create table dbo.member
(
num int identity(1,1) not null,
id varchar(20) not null primary key,
pwd varchar(20)not null,
email varchar(50),
hphone varchar(13),
)
--1
create procedure dbo.insert_m
(
@id varchar(20),
@pwd varchar(20),
@email varchar(50),
@hphone varchar(13)
)
as
insert member(id,pwd,email,hphone)
values(@id,@pwd,@email,@hphone)
go
insert_m 'test1','1234','a@a.com','000-0000-0000'
--2
create proc select_m
as
select *
from member
order by num desc
go
exec select_m
--3
alter proc dbo.select_m2 --alter 는 create로 실수로 틀린값을 생성했을때 수정하는 명령이다
@id varchar(20)
as
select *
from member
where id=@id
go
exec select_m2 test1
--4
create proc dbo.edit_m
@email varchar(20),
@hphone varchar(13),
@id varchar(20)
as
begin tran
update member
set email=@email, hphone=@hphone
where id=@id
commit tran
go
exec edit_m 'b@b.com','111-111-1111',test1
--5
create proc dbo.delete_m
@id varchar(20)
with encryption
as
delete member
where id=@id
go
exec delete_m test1