[SQL/ORACLE] 저장 프로시저 (Stored Procedure) 형식, 사용 예제

 

• 자주 사용되는 DML, DQL을 모듈화(독립적인 객체) 시켜서 DB에 저장하였다가 필요할 때 호출해서 사용
• 서로 연관있는 DML과 DQL을 연결해서 실행하는 단위
• JAVA의 메소드. 함수와 같음
• [IN|OUT]
  IN은 안으로 데이터를 받는 매개변수의 의미,
  OUT은 데이터를 내보내는 리턴의 역할. OUT 사용시 호출 ⇒ execute 프로시저명(인자, :변수명)

프로시저 생성 및 실행방법

    CREATE [OR REPLACE] PROCEDURE 프로시저명(변수명 [IN|OUT] 자료형, 변수명 자료형) 
    --자료형은 타입만 기재 (ex. 변수명 VARCHAR2)
    IS
        실행부 BEGIN에서 사용할 변수선언;
    BEGIN
        --초기화, SQL문, 함수호출, 절차적 요소(조건문, 반복문)
        실행할 문장1;
        실행할 문장2;
    END;
    /

    -- CREATE [OR REPLACE] PROCEDURE 프로시저명 --매개변수가 없을시엔 소괄호 생략

    -- <실행방법>
    -- 매개변수가 있는 함수(OUT) 호출시
    EXEC[UTE] 프로시저명(인자);
        variable 변수명 자료형;
        PRINT 변수명; -- 저장된 값 출력

    -- 매개변수가 없는 함수(IN) 호출시
    EXEC[UTE] 프로시저명;
    EXEC[UTE] 프로시저명();

프로시저 사용 예제

• 기본적인 프로시저 사용방법

    create or replace PROCEDURE del_dept(delNo number)
        IS
        BEGIN
            delete from dept3
            WHERE deptno=delNo;
            DBMS_OUTPUT.PUT_LINE('#삭제수행 완료');
        END;
  
    EXECUTE del_dept(20); --실행문

• 매개변수 OUT의 리턴값이 존재하는 프로시저

    create or replace PROCEDURE del_dept(delNo IN number, tomorrow OUT varchar2)
        IS
        BEGIN
            delete from dept3
            WHERE deptno=delNo;
            DBMS_OUTPUT.PUT_LINE('#삭제수행 완료');
                    commit;
                    SELECT TO_CHAR(sysdate+1,'yy/mm/dd') INTO tomorrow FROM dual;
        END;
  
    variable tomorrow varchar2(15) --리턴값을 받을 변수 선언
    EXEC del_dept(40, :tomorrow);
    print tomorrow; --리턴값 확인

• 삭제와 조건문 if를 사용한 프로시저

    create or replace PROCEDURE del_ename(delName IN varchar2)
        IS
            vcount NUMBER;
        BEGIN
            DELETE FROM emp3
            WHERE ename = delName;
            SELECT count(*) INTO vcount FROM emp3;
                    if()
            then DBMS_OUTPUT.PUT_LINE(delName||' 사원이 삭제되었고 총 사원수는 '|| vcount||'명 입니다.');
                    else
            end if;
                    commit;
        END;
  
    EXECUTE del_ename('SMITH');

• 사원이름 존재유무 확인 프로시저

    create or replace PROCEDURE del_ename(delName IN varchar2)
        IS
            existN NUMBER;
            vcount NUMBER;
        BEGIN
            SELECT count(ename) INTO existN FROM emp3 WHERE ename = delName;
  
            if(existN = 0)        
            then 
                SELECT count(*) INTO vcount FROM emp3;
                DBMS_OUTPUT.PUT_LINE(delName||' 사원은 존재하지않고 총 사원수는 '|| vcount||'명 입니다.');
            else
                  DELETE FROM emp3 WHERE ename = delName;
                  SELECT count(*) INTO vcount FROM emp3;
                  DBMS_OUTPUT.PUT_LINE(delName||' 사원이 삭제되었고 총 사원수는 '|| vcount ||'명 입니다.');
            end if;
            commit;
        END;
  
    EXECUTE del_ename('SMITH');

• 위의 코드 개선. if then과 else의 select문을 한번만 정의해서 출력

    del_ename('ADAMS'), del_ename('adams') 상관없이 사원삭제
  
    create or replace procedure del_ename(delEname varchar2)
    is
     -- 변수선언
       total number;
       cnt   number;
    begin
  
       --사원이름 존재 유(1)무(0) 조회
       select count(ename) into cnt
       from emp3
       where upper(ename) = upper(delEname);
  
       select count(*) into total
            from emp3;
  
       --if cnt=0
       if cnt>0
         then /*존재한다면*/
            delete from emp3
            where lower(ename)= lower(delEname);    
            commit;
            dbms_output.put_line('#'||delEname||'사원이 삭제되었고 총 사원수는 '||
                      (total-1)||'명 입니다!!');
         else
            dbms_output.put_line('#'||delEname||'사원은 존재하지않고 총 사원수는 '||
                      total||'명 입니다!!');
       end if;
    end;

• SELECT절에서 데이터가 선택되지 않은것은 null이 아닌 no select로 아예 행이 없는것을 의미함