📌Today I Learned

오라클 수업

ex_pl/sql

함수 return

1. 단일값 O
2. 다중값 X > cursor

프로시저 out parameter

1. 단일값(단일 레코드)
   a. number
   b. varchar2
   c. date
   
2. 다중값(다중 레코드)
   a. cursor

create or replace procedure procBuseo (
    pbuseo in varchar2,
    pcursor out sys_refcursor --커서의 자료형 
)
is
    -- cursor vcursor is select..   > 내부 소비 시 이렇게 사용
begin
    open pcursor
    for 
    select * from tblInsa where buseo = pbuseo;
end procBuseo;


declare
    vcursor sys_refcursor; -- 커서 참조변수
    vrow tblInsa%rowtype;
begin
    procBuseo('영업부', vcursor);
    loop
        fetch vcursor into vrow;
        exit when vcursor%notfound;
        
        -- 업무
        dbms_output.put_line(vrow.name);
    end loop;
end;

프로시저 총 정리 > CRUD

-- 1. 추가 작업(C)
create or replace procedure 추가작업 (
    추가할 데이터 -> in 매개변수,
    추가할 데이터 -> in 매개변수,
    추가할 데이터 -> in 매개변수, -- 원하는 만큼
    성공 유무 반환-> out 매개변수 -- 피드백(1, 0)
)
is
    내부 변수 선언
begin
    작업(insert + (select, update, delete))
exception
    when others then
        예외처리
end 추가작업;


-- 할 일 추가하기(C)
create or replace procedure procAddTodo(
    ptitle varchar2,
    presult out number -- 1 or 0
)
is
begin
    insert into tblTodo (seq, title, adddate, completedate) values (seqTodo.nextVal, ptitle, sysdate, null);
    
    presult := 1; -- 성공
exception
    when others then
        presult := 0;
end procAddTodo;


select * from tbltodo; -- 24

create sequence seqTodo start with 25;

declare
    vresult number;
begin
    procAddTodo('새로운 할 일입니다.', vresult);
    dbms_output.put_line(vresult);
end;


-- 2. 수정 작업(U)
create or replace procedure 수정작업(
    수정할 데이터 -> in 매개변수,
    수정할 데이터 -> in 매개변수,
    수정할 데이터 -> in 매개변수, -- 원하는 개수
    식별자       -> in 매개변수,  -- where절에 사용할 PK or 데이터 (조건)
    성공 유무 반환-> out 매개변수 -- 피드백(1, 0)
)
is
    내부 변수 선언
begin
    작업(update + (insert, update, delete, select...))
exception
    when others then
        예외처리
end;


-- 할 일 수정하기(U) > completedate > 채우기 > 할 일 완료 처리하기
create or replace procedure procCompleteTodo(
    -- pcompletedate date > 수정할 날짜 > 지금 > sysdate 처리
    pseq in number, -- 수정할 할 일 번호
    presult out number
)
is
begin
    update tblTodo set 
        completedate = sysdate 
            where seq = pseq;
            
    presult := 1;
exception
    when others then
        presult := 0;
end procCompleteTodo;


declare
    vresult number;
begin
    procCompleteTodo(25, vresult);
    dbms_output.put_line(vresult);
end;

select * from tbltodo;


-- 3. 삭제 작업(D)
create or replace procedure 삭제작업 (
    식별자        -> in 매개변수,
    성공 유무 반환 -> out 매개변수
)
is
    내부 변수 선언
begin
    작업(delete + (insert, update, delete, select))
exception
    when others then
        예외처리
end;


create or replace procedure procDeleteTodo(
    pseq in number,
    presult out number
)
is
begin
    delete from tblTodo where seq = pseq;
    presult := 1;
exception
    when others then
        presult := 0;
end procDeleteTodo;


declare
    vresult number;
begin
    procDeleteTodo(25, vresult);
    dbms_output.put_line(vresult);
end;

select * from tblTodo;



-- 4. 읽기 작업(R)
-- : 조건 유/무
-- : 반환 단일행/다중행, 단일컬럼/다중컬럼

create or replacr procedure 읽기작업(
    조건 데이터 -> int 매개변수,
    단일 반환값 -> out 매개변수,
    다중 반환값 -> out 매개변수(커서)
)
is
    내부 변수 선언
begin
    작업(select + (insert, update, delete, select))
exception
    when others then
        예외처리
end;


-- 한 일 몇 개? 안 한 일 몇 개? 총 몇 개?
create or replace procedure procCountTodo(
    pcount1 out number, -- 한 일
    pcount2 out number, -- 안 한 일
    pcount3 out number  -- 모든 일
)
is
begin
    select count(*) into pcount1 from tblTodo where completedate is not null;
    select count(*) into pcount2 from tblTodo where completedate is null;
    select count(*) into pcount3 from tblTodo;
exception
    when others then
        dbms_output.put_line('예외 처리'); -- 실제는 로그 남김
end procCountTodo;


declare
    vcount1 number;
    vcount2 number;
    vcount3 number;
begin
    procCountTodo(vcount1, vcount2, vcount3);
    dbms_output.put_line(vcount1);
    dbms_output.put_line(vcount2);
    dbms_output.put_line(vcount3);
end;


create or replace procedure procCountTodo(
    psel in number,     -- 선택(1(한 일), 2(안 한 일), 3(모든 일))
    pcount out number
)
is
begin
    if psel = 1 then
        select count(*) into pcount from tblTodo where completedate is not null;
    elsif psel = 2 then
        select count(*) into pcount from tblTodo where completedate is null;
    elsif psel = 3 then
        select count(*) into pcount from tblTodo;
    end if;
exception
    when others then
        dbms_output.put_line('예외 처리'); -- 실제는 로그 남김
end procCountTodo;

declare
    vcount number;
begin
    procCountTodo(3, vcount);
    dbms_output.put_line(vcount);
end;


-- 번호 > 할 일 1개 반환
create or replace procedure procGetTodo(
    pseq in number,
    prow out tblTodo%rowtype
)
is
begin
    select * into prow from tblTodo where seq = pseq;
exception
    when others then
        dbms_output.put_line('예외');
end procGetTodo;


declare
    vrow tblTodo%rowtype;
begin
    procGetTodo(1, vrow);
    dbms_output.put_line(vrow.title);
end;

select * from tblTodo;



-- 다중 레코드 반환
-- 1. 한 일 목록 반환
-- 2. 안 한 일 목록 반환
-- 3. 모든 일 목록 반환

create or replace procedure procListTodo(
    psel in number,
    pcursor out sys_refcursor
)
is 
begin
    if psel = 1 then
        open pcursor
        for
        select * from tblTodo where completedate is not null;
    elsif psel = 2 then
        open pcursor
        for
        select * from tblTodo where completedate is null;
    elsif psel = 3 then
        open pcursor
        for
        select * from tblTodo;
    end if;
exception
    when others then
        dbms_output.put_line('예외');
end procListTodo;

declare
    vcursor sys_refcursor;
    vrow tblTodo%rowtype;
begin
    procListTodo(3, vcursor);
    loop
        fetch vcursor into vrow;
        exit when vcursor%notfound;
        
        dbms_output.put_line(vrow.title || ', ' || vrow.completedate);
        
    end loop;
end;

DB 프로젝트

데이터베이스에 들어갈 더미데이터를 만들었다. 그리고 테이블 정의서(DDL)도 함께 작성하였다.