[Database] 20장 PL/SQL 2

 

 cursor 문

 

select 문에서 생성된 결과 집합에 대해 개별적인 행 단위 작업을 가능하게 한다.

query 결과를 읽거나 수정, 삭제할 수 있도록 해준다.

 

select 문의 결과를 정의한 후 사용해야 한다.

 

cursor 문

 

커서 속성

 

 

-- corsor 실행
-- 2020년도 및 상품별 총 입고수량을 출력하는 커서
declare
    v_prod varchar2(30);
    v_qty number(10, 0);
    
    cursor UpRemain_cur is
    -- UpRemain_cur이 buy_prod, sum(buy_qty)의 컬럼을 가지고 있음
        select buy_prod, sum(buy_qty) from buyprod
        where extract(year from buy_date) = 2020
        group by buy_prod order by buy_prod asc;
begin
    open UpRemain_cur;
    fetch UpRemain_cur into v_prod, v_qty;
    while(UpRemain_cur%found) loop -- 데이터가 있을 때 반복
        dbms_output.put_line(UpRemain_cur%rowcount || '번째 상품 = ' 
        || v_prod || ' 입고수량 = ' || v_qty || '입니다.');
        fetch UpRemain_cur into v_prod, v_qty; -- cursor에 저장되어 있는 다음 것을 찾아 저장 > 이후 while문을 돌면 다음 것을 읽음.
    end loop;
    close UpRemain_cur; -- 메모리에 올라온 것들을 끊어줘야함.
end;
/

 

결과 화면1 : 74번째 상품까지 출력됨.

 

 

-- 직업을 변수로 받아 이름 회원명과 마일리지를 출력하는 커서
declare
    v_name varchar2(30);
    v_mileage number(10);
    
    cursor member_cur (v_job varchar2) is
        select mem_name, mem_mileage from member
        where mem_job = v_job
        order by mem_name asc;
begin
     open member_cur('주부');
     loop
        fetch member_cur into v_name, v_mileage;
        exit when member_cur%notfound;
        dbms_output.put_line(member_cur%rowcount || '번째 ' || v_name || ', ' || v_mileage);
     end loop;
     close member_cur;
end;
/

 

결과 화면2

 

 

 

for loop 문

 

• FOR LOOP를 이용하는 CURSOR

각 반복하는 동안 커서를 자동으로 open하고 모든 행이 처리되면 자동으로 커서를 close 한다. 

-- record_name 은 묵시적으로 자동 생성되므로 선언하지 않는다.
FOR record_name IN cursor_name LOOP 
    statement;
END LOOP;

 

 

• Subquery를 이용한 FOR LOOP

서브쿼리를 사용하여 커서를 선언하지 않아도 동일한 결과를 보여준다.

FOR record_name IN (subquery) LOOP

 

 

-- 직업을 입력받아서 FOR LOOP를 이용하는 CURSOR 
accept p_job prompt '직업을 입력하세요 :'

declare 
    v_name varchar2(30);
    v_mileage  number(10);
    cursor member_cur  is
        select mem_name, mem_mileage 
            from member
            where mem_job = '&p_job' 
            order by mem_name asc;
begin   
    for mem_rec in member_cur loop    
        dbms_output.put_line( member_cur%rowcount || '번째 ' 
        || mem_rec.mem_name || ', ' || mem_rec.mem_mileage );
    end loop;
end;
/

 

결과 화면3 : 주부 입력

 

 

-- Subquery를 이용한 FOR LOOP
begin   
    for mem_rec in (select mem_id, mem_name, mem_mileage
        from member order by mem_name asc)  loop    
            dbms_output.put_line( mem_rec.mem_id || ', ' 
            ||  mem_rec.mem_name || ', ' 
            || mem_rec.mem_mileage );  
    end loop;
end;
/

 

결과 화면4

 

 

 

저장 프로시져 (Stored Procedure)

 

: DB 내부에 저장된 일련의 SQL 명령문들을 하나의 함수처럼 실행하기 위한 쿼리의 집합을 이야기 한다.

 

수행될 때 문법을 검사하고 컴파일 되며 컴파일된 버전은 서버에 저장된다.

어떤 클라이언트든 쓸 수 있다는 장점이 있다. => 캡슐화

 

- 장점

클라이언트든 쓸 수 있음 (캡슐화)

view와 동일한 개념으로 데이터베이스 내부를 구조 보안함

서버 보호

자료 무결성 권한을 구현

Query 속도가 향상됨

Network Traffic 감소

 

 

create [ or replace ] procedure procedure_name
    [( argument  [mode] [ {:= | default} expression] 
    , …)]  
    { is | as }
begin
    pl/sql_block; 
end;

 

 

 

-- Stored Procedure
-- 상품 코드를 매게변수(parameter)로 하여 재고수량 add

-- 만들거나 수정시키는 procedure, 프로시져 이름 : usp_prod_totalstock_update (총 재고수량 업데이트 프로그램)
create or replace procedure usp_prod_totalstock_update
    (v_prod_id in prod.prod_id%type, -- in은 외부로부터 데이터를 받는다는 뜻
    v_qty in prod.prod_totalstock%type) -- prod_totalstock와 똑같은 타입으로 받아 v_qty 이름으로 만듦
is
begin
    update prod
        set prod_totalstock = prod_totalstock + v_qty -- 현재 재고에다가 지금 들어온 수량을 더하여 현재 재고에 넣음
    where prod_id = v_prod_id;
    dbms_output.put_line('정상적으로 업데이트 되었습니다.');
    commit;
    
exception -- 에러가 났을 때
    when others then -- others 오라클 에서 에러가 났을 경우 (others)
        dbms_output.put_line('예외 발생: ' || sqlerrm);
        rollback;
end;
/

 

결과 후 프로시저 확인

 

 

위와 같이 실행할 경우 제대로 저장되었는지 확인할 수 없다. => exec 또는 execute를 사용한다.

 

exec 또는 execute  procedule_name ( 매개변수, …);

 

 

-- execute 사용
-- 조회
select prod_id, prod_totalstock
    from prod
    where prod_id = 'P102000006';

-- 업데이트 후 조회하면 500개가 상승된다.
execute usp_prod_totalstock_update('P102000006', 500);

 

결과 화면5

 

 

create or replace procedure usp_MemberID
    (p_mem_id in member.mem_id%type,
    p_mem_name out member.mem_name%type, -- 밖으로 mem_name을 보냄
    p_mem_like out member.mem_like%type)
is
begin
    select mem_name, mem_like
    into p_mem_name, p_mem_like
    from member
    where mem_id = p_mem_id;
end;

 

결과 화면6

 

var mem_name varchar2(20);
var mem_like varchar2(20);
execute usp_memberID('a001', :mem_name, :mem_like);
print mem_name;
print mem_like;

 

결과 화면7 : 위에서 한 out으로 인해 저장되어 출력이 됨

 

 

create or replace procedure usp_memberCartTop
    (p_year in varchar2,
    p_amt out number, -- 금액
    p_mem_name out member.mem_name%type)
is
    v_year varchar2(5); -- 프로시저는 is와 begin 사이에서 변수선언을 함
begin
    v_year := (p_year || '%'); -- ex) 2020%
    select mem_name, mem_amt into p_mem_name, p_amt
    from (select mem_name, sum(prod_price * cart_qty) mem_amt
        from member, cart, prod
        where cart_no like v_year -- cart_no(구매번호)가 2020% 인것들 조회 -> 2020년에 결제한 내역 조회
        and cart_member = mem_id
        and cart_prod = prod_id
        group by mem_name
        order by sum(prod_price * cart_qty) desc)
    where rownum <= 1;
end;
/

 

결과 화면8

 

var send_member varchar2;
var send_amt number;
exec usp_membercarttop('2020', :send_amt, :send_member);
print send_member;
print send_amt;

 

결과 화면9

 

 

create or replace procedure usp_prod_info
    (v_prod_id  in prod.prod_id%type,
    p_yyyymm    in varchar2,
    p_inqty     out number,
    p_outqty    out number)
is
    v_yyyymm varchar2(7);
begin
    v_yyyymm := (p_yyyymm || '%');
    
    select nvl(sum(buy_qty), 0) into p_inqty
        from buyprod
        where to_char(buy_date, 'YYYYMMDD') like v_yyyymm and buy_prod = v_prod_id
        group by buy_prod;
----
    select nvl(sum(cart_qty), 0) into p_outqty
        from cart
        where cart_no like v_yyyymm and cart_prod = v_prod_id
        group by cart_prod;
end;

 

-- 실행
var send_inqty number;
var send_outqty number;
exec usp_prod_info('P101000001', '202004', :send_inqty, :send_outqty);
print send_inqty;
print send_outqty;

 

결과 화면10

 

-- 환경설정 변수 변경. 매 세션마다 초기화 되므로 매번 재설정 해야 함
set serveroutput on

declare
    send_inqty number;
    send_outqty number;
    
begin
    usp_prod_info('P101000001', '202004', send_inqty, send_outqty);
    dbms_output.put_line('*입고량 => ' || send_inqty);
    dbms_output.put_line('*출고량 => ' || send_outqty);
end;

 

결과 화면11 : 10번의 결과 화면과 출력 방식을 달리함. 이와 같은 방식을 쓰는 게 더 보기 좋음.

 

 

 

User Defined 함수

 

Function은 Procedure가 갖는 장점과 동일하지만 반환값이 있어 오라클 내장함수처럼 사용할 수 있다.

자주 반복되는 subquery, 복잡한 계산식을 사용자가 만들어 일반 함수처럼 사용할 수 있다.

반환할 데이터 타입을 return 으로 선언해야 하며 실행영역에서 return 문이 있어야 한다.

 

 

create [ or replace ] function function_name
    [( argument  [mode] [ {:= | default} expression] 
    , …)]
return data_type
{ is | as }
begin
    pl/sql_block; 
end;

 

 

 

-- function
-- 회원 아이디를 받으면 해당 이름을 리턴하는 함수 만들기
create or replace function fn_memName
    (p_mem_id in varchar2)
    return varchar2 -- 필수! 타입으로 줘야함.
is
    r_name varchar2(30);
begin
    select mem_name into r_name from member
        where mem_id = p_mem_id;
    return r_name;
exception
    when others then
        dbms_output.put_line('예외 발생: ' || sqlerrm);
        return null;
end;
/

 

-- fn_memName 실행 테스트 방법1
var m_name varchar2;
execute : m_name := fn_memName('a001');
print m_name;

 

결과 화면12

 

-- fn_memName 실행 테스트 방법2
-- 실제 함수처럼 sql 구문에서 실행
select cart_no, cart_prod, cart_member, fn_memName(cart_member)
    from cart
    where cart_no = '2020040100001';

 

결과 화면13

 

-- fn_memName 실행 테스트 방법3
declare
    m_name varchar2(20);
begin
    m_name := fn_memName('a001');
    dbms_output.put_line('* 이름 => ' || m_name);
end;

 

결과 화면14

 

 

-- 년도 및 상품코드를 입력 받으면 해당년도의 평균 판매 수량을 반환하는 함수
create or replace function fn_prodAvgQty
    (p_year in number default (extract(year from sysdate)),
    p_prod_id in varchar2)
return number
is
    r_qty number(10);
    v_year varchar2(5) := to_char(p_year) || '%';
begin
    select nvl(avg(cart_qty), 0) into r_qty from cart
        where cart_prod = p_prod_id and cart_no like v_year;
    return r_qty;
exception
    when others then
        dbms_output.put_line('예외 발생 : ' || sqlerrm);
        return 0;
end;
/

 

-- fn_prodAvgQty 실행 테스트 방법1
var qty number;
exec : qty := fn_prodAvgQty(2020, 'P201000018');
print qty;

 

결과 화면15

 

-- fn_prodAvgQty 실행 테스트 방법2
-- 실제 함수처럼 sql 구문에서 실행
select prod_id, prod_name,
    fn_prodAvgQty(2004, prod_id) "2004년 평균 판매수량",
    fn_prodAvgQty(2020, prod_id) "2020년 평균 판매수량",
    fn_prodAvgQty(2024, prod_id) "2024년 평균 판매수량"
    from prod;

 

결과 화면16

 

-- fn_prodAvgQty 실행 테스트 방법3
declare
m_year varchar2(5);
begin
    m_year := '2020';
    dbms_output.put_line('*연도 => ' || m_year);
end;

 

결과 화면17