#1
p.353 예제 안되므로 여쭤볼 것.
#2
PL/SQL 복습
1.
DECLARE 선언부 - 옵션
BEGIN 실행부 - 필수
EXCEPTION - 예외처리부 - 옵션
END
2.
쿼리문을 수행하기 위해서 ' / ' 가 반드시 입력되어야 하며, PL/SQL 블록은 행에 ' / '가 있으면 종결된 것으로 간주.
#2
커서
1. SELECT 문의 수행 결과가 여러 개의 로우로 구해지는 경우에 모든 로우에 대해 처리를 하려면 커서를 사용해야 합니다.
2. 커서는 CURSOR, OPEN, FETCH, CLOSE 4단계 명령에 의해 사용됩니다.
3. 키워드 CURSOR 뒤에 커서의 이름을 기술하고 IS 뒤에 커서 내에서 수행할 SELECT 문을 정의합니다. SELECT 문장에는 INTO 절을 포함하지 않아야 합니다.
4. CURSOR FOR LOOP는 명시적 CURSOR에서 로우를 처리합니다. CURSOR가 내부적으로 OPEN되고, FETCH되고 나서 CLOSE되기 때문에 OPEN ~ FETCH ~ CLOSE 과정 없이 DECLARE 절에서 선언만하고 바로 사용할 수 있어 간편합니다. CURSOR의 데이터를 읽어와서 저장할 변수도 따로 선언할 필요가 없습니다.
5.
만들어진 stored procedure list 보기
-- select * from user_procedures;
프로시저의 내용까지 확인
6.
SQL> CREATE OR REPLACE PROCEDURE EMP_EX01
2 IS
3 V_EMPNO EMP.EMPNO%TYPE; --변수 선언
4 V_ENAME EMP.ENAME%TYPE;
5 V_SAL NUMBER(7, 2);
6 CURSOR EMP_CURSOR IS -- 1. 커서 선언 : 하나 이상의 ROW를 담을 객체
7 SELECT EMPNO, ENAME, SAL FROM EMP
8 WHERE DEPTNO = 20;
9 BEGIN -- 실제 실행 코드
10 OPEN EMP_CURSOR; --2. 커서 시작
11 LOOP -- 한 줄 이상 대입변수 출력, LOOP 구문 사용
12 FETCH EMP_CURSOR INTO V_EMPNO, V_ENAME, V_SAL; --3. 변수를 대입
13 EXIT WHEN EMP_CURSOR%ROWCOUNT > 5 OR EMP_CURSOR%NOTFOUND;
14 DBMS_OUTPUT.PUT_LINE(V_EMPNO||' '||V_ENAME||' '||V_SAL);
15 END LOOP;
16 CLOSE EMP_CURSOR;
17 END EMP_EX01;
18 /
8. EX_VIEW01을 호출하게 되면 사원의 이름, 봉급, 커미션이 측정된 사원만 출력하자.
SQL> CREATE OR REPLACE PROCEDURE EX_VIEW01
2 IS
3 R_ENAME EMP.ENAME%TYPE;
4 R_SAL EMP.SAL%TYPE;
5 R_COMM EMP.COMM%TYPE;
6 CURSOR RES IS
7 SELECT ENAME, SAL, COMM FROM EMP
8 WHERE COMM IS NOT NULL;
9 BEGIN
10 OPEN RES;
11 LOOP
12 FETCH RES INTO R_ENAME, R_SAL, R_COMM;
13 EXIT WHEN RES%NOTFOUND;
14 DBMS_OUTPUT.PUT_LINE(R_ENAME||' '||R_SAL||' '||R_COMM||' ');
15 END LOOP;
16 CLOSE RES;
17 END EX_VIEW01;
18 /
9. 사원테이블에서 부서번호 20번의 사번, 이름 봉급을 출력하자.
SQL> CREATE OR REPLACE PROCEDURE EMP_EX02
2 IS
3 V_EMP EMP%ROWTYPE;
4 CURSOR EMP_CURSOR IS
5 SELECT EMPNO, ENAME, SAL
6 FROM EMP
7 WHERE DEPTNO = 20;
8 BEGIN
9 FOR V_EMP IN EMP_CURSOR LOOP
10 EXIT WHEN EMP_CURSOR%ROWCOUNT >5 OR EMP_CURSOR%NOTFOUND;
11 DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO||' '||V_EMP.ENAME||' '||V_EMP.SAL);
12 END LOOP;
13 END EMP_EX02;
14 /
10. 실행 시 부서번호를 전달 커서를 통해 값을 전달해서 해당 부서번호를 출력하는 프로시저를 만들자.
SQL> CREATE OR REPLACE PROCEDURE EMP_EX03
2 IS
3 V_EMPNO EMP.EMPNO%TYPE;
4 V_ENAME EMP.ENAME%TYPE;
5 V_SAL NUMBER(7, 2);
6 CURSOR EMP_CURSOR(V_DEPTNO NUMBER) IS -- 커서에 매개변수 선언
7 SELECT EMPNO, ENAME, SAL
8 FROM EMP WHERE DEPTNO = V_DEPTNO;
9 BEGIN
10 OPEN EMP_CURSOR(10);
11 LOOP
12 FETCH EMP_CURSOR INTO V_EMPNO, V_ENAME, V_SAL;
13 EXIT WHEN EMP_CURSOR%ROWCOUNT > 5 OR EMP_CURSOR%NOTFOUND;
14 DBMS_OUTPUT.PUT_LINE(V_EMPNO||' '||V_ENAME||' '||V_SAL);
15 END LOOP;
16 CLOSE EMP_CURSOR;
17
18 OPEN EMP_CURSOR(20);
19 LOOP
20 FETCH EMP_CURSOR INTO V_EMPNO, V_ENAME, V_SAL;
21 EXIT WHEN EMP_CURSOR%ROWCOUNT > 5 OR EMP_CURSOR%NOTFOUND;
22 DBMS_OUTPUT.PUT_LINE(V_EMPNO||' '||V_ENAME||' '||V_SAL);
23 END LOOP;
24 CLOSE EMP_CURSOR;
25 END EMP_EX03;
26 /
11.
SQL> CREATE OR REPLACE PROCEDURE EMP_EX04(P_EMPNO IN EMP.EMPNO%TYPE)
2 IS
3 V_SAL EMP.SAL%TYPE;
4 V_UPDATE_ROW NUMBER;
5 BEGIN
6 SELECT SAL INTO V_SAL FROM EMP
7 WHERE EMPNO = P_EMPNO;
8
9 IF SQL%FOUND THEN -- 명시적커서(SQL%)를 찾았을 때 아래를 수행하라.
10 DBMS_OUTPUT.PUT_LINE('검색한 데이터가 존재합니다.:'||V_SAL);
11
12 UPDATE EMP SET SAL = SAL * 1.1
13 WHERE DEPTNO = P_EMPNO;
14 V_UPDATE_ROW := SQL%ROWCOUNT;
15 DBMS_OUTPUT.PUT_LINE('급여가 인상된 사원 수 :'||V_UPDATE_ROW);
16 END IF;
17 END;
18 /
12.
명시적 커서 : BEGIN안에서 지역변수처럼 사용
13.
Example 6-22 Passing Parameters to a Cursor FOR Loop
DECLARE
CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
SELECT * FROM employees WHERE job_id = job AND salary > max_wage;
BEGIN
FOR person IN c1('CLERK', 3000)
LOOP
-- process data record
DBMS_OUTPUT.PUT_LINE('Name = ' || person.last_name || ', salary = ' ||
person.salary || ', Job Id = ' || person.job_id );
END LOOP;
END;
/
Example 6-24 Cursor Variable Returning a %ROWTYPE Variable
DECLARE TYPE TmpCurTyp IS REF CURSOR RETURN employees%ROWTYPE; tmp_cv TmpCurTyp; -- declare cursor variable TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE; emp_cv EmpCurTyp; -- declare cursor variable
#4
함수
1. 정의 및 활용
- 보통 값을 계산하고 결과값을 반환하기 위해서 함수를 많이 사용
- 대부분 구성이 프로시져와 유사하지만 in 파라미터만 사용
- 반드시 반환될 값의 데이터타입을 return문에 선언하고 return문을 통해서 반드시 값을 반환
2. 형식
CREATE FUNCTION 함수명[파라미터]
RETURN DATATYPE
IS
[변수 선언 부분]
BEGIN
-- PL/SQL 블록에는 적어도 한개의 RETURN 문이 있어야 한다.
-- PL/SQL 블록은 함수가 수행할 내용을 정의한 몸체부분이다.
END;
3. 예제
ⓐ 사번을 입력받아 연봉을 리턴하는 함수
CREATE FUNCTION getAvg(v_id in emp.empno%type)
return number
is
v_sal emp.sal%type := 0;
vtot number := 0;
vcomm emp.comm%type;
begin
select sal, comm into v_sal, vcomm
from emp where empno = v_id ; -- 받은 사번을 입력한다.
vtot := v_sal * 12 + nvl(vcomm, 0); -- 정의된 변수를 사용한다. 연ㅅ나
return vtot; --리턴을 명시한다.
end;
/
[실행]
var salary number; -- 반환값을 저장할 변수
exec :salary := getAvg(7900) -- 값전달 실행 확인
print -- 출력 확인
[조회]
select empno, getAvg(empno)
from emp;
ⓑ 사번을 입력받아서 부서명을 되돌리는 함수를 작성하자.
SQL> create FUNCTION getDept(p_empno in emp.empno%type)
2 return varchar2 -- 문자열로 리턴하겠다
3 is
4 v_deptno emp.deptno%type; --select문에서 리턴을 하는 부서번호 대입
5 v_res varchar2(60); --출력구문, 부서명을 저장하는 함수
6
7 begin
8 select deptno into v_deptno from emp
9 where empno = p_empno; -- 사원번호에 해당하는 부서번호 출력
10
11 if v_deptno = 10 then
12 v_res := 'ACCOUNTING 부서 사원입니다';
13 elsif v_deptno = 20 then
14 v_res := 'Research 부서 사원입니다.';
15 elsif v_deptno = 30 then
16 v_res := 'SALES 부서 사원입니다';
17 elsif v_deptno = 40 then
18 v_res := 'OPERATIONS 부서 사원입니다.';
19 END IF;
20 RETURN V_RES;
21 END;
22 /
ⓒ
CREATE OR REPLACE PROCEDURE ex_getDept(p_empno in emp.empno%type, v_res out varchar2)
is
V_deptno emp.deptno%type ; -- SELECT문에서 리턴을 하는 부서번호 대입
BEGIN
SELECT DEPTNO INTO V_DEPTNO FROM EMP
WHERE EMPNO = P_EMPNO; -- 사원번호에 해당하는 부서번호 출력
IF V_DEPTNO = 10 THEN
V_RES := 'ACCOUNTING 부서 사원입니다';
ELSIF V_DEPTNO = 20 THEN
V_RES := 'RESEARCH 부서 사원입니다';
ELSIF V_DEPTNO = 30 THEN
V_RES := 'SALES 부서 사원입니다.';
ELSIF V_DEPTNO = 40 THEN
V_RES := 'OPERATIONS 부서 사원입니다.';
END IF;
END;
/
[실행]
SQL> var res varchar2(50)
SQL> execute ex_getDept(7900, :res);
SQL> print
4. apex로 프로시저 만들기
#5
연습
--QUESTION(1~5 프로시져, 6~10 함수)
--1. 사원테이블에서 모든 데이터를 출력하라
SQL> CREATE OR REPLACE PROCEDURE PRO_VIEW01
2 IS
3 R_EMPNO EMP.EMPNO%TYPE;
4 R_ENAME EMP.ENAME%TYPE;
5 R_JOB EMP.JOB%TYPE;
6 R_MGR EMP.MGR%TYPE;
7 R_HIREDATE EMP.HIREDATE%TYPE;
8 R_SAL EMP.SAL%TYPE;
9 R_COMM EMP.COMM%TYPE;
10 R_DEPTNO EMP.DEPTNO%TYPE;
11 CURSOR RES IS
12 SELECT * FROM EMP;
13 BEGIN
14 OPEN RES;
15 LOOP
16
17 FETCH RES INTO R_EMPNO, R_ENAME, R_JOB, R_MGR, R_HIREDATE, R_SAL, R_COMM, R_DEPTNO;
18 EXIT WHEN RES%NOTFOUND;
19 DBMS_OUTPUT.PUT_LINE(R_EMPNO||' '||R_ENAME||' '||R_JOB||' '||R_MGR||' '||R_HIREDATE||' '||R_SAL||' '||R_COMM||' '||R_DEPTNO);
20 END LOOP;
21 CLOSE RES;
22 END PRO_VIEW01;
23 /
--2. 사원테이블에서 사원번호, 사원이름, 월급을 출력하라
SQL> CREATE OR REPLACE PROCEDURE PRO_VIEW02
2 IS
3 R_EMPNO EMP.EMPNO%TYPE;
4 R_ENAME EMP.ENAME%TYPE;
5 R_SAL EMP.SAL%TYPE;
6 CURSOR RES IS
7 SELECT EMPNO, ENAME, SAL FROM EMP;
8 BEGIN
9 OPEN RES;
10 LOOP
11 FETCH RES INTO R_EMPNO, R_ENAME, R_SAL;
12 EXIT WHEN RES%NOTFOUND;
13 DBMS_OUTPUT.PUT_LINE(R_EMPNO||' '||R_ENAME||' '||R_SAL);
14 END LOOP;
15 CLOSE RES;
16 END PRO_VIEW02;
17 /
--3. 사원테이블에서 월급을 뽑는데 중복된 데이터가 없게 출력하라
SQL> CREATE OR REPLACE PROCEDURE PRO_VIEW03
2 IS
3 R_SAL EMP.SAL%TYPE;
4 CURSOR RES IS
5 SELECT DISTINCT SAL FROM EMP;
6 BEGIN
7 OPEN RES;
8 LOOP
9 FETCH RES INTO R_SAL;
10 EXIT WHEN RES%NOTFOUND;
11 DBMS_OUTPUT.PUT_LINE(R_SAL);
12 END LOOP;
13 CLOSE RES;
14 END PRO_VIEW03;
15 /
--4. 사원테이블에서 사원이름과 월급을 출력하는데 각각의 컬럼명을
-- '이 름','월 급'으로 바꿔서 출력하라. 단, ALIAS에 공백추가
SQL> CREATE OR REPLACE PROCEDURE PRO_VIEW04
2 IS
3 R_ENAME EMP.ENAME%TYPE;
4 R_SAL EMP.SAL%TYPE;
5 CURSOR RES IS
6 SELECT ENAME, SAL FROM EMP;
7 BEGIN
8 OPEN RES;
9 DBMS_OUTPUT.PUT_LINE('이 름'||' '||'월 급');
10 LOOP
11 FETCH RES INTO R_ENAME, R_SAL;
12 EXIT WHEN RES%NOTFOUND;
13 DBMS_OUTPUT.PUT_LINE(R_ENAME||' '||R_SAL);
14 END LOOP;
15 CLOSE RES;
16 END PRO_VIEW04;
17 /
--5. 사원테이블에서 사원이름, 월급을 뽑고, 월급과 커미션을 더한 값을
-- 출력하는데 컬럼명을 '실급여'이라고 해서 출력하라.
-- 단, NULL값은 나타나지 않게 하라.
SELECT ENAME, SAL, (ENAME + NVL(COMM,0)) "실급여" FROM EMP;
--6. 사원테이블에서 'SCOTT'이라는 사원의 사원번호, 이름, 월급을 출력하라
CREATE FUNCTION EX06( MYNAME IN EMP.ENAME%TYPE )
RETURN SYS_REFCURSOR
IS
RES SYS_REFCURSOR;
BEGIN
OPEN RES FOR
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE ENAME =MYNAME;
RETURN RES;
END;
SQL> VAR MY REFCURSOR
SQL> EXEC :MY := EX06('SCOTT');
SQL> CREATE OR REPLACE FUNCTION PRO_VIEW06(
2 V_ENAME IN EMP.ENAME%TYPE,
3 V_EMPNO OUT EMP.EMPNO%TYPE,
4 V_ENAME OUT EMP.ENAME%TYPE,
5 V_SAL OUT EMP.SAL%TYPE)
6 RETURN NUMBER
7 IS
8 V_EMPNO EMP.EMPNO%TYPE;
9 V_ENAME EMP.ENAME%TYPE;
10 V_SAL EMP.SAL%TYPE;
11 BEGIN
12 SELECT EMPNO, SAL
13 INTO V_EMPNO, V_SAL
14 FROM EMP
15 WHERE ENAME = V_ENAME;
16 SELECT ENAME
17 INTO V_ENAME
18 FROM EMP
19 WHERE ENAME = V_ENAME;
20 DBMS_OUTPUT.PUT_LINE('성 명: ' ||V_ENAME);
21 DBMS_OUTPUT.PUT_LINE('사 번: ' ||TO_CHAR(V_EMPNO));
22 DBMS_OUTPUT.PUT_LINE('급 여: ' ||TO_CHAR(V_SAL));
23 RETURN EMPNO;
24 END;
25 /
문제5) EMP 테이블에서 이름을 입력 받아 부서번호,부서명,급여를 검색하는 FUNCTION을 작성하여라. 단 부서번호를 RETURN에 사용하여라.
CREATE OR REPLACE FUNCTION emp_disp( v_ename IN emp.ename%TYPE, v_dname OUT dept.dname%TYPE, v_sal OUT emp.sal%TYPE) RETURN NUMBER IS v_deptno emp.deptno%TYPE; v_dname_temp dept.dname%TYPE; v_sal_temp emp.sal%TYPE; BEGIN SELECT sal,deptno INTO v_sal_temp,v_deptno FROM emp WHERE ename = UPPER(v_ename); SELECT dname INTO v_dname_temp FROM dept WHERE deptno = v_deptno; v_dname := v_dname_temp; v_sal := v_sal_temp; DBMS_OUTPUT.PUT_LINE('성 명 : ' || v_ename); DBMS_OUTPUT.PUT_LINE('부서번호 : ' || TO_CHAR(v_deptno)); DBMS_OUTPUT.PUT_LINE('부 서 명 : ' || v_dname_temp); DBMS_OUTPUT.PUT_LINE('급 여 : ' || TO_CHAR(v_sal_temp,'$999,999')); RETURN v_deptno; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('입력한 MANAGER는 없습니다.'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('자료가 2건 이상입니다.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('기타 에러입니다.'); END; / |
출처: https://laserbit.tistory.com/entry/오라클-FUNCTION-생성 [미친듯이파고든다]
/
★
DECLARE
V_EMP EMP%ROWTYPE;
BEGIN
V_EMP := EX06('KING');
DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO || ' ' || V_EMP.ENAME || ' ' || V_EMP.SAL);
END;
/
--7. 사원테이블에서 직위가 'SALESMAN'인 사원의 사원번호, 이름, 직위를
-- 출력하라
SELECT EMPNO, ENAME, JOB FROM EMP WHERE JOB = 'SALESMAN';
CREATE FUNCTION EX06( MYJOB IN EMP.JOB%TYPE )
RETURN SYS_REFCURSOR
IS
RES SYS_REFCURSOR;
BEGIN
OPEN RES FOR
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE JOB =MYJOB;
RETURN RES;
END;
--8. 사원테이블에서 사원번호가 7499, 7521, 7654인 사원의 사원번호, 이름
-- 월급을 출력하라
SELECT EMPNO, ENAME, SAL FROM EMP WHERE EMPNO IN (7499, 7521, 7654)
--9. 사원테이블에서 월급이 1500에서 3000사이인 사원의 사원번호, 이름,
-- 월급을 출력하라.
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL BETWEEN 1500 AND 3000;
--10. 사원테이블에서 이름의 첫글자가 A이고 마지막 글자가 N이 아닌 사원의
-- 이름을 출력하라
SELECT ENAME FROM EMP WHERE SELECT SUBSTR(ENAME, 1) FROM EMP = 'A' AND SELECT SUBSTR(ENAME, -1) FROM EMP != 'N'
'국비교육' 카테고리의 다른 글
| 1월 30일 국비교육 (0) | 2019.01.30 |
|---|---|
| 1월 29일 국비교육 (0) | 2019.01.29 |
| 1월 25일 국비교육 (0) | 2019.01.25 |
| 1월 24일 국비교육 (0) | 2019.01.24 |
| 1월 23일 국비교육 (0) | 2019.01.23 |
댓글