실습 문제
두 숫자를 제공하면 덧셈을 해서 결과값을 반환하는 함수를 정의하시오. (add_num)
CREATE OR REPLACE FUNCTION add_num (num1 INTEGER, num2 INTEGER)
RETURN INTEGER
IS
BEGIN
RETURN num1 +num2;
END;
SELECT ADD_NUM(2,5) FROM dual;부서 번호를 입력하면 해당 부서에서 근무하는 사원 수를 반환하는 함수를 정의하시오. (get_emp_count)
CREATE OR REPLACE FUNCTION get_emp_count(dept_no IN emp.deptno%TYPE)
RETURN INTEGER
IS
emp_count INTEGER;
BEGIN
SELECT COUNT(empno)
INTO emp_count
FROM emp
WHERE deptno = dept_no;
RETURN emp_count;
END;
SELECT deptno, dname, GET_emp_COUNT(deptno) FROM dept;emp 테이블의 입사일을 입력하면 근무연차를 구하는 함수를 정의하세요.
(소수점은 절삭한다 TRUNC 사용, GET_INFO_HIREDATE)
CREATE OR REPLACE FUNCTION get_info_hiredate(p_hiredate IN emp.hiredate%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN TRUNC(MONTHS_BETWEEN(SYSDATE, p_hiredate) / 12);
END;
SELECT empno, ename,GET_INFO_HIREDATE(hiredate) FROM emp;emp 테이블을 이용해서 사원 번호를 입력하면 해당 사원의 관리자 이름을 구하는 함수를 정의하세요. (get_mgr_name)
-- 서브쿼리 형태로 작성
CREATE OR REPLACE FUNCTION GET_MGR_NAME(emp_no IN emp.empno%TYPE)
RETURN VARCHAR2
IS
-- 변수 선언
m_name VARCHAR2(10);
BEGIN
SELECT ename
INTO m_name
FROM emp
WHERE empno = (SELECT mgr FROM emp WHERE empno = emp_no);
RETURN m_name;
END;
SELECT empno, ename, GET_MGR_NAME(empno) FROM emp;
--
-- Join으로 작성
CREATE OR REPLACE FUNCTION GET_MGR_NAME( emp_no emp.empno%TYPE)
RETURN VARCHAR2
IS
m_name VARCHAR2(10);
BEGIN
SELECT m.ename
INTO m_name
FROM emp e, emp m
WHERE e.mgr = m.empno AND e.empno = emp_no ;
RETURN m_name;
END;
--emp 테이블을 이용해서 사원 번호를 입력하면 급여 등급을 구하는 함수를 정의하세요. (GET_SAL_GRADE)
--
CREATE OR REPLACE FUNCTION GET_SAL_GRADE (emp_no emp.empno%TYPE)
RETURN CHAR
IS
sgrade CHAR(1);
BEGIN
SELECT CASE WHEN sal>=4000 THEN 'A'
WHEN sal>=3000 AND sal < 4000 THEN 'B'
WHEN sal>=2000 AND sal < 3000 THEN 'C'
WHEN sal>=1000 AND sal < 2000 THEN 'D'
ELSE 'F'
END grade
INTO sgrade
FROM emp
WHERE empno = emp_no;
RETURN sgrade;
END;
--
-- 기존의 테이블을 사용함
CREATE OR REPLACE FUNCTION GET_SAL_GRADE (emp_no emp.empno%TYPE)
RETURN NUMBER
IS
sgrade NUMBER;
BEGIN
SELECT s.grade
INTO sgrade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal AND e.empno = emp_no;
RETURN sgrade;
END;
--
SELECT ename,sal,GET_SAL_GRADE(empno) "급여등급" FROM emp ORDER BY "급여등급";사원번호를 입력하면 근무지를 구하는 함수 (find_loc, varchar2 이용)
-- 서브 쿼리 형태
CREATE OR REPLACE FUNCTION FIND_LOC (emp_no emp.empno%TYPE)
RETURN VARCHAR2
IS
-- 변수 선언
dloc VARCHAR2(14);
BEGIN
SELECT loc
INTO dloc
FROM dept
WHERE deptno = (SELECT deptno FROM emp WHERE empno = emp_no);
RETURN dloc;
END;
--
-- Join 형태
CREATE OR REPLACE FUNCTION FIND_LOC (emp_no emp.empno%TYPE)
RETURN VARCHAR2
IS
dloc VARCHAR2(14);
BEGIN
SELECT d.loc
INTO dloc
FROM emp e JOIN dept d
ON e.deptno = d.deptno
WHERE e.empno = emp_no;
RETURN dloc;
END;
--
SELECT FIND_LOC(empno), ename FROM emp;생성된 함수 확인하기
데이터 사전 (DATA Dictionary)을 통해 검색.
데이터 사전에 저장된 모든 값은 대문자로 저장되기 때문에 대문자로 검색.
SELECT object_name, object_type FROM user_objects
WHERE object_type = 'FUNCTION';작성된 함수의 소스 코드 확인
SELECT text FROM user_source
WHERE type='FUNCTION' AND name='TAX';프로시저
프로시저 생성
CREATE OR REPLACE PROCEDURE hello_world
IS
-- 변수 선언
message VARCHAR2(100);
BEGIN
message := 'Hello World~~';
DBMS_OUTPUT.PUT_LINE(message);
END;프로시저 실행
EXECUTE HELLO_WORLD;CREATE OR REPLACE PROCEDURE HELLO_ORACLE(p_message IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(p_message);
END;
EXECUTE HELLO_ORACLE('KOREA');작성된 STORED PROCEDURE 확인
SELECT object_name, object_type FROM user_objects WHERE object_type = 'PROCEDURE';작성된 프로시저의 소스 코드 확인
SELECT text FROM user_source WHERE type='PROCEDURE';부서테이블에 부서 정보를 입력하는 프로시저를 생성
CREATE OR REPLACE PROCEDURE add_department(
p_deptno IN dept.deptno%TYPE,
p_dname IN dept.dname%TYPE,
p_loc IN dept.loc%TYPE)
IS
BEGIN
-- PARAMETER 변수에 입력받은 값으로 부서(dept)테이블의 각 컬럼에 데이터를 추가하고
-- 정상적으로 TRANSACTION 종료
INSERT INTO dept
VALUES(p_deptno, p_dname,p_loc);
COMMIT;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(p_dname || 'register is failed');
ROLLBACK;
END;
EXEC ADD_DEPARTMENT(60,'IT SERVICE', 'BUSAN');
SELECT * FROM dept;사원 테이블에 사원정보를 저장
CREATE OR REPLACE PROCEDURE register_emp(e_empno IN emp.empno%TYPE,
e_ename IN emp.ename%TYPE,
e_job IN emp.job%TYPE,
e_mgr IN emp.mgr%TYPE,
e_sal IN emp.sal%TYPE,
e_comm IN emp.comm%TYPE,
e_deptno IN emp.deptno%TYPE )
IS
BEGIN
INSERT INTO emp (empno,ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (e_empno,e_ename,e_job, e_mgr, SYSDATE, e_sal, e_comm, e_deptno);
COMMIT;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(e_ename || 'Register Failed!');
ROLLBACK;
END;
EXEC register_emp(9000,'PETER','MANAGER', 7902, 6000, 200, 30);부서 번호를 통해서 부서명과 부서의 위치 구하기
CREATE OR REPLACE PROCEDURE output_department(p_dept_no IN dept.deptno%TYPE)
IS
-- 변수 선언
d_dname dept.dname%TYPE;
d_loc dept.loc%TYPE;
BEGIN
-- PARANMETER 변수로부터 부서번호를 받아 해당 부서의 정보 질의
SELECT dname, loc
INTO d_dname, d_loc
FROM dept
WHERE deptno = p_dept_no;
DBMS_OUTPUT.PUT_LINE(d_dname || ',' || d_loc);
END;
EXEC OUTPUT_DEPARTMENT(10);사원이 입사한 연도를 입력해서 사원 정보 구하기
CREATE OR REPLACE PROCEDURE info_hiredate(p_year IN VARCHAR2)
IS
-- %ROWTYPE 으로 데이터 타입이 지정되어 있는
-- 사원테이블(emp)의 하나의 행이 가지는 모든 컬럼의 데이터 타입을 가져옴
e_emp emp%ROWTYPE; --record 형태
BEGIN
SELECT empno, ename, sal
-- 단일행일 경우에 INTO 사용 할 수 있지만 다중행일 경우에는 오류가 발생한다.
-- 다중행은 CURSOR를 사용해야한다.
INTO e_emp.empno, e_emp.ename, e_emp.sal
FROM emp
WHERE TO_CHAR(hiredate,'YYYY') = p_year;
DBMS_OUTPUT.PUT_LINE(e_emp.empno || ',' || e_emp.ename || ',' || e_emp.sal);
END;커서를 이용하여 질의 수행 결과 반환되는 여러행을 처리
CREATE OR REPLACE PROCEDURE info_hiredate(p_year IN VARCHAR2)
IS
e_emp emp%ROWTYPE;
-- 커서 선언
CURSOR emp_cur IS
SELECT empno, ename, sal
FROM emp
WHERE TO_CHAR(hiredate, 'YYYY') = p_year;
BEGIN
-- 커서 열기
OPEN emp_cur;
-- 커서로부터 데이터 읽기
LOOP
FETCH emp_cur INTO e_emp.empno , e_emp.ename , e_emp.sal;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(e_emp.empno || ',' || e_emp.ename || ',' || e_emp.sal);
END LOOP;
--커서 닫기
CLOSE emp_cur;
END;
----------------------------------------------
EXEC INFO_HIREDATE('1981');
----------------------------------------------SALES 부서에 속한 사원의 정보 보기
CREATE OR REPLACE PROCEDURE emp_info (p_dept IN dept.dname%TYPE)
IS
-- 커서 선언
CURSOR emp_cur IS
SELECT empno, ename
FROM emp e JOIN dept d
ON e.deptno = d.deptno
WHERE dname = UPPER(p_dept);
-- 변수 선언
e_empno emp.empno%TYPE;
e_ename emp.ename%TYPE;
BEGIN
OPEN emp_cur;
-- 커서로부터 데이터 일기
LOOP
FETCH emp_cur INTO e_empno, e_ename;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(e_empno || ',' || e_ename);
END LOOP;
CLOSE emp_cur;
END;
--------------------------------------
EXEC EMP_INFO('SALES');
--------------------------------------실습문제 2
1) 업무(job)를 입력하여 해당 업무를 수행하는 사원들의 사원 번호,이름, 급여, 업무를 출력하세요.
CREATE OR REPLACE PROCEDURE job_info(p_job IN emp.job%TYPE)
IS
e_emp emp%ROWTYPE;
CURSOR emp_cur IS
SELECT empno, ename, sal, job
FROM emp
WHERE p_job = job;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO e_emp.empno , e_emp.ename , e_emp.sal , e_emp.job;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(e_emp.empno || ',' || e_emp.ename || ',' || e_emp.sal || ',' || e_emp.job);
END LOOP;
CLOSE emp_cur;
END;
EXEC JOB_INFO('MANAGER');2) 사원 번호와 새 업무를 입력하면 EMP 테이블의 해당 사원의 업무를 갱신할 수 있는 프로시저를 작성하시오.
CREATE OR REPLACE PROCEDURE update_job(e_empno IN emp.empno%TYPE,
e_job IN emp.job%TYPE)
IS
BEGIN
UPDATE emp SET job = e_job WHERE empno = e_empno;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(e_empno || ',' || 'Update Failed');
ROLLBACK;
END;
EXEC UPDATE_JOB(7369,'DRIVER');Table Book 생성
CREATE TABLE book(
bookid NUMBER PRIMARY KEY,
bookname VARCHAR2(60) NOT NULL,
publisher VARCHAR2(60) NOT NULL,
price NUMBER NOT NULL
);Table BOOK에 데이터 삽입
INSERT INTO book VALUES (1,'자바를 찾아서','서울',30000);
INSERT INTO book VALUES (2,'도시','천국',10000);
INSERT INTO book VALUES (3,'하늘','신라',50000);
COMMIT;동일한 도서가 있는지 점검한 후 동일한 도서가 없으면 삽입하고
동일한 도서가 있으면 가격을 업데이트하는 프로시저를 작성하시오. (book_info)
CREATE OR REPLACE PROCEDURE BOOK_INFO(
mybookid book.bookid%TYPE,
mybookname book.bookname%TYPE,
mypublisher book.publisher%TYPE,
myprice book.price%TYPE
)
IS
mycount NUMBER;
BEGIN
SELECT COUNT(*) INTO mycount FROM book WHERE bookname = mybookname;
IF mycount != 0 THEN
-- 동일한 도서가 있으면 가격을 업데이트
UPDATE book SET price = myprice
WHERE bookname = mybookname;
COMMIT;
ELSE
-- 동일한 도서가 없으면 삽입
INSERT INTO book (bookid, bookname, publisher, price)
VALUES (mybookid, mybookname, mypublisher, myprice);
COMMIT;
END IF;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR!');
ROLLBACK;
END;
EXEC BOOK_INFO(4,'스포츠','쿨서울',20000); -- 삽입
EXEC BOOK_INFO(5,'도시' , '천국' ,50000); -- 가격 변경
EXEC BOOK_INFO(4,'도시2','천국',50000); -- 오류 발생
Lucky Things🍀