연습문제
부서번호를 입력받아서(파라미터로 받아서-p_deptno number) 부서 평균 급여(변수선언)보다 많이 받으면 10%, 적거나 같으면 20% 인상을 적용하여
급여 테이블을 수정(update - commit)하는 프로시저를 작성하시오.
변수에 값을 담기
- SELECT ...INTO -> 한 번에 한 건만 가능 - XXXVO
- FETCH..INTO -> 여러건을 처리 - 한 행씩 접근하기 - 반복문 결합하여 사용 List<VO>, List<Map>
OPEN emp_cur;
CLOSE emp_cur;
변수 rate number(3,1) - 99.9
avg_sal number(7,2) - 99999.99<CURSOR 정의>
--커서 선언하기
CURSOR emp_cur IS
SELECT empno, ename, sal
FROM emp
WHERE deptno = p_deptno;
--급여평균을 구한다
SELECT avg(sal) INTO avg_sal
FROM emp
WHERE deptno = p_deptno;
LOOP
FETCH emp_cur INTO v_empno, v_ename, v_sal
EXIT WHEN emp_cur%NOTFOUND;
IF v_sal > avg_sal THEN
rate :=1.1;
ELSIF v_sal <= avg_sal THEN
rate:=1.2;
END IF;
UPDATE emp
SET sal = sal * rate
WHERE empno = v_empno;
END LOOP;create or replace procedure proc_emp_update2(p_deptno IN number)
is
--평균급여 담기
avg_sal number(7,2):=0.0;
--커서에서 꺼내온 사원번호 담기
v_empno number(5):=0;
-- 커서에서 꺼내온 급여 담기
v_sal number(7,2):=0;
-- 커서에서 꺼내온 이름 담기
v_ename varchar2(20):=' ';
--인상요율담을 변수
rate NUMBER(3,1) :=0;
CURSOR emp_cur IS
SELECT empno, ename, sal
FROM emp
WHERE deptno = p_deptno;
BEGIN
SELECT avg(sal) INTO avg_sal
FROM emp
WHERE deptno = p_deptno;
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_empno, v_ename, v_sal;
EXIT WHEN emp_cur%NOTFOUND;--커서에 값이 없을 때
IF v_sal > avg_sal THEN--10%인상요율
rate := 1.1;
ELSIF v_sal <= avg_sal THEN--20%인상요율
rate := 1.2;
END IF;
UPDATE emp
SET sal = sal*rate
WHERE empno = v_empno;
END LOOP;
commit;
CLOSE emp_cur;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
/수험자가 제공한 답변과 데이터베이스에 저장된 정답을 비교하여 시험지를 평가
create or replace procedure proc_account1(p_examno in varchar2, msg out varchar2)
is
--수험생이 입력한 1번 답안
u1 number(1):=0;
--수험생이 입력한 2번 답안
u2 number(1):=0;
--수험생이 입력한 3번 답안
u3 number(1):=0;
--수험생이 입력한 4번 답안
u4 number(1):=0;
--수험생이 맞춘 정답 수를 담음
r1 number(3):=0;
--수험생이 틀린 수를 담음
w1 number(3):=0;
jdap number(2):=0;--커서에서 꺼낸값담기
d_no number(3):=1;--문제번호를 담기
cursor dap_cur is
select dap from sw_design;
begin
open dap_cur;
SELECT dap1, dap2, dap3, dap4 INTO u1, u2, u3, u4
FROM exam_paper
where exam_no = p_examno;
loop
fetch dap_cur into jdap;
exit when dap_cur%notfound;
if d_no=1 then
if jdap = u1 then
r1 := r1 +1;
else
w1 := w1 + 1;
end if;
elsif d_no=2 then
if jdap = u2 then
r1 := r1 +1;
else
w1 := w1 + 1;
end if;
elsif d_no=3 then
if jdap = u3 then
r1 := r1 +1;
else
w1 := w1 + 1;
end if;
elsif d_no=4 then
if jdap = u4 then
r1 := r1 +1;
else
w1 := w1 + 1;
end if;
end if;
d_no := d_no + 1;
end loop;
close dap_cur;
msg :='정답 : '||r1|| ' 오답 : '||w1;
update exam_paper
set right_answer = r1,
wrong_answer = w1
where exam_no = p_examno;
commit;
end;SELECT decode(d_no,1, dap)
FROM sw_design;
------------------------------
SELECT decode(d_no,1, dap), decode(d_no,2, dap), decode(d_no,3, dap), decode(d_no,4, dap)
FROM sw_design;
------------------------------
SELECT ceil(d_no/4), decode(d_no,1, dap), decode(d_no,2, dap), decode(d_no,3, dap), decode(d_no,4, dap)
FROM sw_design
GROUP BY ceil(d_no/4);
------------------------------
SELECT ceil(d_no/4)
FROM sw_design
GROUP BY ceil(d_no/4);
------------------------------
SELECT
min(decode(d_no,1,dap))
,min(decode(d_no,2,dap))
,min(decode(d_no,3,dap))
,min(decode(d_no,4,dap))
FROM sw_design
GROUP BY ceil(d_no/4);
------------------------------
SELECT
max(decode(d_no,1,dap))
,max(decode(d_no,2,dap))
,max(decode(d_no,3,dap))
,max(decode(d_no,4,dap))
FROM sw_design
GROUP BY ceil(d_no/4);
------------------------------
SELECT
sum(decode(d_no,1,dap))
,sum(decode(d_no,2,dap))
,sum(decode(d_no,3,dap))
,sum(decode(d_no,4,dap))
FROM sw_design
GROUP BY ceil(d_no/4);
------------------------------
SELECT
count(decode(d_no,1,dap))
,count(decode(d_no,2,dap))
,count(decode(d_no,3,dap))
,count(decode(d_no,4,dap))
FROM sw_design
GROUP BY ceil(d_no/4);
------------------------------
SELECT
avg(decode(d_no,1,dap))
,avg(decode(d_no,2,dap))
,avg(decode(d_no,3,dap))
,avg(decode(d_no,4,dap))
FROM sw_design
GROUP BY ceil(d_no/4);
------------------------------
SELECT
avg(decode(d_no,1,dap)) d1
,avg(decode(d_no,2,dap)) d2
,avg(decode(d_no,3,dap)) d3
,avg(decode(d_no,4,dap)) d4
FROM sw_design
GROUP BY ceil(d_no/4);
------------------------------
SELECT
d1,d2,d3,d4 INTO d1,d2,d3,d4
FROM (
SELECT
avg(decode(d_no,1, dap)) d1
,avg(decode(d_no,2, dap)) d2
,avg(decode(d_no,3, dap)) d3
,avg(decode(d_no,4, dap)) d4
FROM sw_design
GROUP BY ceil(d_no/4)
)exam paper
트리거(Trigger)
- 한 테이블에 날짜로 선언된 컬럼이 있다고 가정했을 때, 이 컬럼에 데이터는 항상 토요일과 일요일만 입력되어야 한다고 했다면 원천적으로 막을 수 있는 방법이 있다.
→ 트리거를 이용해서 UPDATE, INSERT시에 해당 컬럼의 데이터를 checking하면 된다. 또 Insert, Delete, Update시에 항상 특정 테이블에 작업실행에 대한 history가 필요할 경우에도 Trigger를 사용하면, 별도의 작업 없이도 Trigger에서 이를 실행 할 수 있다.
[Syntax]
Create Trigger 트리거명
Before (or After)
UPDATE OR DELETE OR INSERT ON 테이블명
[FOR EACH ROW]
DECLARE
변수선언부
BEGIN
프로그램 코딩부
END;- 위에서 Create문 다음 줄의 BEFORE는 Update, Delete, Insert로 인한 데이터변경이 생기기 전을 의미하고, AFTER는 반대를 의미한다. 주로 BEFORE를 사용하는데, Trigger를 사용하는 주목적이 잘못된 데이터를 막고자 함인데, 미리 Checking 하기 위해서는 BEFORE가 적당하다.
- 또 옵션으로 FOR EACH ROW가 있는데, 이것은 데이터 처리시에 건건이 모두 Trigger가 실행된다는 의미이다. 따라서 건건이 작업할 내용이 아니라면 사용하지 않는 것이 좋다.
- 왜냐하면 FOR EACH ROW가 선언됨에 따라 필요가 있든 없든 건건이 작업시에 계속해서 Trigger가 발생되기 때문에 필요없이 데이터베이스가 일을 하기
때문이다. - FOR EACH ROW를 선언했을 경우에는 Trigger에서 유용한 데이터 속성을 제공한다. Update, Delete, Insert는 사실 데이터를 변경하는 SQL문이기 때문에
항상 반영전 데이터와 반영 후 데이터를 분류할 수 있다. - 예를 들어 Insert문 같은 경우에는 새로 생기는 것이기 때문에 반영 전 데이터는 아무 것도 없는 것이고, 반영 후 데이터는 해당 데이터가 되겠죠. DELETE는 반대 개념이고, Update는 수정이기 때문에 당연히 수정전과 수정후 를 분류할 수 있다.
- 바로 이 반영전과 반영 후의 컬럼 데이터 값을 FOR EACH ROW 선언 후에
가져올 수 있다.
: OLD.컬럼명 => SQL반영 전 해당 컬럼 데이터
: NEW.컬럼명 => SQL변영 후 해당 컬럼 데이터
create or replace trigger trg_deptcopy
after
insert or update or delete on dept
for each row
begin
if inserting then
insert into dept_copy(deptno, dname, loc)
values(:new.deptno, :new.dname, :new.loc);
elsif updating then
update dept_copy
set dname = :new.dname, loc = :new.loc
where deptno = :old.deptno;
elsif deleting then
delete from dept_copy
where deptno = :old.deptno;
end if;
end;