25.[오라클]프로시저 정리

>[프로시저 생성 문법 정리]

---

CREATE OR REPLACE PROCEDURE 프로시저 이름

      (매개변수명1[IN |OUT |IN OUT]) 데이터 타입[:=디폴트값]

       , 매개변수명2[IN |OUT |IN OUT]) 데이터 타입[:=디폴트값]

       ~~~~~~~~~)

IS[AS]

     변수, 상수등을 선언

BEGIN

     실행부

[EXCEPTION 예외처리부] 

END [프로시저 이름];

---

• CREATE OR REPLACE PROCEDURE 프로시저 이름 : 프로시저 생성
• 매개변수 : IN은 입력,OUT은 출력 IN OUT은 입력과 출력을 동시에 진행  
  (미명시할 경우 디폴트로 IN)

 

※SQL문으로 프로시저 생성하기

CREATE OR REPLACE PROCEDURE KSW.TEST_PRO(
    PARAM_JOB_ID     IN JOBS.JOB_ID%TYPE 
   ,PARAM_JOB_TITLE  IN JOBS.JOB_TITLE%TYPE
   ,PARAM_MIN_SALARY IN JOBS.MIN_SALARY%TYPE
   ,PARAM_MAX_SALARY IN JOBS.MAX_SALARY%TYPE
)
IS 
BEGIN 
INSERT INTO JOBS VALUES(
                            PARAM_JOB_ID    
                           ,PARAM_JOB_TITLE 
                           ,PARAM_MIN_SALARY
                           ,PARAM_MAX_SALARY
                           ,SYSDATE
                           ,SYSDATE
                           );
COMMIT;
END;

※아래와 같이 툴을 사용하여 프로시저를 생성해서 써도 된다.(단 오류가 발생할 경우 찾기가 어려우니 참고..)

 

 

>[프로시저 실행]

• SQLPLUS : EXEC 또는 EXECUTE 프로시저명(매개변수1 값,매개변수2 값,...);
• 툴에서 사용 : CALL 프로시저명(매개변수1 값,매개변수2 값.....);

CALL TEST_PRO('TEST_ID','TEST_TITLE',1,5); 처럼 실행할 경우에는 매개변수가 많으면 프로시저 내 순서가 변경될경우나 갯수가 변경되면 많은 혼동이 온다.. 이런경우 매개변수를 선언한 값을 아래와 같이 설정하면 매우 편리하다.

 

매개변수 선언 후 PARAM값을 셋팅한 부분으로 실행

CALL TEST_PRO(
 PARAM_JOB_ID      => 'TEST_ID'  
,PARAM_JOB_TITLE   => 'TEST_TITLE'
,PARAM_MIN_SALARY  => 1000
,PARAM_MAX_SALARY  => 5000
);

 

여기서 주의할 점은 프로시저만 실행할 경우 위와 같이 하면 되지만 익명의 블록,함수,프로시저에서 실행할 경우 CALL,EXEC,EXECUTE를 붙여 실행하면 아래와 같은 오류가 발생을 한다.

익명의 블록에서 프로시저 실행시 오류 발생

익명의 익명의 블록,함수,프로시저에서 실행할 경우에는 CALL및 EXEC,EXECUTE를 제외한 후 실행을 해야 정상적으로 실행이 된다.

CALL,EXEC,EXECUTE제외후 실행했을때

>[프로시저 IN,OUT,INOUT 정리]

IN,OUT,INOUT부분에 대한 프로시저 생성 및 실행

 

위의 이미지와 같이 프로시저를 생성한 후 DECLARE를 써서 값을 A,B,C로 준 다음 프로시저를 실행. 실행 후 IN,OUT,INOUT에 대한 테스트를 했다. 실행 결과를 보면 프로시저에서 IN,INOUT에 대한 부분만 정상적으로 찍혔고 OUT부분에 대한 부분은 찍히지 않았다.

 그리고 OUT,INOUT부분에 데이터를 변경한 후 쿼리내에서 PA_VAR2,PA_VAR3에 대한 결과를 확인하니 프로시저에서 변경한 값이 나온걸 확인 할 수 있다.

정리 하자면

• INㅁ개변수는 참조만 가능하며 값을 할당할 수 없다.
• OUT매개변수에 값을 전달할 수는 있지만 의미는 없다.
• OUT,INOUT매개변수에는 디폴트 값을 설정할수 없다.
• IN매개변수에는 변수나 상수,각 데이터 유형에 따른 값을 전달할수 있지만 OUT,INOUT매개변수를 절달시 반드시 변수와 같은 형태로 값을 넘겨야 한다.

>[프로시저 RETURN문 정리]

함수에서는 연산 수행후 결과값을 반환하는역할을 하시지만 프로시저에서는 로직을 처리하지 않고 수행종료 후 프로시저를 빠져나가버린다.

RETURN용 프로시저 생성

 

RETURN용 프로시저 생성 후 실행

위와 같이 최소 값을 500으로한 다음 프로시저에서는 최소값 12345를 넘는지 IF문에서 확인한다.

확인 후 PARAM값이 12345보다 크지 않아 메시지를 출력한 다음 프로시저를 빠져나오는 것을 확인할수 있다.

 

※프로시저에서 명명기법 정리

변수(variable)	대부분 개발을 할때 접두어나 줄임말로 해서 처리를 한다. (예)v_emp_name,v_emp_id처럼) 그중 변수에서 문자인지 숫자인지 날짜인지에 따라 뒤에다 붙여주면 나중에 어떤 타입인지 찾기가 쉬우니 해당 형식으로 하는것도 나쁘지 않을것 같다. 문자 : vs_emp_name 숫자 : vn_emp_cnt 날짜 : vd_emp_date
상수 (constant)	상수또한 접두어나 줄임말로 사용하면 좋을듯 문자 : cs_emp_name 숫자 : cn_emp_cnt 날짜 : 상수이므로 제외
매개변수 (parameter)	매개변수또한 접두어나  줄임말로 사용하면 좋을듯 문자 : ps_emp_name 숫자 : pn_emp_cnt 날짜 : pd_emp_date
함수 (function)	함수 같은 경우는 호출 부호를 붙여 사용하고 마지막에 function의 약자인 fn을 붙여주면 좋음 get_test_fn,get_my_empty_fn형식과 같음
프로시저 (procedure)	프로시저는 맨뒤에 약자를 붙여주면 좋다. 읽기 : red_my_profile_proc 등록 : ins_my_profile_proc 수정 : upd_my_profile_proc 삭제 : del_my_profile_proc 읽기,등록,수정,삭제 : riud_my_profile_proc 와 같이 쓰면 좋을듯?(정답은 없음)

 

명명 기법에 대한 정답은 없다.. 실제로 개발 당시 cnt는 count의 약자로 쓰일수 있으나 content에 대한 약자로 쓸수 있기 때문에 사람들이 인식을 쉽게 할수 있게 하는게 좋다.(예전 내가 그랬다....욕 엄청 먹었지....)

 

 

To.예전에 개발할때 프로시저를 사용하여 개발을 진행해 본적이 있었다.. 프로시저에 프로시저가 겹겹이 호출하는 형식이라 해당 프로시저를 따라가고 그안에 프로시저를 또 따라가는 형식으로 들어갔었는데.... 엄청나게 꼬아나서 찾는게 힘들었다.....

 

프로시저나 function,view를 사용할때 주석좀 달아넣는 습관을 들이면 뒤에 있는 개발자가 실행 후 어떤 문법인지 찾기 쉬워지니....간단하게라도 주석을 다는 습관을 들이는게 좋을듯하다..(참고로 예전에 어떤분이 주석을 많이 달면 속도가 느려진다니 뭐라니하는데....미친듯이 주석을 다는게 아니면 그리 느려지지 않으니...쫌 달자..주석좀....)