IT & Security/DB

[Oracle] 프로시저 | 사용자 정의함수 | 함수 만들기

*무결성의

무결성의 customizing

  client < ------------ > dbms < ------------ > server

1. DDL문 : 선언적인 방법 (table, view...)  ===> server

2. DML문 : transaction + 프로그래밍방법

3. SP(Stored Procedure) : 프로그래밍방법, 성능 + 자동 X ===> server

4. trigger(방아쇠) : 프로그래밍 방법, 성능 -, 자동 O  ===> server

5. index : 검색성능 +, 갱신성능 -  ===> server

6. DCL문

프로시저 랭기지(pl/sql)

예시 코드

show user
--환경변수 화면출력을 하려면 on이 되어야함
show serveroutput 
set serveroutput on 
--1. 1부터 10까지 자연수 합계를 구해라
-- 선언문
declare 
    v_num int;
    -- 할당문 ':='  파스칼 랭기지에서 유래된 것으로 초기값을 의미한다.
    v_sum int:=0;
begin
    v_num:= 1;
    while v_num <=10
        loop
         v_sum:=v_sum+v_num;
         v_num:=v_num+1;
        end loop;   
        -- 아래는 오라클에서 제공하는 패키지 이름 - 몇가지 함수가 있음
    dbms_output.put_line(v_sum); --문자열은 쓸 수 없나요?
--전체 중 에러가 생긴다면 (exception)
exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm); --error code name
-- sqlerrm : 정상 종료
-- others : 비정상 종료 시킴
end;
/

아래와 같이 if else 문으로 제어해서 loop를 걸어줄 수도 있다.

declare 
    v_num int;
    -- 할당문 ':='  파스칼 랭기지에서 유래된 것으로 초기값을 의미한다.
    v_sum int:=0;
begin
    v_num:= 1;
        loop
         v_sum:=v_sum+v_num;
         if v_num >= 10 then
         	exit;
         -- elseif then
         else
         v_num:=v_num+1;
        end if;
        end loop;
        -- 아래는 오라클에서 제공하는 패키지 이름 - 몇가지 함수가 있음
    dbms_output.put_line(v_sum); --문자열은 쓸 수 없나요?
--전체 중 에러가 생긴다면 (exception)
exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm); --error code name
-- sqlerrm : 정상 종료
-- others : 비정상 종료 시킴
end;
/

-- sqlerrm : 정상 종료 시킴 (롤백을 내가 정할 수 있음)
-- others : 비정상 종료 시킴 -> 하던 행위를 그냥 종료 시킴

아래는 3의 배수만 더하는 예제이다.

declare 
    v_num int;
    -- 할당문 ':='  파스칼 랭기지에서 유래된 것으로 초기값을 의미한다.
    v_sum int:=0;
begin
    v_num:= 3;
        loop
         if v_num >= 20 then
            
         	exit;
         -- elseif then
         else
         dbms_output.put_line(to_char(v_sum)||'+'||to_char(v_num));
         v_sum:=v_sum+v_num;
         v_num:=v_num+3;
        end if;
        end loop;
    dbms_output.put_line('total : '||to_char(v_sum));
    dbms_output.put_line('last number : '||to_char(v_num));
exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm); 
end;
/

조금 다른 방법으로 접근

declare 
    v_num int;
    -- 할당문 ':='  파스칼 랭기지에서 유래된 것으로 초기값을 의미한다.
    v_sum int:=0;
begin
    v_num:= 1;
        loop
         if mod(v_num,3) = 0 then
         	v_sum:= v_sum+v_num;
         end if;
         if v_num >= 20 then
         	exit;
         -- elseif then
         else
         v_num:=v_num+1;
        end if;
        end loop;
    dbms_output.put_line('total : '||to_char(v_sum)||'last number :' 
     || to_char(v_num));
exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm); 
end;
/

에러코드를 확인해보고 싶다면?

에러 유발 -> 0으로 나눠봄 : 부정(?)

exception처리도 다양하게 커스터마이징 할 수 있다.

결과

사용자 정의함수 예시 1)

create or replace function get_dept_name(v_deptno dept.deptno%type)
return dept.dname%type
is 
    v_dname dept.dname%type;
begin
    select dname
    into v_dname
    from dept
    where deptno = v_deptno;
    return v_dname;
exception
    --when zero_divide then 
        --dbms_output.put_line('Try again! divider is 0!');
    when others then 
        dbms_output.put_line(sqlcode||' '||sqlerrm);
        return 0;
end;
/

--위에서 했던 방법은 select문에서 한것 임.
--함수 호출 방법
--1. select 문에서!

select get_dept_name(10)
from dual;

select deptno,dname,get_dept_name(deptno)
from dept;

desc user_objects

select object_name, object_type
from user_objects
order by 2;

desc user_source
select line, text
from user_source
where name='GET_DEPT_NAME';


--2. 다른 모듈에서!
declare
	v_dname varchar2(14);
begin
	--값을 리턴 받을 변수 하나가 필요함.
	v_dname:=get_dept_name(30);
	dbms_output.put_line(v_dname);
end;
/

--3. sql*plus명령! (pl/sql 아님!)
var v_dname varchar2(14)
var 
exec :v_dname:= get_dept_name(30);
print v_dname

예시 2) e

--emp테이블에서 ename으로 sal을 출력하는 function명 f_Sal을 작성하고 호출하라.
create or replace function f_sal(v_ename emp.ename%type)
return emp.sal%type
is 
    v_sal emp.sal%type;
begin
    select sal
    into v_sal
    from emp
    where ename like '%'||v_ename||'%'; --정확치 않은 경우 = 말고 like를 준다.그래서 와일드 카드 기호도 줘야함
    return v_sal;
exception
    when others then 
        dbms_output.put_line(sqlcode||' '||sqlerrm);
        return 0;
end;
/

이외 문제 풀이들..

create or replace function get_dept_name(v_deptno dept.deptno%type)
return dept.dname%type
is 
    v_dname dept.dname%type;
begin
    select dname
    into v_dname
    from dept
    where deptno = v_deptno;
    return v_dname;
exception
    --when zero_divide then 
        --dbms_output.put_line('Try again! divider is 0!');
    when others then 
        dbms_output.put_line(sqlcode||' '||sqlerrm);
        return 0;
end;
/
--위에서 했던 방법은 select문에서 한것 임.
--함수 호출 방법
--1. select 문에서!

select get_dept_name(10)
from dual;

select deptno,dname,get_dept_name(deptno)
from dept;

desc user_objects

select object_name, object_type
from user_objects
order by 2;

desc user_source
select line, text
from user_source
where name='GET_DEPT_NAME';


--2. 다른 모듈에서!
declare
	v_dname varchar2(14);
begin
	--값을 리턴 받을 변수 하나가 필요함.
	v_dname:=get_dept_name(30);
	dbms_output.put_line(v_dname);
end;
/

--3. sql*plus명령! (pl/sql 아님!)
var v_dname varchar2(14)
var 
exec :v_dname:= get_dept_name(30);
print v_dname 


--emp테이블에서 ename으로 sal을 출력하는 function명 f_Sal을 작성하고 호출하라.
create or replace function f_sal(v_ename emp.ename%type)
return emp.sal%type
is 
    v_sal emp.sal%type;
begin
    select sal
    into v_sal
    from emp
    where ename like '%'||v_ename||'%'; --정확치 않은 경우 = 말고 like를 준다.그래서 와일드 카드 기호도 줘야함
    return v_sal;
exception
    when TOO_MANY_ROWS then --정해진 에러명임 :해당 에러가 생기면 아래와 같이 출력하라는 것임.
    dbms_output.put_line('Select only one row!');
        return 0;
    when others then 
        dbms_output.put_line(sqlcode||' '||sqlerrm);
        return 0;
end;
/

select f_sal('SMITH')
from emp;

select ename,sal,f_sal(ename)
from emp;

select f_sal('S')
from dual;

declare
    v_sal number(10);
begin
    v_sal:= f_sal('SMI');
    dbms_output.put_line(v_sal);
end;
/
--select문으로 출력
select f_sal('SMI')
from dual;
--또다른 방법으로 출력
var x_sal number
var 
exec :x_sal:=f_sal('SMI');
print x_sal


--4번 문제임
create or replace function f_sal(v_ename emp.ename%type)
return emp.sal%type
is 
    v_sal emp.sal%type;
    v_job emp.job%type;
begin
    select sal,job
    into v_sal,v_job
    from emp
    where ename like '%' ||v_ename||'%';
    if v_job='MANAGER' then
        v_sal:=v_sal+v_sal*0.05;
    elsif v_job ='SALESMAN' then
        v_sal:= v_sal+v_sal*0.1;
    end if;
    return v_sal;
exception
    when TOO_MANY_ROWS then --정해진 에러명임 :해당 에러가 생기면 아래와 같이 출력하라는 것임.
    dbms_output.put_line('Try again!');
        return 0;
    when others then 
        dbms_output.put_line(sqlcode||' '||sqlerrm);
        return 0;
end;
/
--select 결과
select ename,job,sal,f_sal(ename)
from emp;

select * from salgrade;
--5번 문제 : salgrade테이블에서 grade를 입력하면 해당 grade의 중간 값 sal이 리턴 되도록
--f_midsal함수 작성하고 호출하라
create or replace function f_midsal(v_grade salgrade.grade%type)
return salgrade.losal%type
is 
    v_midsal salgrade.losal%type; --losal과 hisal은 타입이 같으므로 무엇을 사용하던지, 무관하다.

begin
    select round((losal+hisal)/2)
    into v_midsal
    from salgrade
    where grade = v_grade;
    return v_midsal;
exception
    when others then 
        dbms_output.put_line(sqlcode||sqlerrm);
        return 0;
end;
/
select grade,losal,hisal,f_midsal(grade) as midsal
from salgrade;

--프로시저로 위 함수를 바꿔보기 object 명과, type을 바꿔줌
--c언어의 마치 void처럼 ! return type이 없다.
create or replace procedure p_midsal(v_grade salgrade.grade%type)
is 
    v_midsal salgrade.losal%type;
begin
    select round((losal+hisal)/2)
    into v_midsal
    from salgrade
    where grade = v_grade;
    dbms_output.put_line(v_midsal);
exception
    when others then 
        dbms_output.put_line(sqlcode||sqlerrm);
end;
/
-- select로 프로시저 호출 할 수 없음.
-- exec로 출력 가능(3번째 방법) sql+에서 방법중 엑시큐트(exec)로 출력!
exec p_midsal(2);

begin 
    p_midsal(2);
end;
/

만약 화면 출력 &프린트가 안되는 경우 ->

show serveroutput
set serveroutput on

꼭 해주어야 해결이 됨

최종(정리해놓기)

create or replace function get_dept_name(v_deptno dept.deptno%type)
return dept.dname%type
is 
    v_dname dept.dname%type;
begin
    select dname
    into v_dname
    from dept
    where deptno = v_deptno;
    return v_dname;
exception
    --when zero_divide then 
        --dbms_output.put_line('Try again! divider is 0!');
    when others then 
        dbms_output.put_line(sqlcode||' '||sqlerrm);
        return 0;
end;
/
--위에서 했던 방법은 select문에서 한것 임.
--함수 호출 방법
--1. select 문에서!

select get_dept_name(10)
from dual;

select deptno,dname,get_dept_name(deptno)
from dept;

desc user_objects

select object_name, object_type
from user_objects
order by 2;

desc user_source
select line, text
from user_source
where name='GET_DEPT_NAME';


--2. 다른 모듈에서!
declare
	v_dname varchar2(14);
begin
	--값을 리턴 받을 변수 하나가 필요함.
	v_dname:=get_dept_name(30);
	dbms_output.put_line(v_dname);
end;
/

--3. sql*plus명령! (pl/sql 아님!)
var v_dname varchar2(14)
var 
exec :v_dname:= get_dept_name(30);
print v_dname 


--emp테이블에서 ename으로 sal을 출력하는 function명 f_Sal을 작성하고 호출하라.
create or replace function f_sal(v_ename emp.ename%type)
return emp.sal%type
is 
    v_sal emp.sal%type;
begin
    select sal
    into v_sal
    from emp
    where ename like '%'||v_ename||'%'; --정확치 않은 경우 = 말고 like를 준다.그래서 와일드 카드 기호도 줘야함
    return v_sal;
exception
    when TOO_MANY_ROWS then --정해진 에러명임 :해당 에러가 생기면 아래와 같이 출력하라는 것임.
    dbms_output.put_line('Select only one row!');
        return 0;
    when others then 
        dbms_output.put_line(sqlcode||' '||sqlerrm);
        return 0;
end;
/

select f_sal('SMITH')
from emp;

select ename,sal,f_sal(ename)
from emp;

select f_sal('S')
from dual;

declare
    v_sal number(10);
begin
    v_sal:= f_sal('SMI');
    dbms_output.put_line(v_sal);
end;
/
--select문으로 출력
select f_sal('SMI')
from dual;
--또다른 방법으로 출력
var x_sal number
var 
exec :x_sal:=f_sal('SMI');
print x_sal


--4번 문제임
create or replace function f_sal(v_ename emp.ename%type)
return emp.sal%type
is 
    v_sal emp.sal%type;
    v_job emp.job%type;
begin
    select sal,job
    into v_sal,v_job
    from emp
    where ename like '%' ||v_ename||'%';
    if v_job='MANAGER' then
        v_sal:=v_sal+v_sal*0.05;
    elsif v_job ='SALESMAN' then
        v_sal:= v_sal+v_sal*0.1;
    end if;
    return v_sal;
exception
    when TOO_MANY_ROWS then --정해진 에러명임 :해당 에러가 생기면 아래와 같이 출력하라는 것임.
    dbms_output.put_line('Try again!');
        return 0;
    when others then 
        dbms_output.put_line(sqlcode||' '||sqlerrm);
        return 0;
end;
/
--select 결과
select ename,job,sal,f_sal(ename)
from emp;

select * from salgrade;
--5번 문제 : salgrade테이블에서 grade를 입력하면 해당 grade의 중간 값 sal이 리턴 되도록
--f_midsal함수 작성하고 호출하라
create or replace function f_midsal(v_grade salgrade.grade%type)
return salgrade.losal%type
is 
    v_midsal salgrade.losal%type; --losal과 hisal은 타입이 같으므로 무엇을 사용하던지, 무관하다.

begin
    select round((losal+hisal)/2)
    into v_midsal
    from salgrade
    where grade = v_grade;
    return v_midsal;
exception
    when others then 
        dbms_output.put_line(sqlcode||sqlerrm);
        return 0;
end;
/
select grade,losal,hisal,f_midsal(grade) as midsal
from salgrade;

--프로시저로 위 함수를 바꿔보기 object 명과, type을 바꿔줌
--c언어의 마치 void처럼 ! return type이 없다.
create or replace procedure p_midsal(v_grade salgrade.grade%type)
is 
    v_midsal salgrade.losal%type;
begin
    select round((losal+hisal)/2)
    into v_midsal
    from salgrade
    where grade = v_grade;
    dbms_output.put_line(v_midsal);
exception
    when others then 
        dbms_output.put_line(sqlcode||sqlerrm);
end;
/
-- select로 프로시저 호출 할 수 없음.
-- exec로 출력 가능(3번째 방법) sql+에서 방법중 엑시큐트(exec)로 출력!
exec p_midsal(2);

begin 
    p_midsal(2);
end;
/
--test table생성
drop table testemp;
create table testemp
as select empno,ename,sal,deptno from emp;
select * from testemp;
alter table testemp
add grade number(1);
alter table testemp
add sumsal number(10);

update testemp t;
/*예시
set sumsal = (select sum(sal)
                from testemp
                where t.deptno=deptno );
*/     
/
create or replace procedure p_sum01(v_deptno testemp.deptno%type)
is 
    v_sumsal testemp.sumsal%type;
begin
    select sum(sal)
    into v_sumsal
    from testemp
    where deptno=v_deptno;
    if v_deptno=10 then
        v_sumsal:=0;
    end if;
    if v_sumsal >= 10000 then
        dbms_output.put_line('10000 over!');
    end if;
    update testemp 
    set sumsal = v_sumsal
    where deptno = v_deptno;
    --commit은 db에 반영하겠다는 뜻이다.
    commit;
exception
    when others then
    dbms_output.put_line(sqlcode);
    rollback;
end;
/

exec p_sum01(30);
select * from testemp;
commit;-- 변경을 저장하려면 커밋해줘야함. 안하고 바로 컴파일 시 롤백 당함.

--insert문에 update가 꼭 따라다녀야 한다.
create or replace procedure p_insert01(v_empno testemp.empno%type,
                                       v_ename testemp.ename%type,
                                       v_sal testemp.sal%type,
                                       v_deptno testemp.deptno%type)
is 
begin
    insert into testemp
    values(v_empno,v_ename,v_sal,v_deptno,null,null);
    update testemp
    set sumsal=(select sum(sal)
                from testemp
                where deptno=v_deptno)
    where deptno=v_deptno;
    commit;
exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
end;
/
select * from testemp;
exec p_insert01(9998,'kim',2000,20);


-- 7-1번 문제임 위는 7번이고, 이를 합해서 만든 것!
create or replace procedure p_insert02(v_empno testemp.empno%type,
                                       v_ename testemp.ename%type,
                                       v_sal testemp.sal%type,
                                       v_deptno testemp.deptno%type)
is 
    v_sumsal testemp.sumsal%type;
begin
    insert into testemp
    values(v_empno,v_ename,v_sal,v_deptno,null,null);
    update testemp
    set sumsal=(select sum(sal)
                from testemp
                where deptno=v_deptno)
    where deptno=v_deptno;
    select sum(sal)
    into v_sumsal
    from testemp
    where deptno=v_deptno;
    if v_deptno=10 then
        v_sumsal:=0;
    end if;
    if v_sumsal >= 10000 then
        dbms_output.put_line('10000 over!');
    end if;
    commit;
exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
end;
/
select * from testemp where ;
exec p_insert02(9999,'kim',2000,20);

'IT & Security > DB' 카테고리의 다른 글

[오라클] 예제 문제 (0)	2021.05.19
[DB] 오라클 서브쿼리 예제 (0)	2021.04.13
[Oracle DB] 집계함수 \| Join (0)	2021.03.26
[ Oracle DB ] 함수 \| 내장함수 \| 집계함수 \| 단일행 함수 \| 변환함수 \| 사용자 정의 함수 (0)	2021.03.10

Contents

새소식

인기 검색어

[Oracle] 프로시저 | 사용자 정의함수 | 함수 만들기

'IT & Security > DB' 카테고리의 다른 글

당신이 좋아할만한 콘텐츠

티스토리툴바