PROCEDURE, FUNCTION 연습

PROCEDURE

CREATE OR REPLACE PROCEDURE emp_info(
    p_empdno IN employee.dno%TYPE) IS
BEGIN
for empdno_buf in( SELECT * FROM employee WHERE dno = p_empdno)
    loop
    dbms_output.put_line('부서번호 : '||empdno_buf.dno||' 사원번호 : '||empdno_buf.eno||' 이름 : '||empdno_buf.ename||'/ 급여 : '||empdno_buf.salary);
    end loop;
END;
/

EXECUTE emp_info(10);

 

FUNCTION

 

학생의 담당교수를 불러오는 함수

CREATE OR REPLACE FUNCTION PROF_GET_NAME(
    V_PRONUM PROFESSOR.PRONUM%TYPE)
    RETURN PROFESSOR.PRONAME%TYPE
    IS
    V_PRONAME PROFESSOR.PRONAME%TYPE;
BEGIN
    SELECT PRONAME INTO V_PRONAME FROM PROFESSOR WHERE PRONUM = V_PRONUM;
    RETURN V_PRONAME;
        EXCEPTION WHEN OTHERS THEN
        dbms_output.put_line(SQLCODE||SQLERRM);
        RETURN 0;
END;
/

SELECT STNAME NAME ,MAJOR MAJOR, PROF_GET_NAME(PRONUM) PFS
FROM STUDENT;

 

교수별 담당 학생수를 나타내는 함수 

CREATE OR REPLACE FUNCTION STD_COUNT(
V_PRONUM PROFESSOR.PRONUM%TYPE)
RETURN NUMBER
IS
ST_COUNT NUMBER;
BEGIN 
    SELECT COUNT(*) INTO ST_COUNT FROM STUDENT WHERE PRONUM = V_PRONUM;
    RETURN ST_COUNT;
            EXCEPTION WHEN OTHERS THEN
        dbms_output.put_line(SQLCODE||SQLERRM);
        RETURN 0;
END;
/

SELECT PRONAME 이름 ,PROMAJOR 학과, STD_COUNT(PRONUM) "담당 학생수"
FROM PROFESSOR;