오라클 SQL 19강

--19강

--저장프로시저(PROCEDURE)와 함수(FUNCTION)

--학생테이블에서 학번과 학년을 입력으로하여 수정하는 저장프로시저를 작성하라.

CREATE OR REPLACE PROCEDURE TEST2

(V_STU_NO IN STUDENT.STU_NO%TYPE,

V_STU_GRADE IN STUDENT.STU_GRADE%TYPE)

IS

BEGIN

UPDATE STUDENT

SET STU_GRADE = V_STU_GRADE

WHERE STU_NO = V_STU_NO;

END TEST2;

/

SELECT * FROM STUDENT

WHERE STU_NO = 20153075;

EXECUTE TEST2(20153075,3);

SELECT * FROM STUDENT

WHERE STU_NO = 20153075;

---

--학번을 입력으로 학생의 이름을 검색하는 프로시저를 작성하라.

CREATE OR REPLACE PROCEDURE TEST3

(V_STU_NO IN STUDENT.STU_NO%TYPE,

V_STU_NAME OUT STUDENT.STU_NAME%TYPE)

IS

BEGIN

SELECT STU_NAME

INTO V_STU_NAME FROM STUDENT

WHERE STU_NO=V_STU_NO;

END TEST3;

/

DROP PROCEDURE TEST3;

 

VARIABLE D_STU_NAME VARCHAR2(12);

EXECUTE TEST3 (20153075,:D_STU_NAME);

PRINT D_STU_NAME;

---

--함수 : FUNCTION

CREATE OR REPLACE FUNCTION TEST6

(V_ENR_GRADE IN NUMBER)

RETURN CHAR

IS

ENR_SCORE CHAR;

BEGIN

IF V_ENR_GRADE >= 90 THEN ENR_SCORE := 'A';

ELSIF V_ENR_GRADE >=80 THEN ENR_SCORE :='B';

ELSIF V_ENR_GRADE >=70 THEN ENR_SCORE :='C';

ELSIF V_ENR_GRADE >60 THEN ENR_SCORE :='D';

ELSE ENR_SCORE :='F';

END IF;

RETURN (ENR_SCORE);

END TEST6;

/

VARIABLE D_SCORE CHAR;

EXECUTE :D_SCORE :=TEST6(88);

PRINT D_SCORE;

 

SELECT ENR_GRADE , TEST6(ENR_GRADE) SCORE

FROM ENROL

WHERE STU_NO = 20153075;

---

--EXCEPTION(예외처리)

--NO_DATA_FOUND/NOT_LOGGED_ON/TOO_MANY_ROWS/VALUE_ERROR/ZERO_DEVIDE/

--INVALID_CURSOR/DUP_VAL_ON_INDEX...

CREATE OR REPLACE PROCEDURE TEST10

(V_STU_NO IN STUDENT.STU_NO%TYPE)

IS

V_STU_NAME STUDENT.STU_NAME%TYPE;

BEGIN

SELECT STU_NAME

INTO V_STU_NAME

FROM STUDENT

WHERE STU_NO=V_STU_NO;

DBMS_OUTPUT.PUT_LINE(V_STU_NAME);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('해당데이터가 없습니다');

END TEST10;

/

EXECUTE TEST10(20153054);

---

--급여가 2000이상인 사원의 사원번호,사원이름,부서이름을 부서이름순으로 검색하는 프로시저를 작성하라.

CREATE OR REPLACE PROCEDURE PROC1

IS

V_EMPNO EMP.EMPNO%TYPE;

V_ENAME EMP.ENAME%TYPE;

V_DNAME DEPT.DNAME%TYPE;

CURSOR RESULT IS

SELECT EMPNO,ENAME,DNAME

FROM EMP NATURAL JOIN DEPT

WHERE SAL > 2000

ORDER BY DNAME;

BEGIN

OPEN RESULT;

LOOP

FETCH RESULT INTO V_EMPNO,V_ENAME,V_DNAME;

EXIT WHEN RESULT%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('사원번호:'||V_EMPNO||'사원이름'||V_ENAME||'부서이름'||V_DNAME);

END LOOP;

CLOSE RESULT;

END PROC1;

/

 

SELECT * FROM EMP;

SELECT * FROM DEPT;

EXECUTE PROC1();

---

--신입사원을 채용하였다. 사원번호,사원직무,상급자사원번호,급여,부서번호를 입력받아 사원테이블에 삽입하는 프로시저를 작성하라.

CREATE OR REPLACE PROCEDURE PROC2

(V_EMPNO IN EMP.EMPNO%TYPE,

V_ENAME IN EMP.ENAME%TYPE,

V_JOB IN EMP.JOB%TYPE,

V_MGR IN EMP.MGR%TYPE,

V_SAL IN EMP.SAL%TYPE,

V_DEPTNO IN EMP.DEPTNO%TYPE)

IS

BEGIN

INSERT INTO EMP

VALUES (V_EMPNO,V_ENAME,V_JOB,V_MGR,NULL,V_SAL,NULL,V_DEPTNO);

COMMIT;

END PROC2;

/

EXECUTE PROC2(1111,'AAA','대리',2222,3000,10);

DESC EMP;

DROP PROCEDURE PROC2;

SELECT * FROM EMP;

---

--부서번호를 변경하는 프로시저를 작성하시오.

CREATE OR REPLACE PROCEDURE PROC3

(V_EMPNO IN EMP.EMPNO%TYPE,

V_DEPTNO IN EMP.DEPTNO%TYPE)

IS

BEGIN

UPDATE EMP

SET DEPTNO = V_DEPTNO

WHERE EMPNO = V_EMPNO;

COMMIT;

END PROC3;

/

SELECT * FROM EMP;

EXECUTE PROC3 (7839,20);

ROLLBACK;

---

--FUNCTION(함수)

--최고급여를 받는 사원이름을 출력하는 함수를 작성하라

CREATE OR REPLACE FUNCTION FUN_1

RETURN EMP.ENAME%TYPE

IS

V_ENAME EMP.ENAME%TYPE;

BEGIN

SELECT ENAME INTO V_ENAME

FROM EMP

WHERE SAL = (SELECT MAX(SAL) FROM EMP);

RETURN V_ENAME;

END FUN_1;

/

SELECT DISTINCT FUN_1 () FROM EMP;

---

--학과를 입력받아 과 점수의 표준편차를 출력하는 함수를 작성하시오.

CREATE OR REPLACE FUNCTION FUN_2

(V_DEPT STUDENT.STU_DEPT%TYPE)

RETURN NUMBER

IS

V_STDDEV NUMBER;

BEGIN

SELECT ROUND(STDDEV(ENR_GRADE),2) INTO V_STDDEV

FROM STUDENT NATURAL JOIN ENROL

WHERE STU_DEPT=V_DEPT;

RETURN V_STDDEV;

END FUN_2;

/

SELECT DISTINCT FUN_2('기계')

FROM STUDENT;

---

--PACKAGE

--명세부

CREATE OR REPLACE PACKAGE PACK1

IS

PROCEDURE TEST2

(V_STU_NO IN STUDENT.STU_NO%TYPE,

V_STU_GRADE IN STUDENT.STU_GRADE%TYPE);

FUNCTION TEST6

(V_ENR_GRADE IN NUMBER)

RETURN CHAR;

END;

/

---

--몸체부

CREATE OR REPLACE PACKAGE BODY PACK1

IS

PROCEDURE TEST2

(V_STU_NO IN STUDENT.STU_NO%TYPE,

V_STU_GRADE IN STUDENT.STU_GRADE%TYPE)

IS

BEGIN

UPDATE STUDENT

SET STU_GRADE = V_STU_GRADE

WHERE STU_NO = V_STU_NO;

END TEST2;

 

FUNCTION TEST6

(V_ENR_GRADE IN NUMBER)

RETURN CHAR

IS

ENR_SCORE CHAR;

BEGIN

IF V_ENR_GRADE >= 90 THEN ENR_SCORE :='A';

ELSIF V_ENR_GRADE>=80 THEN ENR_SCORE :='B';

ELSIF V_ENR_GRADE>=70 THEN ENR_SCORE :='C';

ELSIF V_ENR_GRADE>=60 THEN ENR_SCORE :='D';

ELSE ENR_SCORE :='F';

END IF;

RETURN (ENR_SCORE);

END TEST6;

END;

/

SELECT * FROM STUDENT

WHERE STU_NO = 20153088;

EXECUTE TEST2(20153088,2);

VARIABLE D_SCORE CHAR;

EXECUTE :D_SCORE :=TEST6(90);

PRINT D_SCORE;

---

--TRIGGER란?

CREATE TABLE TMP_TBL1

(USERID VARCHAR2(10),

WORKDATE DATE,

BIGO CHAR(1));

CREATE OR REPLACE TRIGGER TRL1

AFTER UPDATE

ON STUDENT

BEGIN

INSERT INTO TMP_TBL1

VALUES (USER,SYSDATE,'u');

END TRL1;

/

 

UPDATE STUDENT

SET STU_WEIGHT=STU_WEIGHT*0.9;

 

SELECT * FROM TMP_TBL1;

ROLLBACK;

---