[오라클 사용자 함수] ORACLE Stored Function 실습

//*****************************************

-- Stored Function(사용자 함수)

   - 스토어드 함수는 시스템 함수처럼 쿼리에서 호출하거나 저장 프로시저처럼 EXECUTE 문을 통해 실행할 수 있다.

   - 패키지 내에서 Function은 중복 정의가 가능하다.

----------------------------------------------

-- 형식(인수나 RETURN 에서는 자료형의 크기를 명시하지 않는다.)

CREATE [OR REPLACE] FUNCTION 함수이름

[(

   매개변수1 IN 자료형,

   매개변수2 IN 자료형

)]

RETURN datatype;

IS 

   [변수의 선언]

BEGIN

   .............

    RETURN (값);

   [EXCEPTION]

   .............

END;

/

----------------------------------------------

-- 함수 목록 확인

SELECT object_name FROM user_procedures;

----------------------------------------------

-- EXECUTE 또는 CALL를 이용한 함수 실행

 1. EXECUTE

    EXEC :바인딩변수 := 함수명([인수, 인수]);

    PRINT :바인딩변수

 2. CALL

    CALL 함수명([인수, 인수]) INTO :바인딩변수;

    PRINT :바인딩변수

----------------------------------------------

-- 합구하기

CRTEATE OR REPLACE FUNCTION fnSum

(

    n  IN  NUMBER

)

RETURN  NUMBER

IS

   s NUMBER := 0;

BEGIN

   FOR i IN 1..n LOOP

       s := s + i;

   END LOOP;

   RETURN s;

END;

/   

SELECT fnSum(100) FROM dual;

-- 성별 구하기(DECODE, CASE 는 PL/SQL 사용 불가)

CRTEATE OR REPLACE FUNCTION fnGender

(

    ssn  IN  VARCHAR2

)

RETURN  VARCHAR2

IS

   s VARCHAR2(6) := '여자';

BEGIN

   IF LENGTH(ssn) != 14 THEN 

      RAISE_APPLICATION_ERROR(-20001, '주민번호는 14자리');

   END IF;

   

   IF MOD(SUBSTR(ssn, 8, 1), 2) = 1 THEN

       s := '남자';

   END IF;

   

   RETURN s;

END;

/

SELECT name, ssn, fnGender(ssn) FROM insa;

-- 생일 구하기

CRTEATE OR REPLACE FUNCTION fnBirth

(

    ssn  IN  VARCHAR2

)

RETURN  DATE

IS

BEGIN

   IF LENGTH(ssn) != 14 THEN 

      RAISE_APPLICATION_ERROR(-20001, '주민번호는 14자리');

   END IF;

   

   RETURN TO_DATE(SUBSTR(ssn, 1, 6), 'RRMMDD');

END;

/

SELECT name, ssn, fnGender(ssn), fnBirth(ssn) FROM insa;

-- 나이 구하기

CRTEATE OR REPLACE FUNCTION fnAge

(

    ssn  IN  VARCHAR2

)

RETURN  NUMBER

IS

  age  NUMBER;

  s    DATE;

BEGIN

   s := fnBirth(ssn);

   age := TRUNC(MONTHs_BETWEEN(SYSDATE, s)/12);

   RETURN age;

END;

/

   

SELECT name, ssn, fnGender(ssn), fnBirth(ssn), fnAge(ssn) FROM insa;

-- fnPow(2, 10), fnPow(2, -2)

CRTEATE OR REPLACE FUNCTION fnPow

(

    a NUMBER,

b NUMBER

)

RETURN  NUMBER

IS

  s    NUMBER := 1;

BEGIN

   IF b > 0 THEN

      FOR n IN 1..b LOOP

      s := s * a;

  END LOOP;

   ELSE

      FOR n IN 1..(-b) LOOP

      s := s / a;

  END LOOP;   

   END IF;

   

   RETURN s;

END;

/

SELECT fnPow(2, 10), fnPow(2, -2) FROM dual;

//*****************************************

-- 문제

-- score1 테이블 작성

    hak     문자(20)  기본키

    name   문자(30)  NOT  NULL

    kor      숫자(3)     NOT  NULL

    eng      숫자(3)    NOT  NULL

    mat      숫자(3)    NOT  NULL

-- score2 테이블 작성

    hak     문자(20)  기본키, score1 테이블의 참조키

    kor      숫자(2,1)     NOT  NULL

    eng      숫자(2,1)    NOT  NULL

    mat      숫자(2,1)    NOT  NULL

CREATE TABLE score1 (

    hak VARCHAR2(20) NOT NULL

    ,name VARCHAR2(30) NOT NULL

    ,kor NUMBER(3) NOT NULL

    ,eng NUMBER(3) NOT NULL

    ,mat NUMBER(3) NOT NULL

    ,CONSTRAINT pk_score1_hak PRIMARY KEY(hak)

);

CREATE TABLE score2 (

    hak VARCHAR2(20) NOT NULL

    ,kor NUMBER(2,1) NOT NULL

    ,eng NUMBER(2,1) NOT NULL

    ,mat NUMBER(2,1) NOT NULL

    ,CONSTRAINT pk_score2_id PRIMARY KEY(hak)

    ,CONSTRAINT fk_score2_id FOREIGN KEY(hak)

              REFERENCES score1(hak)

);

-- 평점을 구하는 함수 작성

   -- 함수명 : fnGrade(s)

       95~100:4.5    90~94:4.0

       85~89:3.5     80~84:3.0

       75~79:2.5     70~74:2.0

       65~69:1.5     60~64:1.0

       60미만 0

   

CREATE OR REPLACE FUNCTION fnGrade

(

    pScore NUMBER

)

RETURN NUMBER

IS

   n NUMBER(2,1);

BEGIN

   IF pScore>=95 THEN n:=4.5;

   ELSIF pScore>=90 THEN n:=4.0;

   ELSIF pScore>=85 THEN n:=3.5;

   ELSIF pScore>=80 THEN n:=3.0;

   ELSIF pScore>=75 THEN n:=2.5;

   ELSIF pScore>=70 THEN n:=2.0;

   ELSIF pScore>=65 THEN n:=1.5;

   ELSIF pScore>=60 THEN n:=1.0;

   ELSE n:=0.0;

   END IF;

   RETURN n;

END;

/

SELECT fnGrade(85), fnGrade(90) FROM DUAL;

   

-- score1 테이블과 score2 테이블에 데이터를 추가하는 프로시져 만들기

   프로시져명 : pScoreInsert

   실행예 : EXEC pScoreInsert('1111', '가가가', 80, 60, 75);

   

   score1 테이블 => '1111', '가가가', 80, 60, 75  정보 추가

   score2 테이블 => '1111',            3.0, 1.0, 2.5 정보 추가(국, 영, 수 점수가 평점으로 계산되어 추가)

   

   단, 국여, 영어, 수학 점수는 0~100 사이가 아니면 예외 발생하고 종료

   

CREATE  OR  REPLACE  PROCEDURE  pScoreInsert

(

        phak    IN  VARCHAR2

        ,pname  IN  VARCHAR2

        ,pkor   IN  NUMBER

        ,peng   IN  NUMBER

        ,pmat   IN  NUMBER

)

IS

BEGIN

      IF  pkor<0 OR  pkor>100 OR peng<0 OR peng>100 OR pmat<0 OR pmat>100 THEN

           RAISE_APPLICATION_ERROR(-20001, '점수는 0~100사이만 가능합니다.');

      END IF;

      

      INSERT INTO score1(hak, name, kor, eng, mat) VALUES

               (phak, pname, pkor, peng, pmat);

      INSERT INTO score2(hak, kor, eng, mat) VALUES

               (phak, fnGrade(pkor), fnGrade(peng), fnGrade(pmat));

      COMMIT;

END;

/

EXEC pScoreInsert('111', '가가가', 80, 75, 95);

EXEC pScoreInsert('222', '나나나', 70, 65, 50);

EXEC pScoreInsert('333', '다다다', 180, 90, 85);

SELECT * FROM score1;

SELECT * FROM score2;

   

-- score1 테이블과 score2 테이블에 데이터를 수정하는 프로시져 만들기

   프로시져명 : pScoreUpdate

   실행예 : EXEC pScoreUpdate('1111', '가가가', 90, 60, 75);

   

   score1 테이블 => 학번이 '1111' 인 자료를  '가가가', 90, 60, 75  으로 정보 수정

   score2 테이블 => 학번이 '1111' 인 자료를           4.0, 1.0, 2.5 으로 정보 수정(국, 영, 수 점수가 평점으로 계산되어 수정)

   

   단, 국여, 영어, 수학 점수는 0~100 사이가 아니면 예외 발생하고 종료

   

CREATE  OR  REPLACE  PROCEDURE  pScoreUpdate

(

        phak    IN  VARCHAR2

        ,pname  IN  VARCHAR2

        ,pkor   IN  NUMBER

        ,peng   IN  NUMBER

        ,pmat   IN  NUMBER

)

IS

BEGIN

      IF  pkor<0 OR  pkor>100 OR peng<0 OR peng>100 OR pmat<0 OR pmat>100 THEN

           RAISE_APPLICATION_ERROR(-20001, '점수는 0~100사이만 가능합니다.');

      END IF;

      

      UPDATE score1 SET name=pname, kor=pkor, eng=peng, mat=pmat WHERE hak=phak;

      UPDATE score2 SET kor=fnGrade(pkor), eng=fnGrade(peng), mat=fnGrade(pmat) WHERE hak=phak;

      COMMIT;

END;

/

EXEC pScoreUpdate('111', '가가가', 95, 80, 95);

SELECT * FROM score1;

SELECT * FROM score2;

   

-- score1 테이블과 score2 테이블에 데이터를 삭제하는 프로시져 만들기

   프로시져명 : pScoreDelete

   실행예 : EXEC pScoreDelete('1111');

   score1 과 score2 테이블 정보 삭제

CREATE  OR  REPLACE  PROCEDURE  pScoreDelete

(

        phak    IN  VARCHAR2

)

IS

BEGIN

      DELETE FROM score2 WHERE hak=phak;

      DELETE FROM score1 WHERE hak=phak;

      COMMIT;

END;

/

   

-- score1과 score2 테이블에 가상 컬럼을 추가하여 총점과 평균계산하기

 score1 : tot NUMBER(3), ave NUMBER(4,1)

 score2 : tot NUMBER(4, 1), ave NUMBER(4,1)

ALTER TABLE score1 ADD (

    tot NUMBER(3) GENERATED ALWAYS AS ( kor+eng+mat ) VIRTUAL

    ,ave NUMBER(4,1) GENERATED ALWAYS AS ((kor+eng+mat)/3) VIRTUAL

);

ALTER TABLE score2 ADD (

    tot NUMBER(4,1) GENERATED ALWAYS AS ( kor+eng+mat ) VIRTUAL

    ,ave NUMBER(2,1) GENERATED ALWAYS AS ((kor+eng+mat)/3) VIRTUAL

);

SELECT * FROM score1;

SELECT * FROM score2;