08월 05일 월 | 데이터입출력 구현 01 - 프로시저

 

SQL 프로시저

PL / SQL 프로시저

-Procedural Language extension to SQL

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

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

Procedure Language이다.

 

 

 

 

PL / SQL 형식

- create procedure 프로시저명

create function 

alter function

drop function 

 

create or replace prodedure 프로시저명(매개변수)

is 내부변수선언

begin 명령어

end;

/

 

 

프로시저 만들기

1) 프로시저 함수

-- 콘솔창 출력하기 위한 명령어 set serveroutput on; declare --변수선언 및 대입     a number := 3;     b number := 5; begin  --콘솔창 출력     dbms_output.put_line(a);     dbms_output.put_line(b);     dbms_output.put_line(a+b); end; /

 

 

 

2) 문자열을 붙였을 때

dbms_output.put_line(a||'+'||b||'='||(a+b));

 

 

3) if문, 반복문 

-- 조건문 declare   -- 성적프로그램   uname varchar2(50) := '김연경';   kor number := 100;   eng number := 90;   mat number := 86;   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(kor);   dbms_output.put_line(eng);   dbms_output.put_line(mat);   dbms_output.put_line(aver);   dbms_output.put_line(uname||'의 평균성적은 '||aver||' 입니다.');     dbms_output.put_line(uname||'의 학점은 '||grade||' 입니다.'); end; /

 

 

--반복문 1 declare   dan number := 4;   i number default 0; begin   loop   i := i+1;   dbms_output.put_line();   exit when i=9;    end loop; end; / --반복문 2 declare   dan number := 4;   i number default 0; begin   for i in 1..9 loop --for 변수 in 시작값..끝값 loop 문법   dbms_output.put_line(dan || '*' || i || '=' || (dan*i));   end loop; end; / --반복문 3 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단이다.

 

 

 

 

프로시저로 테이블 데이터 불러오기

프로시저를 이용해 이미 생성된 테이블의 데이터를 불러올 것이다.

지난 번에 만들었던 sungjuk테이블을 드랍하고 새로 sungjuk테이블을 만들어서

데이터를 삽입하였다.

 

 

1) 프로시저를 이용해 sno=3 가져오기

-- 프로시저를 이용해서 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_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(50); 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='BUSAN' then v_juso:='부산';   elsif v_addr='JEJU' 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_juso);   dbms_output.put_line('영문주소: '|| v_addr); end; /

 

 

 

 

PL / SQL 형식

- %type

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

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

 

 

- %rowtype

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

-- 2) %rowtype형 declare   sj sungjuk%rowtype; begin   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; /

 

 

 

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

-- sno >= 5 레코드 조회 select * from sungjuk where sno>=5; -- 프로시저를 이용해서 sno>=5 레코드 조회 declare  v_cursor sys_refcursor; -- 커서를 담는 변수선언 및 자료형  rec sungjuk%rowtype; -- 한 줄 단위로 담기 begin  open v_cursor for  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);  end loop; end; /

 

 

프로시저 생성

create procedure 프로시저명;  -- 프로시저 생성

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

 

create or replace test

is

begin

 

end;

 

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

 

 

 

 

 

★ CRUD 프로시저 생성

 

1) CREATE 행추가 프로시저 insert

-- 1) 행추가 프로시저 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; drop procedure sungjukInsert; -- 행추가 테스트 execute sungjukInsert('손흥민',100,100,100,'SEOUL'); --대소문자 -- 결과 확인 select * from sungjuk order by sno desc;

 

 

2) UPDATE 행변경 프로시저

--2) 행수정 프로시저 (Update) update sungjuk  set uname=? ,kor=?, eng=?, mat=?, aver=?, addr=? where sno=?; 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_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  where sno=v_sno;  commit; end; drop procedure sungjukUpdate; execute sungjukUpdate('김연아',95,100,100,3);

 

 

 

 

3) DELETE 행삭제 프로시저

--3) 행 삭제 프로시저 --delet 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);

 

 

 

 

4) READ 행읽기 프로시저

-- 4) 상세보기 Read select * from sungjuk where sno = 4; 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 프로시저

-- 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;   -- close v_cursor; 커서반납 end;

 

 

 

6) 레코드 갯수 프로시저

-- 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) 검색 프로시저

-- 이름에 '나'글자 있는지 검색
-- where uname like '%나%'
-- 이름 또는 주소에 알파벳 'O'가 있는지 검색
-- 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) 페이징 프로시저

 

 

-- 8) 페이징 프로시저 select uname, addr, rownum from sungjuk; select sno, uname, addr, rownum from sungjuk where rownum >=1 and rownum <=5; 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;