<DataBase_231109목> PL/SQL 변수, 커서, 프로시저, 함수, 트리거

-- 컬럼명은 변수로 사용할 수 없다 -> select ~ into ***

-- 입력한 사번의 사원명을 출력
accept bun prompt '사번 입력->';
declare
v_name sawon_saname%type;
begin
select saname into v_name from sawon where sabun=&bun;
dbms_output.put_line('이름 : ' || v_name ||', 급여 : ' || v_pay);
end;
/

--*** PL/SQL에서의 변수 선언 방법
--v_name sawon.saname%type; // sawon.saname과 같은 타입(길이) 선언 속성
--v_pay  constant  sawon.sapay%type := 2000;  // 변경 불가능한 상수형 변수 선언
--v_bun sawon.sabun%type := 15 not null; // 변수에 not null 제약설정
--v_loc dept.loc%type := '서울';
--a sawon%rowtype; // a.sabun, a.saname, ... 변수를 한 번에 선언

 

--------------------------------------------------------

 

<커서, 프로시저, 함수, 트리거>

 

 

--------------------------------------------------------

 

<커서>

 

%isopen : 커서가 열려있다.

%notfound : 가져올 행이 없다

%found : 존재하는 행이 있다

%rowcount : 처리된 행 수

 

declare
	cursor mycur is select saname, sapay from sawon;
    v_name sawon.saname%type;
    v_pay sawon.sapay%type;
begin
	if mycur%isopen then close mycur;
    end if;
    
    open mycur;
    
    loop
    	fetch mycur into v_name, v_pay;
        exit when(mycur%notfound);
        
        dbms_output.put_line('이름:'||v_name||',급여:'||v_pay);
    end loop;
    close mycur;
end;
/

 

 

--------------------------------------------------------

 

// 연습문제

 

1. alter table sawon add comm number(10); 이 조건을 추가하여 
-- 1. 사번을 입력받아 2.사원의 급여를 기준으로 3.커미션을 변경(update)하는 프로그램을 짜라
-- 커미션 기준
-- 급여가 1000 미만 -> 급여의 10%
-- 급여가 1000~2000 -> 급여의 15%
-- 급여가 2000 초과 -> 급여의 20%
-- 급여가 null -> 0

 

<내가 풀이한>

set verify off
set serveroutput on

accept bun prompt '사번 입력->'

declare
	v_pay sawon.sapay%type;
	v_comm sawon.comm%type;
    
begin
select sapay, comm into v_pay, v_comm from sawon
where sabun=&bun;

if v_pay<1000 then v_comm:=v_pay*0.1;
elsif v_pay>=1000 and v_pay<=2000 then v_comm:=v_pay*0.15;
elsif v_pay>2000 then v_comm:=v_pay*0.2;
elsif v_pay is null then v_comm:=0;
else v_comm:=0;
end if;

update sawon set comm=v_comm
where sabun=&bun;
end;
/

 

<선생님이 풀이한>

set verify off
set serveroutput on

accept bun prompt '사번 입력->'

declare
	v_bun sawon.sabun%type := &bun;
	v_pay sawon.sapay%type;
	v_comm sawon.comm%type;
    
begin
	select sapay into v_pay from sawon where sabun=v_bun;

	if v_pay<1000 then v_comm:=v_pay*0.1;
	elsif v_pay<=2000 then v_comm:=v_pay*0.15;
	elsif v_pay>2000 then v_comm:=v_pay*0.2;
	else v_comm:=0;
	end if;

	update sawon set comm=v_comm where sabun=v_bun;
    commit;
    
    dbms_output.put_line(v_bun || '번 사원의 커미션은' || v_comm || '으로 변경!');
end;
/

 

+ 커서 추가한 코드

set verify off

declare
    cursor mycur is select sabun, sapay, comm from sawon;
    v_bun sawon.sabun%type;
    v_pay sawon.sapay%type;
    v_comm sawon.comm%type;

begin
    if mycur%isopen then close mycur;
    end if;
    
    open mycur;
loop
        fetch mycur into v_bun, v_pay, v_comm;
        exit when(mycur%notfound);

if v_pay<1000 then v_comm:=v_pay*0.1;
elsif v_pay>=1000 and v_pay<=2000 then v_comm:=v_pay*0.15;
elsif v_pay>2000 then v_comm:=v_pay*0.2;
elsif v_pay is null then v_comm:=0;
else v_comm:=0;
end if;

update sawon set comm=v_comm
where sabun=v_bun;

    dbms_output.put_line(v_bun||'번 사원의 커미션은'||v_comm||'으로 변경!');
    end loop;
    commit; --commit 처리를 통해 데이터 완전 박제
    close mycur;
end;
/