[ Oracle ] 프로시저와 sql*plus 예제 (프로시저 호출하는 방법)

 

아래 sql 예제들은 oracle에서 기본 제공하는 scott 스키마를 이용했다.

 

 

프로시저 호출

 

프로시저는 select 문에서 호출할 수 없고,

다른 모듈에서 호출해 사용한다.

 

p_midsal 이라는 프로시저를 다른 모듈에서 호출하려면, 아래와 같이 쓰면 된다.

 

 

> begin

        p_midsal(3);

   end;

   /

 

 

 

또는 sql*plus 명령을 아래와 같이 써서 호출할 수도 있다.

 

> exec p_midsal(2);

 

 

우선 emp 와 동일한 내용을 가진 testemp 테이블을 만들고 컬럼을 추가하겠다.

 

 

 

프로시저 예제

 

 

-- CTAS 기법으로 testemp 테이블을 만들고 컬럼 두개를 추가하시오.
-- (grade number(), sumsal number(10))

create table testemp as select * from emp;
select * from testemp;

alter table testemp
add grade number(1);

alter table testemp add sumsal number(10);

 

 

 

 

-- 6.procedure p_sum01 이름으로 부서번호 하나당 급여합계(sumsal)를 update 하라.

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;
    update testemp
    set sumsal = v_sumsal
    where deptno=v_deptno;
    commit;
exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
end;
/

 

 

위와 같이 프로시저를 구성하면 된다.

호출은 sql*plus 명령어를 이용하겠다.

 

 

exec p_sum01(30);

select * from testemp;

 

 

 

10, 20, 30 을 순서대로 넣어 실행시켰다.

 

 

 

 

 

 

위 문제를 약간 변형해보겠다.

 

 

-- 단, 부서번호가 10이면 sumsal을 0으로 갱신하고 sumsal이 10000이상이면 '10000 over!' 라는 메시지를 출력하라)
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;
exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
end;
/

 

 

if 문을 저 안에 넣으면 된다.

 

 

 

 

프로시저를 부서번호 10, 20, 30 를 가지고 실행시켰다.

 

 

 

select 를 이용해 testemp를 확인해보면, 아래와 같은 실행결과를 볼 수 있다.

 

 

 

 

 

부서번호가 10인 sumsal 이 0이 된 것을 확인할 수 있다.

 

 

다른 예제도 살펴보자.

 

 

-- procdure p_insert01 이름으로 testemp 테이블에 insert 하는 프로시저를 만들어라.

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=30)
    where deptno=v_deptno;
    commit;
end;
/

select * from testemp;
exec p_insert01(9999,'LEE',1000,30);

 

 

실행시키면, 아래 그림과 같이 데이터가 추가되는 것을 알 수 있다.

 

 

 

 

 

-- procdure p_insert02 이름으로 testemp 테이블에 insert 하는 프로시저를 만들어라.
-- 단, 부서번호가 10이면 sumsal 을 0으로 갱신하고 sumsal이 10000이상이면 '10000 over!' 라는 메시지를 출력하라

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);
    p_sum01(v_deptno);
    commit;
exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
end;
/

exec p_insert02(9995,'PARK',1000,10);
select * from testemp;

 

 

insert 이후에 sum01 프로시저를 활용해 문제 요구사항을 충족시켰다.

 

 

-- 8. procedure p_delete01이름으로 testemp테이블에 delete 하는 프로시저를 만들어라.
-- 단, 부서번호가 10이면 sumsal을 0으로 갱신하고 sumsal 이 10000이상이면 '10000 over!' 라는 메시지를 출력하라.

create or replace procedure p_delete01(v_empno testemp.empno%type)
is
    v_deptno testemp.deptno%type;
begin
    select deptno
    into v_deptno
    from testemp
    where empno=v_empno;
    delete from testemp where empno=v_empno;
--    update testemp
--    set sumsal=(select sum(sal)
--                from testemp
--                where deptno=v_deptno)
--    where deptno=v_deptno;
    p_sum01(v_deptno);
    commit;
exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
end;
/
select * from testemp;

 

 

주석처리된 update 문을 실행해도 되고,

전에 만들었던 p_sum01 프로시저를 호출해 무결성을 유지해도 된다.

 

 

 

 

 

 

예외처리(Exception)

 

PL/SQL의 오류를 의미한다.

 

예외의 종류는는 아래와 같다.

1. 미리 정의된 오라클서버 오류

2. 미리 정의되지 않은 오라클서버오류

3. 사용자정의 오류

 

 

사용자가 정의하는 오류는 선언이 필요하고, 실행부에서 raise문으로 발생시킨다.

 

 

 

 

예외 처리를 실습해보자.

 

 

-- 8. procedure p_delete01이름으로 testemp테이블에 delete 하는 프로시저를 만들어라.
-- 단, 부서번호가 10이면 sumsal을 0으로 갱신하고 sumsal 이 10000이상이면 
-- 사용자 정의 exception인 invalid_sumsal 을 일으키고 '10000 over!' 라는 에러메시지를 출력하라

 

 

문제가 주어졌다.

 

위에서 정의했던 p_sum01 프로시저를 수정하여 에러메시지를 출력해보자.

 

 

 

create or replace procedure p_sum01(v_deptno testemp.deptno%type)
is
    v_sumsal testemp.sumsal%type;
    invalid_sumsal exception;
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
        raise invalid_sumsal;
        
    end if;
    update testemp
    set sumsal = v_sumsal
    where deptno=v_deptno;
    commit;
exception
    when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
    when invalid_sumsal then
        dbms_output.put_line('10000 over!');
        rollback;
end;
/

 

 

 

-- 9. procedure p_grade01 이름으로 사원의 급여(sal)로 salgrade테이블에 해당하는 grade를 찾아서
-- testemp의 grade를 update하라.
-- 단, 해당 grade가 없으면 사용자 정의 exception인 invalid_grade를 일으키고
-- 'Try again! Invalid salary!' 라는 에러메시지를 출력하라

create or replace procedure p_grade01(v_empno testemp.empno%type)
is
    v_grade salgrade.grade%type;
    invalid_grade exception;
begin
    select s.grade
    into v_grade
    from testemp t join salgrade s on
        t.sal between s.losal and s.hisal
    where t.empno=v_empno;
    if sql%notfound then
        raise invalid_grade;
    end if;
    update testemp
    set grade=v_grade
    where empno=v_empno;
    commit;
exception
    when invalid_grade then
        dbms_output.put_line('Try again! Invalid salary!');
        rollback;
    when others then
        dbms_output.put_line(sqlcode||sqlerrm);
        rollback;
end;
/
select * from salgrade;
select * from testemp;
exec p_grade01(7499);