PL/SQL (2) 저장프로시저, 커서, 함수

본 포스팅은 Oracle 11g를 이용하고있으며, Tool은 Toad for Oracle 13.4 를 이용했습니다.

PL/SQL 서브 프로그램

💡 데이터베이스 객체로 저장해서 필요할 때마다 호출하여 사용할 수 있는 PL/SQL 블록
익명블록과 달리 저장하여 공유할 수 있으므로 메모리, 성능, 재사용성 등의 장점이 있다.

 

1. 프로시저 : 결과값을 반환하지 않는다. SQL문에서 사용 할 수 없다.
2. 함수 : 리턴 값을 반드시 반환해야 하는 프로그램, SQL문에서 사용 가능 하다.
3. 패키지 : 하나 이상의 프로시저, 함수, 변수, 예외 등의 묶음
4. 트리거 : 지정된 이벤트가 발생하면 자동으로 실행되는 PL/SQL 블록

 

저장 프로시저

매개 변수를 받을 수 있고, 반복해서 사용할 수 있는 이름이 있는 PL/SQL 블록
연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL블록을 DB에 저장하기 위해 생성

 

💡 왜 저장 프로시저를 이용하는 걸까?

정보의 캡슐화
기능 재사용
트랜잭션 제어
DB 내에서 미리 컴파일 되어 저장되므로 필요할 때 마다 매번 다시 변환해야 하는 SQL문 보다 빠르게 실행
저장 프로시저에서 발생하는 문법 오류는 실행 시간이 아닌 컴파일 때 바로 잡을 수 있다.

 

형식

CREATE [OR REPLACE] PROCEDURE 프로시저명
(
파라미터1 데이터타입,
파라미터2 데이터타입, ...
)
IS [AS]
변수 선언
BEGIN
처리할 로직
  EXCEPTION WHEN OTHERS THEN
          예외처리
END;

 

OR REPLACE (선택) : 지정한 이름을 가진 프로시저가 이미 존재하면 현재 작성한 내용으로 대체

IS / AS : 선언부를 시작하기 위해 IS나 AS를 사용

           단, 선언부가 존재하지 않더라도 반드시 명시해야 하며 DECLARE 키워드는 사용하지 않는다.

EXCEPTION (선택) : 예외를 처리하기 위한 예외처리부

 

 

예제

pd2 테이블에 입력하는 프로시저를 생성하자.

--pd2 테이블에 입력하는 프로시저
create or replace procedure pd2_insert
(
--파라미터
--pd2 테이블에 insert할 때 필요한 파라미터들
p_pdcode    char,
p_pdname    varchar2,
p_price     number,
p_company   varchar2
)
is
--변수 선언 (필요없으면 생략 가능)
begin
insert into pd2(no,pdcode,pdname,price,company)
values(pd2_seq.nextval,p_pdcode,p_pdname,p_price,p_company);
commit; --성공하면 커밋
exception when others then
    dbms_output.put_line('pd2 insert error!');
    rollback;   --실패하면 롤백
end;

 

✔ 저장 프로시저 실행시키기

EXECUTE 프로시저이름(파라미터);  -- 입력할때 프로시저에 등록한 파라미터 순에 맞게 입력해야함
또는
EXEC 프로시저이름(파라미터);

실행 예제

execute pd2_insert('D01','컴퓨터',1500000,'HP');
exec pd2_insert('D02','모니터',470000,'LG');

 

예제2

pd2 테이블 컬럼을 수정하는 프로시저를 생성하자.

--pd2 테이블 컬럼을 수정하는 프로시저 만들기
create or replace procedure pd2_update
(
--파라미터  (1)
p_no pd2.no%type,
p_pdcode pd2.pdcode%type,
p_pdname pd2.pdname%type,
p_price pd2.price%type,
p_company pd2.company%type
)
is
--변수 선언부
v_cnt number(3);
begin
select count(*) into v_cnt
from pd2
where no=p_no;  --(2)
--해당 데이터가 존재하면 update
if v_cnt>0 then
    update pd2
    set pdcode=p_pdcode, pdname=p_pdname,price=p_price,
        company=p_company
    where no=p_no; --(3)
end if;

commit;

exception when others then
    dbms_output.put_line('pd2 update error!');
    rollback;
end;

-실행
exec pd2_update(2, 'B03', 'usb', 31000, '삼성');

 

(1) 테이블명.컬럼명%type    해당 테이블의 해당 컬럼과 동일한 데이터 타입

즉, pd2 테이블의 no 컬럼과 동일한 타입 // 굳이 type이 무엇인지 직접 안찾아내도 된다.

 

(2) 일부러 복잡하게 두었는데, 이는 복잡할때 프로시저를 많이 쓰기때문에 연습용이다.

no=p_no 표시 꼭 해주자.

그래야 (3) 에서 구별이 가능 하기 때문이다.

 

 

예제3

exists 이용해서 수정하는 프로시저를 생성해보자.

create or replace procedure pd2_update2
(
--파라미터
p_no pd2.no%type,
p_pdcode pd2.pdcode%type,
p_pdname pd2.pdname%type,
p_price pd2.price%type,
p_company pd2.company%type
)
is
begin
update pd2 a
set pdcode=p_pdcode, pdname=p_pdname, price=p_price, company=p_company
where exists(select 1 from pd2 b where b.no=a.no and b.no=p_no); --(1)
commit;
exception when others then
    raise_application_error(-20001, 'pd2 update중 예외발생!'); -- (2)
    rollback;
end;

--실행
exec pd2_update2(4, 'D04', '키보드', -500, 'qnix');
-->error
exec pd2_update2(4, 'D04', '키보드', 19000, 'qnix');

(1) 파라미터의 no가 존재한다면 update 해라! (b.no=a.no 그리고 b.no가 p_no => 존재하는지 여부 확인)

 

(2) 프로시저는 다른언어에도 쓰기때문에, raise_application_error 라고 써준다.

사용자 정의 예외번호는 -20001 ~ 20999 까지 있다.

 

💡 일반적으로 프로시저는 '변경' 에 대해서 쓴다.

 

%rowtype

%type과 유사하나, 한 개 이상의 값에 대해 적용
로우타입 변수를 선언해 테이블에 있는 row 대입 가능

 

예제

create or replace procedure prof_info
(
p_profno professor.profno%type
)
is
v_prof_row professor%rowtype; --(1)
v_result varchar2(2000);
begin
select * into v_prof_row
from professor
where profno=p_profno;
v_result := v_prof_row.profno || ' ' || v_prof_row.name || ' ' ||
        v_prof_row.position || ' ' ||
        (v_prof_row.pay + nvl(v_prof_row.bonus,0)); --(2)
    
dbms_output.put_line(v_result);

exception when others then
    raise_application_error(-20002, 'professor 테이블 조회 에러!');
end;

--실행
exec prof_info(1003);

(1) rofessor 테이블 한개의 row(레코드) 정보를 담을 수 있는 타입

 

(2) 읽어올때는 . 으로 하나하나 접근해서 || ' ' || 로 연결

 

사용자 정의 예외

자바에서 강제예외발생 시킨 것 과 비슷하다!

create or replace procedure member_insert
(
p_name member.name%type,
p_jumin member.jumin%type,
p_passwd member.passwd%type,
p_id member.id%type
)
is
system_check_insert_fail exception; --(1)
begin
if to_char(sysdate, 'd') = 1 and to_char(sysdate, 'HH24')=23 then --(2)
raise system_check_insert_fail; --(3)
end if;
insert into member(no, name, jumin, passwd, id)
values(member_seq.nextval, p_name, p_jumin, p_passwd, p_id);
commit;
exception when system_check_insert_fail then
    raise_application_error(-20998,
        '일요일 23:00:00~23:59:59 사이에는 시스템 점검작업으로 서비스 이용불가');
    rollback;
end;

--실행
exec member_insert('홍길동2', '9807091112222', '123', 'hong2');

 

(1) 사용자 정의 예외 // 사용자가 이름 짓고 exception

(2) 일요일 23:00:00 ~ 23:59:59 사이에는 시스템 작업으로 인해 입력 불가 ( d=1 일요일, hh24 23시 )

(3) 사용자 정의 예외 발생시키기 (raise 내가만든 사용자 정의 예외 이름, 자바에서의 throws와 같음)

 

 

out 매개변수 와 in 매개변수

out 매개변수

결과를 출력하는 용도의 매개변수

프로시저는 리턴용으로 out 매개변수 이용한다

 

in 매개변수

일반적인 매개변수, 입력용 매개변수

생략하면 in 매개변수

 

예제

select 프로시저

create or replace procedure prof_info2
(
p_profno    in  professor.profno%type,  --in 매개변수 - in : 입력용
o_name      out professor.name%type,    --out 매개변수 - out : 출력용
o_pay       out professor.pay%type      --out 매개변수
)
is
begin
select name, pay into o_name, o_pay --결과값을 담음 (out 매개변수)
from professor
where profno=p_profno;

exception when others then
    raise_application_error(-20003, 'professor 조회 에러');
end;

 

✔ 짚고 넘어가자

여러개를 record에 넣으려고 할때는 INTO 절을 이용해서 결과를 담아야 한다.
SELECT다음에 기술한 칼럼은 INTO 절에 있는 변수와 1:1로 대응해야하기 때문에 
개수와 데이터 타입, 길이를 일치시켜야한다.

SELECT 컬럼 INTO 담을 변수

 

PL/SQL 커서

🔎쿼리에 의해 반환되는 결과는 메모리 상에 위치 하게 되는데, PL/SQL 에서는 커서를 사용하여 이 결과 집합에 접근 가능.
즉, 커서를 사용하면 결과집합의 각 개별 데이터에 접근이 가능하다.

 

명시적 커서

🔎 사용자가 직접 쿼리의 결과에 접근해서 이를 사용하기 위해 명시적으로 선언한 커서

 

명시적 커서를 사용하기 위한 절차

[1] 커서 선언 - 쿼리 정의 cursor 커서명 is select 문장;

[2] 커서 열기(open) - 쿼리 실행 open 커서명;

[3] 패치(fetch) - 쿼리의 결과에 접근, 루프를 돌며 개별 값들에 접근
fetch 커서명 into 변수 ... ;

[4] 커서 닫기[close] - 메모리상에 존재하는 쿼리의 결과를 소멸시킴 close 커서명;

 

예시

create or replace procedure pd2_select
is
--[1] 커서 선언
cursor pd2_csr is
select no, pdcode, pdname, price from pd2;
-- 변수 선언
pd2_rcd     pd2%rowtype;    
begin
--[2] 커서 열기
open pd2_csr;
--[3] 패치 - 쿼리의 결과에 접근, 루프를 돌며 개별 값 들에 접근
loop
    fetch pd2_csr into pd2_rcd.no, pd2_rcd.pdcode, pd2_rcd.pdname,
        pd2_rcd.price;
        
    exit when pd2_csr %notfound;
    
    dbms_output.put_line(pd2_rcd.no || ' ' || pd2_rcd.pdcode || ' ' ||
        pd2_rcd.pdname || ' ' || pd2_rcd.price);
end loop;

--[4] 커서 닫기
close pd2_csr;

exception when others then
    raise_application_error(-20006, 'pd2 select error');
end;

 

실행
exec pd2_select();

실행 결과

 

 

묵시적 커서 

🔎 오라클 내부에서 각 쿼리 결과에 접근하여 사용하기 위한 내부적 커서

• 모든 쿼리가 실행될 때마다 오픈된다.
• 오라클 내부에서 접근하고 사용되는 커서이므로 선언, 오픈 등의 작업을 할 필요가 없다.
• 항상 가장 최근에 실행된 sql 문장에 대한 커서를 내부적으로 갖고 있는데 , 이를 sql 커서라 하며, 'SQL' 이라는 이름으로 속성에 접근할 수 있다.

 

 

%NOTFOUND

• 커서에서만 사용 가능한 속성
• 더 이상 패치(할당)할 로우가 없음을 의미 (출력할거 다 출력함)
• 쿼리의 마지막 결과까지 패치한 후에 자동으로 루프를 빠져나가게 됨

 

for loop cursor 문

🔎 커서의 for loop문을 사용하면 커서의 open, fetch, close 가 자동적으로 발생 되어지기 때문에 open, fetch, close문을 기술할 필요가 없다

형식

for 변수명 (레코드 정보가 담기는 변수) in 커서명 loop
    실행문장;
end loop;

 

예시

create or replace procedure pd2_select2
is
cursor pd2_csr is select no, pdcode, pdname, price from pd2; --(1)
begin
for pd2_row in pd2_csr loop --(2)
dbms_output.put_line(pd2_row.no || ' ' || pd2_row.pdcode || ' ' ||
pd2_row.pdname || ' ' || pd2_row.price);
end loop;
exception when others then
    raise_application_error(-20007, 'pd2 select error');
end;

 

(1) 커서 선언

(2) 레코드 단위로 읽기 가능. pd2_row라는 변수를 선언 할 필요 없이 바로 작성해도 된다.

--실행
exec pd2_select2;

 

실행 결과

 

sys_refcursor

저장 프로시저의 select결과물을 java에서 읽기 위해서는 sys_refcursor 타입을 사용해야 함

(java에서는 dbms_output.put_line이 없으니까...)

 

create or replace procedure pd2_select3
(
pd2_cursor out SYS_REFCURSOR --(1)
)
is
begin
open pd2_cursor for --(2)
select no, pdcode, pdname, price, company, regdate
from pd2;
exception when others then
    raise_application_error(-20009, 'pd2테이블 조회중 에러!');
end;

 

(1) select 결과물을 담아서 출력해야 하므로 out 매개변수 사용해야 한다.

(2) open for 이용. select 결과값이 out으로 넘어감 → java에서 사용 가능해진다.

 

 

---

 

함수

사용자 정의 함수를 뜻한다.
특정 기능을 수행한 뒤, 결과값을 반환하는 서브 프로그램

 

형식

create or replace function 함수명
(
파라미터1 데이터타입,
파라미터2 데이터타입,...
)
return 데이터타입 --return 해주는것의 자료형
is 또는 as
변수선언
begin
처리할 로직
    exception when others then
        예외처리할 문장
end;

 

예제

주민번호를 넣으면 성별을 리턴하는 함수

create or replace function get_gender
(
p_ssn varchar2 --(1)
)
return varchar2 --(2)
is
--변수선언
v_gender varchar2(10);
begin
--처리할 로직
select case when substr(p_ssn, 7, 1) in ('1','3') then '남자'
else '여자' end
into v_gender --(3)
from dual;
return v_gender;
exception when others then
    dbms_output.put_line('error');
end;

(1) 파라미터 (주민번호, 단 이때 varchar2() < 처럼 () 쓰면 안된다.)

(2) 반환타입 (성별의 자료형)

(3) case에 나온 결과를 into →  v_gender에 넣어라.

 

함수 실행

select get_gender('9901051112222') from dual;

 

예제 2

주민번호를 이용해서 나이를 구하는 함수 만들기

create or replace function get_age
(
--파라미터 (주민번호)
p_ssn varchar2
)
return number --반환타입 (나이)
is
--변수선언
v_age number(3); --(나이)
begin
--처리할 로직
select extract(year from sysdate)
-(substr(p_ssn,1,2) + case when substr(p_ssn,7,1) in ('1','2') then 1900
else 2000 end) + 1
into v_age --(결과를 v_age에 넣는다)
from dual;
return v_age;   --반환
                            
exception when others then
    dbms_output.put_line('error');
end;


--함수 실행
select get_age('0006073334444') from dual;

 

---

 

처음 배웠을때에는 이걸 쓸 일이 있을까..? 했는데, 학원 프로젝트 하면서 프로시저와 함수를 무조건 하나는 썼었다.

특히, 파이널 프로젝트 할때에는 엑셀파일을 업로드 함과 동시에 두개의 테이블에 insert 하는 과정을 거쳤어야했는데,

sequence 를 넣어주었는데 이를 어떻게 처리해야하나 서칭을 해본 결과 요행으로 함수를 적용하는 방법이 있다고 해서

그때 함수를 생성해서 넣어주었었다...

 

자세한 내용은 다음에 포스팅을 해보도록 하고, 그당시 내가 썼었던 코드를 잠깐 보여주자면,

 

create or replace
FUNCTION GET_hMemNo RETURN NUMBER AS
no NUMBER;
BEGIN
  SELECT household_member_seq.nextval
  INTO no
  FROM dual;
  return no;
END GET_hMemNo;

- 당시 작성한 함수. seqeunce의 다음 값을 얻고자 했다. 결과값이 여러개가 나올 수 있으므로 INTO를 이용했다.

 

	<insert id="insertExcelCode" parameterType="map">
		insert into household_info(
			household_code,
			apt_no,
			dong,
			ho, 
			auth_code
		)
		<foreach collection="excelContent" item="item" separator="union all">
		select
			#{item.A},
			#{aptNo},
			#{item.B},
			#{item.C},
			'RESIDENT'
		from dual
		</foreach>
	</insert>

	<insert id="insertExcelCodeMem" parameterType="map">
		insert into household_member(
			h_member_no,
			household_code,
			h_memb_name,
			birth,
			relation
		)
		<foreach collection="excelContent" item="item" separator="union all">
		select
			GET_hMemNo(),
			#{item.A},
			#{item.D},
			#{item.E},
			#{item.F}
		from dual
		</foreach>
	</insert>

당시 매퍼 코드이다.

 

다음 포스팅은 트리거에 대해 포스팅 해보도록 하며 이만 마치겠다.