실습문제(21일~22일차)
[실습문제]
1.두 숫자를 제공하면 덧셈을 해서 결과값을 반환하는 함수를 정의하시오.(add_num)
--내 답
CREATE OR REPlACE FUNCTION add_num(num_1 IN NUMBER, num_2 IN NUMBER)
RETURN NUMBER
IS
result_num NUMBER;
BEGIN
result_num := num_1 + num_2;
RETURN result_num;
END;
DECLARE
RESULT NUMBER;
BEGIN
-- 함수 호출
RESULT := add_num(10, 20);
-- 결과 출력
DBMS_OUTPUT.PUT_LINE('결과: ' || RESULT);
END;
--강사님 답
CREATE OR REPlACE FUNCTION add_num(num_1 IN INTEGER, num_2 IN INTEGER)
RETURN INTEGER
IS
BEGIN
RETURN num_1 + num_2;
END;
SELECT ADD_NUM(2,5) FROM dual;
SELECT ename,ADD_NUM(sal,NVL(comm,0)) 실급여 FROM emp;
2.부서번호를 입력하면 해당부서에서 근무하는 사원수를 반환하는 함수를 정의하시오.(get_emp_count)
--내답
CREATE OR REPlACE FUNCTION get_emp_count(dept_no NUMBER)
RETURN NUMBER
IS
emp_num NUMBER;
BEGIN
SELECT COUNT(empno)
INTO emp_num
FROM emp
WHERE deptno = dept_no;
RETURN emp_num;
END;
SELECT get_emp_count(10) FROM emp;
--강사님답
CREATE OR REPlACE FUNCTION get_emp_count(dept_no emp.deptno%TYPE)
RETURN NUMBER
IS
emp_num INTEGER;
BEGIN
SELECT COUNT(empno)
INTO emp_num
FROM emp
WHERE deptno = dept_no;
RETURN emp_num;
END;
SELECT deptno,dname,GET_EMP_COUNT(deptno) 사원수 FROM dept;
3.emp테이블의 입사일을 입력하면 근무연차를 구하는 함수를 정의하시오
(소수점 자리 절삭, get_info_hiredate)
CREATE OR REPLACE FUNCTION get_info_hiredate(
hire_date emp.hiredate%TYPE)
RETURN NUMBER
IS
BEGIN
RETURN TRUNC(MONTHS_BETWEEN(SYSDATE,hire_date)/12);
END;
SELECT ename,GET_INFO_HIREDATE(hiredate) 근무연차 FROM emp;
4.emp테이블을 이용해서 사원번호를 입력하면 해당 사원의 관리자 이름을
구하는 함수를 정의하시오. (get_mgr_name)
CREATE OR REPLACE FUNCTION get_mgr_name(emp_no 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;
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;
SELECT empno,ename,GET_MGR_NAME(empno) "관리자 이름" FROM emp;
5.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 sal DESC;
6.사원번호를 입력하면 근무지를 구하는 함수(find_loc)
-- 내답(JOIN형태 oracle. sql형식으로 해도됨)
CREATE OR REPLACE FUNCTION find_loc2 (emp_no emp.empno%TYPE)
RETURN VARCHAR2
IS
dept_loc VARCHAR2(14);
BEGIN
SELECT d.loc
INTO dept_loc
FROM emp e, dept d
WHERE e.empno = emp_no AND e.deptno = d.deptno;
RETURN dept_loc;
END;
SELECT find_loc2(7698) FROM dual;
SELECT empno,ename,FIND_LOC2(empno) FROM emp;
--강사님 답(서브쿼리를 이용한 방법)
CREATE OR REPLACE FUNCTION find_loc (emp_no emp.empno%TYPE)
RETURN VARCHAR2
IS
dept_loc VARCHAR2(14);
BEGIN
SELECT loc
INTO dept_loc
FROM dept
WHERE deptno = (SELECT deptno FROM emp WHERE empno = emp_no);
RETURN dept_loc;
END;
SELECT find_loc(7698) FROM dual;
SELECT empno,ename,FIND_LOC(empno) 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';--모두 출력하면 너무 많기 떄문에 AND name = 'TAX'로 보고 싶은 함수만 본다
- 생성된 함수 확인하기
- 데이터 사전(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';--모두 출력하면 너무 많기 떄문에 AND name = 'TAX'로 보고 싶은 함수만 본다
프로시저
- 프로시저(반환하는 값이 없음)
CREATE OR REPLACE PROCEDURE hello_world --전달하는 데이터가 없다면 소괄호를 명시하지 않아도됨.
IS
--변수선언
message VARCHAR2(100);
BEGIN
message := 'Hello Worid~~';
DBMS_OUTPUT.PUT_LINE(message);
END;
프로시저 실행
EXECUTE HELLO_WORLD;
--둘 중 뭘 사용해도 상관없음
EXEC HELLO_WORLD;
--------------------------------
CREATE OR REPLACE PROCEDURE hello_oracle(p_message IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(p_message);
END;
SELECT text
FROM user_source
WHERE type ='FUNCTION' AND name = 'TAX';
EXEC 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' AND name = 'HELLO_ORACLE' --AND를 안쓰면 위에 2개 다 나온다.
- 부서테이블에 부서정보를 입력하는 프로시저를 생성
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
--PRAMTER 변수에 입력받은 값으로 부서(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);
SELECT *FROM emp;
- 부서번호를 통해서 부서명과 부서의 위치 구하기
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
--PARAMETER 변수로부터 부서번호를 받아 해당 부서의 정보 질의
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);
결과
ACCOUNTING,NEW YORK
- 사원의 입사한 연도를 입력해서 사원정보 구하기
CREATE OR REPLACE PROCEDURE info_hiredate(p_year IN VARCHAR2)
IS
--%ROWTYPE으로 데이터 타입이 지정되어 있는 사원테이블(emp)의 하나의 행이 가지는 모든 컬럼의 데이터 타입을 가져옴.(레코드형태)
e_emp emp%ROWTYPE;
BEGIN
SELECT empno,ename,sal
--단일행일 경우에 INTO를 사용할 수 있지만 다중행일 경우에는 오류발생.(다중행일 경우 커서를 사용해야함)
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;
하나의 행이 반환되어 에러가 발생하지 않음
EXEC INFO_HIREDATE('1980');
결과
7369,SMITH,800
- 커서를 이용하여 질의 수행 결과 반환되는 여러 행을 처리
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');
결과
7499,ALLEN,1600
7521,WARD,1250
7566,JONES,2975
7654,MARTIN,1250
7698,BLAKE,2850
7782,CLARK,2450
7839,KING,5000
7844,TURNER,1500
7900,JAMES,950
7902,FORD,3000
- SALES 부서에 속한 사원의 정보 보기
CREATE OR REPLACE PROCEDURE emp_info(p_dept 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); --dept 테이블의 dname 열이 대소문자에 민감하지 않게 하기 위해서
--변수 선언
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');
결과
7499,ALLEN
7521,WARD
7654,MARTIN
7698,BLAKE
7844,TURNER
7900,JAMES
9000,PETER
실습문제
- 1.업무(job)를 입력하여 해당 업무를 수행하는 사원들의 사원번호,이름,급여,업무를 출력하시오.(job_info)
--내가 쓴답
CREATE OR REPLACE PROCEDURE job_info(p_job emp.job%TYPE)
IS
--커서선언
CURSOR emp_cur IS
SELECT empno,ename,sal,job
FROM emp
WHERE job = p_job;
e_empno emp.empno%TYPE;
e_ename emp.ename%TYPE;
e_sal emp.sal%TYPE;
e_job emp.job%TYPE;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO e_empno,e_ename,e_sal,e_job;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(e_empno || ',' || e_ename ||','|| e_sal ||','||e_job);
END LOOP;
CLOSE emp_cur;
END;
EXEC JOB_INFO('MANAGER');
--강사님답
CREATE OR REPLACE PROCEDURE job_info(p_job emp.job%TYPE)
IS
--커서 선언
CURSOR emp_cur IS
SELECT empno,ename,sal,job
FROM emp WHERE job = p_job;
--변수 선언
e_emp emp%ROWTYPE;
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');
결과
7566, JONES, 2975, MANAGER
7698, BLAKE, 2850, MANAGER
7782, CLARK, 2450, MANAGER
9000, PETER, 6000, MANAGER
- 2.사원번호와 새 업무를 입력하면 EMP 테이블의 해당 사원의 업무를 갱신할 수 있는 프로시저를 작성하시오
CREATE OR REPLACE PROCEDURE change_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 is failed');
ROLLBACK;
END;
EXEC change_job(7369,'DRIVER');
SELECT ename,job FROM emp WHERE empno = 7369;
book 테이블 만들고 연습
- book 테이블 만들기
CREATE TABLE book( bookid NUMBER PRIMARY KEY,
bookname VARCHAR2(60) NOT NULL,
publisher VARCHAR2(60) NOT NULL,
price NUMBER NOT NULL
);
INSERT INTO book VALUES (1, '자바를 찾아서','서울','30000'); --첫번 째 숫자는 프라이머리 키 이기 때문에 숫자가 중복되면 안됨.
INSERT INTO book VALUES (2, '도시','천국','10000');
INSERT INTO book VALUES (3, '하늘','신라','50000');
COMMIT;
SELECT * FROM book;
- 동일한 도서가 있는지 점검한 후 동일한 도서가 없으면 삽입하고 동일한 도서가 있으면 가격을 업데이트하는 프로시저를 작성.(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;
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;
--BEGIN아래 쓰는 이유
--SELECT COUNT(*) FROM book
--WHERE bookname = '하늘'; --테이블 안에 값이 있으면 1 없으면 0
EXEC BOOK_INFO(4,'스포츠','쿨서울',20000);
EXEC BOOK_INFO(5,'도시','천국',50000);
SELECT * FROM book;
EXEC BOOK_INFO(4,'도시2','천국',50000);
결과
ERROR!!
패키지(23일차)
패키지는 업무와 관련된 Stored Procedure 및 Stored function 을 관리하고, 이를 패키지 단위로 배포할 때 유용하게 사용됨.
트리거(23일차)
트리거는 데이터의 변경(INSERT,DELETE,UPDATE)문이 실행될 때 자동으로 같이 실행되는 프로시저를 말함. 오라클은 기본적으로 실행 전(BEFORE)과 실행 후 (AFTER) 트리거를 지원
'쌍용교육(JAVA) > PL\SQL' 카테고리의 다른 글
| 쌍용교육 -PL/SQL 수업 21일차 PL/SQL 서브프로그램 (0) | 2024.04.13 |
|---|---|
| 쌍용교육 -PL/SQL 수업 21일차 (IF문,CASE문,LOOP문,CURSOR) (0) | 2024.04.13 |
| 쌍용교육 -PL/SQL 수업 21일차 (레코드) (0) | 2024.04.12 |
| 쌍용교육 -PL/SQL 수업 21일차 콜렉션(1.varray2.중첩 테이블3.Associative array(index-by table)4.콜렉션을 데이터베이스 객체로 생성) (0) | 2024.04.12 |
| 쌍용교육 -PL/SQL 수업 21일차 변수와상수(1.변수2.상수3.%TYPE4.%ROWTYPE) (0) | 2024.04.12 |
