[Oracle] PL/SQL class 2 - 변수, 커서,

2023.11.09

 

SQL에서 사용하는 select는 plsql에서 변수로 사용되지 않음.

컬럼명은 변수로 사용할 수 없다!! ==> select 컬럼명 into 사용

 

set serveroutput on

 

(설명)

입력한 사번의 사원명을 출력

set serveroutput on

accept bun prompt '사번입력 -->'

declare

             v_name sawon.saname%type;                // 테이블명.컬럼명%type 으로 변수선언 

             v_pay sawon.sapay%type

begin

           select saname, sapay into v_name, v_pay from sawon  where sabun=&bun;         // into+변수 선언해야 됨.

           dbms_ouptput.put_line('이름: '|| v_name || ', 급여: ' || v_pay )

end;

/

 

## PL/SQL에서의 변수 선언 방법

v_name                     sawon.saname%type;                          // sawon.sname과 같은 타입(길이) 선언 속성

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, ...   변수를 한 번에 선언

                                  

 

(연습1)

사번을 입력받아 사원의 급여를 기준으로 커미션을 변경(update)하는 프로그램

set serveroutput on
set verify off

alter table sawon add comm number(10);             

                                                               // comm 테이블 생성--> alter table 테이블명 add 생성할 컬럼명 number(자리수)

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;

                                                                                            //update 테이블 set 컬럼 = 값 where 조건대상컬럼 = 조건값
             commit;          // 실제 sawon 테이블에 저장
                          dbms_output.put_line(v_bun||'번 사원의 커미션은' || v_comm || '으로 변경 되었습니다.');
end;
/

 

 

PL/SQL 4가지 프로그램 단위

## 1) 커서 (cur)

대기업 단위에서 DB처리 시(ex.급여 등) 반복문을 처리할 때 사용하는 프로그램

 

** 커서에서 사용하는 속성 종류

-- %isopen                        : 커서가 열려 있다.

-- %notfound                     : 가져올 해이 없다.

-- %found                          : 존재하는 행이 있다.

-- %rowcount                     : 처리된 행수

 

(설명2)

사원 테이블에서 이름과 급여를 출력하시오.(커서 사용)

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;                 // fetch(행갖고오기) + mycur + into  --> select 컬럼명 + into 와 비슷
              exit when(mycur%notfound);                       // 가저올 행이 없다면, 오픈한 커서가 참일때 닫는다 란 명령어
                             dbms_output.put_line('이름: '|| v_name|| ',급여:' || v_pay);
              end loop;
              close mycur;                                                // 모든 커서가 돌아간 이후 최종적으로 닫는 명령어
end;
/

 

 

(연습2)
사번을 입력받은 직원의 커미션을 모두 업데이트 하세요.

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

             cnt number(3);                                                 // 변경된 내역을 정수로 확인
begin
              if mycur%isopen then close mycur;
              end if;
              open mycur;
                             loop
              fetch mycur into v_name, v_pay, v_comm, v_bun;
              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;
                            else v_comm :=0;
              end if;
              update sawon set comm = v_comm where sabun=v_bun;

                           cnt :=mycur%rowcount;
                             end loop;

              commit;

                             dbms_output.put_line('업데이트 한 사원 수는' || cnt || '명입니다.');
              close mycur;

end;
/

## 프로시져 -- 객체

## 함수 -- 객체

## 트리거 -- 객체