[days15] 저장 프로시저 예제

 저장 프로시저
exec up_insertdept('부서명');
exec up_insertdept('부서명', '지역명');
exec up_insertdept('지역명');
exec up_insertdept; --매개변수 없을경우 이것도 가능
--이게 다 허용이 되는 저장 프로시저 하나 만들자

 

1. 부서 정보 컬럼을 추가하는 저장 프로시저 생성

create sequence seq_dept
increment by 10
start with 50
nocache;

-- 저장 프로시저 만들기
create or replace procedure up_insertdept
(
        pdname dept.dname%type := null
        , ploc dept.loc%type default null
)
is
begin
        insert into dept( deptno, dname, loc )
        values (  seq_dept.nextval, pdname,ploc );
        commit;
--exception
   -- rollback;
end;

exec up_insertdept('QC', 'SEOUL');
exec up_insertdept('QC_02');
exec up_insertdept(ploc=>'지역명'); --내맘대로 지정하기, => 기호
exec up_insertdept(ploc=>'지역명', pdname =>'부서명'); --원래 부서번호, 부서명 들어가는데 난 지역명 부서명 순서로 넣을거다

2. up_deletedept 부서 정보를 삭제하는 저장 프로시저

-매개변수로 부서번호를 를 넘겨주면 해당 부서 삭제

create or replace procedure up_deletedept
(
    pdeptno number --사이즈 붙히면 안됨, ;이아니라 , 
)
is
begin
    delete from dept
    where deptno = pdeptno;
    commit;
--exception
end;
----위 프로시저 테스트
exec up_deletedept(50);--PL/SQL 프로시저가 성공적으로 완료되었습니다.

3. dept 수정하는 프로시저

create or replace procedure up_updatedept
(
    pdeptno dept.deptno%type
    ,pdname dept.dname%type :=null
    ,ploc dept.loc%type :=null --얘네 둘은 null이 올 수 있다
)
is
    vdname dept.dname%type; --원래값을 넣을 공간
    vloc dept.loc%type;  --원래값을 넣을공간
begin
    if (pdname is null or ploc is null) then
    select dname, loc into vdname, vloc
    from dept
    where deptno = pdeptno;
    end if;
    --
    update dept
    set dname = case
                when pdname is null then vdname --널이라면 원래값
                else pdname --아니면 넘어온 값
                end
    --set dname nvl2(dname, pdname, vdname); 위의 case문 대신에
        , loc = nvl(ploc, vloc) -- nvl(), nvl2(),    nvl(comm, 0)
    where deptno = pdeptno;
    commit;

--exception rollback;
end;


exec up_updatedept(70, '부서명', '지역명' );
exec up_updatedept(70, 'QC');

--dname이 '부서명' => QC로 컬럼을 수정하면 loc가 null이 아니라 기존의 값이 출력된다.

 

 

모든 부서 정보를 조회하는 저장 프로시저 - 명시적 커서

create or replace procedure up_selectdept
--() 파라미터 필요없음
is
    cursor cur_dept is (select * from dept);
    vrow dept%rowtype;
begin
    open cur_dept;
        loop
            fetch  cur_dept into vrow;
            exit when cur_dept%notfound;
            dbms_output.put_line(vrow.deptno||','||vrow.dname||','||vrow.loc);
        end loop;
    close cur_dept;
--exception
end;

exec up_selectdept;

위의 쿼리를 암시적 커서로 변경

create or replace procedure up_selectdept2
--() 파라미터 필요없음
is
begin
     for vrow in (select * from dept)
       loop
        dbms_output.put_line(vrow.deptno||','||vrow.dname||','||vrow.loc);
       end loop;
--exception
end;