SQL PL/SQL 프로시저 7_ 패키지 코드실습 및 개발

--상황가정--
/*나는 음원회사의 데이터분석업무를 하고있다. 올해의 vip밴드를 고르기 위해
작년도의 수상갯수와 좋아요수를 합산하여 가장 높은 수를 기록하는 밴드를 골라야하는데
이 작업이 매번 있다보니 이에 대한 패키지를 만들어, 이름을 입력하면 원하는 정보를
출력해주는 패키지를 만들고자 한다*/

--패키지 출력문--
/*밴드명: 출력시 변수 사용
  어워드 수:(프로시저)
  좋아요 수:(프로시저)
  합계: (함수)*/

직접 테이블을 만들고 패키지의 출력문대로 출력될 수 있도록 패키지의 코드를 연구해보도록 하자.

 

로직구성

--로직구성--
/* 1. 밴드정보 테이블 생성
    -이름, 좋아요 수, 어워드 수, 대표곡
   2.패키지 로직
    -프로시저에서 in파라미터값으로 밴드의 이름을 입력
    -입력한 값을 where절 조건으로 두어 좋아요수와 어워드 수를 변수에 저장
    -저장된 두 값을 따로따로 출력할 수 있도록 프로시저에서 dbms출력문으로 작성
    (여기서 함수에서도 사용가능하도록 변수는 전역변수로 작성예정)
    -함수에서 두 변수에 저장된 값을 더하여 변수에 저장후 리턴값으로 반환*/

 

테이블 생성

--테이블 생성--
create table band_info
(
name varchar2(100),
like_no number,
prize number,
sing varchar2(100)
);

--값 삽입--
insert into band_info values('유다빈 밴드', 13243, 11, '항해');
insert into band_info values('실리카겔', 30243, 12, 'no_pain');
insert into band_info values('터치드', 40293, 10, '야경');
insert into band_info values('눈뜨고 코베인', 30425, 7, '지구를 지키지말거라');
insert into band_info values('전자양', 34923, 10,'콘트롤타워');

select * from band_info;

밴드의 정보가 담긴 테이블을 간단하게 생성해주고 값도 삽입하여  테이블을 완성시켜준다.

 

 

패키지 사양 작성

--패키시 작성--

--패키지 사양작성--
create or replace package band_sele
is
function get_band return number;
procedure set_band(p_name varchar2);
end band_sele;
/

패키지 본문에서 사용할 함수와 프로시저를 간단하게 정의한다. 함수에서 사용할 파라미터값은 없기때문에 생략했고 총합계를 반환할 리턴값을 정의해주었다. 프로시저에서는 밴드의 이름으로 in파라미터 값을 받아야하므로 파라미터와 그 자료형을 적어 사양작성을 마무리했다.

 

 

패키지 본문 작성

--패키지 본문작성--
create or replace package body band_sele
is
v_like number;
v_prize number;
function get_band
return number
is
v_total number;
begin
v_total := v_like + v_prize;
return v_total;
end get_band;
procedure set_band(p_name varchar2)
is
begin
select like_no,prize into v_like, v_prize from band_info where name=p_name;
dbms_output.put_line('좋아요 수: '||v_like);
dbms_output.put_line('어워드 수: '||v_prize);
end set_band;
end;
/

로직구성한대로 좋아요 수와 어워드 수를 저장할 변수를 전역변수부분에 정의하여 함수도 인식하게 해주었고, 함수에서 v_total변수를 정의하여 좋아요수와 어워드 수 합산값을 저장하도록했고, 프로시저에서는 파라미터를 설정하여 밴드의 이름을 파라미터의 값으로 입력받을 수 있도록 했다. begin절에서 where절에 name=p_name을 조건으로 설정하여 사용자가 입력하는 밴드의 이름에 맞는 정보를 출력할 수 있도록 패키지의 본문을 작성했다.

 

 

패키지 호출

--서버 on--
set serveroutput on;
show serveroutput;


--패키지 호출--
declare
v_name varchar2(100);
v_total number;
begin
v_name := '유다빈 밴드';
dbms_output.put_line('밴드명: '||v_name);
--프로시저는 out파라미터가 있지 않는이상 반환값이 없기때문에 dbms문으로 출력이 불가능하다--
band_sele.set_band(v_name);
--함수에 넣을 in값이 없더라도 괄호는 반드시 적어주어야함--
v_total := band_sele.get_band();
dbms_output.put_line('총 합계: '||v_total);
end;
/


declare
v_name varchar2(100);
v_total number;
begin
v_name := '터치드';
dbms_output.put_line('밴드명 : ' || v_name);
band_sele.set_band(v_name);
v_total := band_sele.get_band();
dbms_output.put_line('총 합계: '||v_total);
end;
/

declare절에서 이름과 함수의 리턴값을 반환받을 변수를 선언하였고 begin절에서 밴드의 이름도 함께 출력되도록 변수에 입력할 밴드의 이름을 v_name 변수에 저장한 후 그 저장된 변수를 프로시저의 파라미터값으로 전달하도록 작성했다. 문제없이 패키지의 결과가 출력되는걸 볼 수있다..!

 

 

커서활용 / 다중행으로 출력하기

--로직구성--
/* 
   1.패키지 로직
    -프로시저에서 반복문을 통해 커서를 통해 이름, 좋아요 수, 어워드 수를 순서대로 출력
    -while loop문사용
    -저장된 값을 따로따로 출력할 수 있도록 프로시저에서 dbms출력문으로 작성
    (여기서 함수에서도 사용가능하도록 변수는 전역변수로 작성예정)
    -함수에서 두 변수에 저장된 값을 더하여 변수에 저장후 리턴값으로 반환*/

위와 같은 결과를 패키지 안에 커서를 이용하여 반복되어 다중행을 출력할 수 있도록 출력해보고자 한다.

 

패키지 사양작성

create or replace package loop_p
is
function get_band return number;
procedure set_band;
end;
/

이번에는 입력받을 파라미터의 값이 없으므로 파리미터는 적지 않고, 함수에서 반환받을 리턴값만 작성해주었다.

 

 

패키지 본문작성

--패키지 본문--
create or replace package body loop_p
is
   v_name varchar2(100);
   v_like number;
   v_prize number;
function get_band
  return number
is
  v_total number;
begin
  v_total := v_like + v_prize;
  return v_total;
 end get_band;
procedure set_band
is
  cursor band_c is
    select name, like_no, prize from band_info;
begin
    open band_c;
    fetch band_c into v_name,v_like, v_prize;
while band_c%FOUND loop
   dbms_output.put_line('밴드명: '||v_name);
   dbms_output.put_line('좋아요 수: '||v_like);
   dbms_output.put_line('어워드 수: '||v_prize);
   fetch band_c into v_name,v_like, v_prize;
  end loop;
 close band_c;
end set_band;
end;
/

이번에는 프로시저안에 커서를 추가했고 커서안에서도 while loop문을 통해 반복하면서 원하는 값이 다중행으로 출력되도록 했다. 또한 커서를 이용하기때문에 이번에는 밴드명까지 아예 커서의 값으로 출력되도록 쿼리를 작성하였고 함수에서는 좋아요 수와 어워드 수를 합산한 총결과만 리턴값으로 반환되도록 했다.

 

 

패키지 호출

--패키지 호출--
declare
v_total number;
begin
loop_p.set_band();
v_total := loop_p.get_band;
dbms_output.put_line('총 합계: '||v_total);
end;
/

출력이 되긴 하나, 총합에 대한 부분이 함수에 존재하기때문에 커서의 루프문안에 들어가 있지 않아, 맨 마지막 값만 총합이 출력되는 문제가 발생했다.. 그렇다고 총합까지 프로시저의 커서안에 넣으면 사실상 그냥 커서를 사용하는것과 아무 차이가 없으며 패키지를 사용하는 이유가 없어지게 되므로이번에는 아예 커서 복습겸 커서로만 출력해보고자 한다.

 

 

커서로만 출력하기

declare
v_name band_info.name%type;
v_like band_info.like_no%type;
v_prize band_info.prize%type;
cursor loop_c is
select name, like_no, prize from band_info;
begin
open loop_c;
fetch loop_c into v_name, v_like,v_prize;
while loop_c%found loop
dbms_output.put_line('밴드명: '|| v_name);
dbms_output.put_line('좋아요 수: '||v_like);
dbms_output.put_line('어워드 수: '|| v_prize);
dbms_output.put_line('총 합계: '||(v_like+v_prize));
dbms_output.put_line('                       ');
fetch loop_c into v_name, v_like,v_prize;
end loop;
close loop_c;
end;
/

코드 결과 중 일부

커서로 출력할때는 변수에 %type과 select into절을 사용하여 값을 받아올 수 있도록했고, begin절에 while loop문을 통해 반복되면서 변수에 저장되는 값이 출력될 수 있도록 출력문을 함께 적어주는 방식으로 커서문의 쿼리를 작성했다. 역시 커서로 쿼리문을 출력하니 깔끔하게 잘 출력된다. 

 

---

학습일기

패키지를 조금더 잘 활용해보고 싶어서 이것저것 작성한 코드연구였는데 여렵긴했으나 꽤나 재미있었다. 테이블 설계와 어떻게 결과가 나왔으면 하는지에 대해 로직구성부터 패키지 호출까지 직접 스스로해보다보니 확실히 패키지의 사용법과 활용이 손에 익혀지는 느낌이 든다. 사실 오늘은 그냥 연습겸 하는 코드개발이라 억지로 커서를 넣어 다중행 패키지로 한번 작성해보았으나, 때에 맞게 어떤 패키지를 사용해야하고, 어떤 프로시저를 사용하는지에대한 결정도 중요한것같다. 그래도 오늘한 코드연구는 모두 생각했던 대로 결과가 나와서 좋다..!!