PL-SQL/procedure_update.sql at master · devheyrin/PL-SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
set serveroutput on
set verify off

create or replace procedure p_sawon_up
	(v_sabun sawon.sabun%type,
    v_pay sawon.sapay%type,
    v_sajob sawon.sajob%type,
    v_dname dept.dname%type)
is
    v_comm sawon.comm%type;
    v_samgr sawon.sabun%type;
    v_deptno sawon.deptno%type;

begin

    -- 커미션
    if v_sajob = '대리' then
        v_comm := v_pay * 0.1;
    elsif v_sajob = '과장' then
        v_comm := v_pay * 0.15;
    elsif v_sajob = '부장' then
        v_comm := v_pay * 0.2;
    else
        v_comm := 0;
    end if;

    -- 관리자번호
    if v_dname = '영업부' then
        v_samgr := 3;
    elsif v_dname = '총무부' then
        v_samgr := 10;
    elsif v_dname = '전산부' then
        v_samgr := 6;
    elsif v_dname = '관리부' then
        v_samgr := 1;
    else
        v_samgr := 0;
    end if;

    -- deptno
    select deptno into v_deptno from dept where dname = v_dname;

    -- update
    update sawon
    set
    sapay = v_pay,
    sajob = v_sajob,
    comm = v_comm,
    deptno = v_deptno,
    samgr = v_samgr
    where sabun = v_sabun;
end;
/

-- exec p_sawon_up(209, 3500, '과장', '관리부')