0819_Oracle[PL/SQL] : PROCEDURE, FUNCTION 예제

문제

-- score1 테이블 작성
hak     문자(20)  기본키
name   문자(30)  NOT  NULL
kor      숫자(3)     NOT  NULL
eng      숫자(3)    NOT  NULL
mat      숫자(3)    NOT  NULL

 

풀이 >>

CREATE TABLE score1 (
    hak VARCHAR2(30),
    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)
);

-- score2 테이블 작성
hak     문자(20)  기본키, score1 테이블의 참조키
kor      숫자(2,1)     NOT  NULL
eng      숫자(2,1)    NOT  NULL
mat      숫자(2,1)    NOT  NULL
     

풀이 >>

CREATE TABLE score2 (
    hak VARCHAR2(20),
    kor NUMBER(2, 1) NOT NULL,
    eng NUMBER(2, 1) NOT NULL,
    mat NUMBER(2, 1) NOT NULL,
    CONSTRAINT pk_score2_hak PRIMARY KEY (hak),
    CONSTRAINT fk_score2_hak 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
(
	n NUMBER
)
RETURN NUMBER
IS
	s NUMBER(2, 1) := 0;
BEGIN
	IF n>= 95 THEN s := 4.5;
	ELSIF n>=90 THEN s := 4.0;
	ELSIF n>=85 THEN s := 3.5;
	ELSIF n>=80 THEN s := 3.0;
	ELSIF n>=75 THEN s := 2.5;
	ELSIF n>=70 THEN s := 2.0;
	ELSIF n>=65 THEN s := 1.5;
	ELSIF n>=60 THEN s := 1.0;
	ELSE s := 0;
    END IF;
	
	RETURN s;
END;
/

-- 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 VARCHAR2,
	pName VARCHAR2,
	pKor NUMBER,
	pEng NUMBER,
	pMat NUMBER
)
IS
BEGIN
	IF pKor < 0 OR pKor > 100 THEN 
		RAISE_APPLICATION_ERROR(-20001, '점수는 0~100 사이 값만 입력 가능합니다.');
	ELSIF pEng < 0 OR pEng > 100 THEN
		RAISE_APPLICATION_ERROR(-20001, '점수는 0~100 사이 값만 입력 가능합니다.');
	ELSIF 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;
/

-- 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 score1.hak%TYPE,
	pName score1.name%TYPE,
	pKor score1.kor%TYPE,
	pEng score1.eng%TYPE,
	pMat score1.mat%TYPE
)
IS
BEGIN
	IF pKor < 0 OR pKor > 100 THEN 
		RAISE_APPLICATION_ERROR(-20001, '점수는 0~100 사이 값만 입력 가능합니다.');
	ELSIF pEng < 0 OR pEng > 100 THEN
		RAISE_APPLICATION_ERROR(-20001, '점수는 0~100 사이 값만 입력 가능합니다.');
	ELSIF 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;
/

 

-- score1 테이블과 score2 테이블에 데이터를 삭제하는 프로시저 만들기
프로시저명 : pScoreDelete
실행예 : EXEC pScoreDelete('1111');
score1 과 score2 테이블 정보 삭제

 

풀이 >>

CREATE PROCEDURE pScoreDelete(
	pHak score1.hak%TYPE
)
IS
BEGIN
	DELETE FROM score2 WHERE hak = pHak;
	DELETE FROM score1 WHERE hak = pHak;
	COMMIT;
END;
/

 

※ 프로시저 실행시에는 앞에 꼭 EXEC 를 붙여야 한다. 

EXEC pScoreInsert('1111', '홍길동', 80, 90, 90);
EXEC pScoreUpdate('1111', '홍길동', 100, 90, 90);
EXEC pScoreDelete('1111');