SQL PL/SQL 프로시저 4_ 함수와 함수활용법/ 약간의 코드 연구

함수

저장함수는 특정 처리 작업을 자주사용할때  한번에 수행할수 있도록 미리 코드의 로직을 미리 구상하여 만들어놓는다.

함수는 반드시 리턴값이 존재하고, 항상 저장하여 사용한다.

 

sql 함수 만들기 

-저장함수--

--서버 on으로 열어주기--
set serveroutput on;
show serveroutput;


create or replace function func_test1
(p_no number)
return number
as 
v_no number;
begin
v_no := p_no +10;
return v_no;
end;
/

sql에서 함수를 만들때는 create function 함수명을 사용하며 프로시저와 동일하게 괄호를 사용하여 프로시저를 적는다.

함수에는 반드시 리턴값이 존재하기 때문에 파라미터를 정의한 괄호밑에 리턴값을 어떤 자료형으로 받을 것인지 리턴값을 정의한다. 그후 as문을 사용하여 변수가 있다면 변수를 정의하고 begin 실행문 절에 함수에서 사용할 식을 지정해준  후, 리턴값으로

정확하게 받을 값이 무엇인지 정의한후 마무리한다.

실행문에 작성되었으며, 리턴값도 출력하는 값이기때문에 반드시 세미콜론으로 마무리를 해주어야한다..!(이거까먹어서 계속 오류났었음)

위의 쿼리문의 경우에는 in파라미터로 값을 입력받은(in값 생략됨) 수에 10을 더한 값을 반환하는 쿼리문이다.

--함수 실행--

--select문으로 호출--
select func_test1(10) from dual;
select func_test1(2) from dual;

--익명프로시저로 호출--
declare
v_number number;
begin
v_number :=func_test1(100);
dbms_output.put_line(v_number);
end;
/

declare
v_no number;
begin
v_no := func_test1(200);
dbms_output.put_line(v_no);
end;
/

select func_test1(10) from dual;

함수역시 저장프로시저와 동일하게 함수 실행방법이 두가지가 있다. 첫번째는 select 문을 활용하는 방법이다. select절에 함수를 적고 함수옆에 괄호를 사용하여 전달해줄 인수를 전달한 후 테이블은 가상의 테이블인dual테이블을 적는방법이다.

declare v_number number; begin v_number :=func_test1(100); dbms_output.put_line(v_number); end; /

두번째는 익명프로시저로 호출하는 방법인데 declare절에서 변수를 선언해주고 begin실행문절에서 변수안에 함수를 실행했을때 나오는 값을 넣어준후 그 변수를 다시 출력되도록 적는방법이다.

 

 

 

exception사용하기

--함수 생성 실습 / exception사용하기--
create or replace function myfunction
(p_name varchar2)
return varchar2
as
v_job emp.job%type;
begin
select job into v_job from emp where ename=p_name;
return v_job;
exception
when no_data_found then
return '사용자 없음';
end;
/

exception은 예외문이다. 함수나 프로시저의 실행중에 예외의 값이 있을때 설정할 수 있는데 위의 쿼리문에서는 %type을 이용해서 입력되는 이름의 직업을 emp테이블에서 참조하여 가지고오는 쿼리문인데, 만약 입력받는 값중에 emp테이블에 없는 이름이 입력됐다면 사용자없음으로 출력해달라는 예외문으로 설정한것이다. when no_data_found then는 데이터가 없다면이라는 뜻의 옵션이다. 

--함수 실행--
select myfunction('SCOTT')from dual;
select myfunction('KING') from dual;

--익명프로시저로 실행--
declare
v_name varchar2(100);
begin
v_name := myfunction('SCOTT');
dbms_output.put_line(v_name);
end;
/

--익명프로시저 변수 없이 실행--
begin
dbms_output.put_line('SCOTT 직업정보: '||myfunction('SCOTT'));
dbms_output.put_line('KING 직업정보: '||myfunction('KING'));
dbms_output.put_line('JOMI 직업정보: '||myfunction('JOMI'));
end;
/

정상적으로 함수를 실행하게 되면 올바른 직업이 출력되지만 emp테이블에는 없는 JIMI라는 직원의 이름을 함수에 대압하게되면 반환해줄 작업이 없기때문에 예외문으로 처리되어 사용자 없음이 출력된다.

 

 

함수에서 if문 사용하기

--if문 사용하기--
create or replace function mymax
(
p_no1 number,
p_no2 number
)
return number
as
add_num number;
begin
if p_no1 > p_no2 then
add_num := p_no1;
else
add_num := p_no2;
end if;
return add_num; 
end;
/


--조금더 간단하게 작성하기--
create or replace function mymax
(p_no1 number,
p_no2 number)
return number
as
begin
if p_no1 > p_no2 then
return p_no1;
else
return p_no2;
end if;
end;
/

함수에서 if문을 사용할 수 있는데, 입력받는 값을 이용하여 if문에 조건을 걸어, 출력되는 값을 참과 거짓으로 나눠 출력할 수 있다.

위의 쿼리문의 경우 두개의 인수를 입력받고, 그 두개의 인수들 중에서 더 큰값을 출력하도록 하는 쿼리문이다. 

첫번째 쿼리문처럼 변수를 선언하고 if문의 조건에따라 출력될 값을 변수애 저장한 후 저장된 값을 리턴값을 변수로 반환받는 방법이 있으며, 조금더 간단하게 작성하고자 한다면 if 조건문이 실행되면 리턴값으로 바로 반환될 수 있도록 하는 방법이다.

--함수 호출하기--
select mymax(10,23) from dual;

begin
dbms_output.put_line('가장큰 수: '|| mymax(10,100));
end;
/

마찬가지로 select문으로 실행하거나 익명함수에서 실행할 수 있다.

 

 

함수 코드개발연구

if함수코드를 작성하다보니 궁금한점이 생겼다. 둘중 큰 수를출력하는 함수라면 동점이 입력됐을때나 단일if문을 이용하여 예외값으로 다른 값을 출력할 수 있지 않을까?

 

첫번째 코드

--코드연구--
create or replace function mymax
(
p_no1 number,
p_no2 number
)
return number;
as
v_no number;
begin
if p_no1 > p_no2 then
v_no := p_no1;
end if;
return v_no;
exception --특정한상황을 제어하고싶을때는 if else문으로--
--단일if로 참일때만 출력하게 하고 그게 아닐경우 exception으로 출력해보려고했으나, 실패..!--
return p_no2;
end;
/

첫번째 시도해본 코드는 단일if문으로 무조건 참값만 나오도록 했고 조건문에 부합하지 않는 거짓값이 나올경우 exception으로 다른 값을 참조하다록 했으나 출력되지않았다..예외문은 아무때나 쓸수있는게 아니라 쿼리문자체에서 예외상황일경우에만 예외문을 사용해야한다는걸 깨달았다..

 

두번째 코드

--2차 시도--
create or replace function mymax
(
p_no1 number,
p_no2 number
)
return number
as
v_number number;
begin
if p_no1 > p_no2 then
v_number := p_no1;
else
v_number := p_no2;
end if;
return v_number;
exception
when others then
return '숫자가 같습니다'; --return값이 number로 지정되어있어서 실패..!--
end;
/
/*이번에는 if문으로 값을 올바르게 출력한다음에 만약 동점인걸 입력한경우
동점이라고 출력되는 로직을 구성하고싶었으나 return의 정의를 number타입으로 정의해서 안됨..*/

이번에는 동점일경우를 예외문에서 예외처리로 두어 같은 숫자가 입력되었을떄 숫자가 같다는 문구를 출력하려고했으나 이미 프로시저 바로 밑에서 리턴값을 숫자형식으로 정의하였기때문에 예외문에서 문자형 리턴값을 사용할 수 없다는 것이다...

 

세번째 코드

--3차시도--
create or replace function mymax
(
p_no1 number,
p_no2 number
)
return varchar2 --리턴값 자체를 문자로 정의해봄--
as
v_number number;
begin
if p_no1 > p_no2 then
v_number := p_no1;
else
v_number := p_no2;
end if;
return to_char(v_number); --숫자다보니 리턴값을 문자로 출력하고--
exception
when others then
return '동점입니다'; --두개의 수가 같을 경우 동점이라고 출력하는 예외문!--
end;
/
--컴파일은 되었으나, 동점으로 값을 줬을경우 예외문이 출력되지 않음--
--이 부분 조금더 연구 필요..! 그래도 함수가 오류나지 않고 컴파일 됨--

이번에는 sql의 묵시적 형변환의 습성을 이용하여 아예 리턴값을 문자로 정의한 후 변수만 숫자로 정의하여 입력된 파라미터의 값을 to_char함수를 이용하여 문자로 변경하여 리턴값에서 문자를 사용할 수 있도록 했다. 이번에는 문제없이 컴파일됐고 함수의 리턴값도 출력하지만 동점이라는 문구가 출력되지 않는다...ㅠㅠ 궁금해서 찾아보니 exception의 옵션설정을 다른걸로 바꾸거나 if문에서 트리거를 주어 변경하면 될것같은데 이후에도 여러번 시도했으나 게속 출력이 안됐다..좀더 공부하다보면 출력되겠지..!

 

사실 원하는 결과를 출력하진 못했으나 코드연구하는게 재밌고 이 과정에서 함수코드를 엄청 익힐 수 있어서 과정을 한번 올려본다..!

 

---

학습일기

함수는 프로시저와 비슷하면서도 약간 다른부분이 많아 헷갈리는 부분들을 조심해야할거같다. 평소에도 복습한 내용들을 혼자 이것저것 실습코드문을 만들어서 적어보는데, 평소에는 활용느낌이 많이 든다면 오늘 함수를 공부하며 했던 코드연구는 뭔가 학습에 가까운 연구였던것 같다. 처음보는 오류도 엄청났었고 그게 어떤 오류인지 왜 그런 오류가 났는지 찾아가는 과정에서 함수에서 헷갈렸던 부분들을 거의 바로잡을 수 있었다. 아쉽게도 원하는 결과를 출력하진 못했으나 exception 옵션부분은 아직 학습하지 않은 부분들이 많으므로 조금더 공부해보고 다시 시도해봐야겠다..!