8월5일 - 데이터입출력 1 : 프로시저

* 데이터 입출력 과정 : PL/SQL 프로시저, 함수, 트리거, 백업과 복원

 

PL/SQL 프로시저

 

[참고교재] - 컴원미디어 chap15/ 16/ 17

 

1. 프로시저?

  - 프로그래밍 언어의 특성을 수용한, SQL의 확장기능

  - PL/SQL : Procedural Language extension to SQL

  - SQL문장에서 변수 정의, 조건처리(if), 반복처리(loop, while, for) 등을 지원하며 오라클 자체에

    내장되어 있는 Procedure Language이다.

  - 리턴값이 있으면 FUNCTION으로, 리턴값없으면 프로시저

 

 

2. PL/SQL 형식

 

create or replace procedure 프로시저명(매개변수) is    내부변수선언 begin       명령어   end;  /  ->종결의 의미

 

 

3. PL/SQL 문법

 

declare     begin -> begin~ end {} 과 같은 의미   end; /

 

  ** 콘솔창 출력하기 위한 명령어
  set serveroutput on;

 

  1) 연산자

 

declare   --변수선언 및 대입   a number := 3;   b number := 5; begin      --콘솔창 출력   dbms_output.put_line('*실행결과*');   dbms_output.put_line(a);   dbms_output.put_line(b);   dbms_output.put_line(a+b);   --결합연산자   dbms_output.put_line(a || '+' || b '=' || (a+b)); end; /

연산자 출력결과

 

  2-1) 조건문 1

 

declare    --성적프로그램   uname varchar2(50) := '옥수수';   kor    number        := 100;   eng   number        := 95;   mat   number        := 80;    aver   number       := (kor+eng+mat)/3;   grade varchar2(50) := NULL; begin   if     aver>=90 then grade:='A';   elsif aver>=80 then grade:='B';   elsif aver>=70 then grade:='C';   elsif aver>=60 then grade:='D';   else grade :='F';   end if;     --출력   dbms_output.put_line('*실행결과*');   dbms_output.put_line('국어 : ' || kor);   dbms_output.put_line('영어 : ' || eng);   dbms_output.put_line('수학 : ' || mat);   dbms_output.put_line('평균 : ' || aver);   dbms_output.put_line('등급 : ' || grade); end; /

조건문 출력결과

 

  2-2) 조건문 2

     - aver 조건에 기호가 아닌 between으로도 사용이 가능하다. (자바와는 다른 사용법!)

     - 결과값은 위의 조건문 1과 같다.

 

declare    --성적프로그램   uname varchar2(50) := '옥수수';   kor      number      := 100;   eng     number      := 95;   mat     number      := 80;    aver    number       := (kor+eng+mat)/3;   grade  varchar2(50) := NULL; begin   if    aver  between 90 and 100 then grade:='A';   elsif aver between 80 and 89   then grade:='B';   elsif aver between 70 and 79   then grade:='C';   elsif aver between 60 and 69   then grade:='D';   else grade :='F';   end if;      --출력   dbms_output.put_line('*실행결과*');   dbms_output.put_line('국어 : ' || kor);   dbms_output.put_line('영어 : ' || eng);   dbms_output.put_line('수학 : ' || mat);   dbms_output.put_line('평균 : ' || aver);   dbms_output.put_line('등급 : ' || grade); end; /

 

  3-1) 반복문 1

 

declare   dan number := 4;   i   number default 0; begin   loop     i := i+1;     dbms_output.put_line(dan || '*' || i || '=' || (dan*i));     exit when i=9; -- i값이 9이면 반복문 빠져나옴   end loop; end; /

반복문 출력결과

 

  3-2) 반복문 2 : for문

 

declare   dan number := 4;   i   number default 0; begin   for i in 1..9 loop --for in 시작값..종료값     dbms_output.put_line(dan || '*' || i || '=' || (dan*i));     end loop; end; /

 

  3-3) 반복문 3 : while문

 

declare   dan number := 4;   i   number default 0; begin   while i<10 loop     i:=i+1;     exit when i=10;     dbms_output.put_line(dan || '*' || i || '=' || (dan*i));     end loop; end; /

 

4. PL/SQL 테이블과 연결하기

 

  1) 성적테이블 생성 

     - 기존에 만들어져있던 sungjuk테이블과 sequence를 삭제하고 새로 만든다. 

 

--sequence 삭제 후 재생성 drop   sequence sungjuk_seq; create sequence sungjuk_seq; --sungjuk 테이블 삭제 후 재생성 drop   table sungjuk; create table sungjuk(  sno      number      primary key  ,uname varchar(50)  not null  ,kor      number(3)   check(kor between 0 and 100)  ,eng     number(3)   check(eng between 0 and 100)  ,mat     number(3)   check(mat between 0 and 100)  ,tot      number(3)   default 0  ,aver    number(5,2) default 0  ,addr   varchar(30)   check(addr in ('Seoul','Jeju','Suwon','Busan'))   ,wdate date           default sysdate ); insert into sungjuk(sno,uname,kor,eng,mat,addr) values(sungjuk_seq.nextval,'무궁화',40,50,20,'Seoul'); insert into sungjuk(sno,uname,kor,eng,mat,addr) values(sungjuk_seq.nextval,'진달래',90,50,90,'Jeju'); insert into sungjuk(sno,uname,kor,eng,mat,addr) values(sungjuk_seq.nextval,'개나리',20,50,20,'Jeju'); insert into sungjuk(sno,uname,kor,eng,mat,addr) values(sungjuk_seq.nextval,'봉선화',90,90,90,'Seoul'); insert into sungjuk(sno,uname,kor,eng,mat,addr) values(sungjuk_seq.nextval,'나팔꽃',50,50,90,'Suwon'); insert into sungjuk(sno,uname,kor,eng,mat,addr) values(sungjuk_seq.nextval,'선인장',70,50,20,'Seoul'); insert into sungjuk(sno,uname,kor,eng,mat,addr) values(sungjuk_seq.nextval,'소나무',90,60,90,'Busan'); insert into sungjuk(sno,uname,kor,eng,mat,addr) values(sungjuk_seq.nextval,'참나무',20,20,20,'Jeju'); insert into sungjuk(sno,uname,kor,eng,mat,addr) values(sungjuk_seq.nextval,'홍길동',90,90,90,'Suwon'); insert into sungjuk(sno,uname,kor,eng,mat,addr) values(sungjuk_seq.nextval,'무궁화',80,80,90,'Suwon'); select * from sungjuk;

 

  2-1) sno=3 가져오기 

 

select * from sungjuk where sno=3;

 

  2-2) sno=3 가져오기 : 프로시저 이용 

 

declare   v_sno     number;   v_uname varchar2(50);   v_kor      number;   v_eng     number;   v_mat     number;   v_addr    varchar2(30);   v_wdate  date;   begin    --SQL문 작성   select sno, uname, kor, eng, mat, addr, wdate    into v_sno, v_uname, v_kor, v_eng, v_mat, v_addr, v_wdate   from sungjuk   where sno=3;      --출력   dbms_output.put_line('*실행결과*');   dbms_output.put_line('이름 : ' || v_uname);   dbms_output.put_line('국어 : ' || v_kor);   dbms_output.put_line('영어 : ' || v_eng);   dbms_output.put_line('수학 : ' || v_mat);   dbms_output.put_line('주소 : ' || v_addr);   dbms_output.put_line('작성일자 : ' || v_wdate);   end; /

sno=3 프로시저 결과

 

문제) 주소를 한글로 출력하시오 

        - if문도 가능, case~when도 가능하다.

 

declare   v_sno     number;   v_uname varchar2(50);   v_kor     number;   v_eng    number;   v_mat    number;   v_addr   varchar2(30);   v_wdate date;     v_juso   varchar2(30);       begin    --SQL문 작성   select sno, uname, kor, eng, mat, addr, wdate    into v_sno, v_uname, v_kor, v_eng, v_mat, v_addr, v_wdate   from sungjuk   where sno=3;      if    v_addr='Seoul' then v_juso:='서울';   elsif v_addr='Jeju'  then v_juso:='제주';   elsif v_addr='Suwon' then v_juso:='수원';   elsif v_addr='Busan' then v_juso:='부산';   end if;      dbms_output.put_line('*실행결과*');   dbms_output.put_line('이름 : ' || v_uname);   dbms_output.put_line('국어 : ' || v_kor);   dbms_output.put_line('영어 : ' || v_eng);   dbms_output.put_line('수학 : ' || v_mat);   dbms_output.put_line('주소 : ' || v_addr);   dbms_output.put_line('주소 : ' || v_juso);   dbms_output.put_line('작성일자 : ' || v_wdate);   end; /

출력결과 : 주소 한글로 출력

 

5. PL/SQL 참조변수

 

  1) %type
     : 테이블에서 한개 칼럼의 데이터타입 및 사이즈를 참조한다.

 

declare   v_sno   sungjuk.sno%type;   v_uname sungjuk.uname%type;   v_kor   sungjuk.kor%type;   v_eng   sungjuk.eng%type;   v_mat   sungjuk.mat%type;   v_addr  sungjuk.addr%type;   v_wdate sungjuk.wdate%type;   begin    --SQL문 작성   select sno, uname, kor, eng, mat, addr, wdate    into v_sno, v_uname, v_kor, v_eng, v_mat, v_addr, v_wdate   from sungjuk   where sno=3;      dbms_output.put_line('*실행결과*');   dbms_output.put_line('번호 : ' || v_sno);   dbms_output.put_line('이름 : ' || v_uname);   dbms_output.put_line('국어 : ' || v_kor);   dbms_output.put_line('영어 : ' || v_eng);   dbms_output.put_line('수학 : ' || v_mat);   dbms_output.put_line('주소 : ' || v_addr);   dbms_output.put_line('작성일자 : ' || v_wdate);   end; /

  2) %rowtype
     : 테이블의 row타입과 같다는 의미

 

declare   sj sungjuk%rowtype; --한줄을 기준으로 다 가져오겠다는 의미 begin    --SQL문 작성   select *   into sj   from sungjuk   where sno=3;      dbms_output.put_line('*실행결과*');   dbms_output.put_line('번호 : ' || sj.sno);   dbms_output.put_line('이름 : ' || sj.uname);   dbms_output.put_line('국어 : ' || sj.kor);   dbms_output.put_line('영어 : ' || sj.eng);   dbms_output.put_line('수학 : ' || sj.mat);   dbms_output.put_line('주소 : ' || sj.addr);   dbms_output.put_line('작성일자 : ' || sj.wdate);   end; /

 

6. 커서를 이용해서 복수행 처리

 

  1) fetch문
    - 오픈된 커서로부터 한 행을 인출한다
    - 형식) fetch 커서명 into 변수명

 

  2) 프로시저를 이용해서 sno>=5 레코드 조회 

 

declare   v_cursor sys_refcursor; --커서를 담는 변수선언 및 자료형   rec sungjuk%rowtype; --한 행 담을 수 있는 변수선언 begin   open v_cursor for select * from sungjuk where sno>=5; --cursor는 for문을 가리키고 있다   loop     fetch v_cursor into rec; --cursor를 한행인출해서 rec에 저장해라     exit when v_cursor%notfound; --자료가 없으면 빠져나감     dbms_output.put_line(rec.sno);     dbms_output.put_line(rec.uname);     dbms_output.put_line(rec.kor);     dbms_output.put_line(rec.eng);     dbms_output.put_line(rec.mat);     dbms_output.put_line(rec.addr);     dbms_output.put_line(rec.wdate);   end loop; end; /

 

 

 

PL/SQL 생성

 

1. 프로시저 생성 : create procedure 프로시저명; 

   프로시저 삭제 : drop   procedure 프로시저명;

 

2. 프로시저 생성 및 호출

 

--test 프로시저 생성 create or replace procedure test is begin   dbms_output.put_line('테스트'); end; --test 프로시저 호출 execute test;   --test 프로시저 삭제 drop procedure test;

 

 

---

 

PL/SQL - 성적프로시저

 

  **CRUD - Create/ Read/ Update/ Delete 관련한 프로시저

 

  1) 행추가 프로시저 (Create) 

 

create or replace procedure sungjukInsert (   --매개변수(parameter)   --in : 입력변수   v_uname in sungjuk.uname%type   ,v_kor  in sungjuk.kor%type   ,v_eng  in sungjuk.eng%type   ,v_mat  in sungjuk.mat%type   ,v_addr in sungjuk.addr%type ) is begin   insert into sungjuk(sno,uname,kor,eng,mat,aver,addr)   values(sungjuk_seq.nextval          ,v_uname          ,v_kor, v_eng, v_mat, (v_kor+v_eng+v_mat)/3          ,v_addr);   commit; end;

 

  - 행 추가 테스트 및 결과확인

 

--행추가 테스트 execute sungjukInsert('손흥민',100,100,100,'Seoul'); --결과확인 select * from sungjuk order by sno desc;

Create 프로시저 출력결과

 

  2) 행수정 프로시저 (Update)

 

create or replace procedure sungjukUpdate (   v_uname in sungjuk.uname%type   ,v_kor  in sungjuk.kor%type   ,v_eng  in sungjuk.eng%type   ,v_mat  in sungjuk.mat%type   ,v_addr in sungjuk.addr%type   ,v_sno  in sungjuk.sno%type ) is  begin   update sungjuk   set uname=v_uname, kor=v_kor, eng=v_eng, mat=v_mat, aver=(v_kor+v_eng+v_mat)/3, addr=v_addr   where sno=v_sno;   commit; end;

 

  - 행 수정 테스트 및 결과확인

 

--행수정 테스트 execute sungjukUpdate('김연아',100,100,100,'Suwon',3); --결과확인 select * from sungjuk order by sno desc;

Update 프로시저 출력결과

 

  3) 행삭제 프로시저 (Delete)

 

** delete from sungjuk where sno=3;   create or replace procedure sungjukDelete (   v_sno in sungjuk.sno%type ) is  begin   delete from sungjuk where sno=v_sno;   commit; end; execute sungjukDelete(3);

 

  - 행 삭제 테스트 및 결과확인

 

--행 삭제 테스트 execute sungjukDelete(3); --결과확인 select * from sungjuk order by sno desc;

 

  4) 상세보기 (Read)

 

** select * from sungjuk sno=3;   create or replace procedure sungjukRead (   --out 출력매개변수   v_cursor out sys_refcursor   ,v_sno sungjuk.sno%type ) is begin   open v_cursor for   select * from sungjuk where sno=v_sno;   --close v_cursor; 커서반납 end;

 

  5) 목록 (Read)

 

** select * from sungjuk order by sno desc;   create or replace procedure sungjukList (   v_cursor out sys_refcursor ) is begin   open v_cursor for   select * from sungjuk order by sno desc;   --close v_cursor; 커서반납 end;

 

  6) 레코드 갯수 프로시저

 

create or replace procedure sungjukCount (   v_cursor out sys_refcursor ) is begin   open v_cursor for   select nvl(count(*),0) from sungjuk;   --close v_cursor; 커서반납 end;

 

  7) 검색 프로시저

 

- 이름에 '나' 글자 있는지 검색   select uname from sungjuk where uname like '%나%';   - 이름 또는 주소에 알파벳 'o'가 있는지 검색   select uname, addr from sungjuk where uname like '%o%' or addr like 'o';     - 1이면 이름에서 검색, 2이면 이름 또는 주소 검색, 나머지는 검색하지 않음    create or replace procedure sungjukSearch (   v_cursor out sys_refcursor   ,v_code number   ,v_keyword varchar2 ) is begin   if v_code=1 then     open v_cursor for     select * from sungjuk     where uname like '%' || v_keyword || '%'     order by sno desc;   elsif v_code=2 then     open v_cursor for     select * from sungjuk     where uname like '%' || v_keyword || '%'         or addr  like '%' || v_keyword || '%'     order by sno desc;   else      open v_cursor for     select * from sungjuk order by sno desc;   end if; end;

  8) 페이징 프로시저

 

create or replace procedure sungjukPaging (   v_cursor out sys_refcursor   ,v_start number   ,v_end number   ) is begin   open v_cursor for   select AA.sno, AA.uname, AA.addr, AA.rnum   from(       select sno, uname, addr, rownum as rnum       from sungjuk       ) AA   where AA.rnum>=v_start and AA.rnum<=v_end;  end;