2019/08/05 프로시저를 이용

[전 수업] SQL 문을 이용하여 join과 데이터 입력까지 배워 보았다

[본 수업] 프로시저를 이용하여 생성 해놓았던 테이블을 이용해 보자

---

20190805_프로시저.sql

데이터 입출력 과목에 배워야할 내용

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

->도구->데이터베이스 익스포트(백업시키는것)

------------------------------------------------------------오늘 수업------------------------------------------------------------

[컴원미디어 교재]
chapter 15,16,17
ncs모듈은 정의만 보자 너무 어렵게 설명이 되어 있어서 보기 힘들다


[PL/SQL 프로시저]
-Procedural Language extension to SQL
-프로그래밍 언어의 특성을 수용한 SQL의 확장기능
-SQL문장에서 변수 정의,조건처리(if).
 반복처리(loop, while,for)등을 지원하며
 오라클 자체에 내장되어 있는 procedure Language이다

[PL/SQL 형식]

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

declare      내부변수선언  begin      명령어  end;  /

---

 

 

1. declare 로 결과값만 일시적으로 확인

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

'PL/SQL 프로시저가 성공적으로 완료되었습니다.' 라는 문구만 나오고 원하는 결과가 출력되지 않는다

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

한 번은 열어 줘야지 declare 선언한 SQL문장을 콘솔창에 출력해 준다

 

2. 조건문을 프로시저로 이용해 보자

declare--(결과값만 일시적으로 볼때 declare) --변수선언 및 대입 uname varchar2(50) := '무궁화';     kor number := 100;     eng number := 90;     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;     --코딩하면서 elsif 로 쓰는 것을 주의 하라 일반적으로 사용하는 else if가 아니다 --dbms_output.put_line('실행')     dbms_output.put_line('*실행결과*');     dbms_output.put_line('이름: ' || uname);     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; /

if문에 between 을 사용

declare  uname varchar2(50) := '무궁화';     kor number := 100;     eng number := 90;     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 90 then grade:='B';     elsif aver  between 70 and 80 then grade:='C';     elsif aver  between 60 and 70 then grade:='D';     else grade:='F';     end if;           dbms_output.put_line('*실행결과*');     dbms_output.put_line('이름: ' || uname);     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. loop 반복문을 프로시저로 이용하여 데이터를 출력해보자

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

loop로 i를 i+1로 증가시켜주고, exit when으로 break;를 걸듯이 i=9라고 값을 한정 시키고, 마지막엔 end loop를 사용해줘 반복문을 종료

※for문은 시작값과 종료값을 정해 주기 때문에 exit when을 사용할 필요가 없다

 

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

loop앞에 for 변수 in a~b 로 i값을 a~b값까지 증가시켜주고, 출력하고 싶은 값을 데이터를 줬으면, end loop로 반복문을 종료

 

 

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

for문과 마찬가지로, loop 앞에 while 조건문 을 사용하고  loop 뒤에는 증가값을 정해준 뒤  end loop로 마무리

 

---

6. 존재하는 테이블의 데이터값을 조회해서 출력해보자

select * from sungjuk;

--sno=104 행 가져오기

declare     --프로시저를 이용한 변수를 선언해 주고 SQL문으로써 나온 변수를 프로시저를 이용한 변수에 넣어서      --프로시저문을 완성해 준다     v_sno number;     v_uname varchar2(50);     v_kor number;     v_eng number;     v_mat number;     v_addr varchar2(39);     v_wdate date;     v_juso varchar2(30);

declare 문 안쪽에 테이블에서 가져온 데이터를 할당할 변수를 설정해 준다

 

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     --into 를 사용해 주고 위에서 프로시저 문으로 설정해둔 변수를 순서대로 나열해서 써준다     from sungjuk     where sno=104;

begin 문 안쪽에는 SQL문을 똑같이 작성해 주고, 중요한 것은 into 위에 declare에서 설정한 변수를 나열해 준다

if  v_addr='Seoul' then v_juso := '서울';     elsif  v_addr='Jeju'  then v_juso := '제주';     elsif  v_addr='Busan' then v_juso := '부산';     elsif  v_addr='Suwon' 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_juso);  end; /

dbms_output.put_line()로 원하는 값을 출력을 하되 if문으로 조건을 영어 주소를 한글 주소로 바꿔서 출력

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

.칼럼명%type 의 자료형을 붙여 변수 설정

 

2) %rowtype 형

declare     sj sungjuk%rowtype; begin     select *    --모든 칼럼을 가져와가      into sj      --sj에다가 넣어주겠다     from sungjuk     where sno=104;          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; /

declare에서 선언하는 변수가  sj sungjuk%rowtype;  확 줄어 들었다

 

 

7. [커서를 이용해서 복수행 처리]
    fetch문
    오픈된 커서로부터 한행을 인출한다
    (형식) fetch 커서명 into 변수명

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

1. v_cursor 라는 변수를 선언 2. res에 sungjuk%rowtype 자료형을 가진 변수 선언 3. v_cursor 를 조회한 논리적테이블을 위해 open 4. 한 행씩 인출하는 fetch를 res에 대입 5. v_cursor 가 notfound 되는 exit

 

 

8. 프로시저 생성 TEST

create or replace  procedure test is begin   dbms_output.put_line('테스트'); end;

지금까지는 declare로 선언만 해 줬다면, 지금은 create로 프로시저를 생성하였다

※호출은 java와 연동해서 호출해 줄것이다.
execute test;