실습 1
/********************
EX21Jdbc.Callable 패키지 -> FunctionCall 클래스에서 사용
JDBC실습을 위한 파일
: 해당 파일은 KOSMO계정에 연결하여 작성합니다.
********************/
/*
함수생성
: 매개변수로 문자열을 받으면 첫문자를 제외한 나머지 부분을
*로 변환하여 변환하는 함수를 작성하시오.
예) john -> j***
*/
create or replace function setAsterik (str in varchar2)
return varchar2
is
begin
/*
morning -> m******
rpad(기준컬럼(문자열), 길이, 채울문자);
*/
return rpad(substr(str, 1,1), length(str), '*');
end;
/
select SetAsterik('morning') from dual;
/*
EX21Jdbc.callable -> InsertProCall.java
예시] member테이블에 새로운 레코드를 입력하는 프로시저 생성
*/
create or replace procedure kosmoinsertMember
(
p_id in varchar2,
p_pass in member.pass%type,
p_name in member.name%type,
returnVal out number
)
is
--변수가 없는 경우 생략가능
begin
/*
insert가 성공하면 입력된 행의 갯수를 반환한다.
만약 실패하면 0을 반환한다.
*/
insert into member (id, name, pass)
values (p_id, p_pass, p_name);
if SQL%found then --적용된 행이 있으면 true 아니면 false
returnVal := SQL%rowcount;
commit;
else
returnVal := 0;
end if;
end;
/
--데이터사전
select * from user_source where name like upper('%kosmo%');
--바인드변수 생성 : 프로시저 실행 후 반환받은 값을 확인하기 위해 생성
variable row_count number;
--프로시저 실행
execute KosmoInsertMember('hong2', '2222', '나이번', :row_count);
--리턴값 확인하기
print row_count;
--member 테이블 레코드 확인하기
select * from member;
/*
Ex21Jdbc.callable -> DeleteProcCall.java
예시] member테이블에 새로운 레코드를 삭제하는 프로시저 생성
매개변수 : in member_id ->아이디 입력, out returnVal -> 성공/실패 반환
반환값 :
삭제성공시 -> "SUCCESS"반환
삭제실패시 -> "FAIL" 반환
*/
create or replace procedure KosmoDeleteMember
(
member_id in member.id%type,
returnVal out varchar2
)
is
--변수생략
begin
--레코드 삭제
delete from member where id = member_id;
-- delete를 실행 후 영향을 받은 레코드(row)가 있는지 확인
if sql%found then
returnVal := 'SUCCESS';
--delete가 성공했다면 실제 테이블에 적용하기 위해 commit 해야함.
commit;
else
returnVal := 'FAIL';
end if;
--예외처리
exception
when others then
begin
--예외가 발생한 경우에도 '실패' 반환함
returnVal := 'FAIL';
end;
end;
/
-- 리턴값 확인위한 바인드변수 생성 : SUCCESS or FAIL 이라는 문자열이 반환되므로
-- 문자타입으로 설정
variable r_val varchar2(10);
-- 프로시저 실행
execute KosmoDeleteMember('hong3', :r_val);
-- 반환값 확인
print r_val;
-- 레코드 확인
select * from member;
/*
Ex21Jdbc.callable -> UpdateProCall.java
예시] member테이블의 레코드를 수정하는 프로시저를 작성하시오.
수정할 아이디와 이름, 패스워드를 입력받는다.
매개변수 :
in m_id, m_pass, m_name
out returnVal
변환값 :
수정성공시 -> 적용된 행의 갯수 반환
수정실패시 -> 0 반환
프로시저명 : KosmoUpdateMember
*/
create or replace procedure KosmoUpdateMember
(
m_id in varchar2,
m_pass in member.pass%type,
m_name in member.name%type,
returnVal out number
)
is
--변수생략
begin
update member set name = m_name, pass = m_pass where id = m_id;
if SQL%Found then
returnVal := SQL%RowCount;
commit;
else
returnVal := 0;
end if;
end;
/
/*
풀이
create or replace procedure KosmoUpdateMember(
m_id in member.id%type
m_pass in member.pass%type,
m_name in member.name%type,
returnVal out number
)
is
begin
update member
set
name=m_name,
pass=m_pass
where
id = m_id;
if SQL%Found then
returnVal := SQL%RowCount;
commit;
else
returnVal :== 0;
end if;
end;
/
*/
/*
Ex21Jdbc.callable -> IsMemberProcCall.java
예시] 아이디와 패스워드를 매개변수로 전달받아서 회원인지 여부를
판단하는 프로시저를 작성하시오. (해당 프로시저는 웹 프로그램에서 회원인증을
위해 활용될 수 있다.)
매개변수 :
in user_id, user_pass
out returnVal
반환값 :
0: 회원인증실패
1: 아이디는 일치하나 패스워드가 틀린 경우
2: 아이디/패스워드 모두 일치하여 회원인증 성공
프로시저명 : Kosmo_Member_Auth
*/
create or replace procedure Kosmo_Member_Auth (
user_id in varchar2,
user_pass in varchar2,
returnVal out number
)
is
--회원인증 여부를 판단하기 위한 변수 생성
temp_count number := 0;
begin
select count(*) into temp_count
from member
where id = user_id;
--회원아이디가 있는 경우
if temp_count=1 then
--회원아이디, 패스워드 2개의 조건으로 재검증
select count(*) into temp_count
from member
where id = user_id and pass=user_pass;
if temp_count = 1 then
--아이디와 패스워드 모두 일치
returnVal := 2;
else
--아이디만 일치, 패스워드는 틀림
returnVal := 1;
end if;
else
--회원아이디가 없는경우
returnVal := 0;
end if;
end;
/
variable return_val number;
execute Kosmo_member_Auth('hong1','1111', :return_val);
print return_val;
variable return_val number;
execute Kosmo_member_Auth('hong2','2345', :return_val);
print return_val;
