*44일차(DATABASE OBJECT2 / cursor)

@DATABASE OBJECT2

• pl/sql문법을 사용하는 db객체 (function, procedure, cursor, trigger, . . . .)

 

*STORED FUNCTION

• 리턴값이 반드시 존재하는 프로시저 객체
• 함수객체는 일반 sql, 다른 프로시저, 익명블럭에서 호출이 가능
• 기존 sql 실행과 달리 미리 컴파일 하므로 실행속도 빠름

/*

    create [or replace] function 함수명(매개변수1, 매개변수2, ...)

    return 자료형

    is

        -- 지역변수선언

    begin

        -- 실행코드

        return 리턴값;

    exception

        -- 예외처리

        return 예외발생시 리턴값;

    end;

    /

   

*/

--양모자씌우기

--매개변수, 리턴타입도 자료크기는 지정하지 않는다.

--pl,sql의 varchar2(32676 byte)가 최대

create or replace function myfunc(p_emp_name employee.emp_name%type)
return varchar2
is
    result varchar2(32676);
begin
    result := 'd' || p_emp_name || 'b';
    dbms_output.put_line(result || '@myfunc');
    return result;
end;
/

 

--익명블럭에서 호출

--일반 sql문에서 호출

--함수내부의 로그출력 없음

begin
    dbms_output.put_line(myfunc('&이름'));
end;
/
select
    myfunc(emp_name)
from 
    employee;

 

 

--data_dictionary에서 확인

--user_procedure에서 object_type = 'FUNCTION'

select 
    *
from
    user_procedures
where
    object_type = 'FUNCTION';

 

--주민번호를 인자로 성별을 리턴하는 저장함수 fn_get_gender

create or replace function fn_get_gender(
    p_emp_no employee.emp_no%type
)
return char
is
    v_gender char(3);
begin
    case substr(p_emp_no, 8, 1)
        when '1' then v_gender := '남';
        when '3' then v_gender := '남';
        else v_gender := '여';
    end case;
    return v_gender;
end;
/

select
    emp_name, 
    emp_no,
    fn_get_gender(emp_no) gender
from
    employee;

 

 

--주민번호를 인자로 받아서 한국나이를 리턴하는 fn_get_age

-- 현재년도- 출생년도 +1

create or replace function fn_get_age(
    p_emp_no employee.emp_no%type
)
return number
is
    sys_year number := extract(year from sysdate);
    birth_year number;
begin
    -- 출생년도
    case 
        when substr(p_emp_no, 8, 1) in ('1', '2')
            then birth_year := 1900 + substr(p_emp_no, 1, 2);
        else 
            birth_year := 2000 + substr(p_emp_no, 1, 2);
    end case;
    -- 나이 리턴
    return sys_year - birth_year + 1;
end;
/

select
    emp_name,
    fn_get_age(emp_no) age
from
    employee;

 

 

 

*STORED PROCEDURE

• 일련의 작업절차를 객체로 저장하고 호출해서 사용하는 객체
• 리턴값이 없다
• out 매개변수를 이용해 호출부로 값전달 가능
• 미리 컴파일 해두므로 일반 sql대비 처리효율이 좋다
• select문에서 호출불가
• 익명블럭 or 다른 프로시저에서 호출가능

/*

    create [or replace] procedure 프로시져명(매개변수1 mode 자료형, 매개변수2 mode 자료형, ...)

    is

        -- 지역변수 선언

    begin

        -- 실행코드

    end;

    /

   

    mode : in | out | inout

        - in 프로시져 값을 전달(기본값)

        - out 프로시져의 처리내용을 담아서 호출부로 전달

        - inout

*/

create or replace procedure proc_get_emp(
    p_emp_id in employee.emp_id%type,
    p_emp_name out employee.emp_name%type,
    p_phone out employee.phone%type
)
is   
begin
    -- 해당사원조회 
    select 
        emp_name, phone
    into 
        p_emp_name, p_phone
    from
        employee
    where
        emp_id = p_emp_id;
        
    dbms_output.put_line('사원명@proc_get_emp : ' || p_emp_name);
    dbms_output.put_line('전화번호@proc_get_emp : ' || p_phone);
end;
/

--익명블럭에서 호출
declare
    v_emp_id employee.emp_id%type := '&사번';
    v_emp_name employee.emp_name%type;
    v_phone employee.phone%type;
begin
    -- 프로시져호출
    proc_get_emp(v_emp_id, v_emp_name, v_phone);
    
    -- 값 확인
    dbms_output.put_line('사원명 : ' || v_emp_name);
    dbms_output.put_line('전화번호 : ' || v_phone);
    
end;
/

 

 

--사원삭제 프로시저

--DML처리시 트랜잭션 처리까지 함께 할것

create or replace procedure proc_del_emp(
    p_emp_id ex_employee.emp_id%type -- in mode(기본값)
)
is
begin
    -- 삭제
    delete from
        ex_employee
    where
        emp_id = p_emp_id;
    -- 트랜잭션처리
    commit;
    -- 콘솔로깅
    dbms_output.put_line(p_emp_id || '번 사원을 삭제했습니다.');        
end;
/
select * from ex_employee;

begin
    proc_del_emp('&사번');
end;
/

select * from ex_employee;

begin
    proc_del_emp('&사번');
end;
/

 

--upsert 예제

--특정행 없으면 insert

--특정행 있으면 update

-- ex_job테이블
create table ex_job
as
select * from job;

-- 기본키, 자료형 수정
alter table 
    ex_job
add constraint pk_ex_job_code primary key(job_code)
modify job_code varchar2(5)
modify job_name not null;

 

--인자로 전달한 직급코드,직급명에 따라 insert or update처리하는 프로시저

create or replace procedure proc_upsert_ex_job(
    p_job_code ex_job.job_code%type,
    p_job_name ex_job.job_name%type
)
is
    v_cnt number;
begin
    -- 1.p_job_code가 존재하는지 여부
    select
        count(*)
    into
        v_cnt
    from
        ex_job
    where
        job_code = p_job_code;

    -- 2.존재하면 update, 존재하지 않으면 insert
    if v_cnt = 0 then
        insert into
            ex_job
        values (
            p_job_code, p_job_name
        );
    else 
        update
            ex_job
        set
            job_name = p_job_name
        where
            job_code = p_job_code;
    end if;
    
    -- 3.트랜잭션처리
    commit;
end;
/

begin
--    proc_upsert_ex_job('J8', '인턴'); -- insert
    proc_upsert_ex_job('J8', '수습'); -- update
end;
/

select * from ex_job;

-- dd에서 조회
select * from user_procedures where object_type = 'PROCEDURE';

 

 

@CURSOR

• 커서란 sql실행결과를 갖고있는 메모리영역(private sql)에 대한 포인터객체
• 한 행이상의 결과집합인 경우도 순차적으로 접근가능
• open ~ fetch ~ close의 단계를 거쳐 처리하게 된다.

1. 암시적 커서
2. 명시적 커서

 

*커서 속성

• %rowcount :  최근 실행된 sql문의 결과 행 수
• %notfound :  결과집합에서 fetch된 행이 존재하면 f , 존재하지않으면 t
• %found : 결과집합에서 fetch된 행이 존재하면 t , 존재하지않으면 f
• %isopen : 최근 실행된 sql문 커서가 open상태면 t

 

--암시적 커서 확인

declare
    v_emp_id employee.emp_id%type := '&사번';
    v_emp_name employee.emp_name%type;
begin
    select 
        emp_name
    into 
        v_emp_name
    from
        employee
    where
        emp_id = v_emp_id;
    
    if sql%found then
        dbms_output.put_line('조회된 행수 : ' || sql%rowcount);
    end if;

end;
/

 

 

--명시적 커서 확인

• 선언 - open - fetch - close
• 직접 결과집합에 접근해 행에 대한 처리가 가능
• for..in문 안에서 open/close를 자동처리해서 간단히 커서를 사용할 수 있다

declare
    -- 커서선언
    cursor mycursor
    is
    select * from employee where 1 = 0;
    
    erow employee%rowtype;
begin
    -- 커서 open
    open mycursor;

    loop
        -- 커서 fetch
        fetch mycursor into erow; -- 한행씩 가져오기
        exit when mycursor%notfound; -- 더이상 가져올 행이 없는 경우, exit
        
        dbms_output.put_line('사번 ' || erow.emp_id || '  사원명 : ' || erow.emp_name );
    end loop;
    
    -- 커서 close
    close mycursor;
end;
/

 

 

ex)

for..in문에서 명시적 커서 사용하기

open,fetch,close대신 처리

fetch된 행을 담을 변수도 for..in안에서 선언

별도의 exit 작성 불필요

declare
    -- 커서선언
    cursor mycursor
    is
    select * from employee;
begin
    -- open, fetch, close 자동처리
    for erow in mycursor loop
        dbms_output.put_line('사번 ' || erow.emp_id || '  사원명 : ' || erow.emp_name );
    end loop;
end;
/

 

--매개변수가 있는 커서

--커서 선언 시 매개변수도 함께 선언

--open시 매개인자 전달

declare
    cursor cs_emp_by_dept(p_dept_code employee.dept_code%type)
    is
    select * from employee where dept_code = p_dept_code;
    
    v_dept_code employee.dept_code%type := '&부서코드';
    erow employee%rowtype;
begin

    dbms_output.put_line('사번    사원명     부서코드');
    dbms_output.put_line('=========================');
    for erow in cs_emp_by_dept(v_dept_code) loop
        dbms_output.put_line(erow.emp_id || '   ' || erow.emp_name || '     ' || erow.dept_code);
    end loop;
end;
/