pl/sql, procedure, function

[ PL/SQL ]

 

- 구조적인 SQL언어와 달리 SQL을 절차적인 방식에 의해 사용하도록 설계한 언어

 

1) PL/SQL의 구조

DECLARE  -- 선언(변수선언,커서,사용자정의 예외,..)
BEGIN  -- 실행(SELECT,DML,..)
EXCEPTION -- 예외처리
END;
/

 

예1)

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2     I_MSG VARCHAR2(100); -- 변수선언
3  BEGIN
4     I_MSG:='HELLO';  -- 값대입
5     DBMS_OUTPUT.PUT_LINE('I_MSG:' || I_MSG); -- 화면출력
6  END;
7  /

 

[결과]
I_MSG:HELLO

 

SQL> DECLARE
2     VEMPNO NUMBER(4);
3     VENAME VARCHAR2(20);
4     VDEPTNO NUMBER(4);
5     VSAL NUMBER(4);
6     BONUS NUMBER(10);
7  BEGIN
8     SELECT EMPNO,ENAME,DEPTNO,SAL INTO VEMPNO,VENAME,VDEPTNO,VSAL
9     FROM EMP
10     WHERE EMPNO=7369;
11     IF(VDEPTNO=10) THEN
12             BONUS:=VSAL*0.1;
13     ELSIF(VDEPTNO=20) THEN
14             BONUS:=VSAL*0.2;
15     ELSIF(VDEPTNO=30) THEN
16             BONUS:=VSAL*0.3;
17     END IF;
18     DBMS_OUTPUT.PUT_LINE('사원번호 급여 부서번호 보너스');
19     DBMS_OUTPUT.PUT_LINE(VEMPNO ||' '||VSAL||' '||VDEPTNO||' '||BONUS);
20  END;
21  /

 

사원번호 급여 부서번호 보너스
7369 800 20 160

 

 

 

[ 저장 프로시져(Stored Procedure) ]

- 특정한 처리를 수행하는 PL/SQL 서브프로그램. 파라미터를 받아서 특정 작업을 수행한다.

- 형식)

CREATE OR REPLACE PROCEDURE 프로시져명(파라미터,..)
IS
변수선언;
..
BEGIN
실행문;
..
END;
/

 

예)-- 연습용 테이블

CREATE TABLE MEM
(
NUM NUMBER PRIMARY KEY,
NAME VARCHAR2(20),
PHONE VARCHAR2(15),
ADDR VARCHAR2(20)
);

 

예1) 프로시져만들기

CREATE OR REPLACE PROCEDURE ADDMEM
(
NUM NUMBER, -- 파라미터
NAME VARCHAR2,
PHONE VARCHAR2,
ADDR VARCHAR2
)
IS
BEGIN
INSERT INTO MEM
VALUES(NUM,NAME,PHONE,ADDR);
END;
/

 

-- 프로시져 오류보기

SQL>SHOW ERROR

 

-- 프로시져 수행

SQL> EXECUTE ADDMEM(1,'홍','010','대구');

 

예2)

CREATE OR REPLACE PROCEDURE ADDMEM
(
    NUM MEM.NUM%TYPE,
    NAME MEM.NAME%TYPE,
    PHONE MEM.PHONE%TYPE:='010',-- 파라미터값이없으면 디폴트값으로 채워짐
    ADDR MEM.ADDR%TYPE:='서울'
)
IS
BEGIN
INSERT INTO MEM
VALUES(NUM,NAME,PHONE,ADDR);
COMMIT;
END;
/

 

SQL> EXECUTE ADDMEM(2,'김아무');

 

-- 예제테이블

CREATE TABLE HAKSA
(
    HAKBUN NUMBER(10) PRIMARY KEY,
    KOR NUMBER(3),
    ENG NUMBER(3),
    TOT NUMBER(3),
    HAKJUM CHAR(2)
);

 

SQL> INSERT INTO HAKSA(HAKBUN,KOR,ENG) VALUES(1,100,90);

SQL> INSERT INTO HAKSA(HAKBUN,KOR,ENG) VALUES(2,80,70);

 

 -- 학번을 파라미터로 받아서 총점과 학점을 계산해주는 저장 프로시져

CREATE OR REPLACE PROCEDURE HAKSA_PRO(NUM HAKSA.HAKBUN%TYPE)
IS
    HAK HAKSA.HAKJUM%TYPE;
    SUM1 HAKSA.TOT%TYPE;
    AVG1 NUMBER;
BEGIN
    SELECT (KOR+ENG) INTO SUM1
      FROM HAKSA
     WHERE HAKBUN=NUM;

    AVG1:=SUM1/2; --평균구하기

    -- IF절 이용해서 학점구하기
    IF(AVG1>=90) THEN
        HAK:='A';
    ELSIF(AVG1>=80) THEN
        HAK:='B';
    ELSIF(AVG1>=70) THEN
        HAK:='C';
    ELSE
        HAK:='F';
    END IF;

    UPDATE HAKSA
       SET TOT=SUM1
         , HAKJUM=HAK -- 계산된 데이터로 수정하기
     WHERE HAKBUN=NUM;
   
    COMMIT;
END;
/

 

-- 프로시져 실행하기

SQL> EXECUTE HAKSA_PRO(1);

 

 

[ 사용자 정의 함수(Stored Function) ]

- 사용자가 만든 함수이며 어떤 연산을 수행한뒤 결과값을 반환한다.

형식)

CREATE OR REPLACE FUNCTION 함수명(파라미터,..)
RETURN 리턴형
IS
변수선언;
..
BEGIN
실행문장;
..
RETURN 값;
END;
/

 

예) 입력받은 값으로부터 10%의 세율을 구하는 함수

CREATE OR REPLACE FUNCTION TAX(P_VALUE NUMBER)
RETURN NUMBER
IS
VAL NUMBER;
BEGIN
    VAL:=P_VALUE*0.1; -- 세율구하기
    RETURN VAL; --세율반환하기
END;
/

 

SQL> SELECT TAX(3000) FROM DUAL;

TAX(3000)
----------
300

 

 

## IF문 형식

IF 조건절 THEN
    실행문;
ELSE
    실행문;
END IF;

 

Q1) 사원의 급여를 파라미터로 받아서 5000이상이며 15%에 해당하는 금액을 리턴하고 5000미만이면 10%를 리턴하는 함수 작성

CREATE FUNCTION MYTAX(P_VAL NUMBER) RETURN NUMBER
IS
    V_RESULT NUMBER;
BEGIN
    IF(P_VAL>=5000) THEN
        V_RESULT:=P_VAL*0.15;
    ELSE
       V_RESULT:=P_VAL*0.1;
    END IF;

    RETURN V_RESULT;

END;
/

-- FUNCTION호출

SQL> SELECT MYTAX(6000) FROM DUAL;

MYTAX(6000)
-----------
900

 

 

Q2) 파라미터로 사원번호를 받아서 근무하는 부서명을 반환하는 함수작성

CREATE OR REPLACE FUNCTION EMPDNAME(NUM EMP.EMPNO%TYPE)
RETURN VARCHAR2  -- 반환타입
IS
    V_RESULT VARCHAR2(20); --부서명저장할 변수
BEGIN
    SELECT D.DNAME
      INTO V_RESULT
      FROM EMP E,
           DEPT D
     WHERE E.DEPTNO=D.DEPTNO
       AND E.EMPNO=NUM;
   
    RETURN V_RESULT;
END;
/

 

-- FUNCTION호출해보기

SQL> SELECT EMPDNAME(7369) FROM DUAL;