ORACLE 중간 연습... 프로시저를 활용한 데이터 조작

create or replace procedure changeDataProc IS rec_data hanTest%rowtype; tempExpireData hanTest.expire_time%type; tempIsVal hanTest.is_valid%type; tempName hanTest.nfcn%type; tempNameCnt number; Cursor cu1 IS select * from hanTest where is_valid = 0 order by nfuid asc, create_time asc, expire_time asc; BEGIN open cu1; -- 맨 처음 row가져오기 fetch cu1 into rec_data; -- 맨 처음 튜플은 그대로 삽입 insert into hantest2 values rec_data; -- tempExpireData := rec_data.expire_time; tempName := rec_data.nfcn; -- 두번째 튜플부터 반복문을 돌림 loop fetch cu1 into rec_data; if ( tempName = rec_data.nfcn ) then rec_data.create_time := tempExpireData; tempExpireData := rec_data.expire_time; insert into hantest2 values rec_data; tempName := rec_data.nfcn; else tempName := rec_data.nfcn; insert into hantest2 values rec_data; tempExpireData := rec_data.expire_time; end if; exit when cu1%notfound; end loop; close cu1; END; / truncate table hanTest2; exec changeDataProc; delete from hantest2 where nfcn='이원섭' and nfuid='이원섭' and create_time='20010119 071939'; select * from hantest2; create or replace procedure insertOneData IS recOne hantest%rowtype; BEGIN for temp in (select * from hantest where is_valid=1) loop insert into hanTest2 values temp; end loop; END; / execute insertOneData; select * from hantest2 where is_valid = 1; select count(*) from hantest2 where is_valid = 0; select count(*) from hantest2 where is_valid=1; select count(*) from hantest2;