초급의 끄적거림

[SQL] PL/SQL 프로시저 (형식/조건문/반복문), %rowtype, %type 본문

DB/Oracle

[SQL] PL/SQL 프로시저 (형식/조건문/반복문), %rowtype, %type

codingD 2019. 8. 5. 17:42

[앞으로의 진도]

PL/SQL 프로시저, 함수
트리거, 백업과 복원

 

 

[PL/SQL 프로시저]

⊙ Procedural Language extension to SQL

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

⊙ SQL문장에서 변수 정의, 조건처리(if), 반복처리 (loop, while, for) 등을 지원하며 오라클 자체에 내장되어 있는 Procedure Language 이다

 

 

[PL/SQL 형식]

create or repalce procedure 프로시저명 (매개변수)
  is
     내부변수선언;
  begin
     명령어;  (세미콜론으로 종결필요)
end;
/

 

 

[PL/SQK 기본문법]

⊙ 콘솔창 출력하기 위한 명령어

 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) 조건문

 

 2-1) 조건문1  2-2) 조건문2

 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;
 /

 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) 반복문 (결과동일)

 3-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  3-3) 반복문3
 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;
 /
 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;
 /

 

 

[성적테이블 생성]

1) sno=3 인 행을 가져오기

 

  - where 문 사용

 select * from sungjuk where sno=3;

 

 - 프로시저 사용

 declare

  v_sno  number;
  v_uname varchar2(50);    -- 변수명이 칼럼명이랑 똑같으

                     면 헷갈릴 수 있기 때문에 차별화를 둠 (v_)
   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_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) 주소를 한글로 출력하시오

 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
   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='Suwon' then v_juso:='수원';
   elsif v_addr='Busan' then v_juso:='부산';
   elsif v_addr='Jeju'  then v_juso:='제주';
   end if;

  --출력
  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);  
  dbms_output.put_line('주소 한글명 :' || v_juso);  

 end;

 /

 

 

[PL/SQL 참조변수]

 ⊙ %type

   - 테이블에서 한 개 칼럼의 데이터타입 및 사이즈를 참조한다

   - 형식) 변수명 테이블명.칼럼명%type

 

 ⊙ %rowtype

   - 테이블의 row 타입과 같다는 의미

   - 하나 이상의 데이터값을 갖는 데이터 타입으로 배열과 비슷한 역할을 함 (= 테이블에 있는 전체 칼럼의 자료형을 모두 사용)

   - 형식) 변수명 테이블명%rowtype

 

 ⊙ 데이터베이스 칼럼들의 수나 데이터타입을 알지 못할 때 편리

 ⊙ 테이블의 데이터칼럼의 데이터 타입이 변경 될 경우 프로그램을 재수정할 필요가 없다

 

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
  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형 (1번과 동일한 결과)

 declare

    sj sungjuk%rowtype;          --sj 라는 변수에 sungjuk%rowtype

 begin

    select * 

    into sj

    from sungjuk

    where sno=3;

      dbms_output.put_line('*실행결과*');
      dbms_output.put_line('번호 :' || sj.sno);     --sj. 이라고 변수만 추가해주면 됨
      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;

  /

 

 

[커서를 이용해서 복수행 처리]

 ⊙ fetch문

  - 오픈된 커서로부터 한 행을 인출한다

  - 형식) fetch 커서명 into 변수명

 

 1) sno>=5 레코드 조회

 select * from sungjuk where sno>=5;

 

 

 

 

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

 declare

   v_cursor sys_refcursor;     --커서를 담는 변수선언 및 자료형

   rec  sungjuk%rowtype;     --한 행을 담을 수 있는 변수선언

 begin

   open v_cursor for           --커서는 아래 있는 select

   select * from sungjuk where sno>=5;

   loop

     fetch v_cursor into rec;              --한 행 인출해서 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) test  프로시저 생성

 create or replace procedure test

 is 

 begin

   dbms_output.put_line('테스트');

 end;

 

 

2) test 프로시저 호출

 execute test;

 

 

 

 

3) test 프로시저 삭제3) test 프로시저 삭제

 drop procedure test;

 

 

[성적프로시저]

⊙ Create/ Read / Update / Delete 관련한 프로시저 생성

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

 - 매개변수 (parameter) 

 -  in 입력변수 (무언가를 받아들이겠다는 의미)

  create or replace procedure sungjukInsert 

  (

   v_uname in sungjuk.uname%type
    ,v_kor in sungjuk.kor%type
    ,v_eng in sunjukg.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;

 

 

 

 1-1) 행추가 테스트

 execute sungjukInsert('손흥민', 100, 100, 100, 'Seoul');

 

 

   결과 확인

 

 

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

  create or replace rprcedure 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);  --sno가 3인 것을 수정하기 위해서 3을 설정한 것

  select * from sungjuk;

 

 

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

  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);

 

 select * from sungjuk;

 

 

4) 상세보기 (Read)

  - 자바에서 확인하기 위해서 컴파일만 해두기

  create or replace procedure sungjukRead

  (

      --out 출력매개변수

      v_cursor out sys_refcursor   --가지고 나오기 위해서 out 그동안 써오던 in말고 out 사용, 굳이 없어도 문제는 없을 것

      ,v_sno sungjuk.sno%type

   )

  is

  begin

     open v_curesor for select * from sungjuk where sno=v_sno;

   --close v_cursor;  커서반납, 지금 여기서 close 하면 자바에서 움직일 수 없기 때문에 주석처리
  commit;
  end;

 

 

5) 목록 (Read)

   - 자바에서 확인하기 위해서 컴파일만 해두기

  create or replace procedure sungjukList

  (

      v_cursor out sys_refcursor

  )

  is 

  begin

      open v_cursor for select * from sungjuk order by sno desc;

      commit;

  end;

 

 

 

6) 레코드 개수 프로시저

  - 자바에서 확인하기 위해서 컴파일만 해두기

  create or replace procedure sungjukCount

  (

       v_cursor out sys_refcursor

  )

  is

  begin

       open v_curesor for select nvl(count(*), 0) cnt from sungjuk;

   --clos v_cursor

   commit;

  end;

 

 

7) 검색 프로시저

  - 자바에서 확인하기 위해서 컴파일만 해두기

  - 이름에 '나' 라는 글자가 들어가 있는 사람 찾기

     where uname like '%나%';

 

  - 이름 또는 주소에 알파벳 'o'가 있는 검색

     where uname like '%o%' or addr like '%o%';

 

  - 1이면 이름에서 검색 

    2이면 이름 또는 주소에서 검색

    나머지는 검색하지 않음

  create or replace porcedure sungjukSearch

  (

      v_cursor out sys_refcursor

      ,v_code number

      , v_keyword varchar2

  )

  is

  begin

    if v_code=1 hen

       open  v_cursor for

       select * from sungjuk

       where uname like '%' || v_keyworde || '%';

   elsif v_code=2 then

       open v_cursor for

       select * from sungjuk

       where uname like '%'  || v_keyword || '%'

       or      addr    like '%' || v_keyworde || '%'

       order by sno desc;

   else

       open v_cursor for

       select * from sungjuk order by sno desc;

    end if;

    commit;

  end;

 

 

 

8) 페이징 프로시저

  - 자바에서 확인하기 위해서 컴파일만 해두기

  

 

  <rownum이 6이상 10이하인 것을 골라냄>

  select sno, uname, addr, rownum from sungjuk order by sno desc;

   select sno, uname, addr, rownum from sungjuk 
   where rownum<=5 and rownum >=1;
 
   select sno, uname, addr, rownum from sungjuk 
   where rownum >=6 and rownum<=10;                --데이터가 살아 있어도 나타나지 않음 (조회가 안됨) 

                                                                       → 서브쿼리 형태를 한번 만들어서 사용할 것
 
 select AA.sno, AA.uname, AA.addr, AA.rnum
 from (
        select sno, uname, addr, rownum as rnum 
        from sungjuk 
      ) AA
 where AA.rnum>=6 and AA.rnum<=10;   -- → 조회가능

  <6과 10을 변수처리 해서 넣기 : v_sno>

  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;

 

Comments