10/21 26-2 Procedure
쓰임을 먼저 알아야지.. 이거도 테이블에 자료를 넣거나 수정하거나 지우는 일을 할 때 사용한다!! 사용방법은 자바에서 메소드 호출하는 것과 유사 하다!
Store Procedure
프로시저는 RETURN문이 없다 ...근데 리턴 함 뭐로? OUT 매개변수로 값을 RETURN한다.
리턴이나 OUT 이나 역할은 같은 거 같은디 걍 통일해서 좀 쓰지...
저장 프로시져(STORED PROCEDURE)의 장점 .....근데 나는 너가 시져시져....
1. 매우 좋은 성능 -- 한 번 파싱(분석)한 쿼리는 , 또 해석하지 않고 (최초 1번만 파싱 함) 같은 파싱 요청 시, 그 해석 된 결과만 응답 해 줌. 계속 파싱하지 않아서 성능이 좋대요
2. 보안성을 높일 수 있음.
3. 다양한 처리가 가능
4. 네트워크의 부하를 줄일 수 있음. => 1번이랑 비슷한 내용!
[프로시저 형식]
CREATE OR REPLACE PROCEDURE 프로시저명
[(
매개변수 IN 자료형, ---크기 지정 안함,변수생략 불가!
매개변수 OUT 자료형, ---출력용 RETURN 같은 역할
매개변수 IN OUT 자료형 ---입/출력용 EXEC시 숫자 전달 불가능 그래서 잘 안씀 ,역시 변수 전달
----리턴변수선언 없음!
)]
IS
[변수선언]
BEGIN
........ 마찬가지로 리턴없고
END;
/
[실행할 땐]
EXECUTE 프로시저명[(인수1,인수2,..)]
*** 이미 써 왔지만 그래도 다시 정리하면 매개변수의 자료형 지정 시 해당 테이블의 컬럼과 같은
자료형으로 지정하고 싶을 때 자료형을 테이블명.컬럼명%TYPE 요렇게 지정한다.
백문이불여일견
CREATE OR REPLACE PROCEDURE sp_ins_member
(
--매개변수 정의!
id IN member.username%TYPE,
pass IN member.password%TYPE,
name IN member.name%TYPE,
rtval OUT NVARCHAR2
)
IS
BEGIN
INSERT INTO member(username,password,name)
values(id,pass,name);
IF sql%found THEN
rtval := '입력성공';
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- 실행이 1개밖에 없어 의미 없지만 그래도 써놔
rtval := '입력실패: 입력값이 중복이거나 너무 길어요';
END;
/
==> 출력 고고
VAR RT NVARCHAR2(100) ==> 바인드 변수 먼저 선언 해 주고!
EXEC sp_ins_member('KIM','1234','박길동', :RT);
=> 요렇게 넣음 ! EXECUT 안에서 바인드변수에 꼭 : 붙여!! 함수랑 조금 다르니까 잘 익혀둬!!
하지만 또 안찾아 보겠지.... 함수는 EXECUTE :RTVAL := 함수명(매개변수1[,매개변수2,..]); 비교해....
PRINT RT => 입력 성공 했는지 확인!
SELECT* FROM MEMBER; 이거 출력해서 들어가 있는 지 확인 하면 됨
UPDATE PROCEDURE
입력 된 놈들 수정을 해 보자
CREATE OR REPLACE PROCEDURE sp_UDT_member
(
--매개변수 정의!
id IN member.username%TYPE,
pass IN member.password%TYPE,
name_ IN member.name%TYPE,
rtval OUT NCHAR
)
IS
BEGIN
------수정 시 모두 변수로 처리됨, 그래서 컬럼명과 변수 다르게 해야함
UPDATE member SET password=pass,name=name_
WHERE username = id;
IF sql%found THEN
rtval := '입력성공';
COMMIT;
ELSE -- 존재하지 않은 id로 수정 시
rtval := 'ID가 존재하지 않아요';
END iF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;-- 실행이 1개밖에 없어 의미 없지만 그래도 써놔
rtval := '입력실패: 입력값이 너무 길어요';
END;
/
==> 정상적으로 수정
exec sp_UDT_member('choi','4321','최영',:RT);
PRINT RT
--에러도 해보자
exec sp_UDT_member('KIM2','4321','이길동',:RT); --> ID 없음
PRINT RT
exec sp_UDT_member('PAKR','1233456789421','이길동',:RT); --> 넘 길어
PRINT RT
SELECT * FROM MEMBER; 마찬가지로 잘 수정 되었는 지 에러난 거 수정 안된 거 맞는 지 확인!
DELET PROCEDURE
삭제 고고
CREATE OR REPLACE PROCEDURE sp_DEL_member
(
--매개변수 정의!
id IN member.username%TYPE,
rtval OUT NUMBER ==> 아니 이거 대소문자 구분안된다고 한다더니!!! 변수명 구분됨.. 이거땜에 겁나 헛짓함 ㅠㅠ
)
IS
BEGIN
DELETE member WHERE USERNAME = ID;
IF sql%found THEN
DBMS_OUTPUT.PUT_LINE(ID||'가 삭제되었습니다.');
rtval := SQL%ROWCOUNT;
COMMIT;
else -- 존재하지 않은 id로 수정 시
DBMS_OUTPUT.PUT_LINE(ID||'라는 ID가 존재하지 않아요');
rtval := SQL%ROWCOUNT;
END iF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;-- 실행이 1개밖에 없어 의미 없지만 그래도 써놔
rtval := -1 ;
DBMS_OUTPUT.PUT_LINE('자식이 참조 중...');
END;
/
자식 참조하는 에러도 봐야하니까 자식 만들자!!!
CREATE TABLE BBS(
NO NUMBER PRIMARY KEY,
TITLE NVARCHAR2(10) NOT NULL,
USERNAME VARCHAR2(10) REFERENCES MEMBER(USERNAME),
POSTDATE DATE DEFAULT SYSDATE
);
자식에 데이터 좀 넣고
INSERT INTO BBS VALUES(SEQ_BBS.NEXTVAL,'제목1','KIM',SYSDATE);
INSERT INTO BBS VALUES(SEQ_BBS.NEXTVAL,'제목1','PARK',SYSDATE);
SELECT * FROM BBS;
삭제프로시져 실행
VAR RTNUM NUMBER
EXEC SP_DEL_MEMBER('PARK',:RTNUM); => 삭제 GOOD
PRINT RTNUM
SELECT * FROM MEMBER;
EXEC SP_DEL_MEMBER('KOSMO',:RTNUM); ==> 회원이 없는걸?
PRINT RTNUM
EXEC SP_DEL_MEMBER('KIM',:RTNUM); == > 자식 참고중이라 삭제 노노
PRINT RTNUM
+++ 확장 회원인지 아닌지 확인하는 프로시저어어어어어
ISMEMBER PROCEDURE
CREATE OR REPLACE PROCEDURE sp_ISMEMBER
(
id member.username%TYPE, => 이건 값을 넣거나 수정하거나 지우는 게 아니니 까 IN을 넣지 않아요!!
pass member.password%TYPE,
rtval OUT number
)
IS
flag number(1):= 0;
BEGIN
select count(*) INTO flag from member
where username = id;
if flag = 0 then -- 아이디 없음
rtval := -1;
else -- 아이디 있음(비번일치여부)
select count(*) INTO flag from member
where username = id and password = pass ;
if flag = 0 then --불일치
rtval := 0;
else -- 회원
rtval := 1;
end if;
end if;
END;
/
==>적용 해봐용
exec sp_ISMEMBER('KIM', '1234',:rtnum); -- 비회원
print rtnum
exec sp_ISMEMBER('park', '1234',:rtnum); ---불일치
print rtnum
exec sp_ISMEMBER('KIM', '1',:rtnum); -- 회원
print rtnum