2019/08/05 프로시저를 이용한 C.R.U.D

[전 수업] 프로시저를 이용하여 테이블의 데이터를 불러보았다.

[본 수업] 프로시저를 이용하여 테이블에 C(create).R(read).U(update).D(delete)를 사용해 보자

---

--20190805_성적프로시저.sql

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;

declare를 사용하던 전과 다르게, create를 사용하여  sungjuk에 대한 프로시저를 생성 (매개변수 in sungjuk%type) 입력 begin 다음에는 SQL문 입력후  commit; 반드시 입력

execute sungjukInsert('가을비',100,100,100,'Seoul');

begin에 넣어줄 칼럼을 정해 주고 execute 실행명령문 (매개변수)에 데이터를 넣어준다

 

select * from sungjuk order by sno desc;

('가을비',100,100,100,'Seoul') 데이터가 insert됐는지  확인

 

 

 

 

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;

중요! update sungjuk set '변수'에  위에서 설정해준 v_변수 를 할당해서 넣어준다

execute sungjukUpdate('봄바람',80,75,95,'Jeju',78);

수정할 데이터 들을 나열해서 마지막에 where 조건에  맞는 데이터를 넣어줌

 

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;

행 삭제와 관련있는 sno 만 매개변수로 만들어 주었다

execute sungjukDelete(114);

해당 매개변수를 넣어 sungjukDelete 실행

 

select * from sungjuk;

sno=114인 해당 행이 삭제 되었는지 확인

 

 

 

※insert 나 update , delete 모두 해당 변수와 관련있는  매개 변수만 선언해 주면 된다.

 

4)  Read상세보기 

create or replace procedure sungjukRead  (     --out 출력매개변수     v_cursor out sys_refcursor -- out 밑에 있는 SQL문을 의 논리적테이블의 내용을 가져 온다는 의미이다     ,v_sno sungjuk.sno%type  ) is begin     open v_cursor for     select * from sungjuk where sno=v_sno;     --close v_cursor;커서반납 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;     --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) cnt from sungjuk;     --close v_cursor; end;

 

7) 검색 프로시저

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 ||'%)';            elsif v_code =2 then open v_cursor for       select * from sungjuk where        uname like '(%' || v_keyword ||'%)' OR        addr like '(%' || v_keyword ||'%)';          else open v_cursor for select * from sungjuk order by sno desc;                   end if ; end;

keyword 를 입력할때는 꼼수!!!

8. 페이징 프로시저

select AA.sno,AA.uname,AA.addr,AA.rnum   from(  select sno,uname,addr, rownum rnum  from sungjuk)AA  where AA.rnum <=10 and AA.rnum >=6;

rownum은 중간 번호를 조회 할 수 없기 때문에 rownum을 칼럼으로 만들어 주고 다시 조회한다

 

 

 

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 rnum     from sungjuk)AA     where AA.rnum <=v_end and AA.rnum >=v_start;  end;

v_cursor는 select 문을 사용할때만 사용한다