<DataBase_231113월> 프로시저, 함수

SQL 쪽지시험 문제 풀이.txt

0.02MB

SQL_시험 답, 해설.doc

0.10MB

 
 
------------------------------------
 
<이번주 과제>
 
이번주 금요일 까지 조별로 제안서 프로젝트 주제 3개 만들어오는 숙제 (출력 필요없음. 파일로만 제출)
1. 개발동기가 명확해야 함.
- 기존에 있는 것 중에 아쉬운거에 새로운 기능을 넣어서 업그레이드 시키던지
- 없는걸 아예 만들던지.
2. 어떤 데이터 프로그램, 어떤 데이터 시스템을 만들지. 어떤 기능을 구현할지.
(데이터 사이즈가 많고, 늘 insert, update가 발생하는 데이터)
 
------------------------------------
 
create에 들어가는 변수/타입은 테이블을 생성하기 위한 용도로 사용됨. 
(= 테이블의 컬럼 이름, 컬럼 타입, 컬럼 길이 등을 저장하는 데 사용 된다는 말)
 
is 절에 들어가는 변수/타입은 테이블의 컬럼 값을 비교하기 위한 용도로 사용됨
(= 테이블의 컬럼 값과 비교해야 하는 값을 저장하는 데 사용 된다는 말)

create 문에서 사용되는 변수는 데이터베이스 개체의 속성을 정의하는 데 사용되는 반면, is 문에서 사용되는 변수는 PL/SQL 프로시저에서 사용될 데이터를 저장하는 데 사용된다는 말.
 
------------------------------------
 
프로시저는 대상테이블이 있고 그 테이블에 inset update delete를 하기 위한 목적(데이터조작을 위한 목적)
반면, 함수는 대상테이블이 없음.
 
------------------------------------
 

 

 
// 연습문제 1.

/*  procedure
1. sabun은 시퀀스를 이용 (201~299번까지, 2씩 증가하도록)
2. sahire은 현재날짜로 입력 (sysdate 이용)
3. comm은 if 조건문을 사용하여 sajob에 따라 입력
if sajob:='대리' then v_comm:=v_pay*0.1;
elsif sajob:= '과장' then v_comm:=v_pay*0.15;
elsif sajob:= '부장' then v_comm:=v_pay*0.2;
else v_comm:=0;
end if;
4. samgr은 if 조건문을 사용하여
select d.dname, s.smgr from d.dept, s.sawon
where s.deptno=d.deptno;
if d.dname:='영업부' then samgr:=3;
elsif d.dname:='총무부' then samgr:=10;
elsif d.dname:='전산부' then samgr:=6;
이렇게 입력되도록 한다. */
 
// 1. 내가 한 풀이

 
create sequence s_sawon_sabun --sabun은 시퀀스를 이용 (201~299번까지, 2씩 증가하도록)
increment by 2
start with 201
maxvalue 299;

create or replace procedure p_sawon_in
(v_saname sawon.saname%type,
 v_sapay sawon.sapay%type,
 v_sajob sawon.sajob%type,
 v_sasex sawon.sasex%type,
 v_dname dept.dname%type)

is
--dname_dept dept.deptno%type;
--v_comm sawon.comm%type;
   v_samgr sawon.samgr%type;

begin
--select deptno into dname_dept from dept where dname=v_dname;
 
--if v_sajob='대리' then v_comm := v_sapay*0.1;
--elsif v_sajob='과장' then v_comm := v_sapay*0.15;
--elsif v_sajob='부장' then v_comm := v_sapay*0.2;
--end if;

if v_dname='영업부' then v_samgr :=3;
elsif v_dname='총무부' then v_samgr :=10;
elsif v_dname='전산부' then v_samgr :=6;
end if;

insert into sawon (sabun, saname, sapay, sajob, sasex, deptno, comm, samgr, sahire)
-- values (s_sawon_sabun.nextval, v_saname, v_sapay, v_sajob, v_sasex, dname_dept, v_comm, v_samgr, sysdate);
values(s_sawon_sabun.nextval, v_saname, v_sapay, v_sajob, v_sasex,
(select deptno from dept where dname=v_dname),
(case v_sajob when '대리' then v_sapay*0.1 when '과장' then v_sapay*0.14 when '부장' then v_sapay*0.2 else 0 end),
v_samgr, sysdate);

commit;
end;
/
 
------------------------------------
 
show error procedure p_sawon_in
 
------------------------------------
 

 
------------------------------------
 

 
------------------------------------
 
// 연습문제 2.
/* 사원 테이블에 사원 정보를 변경하는 procedure
호출형태
exec p_sawon_up(201, 3500, '과장', '관리부') -> 201번 사원의 정보를 지정한 값들로 변경
커미션은 직책의 조건에 따라 입력(직책이 대리->급여의10%, 과장->급여의15%, 부장->급여의20%, 그외직책->0)
관리자번호는 부서가 영업부이면 3번 사번을 입력, 총부무이면 10번 사번을 입력, 전산부이면 6번 사번을 입력하도록 만든다)
*/
 
// 2.  내가 한 풀이
create or replace procedure p_sawon_up
(v_saname sawon.saname%type,
 v_sapay sawon.sapay%type,
 v_sajob sawon.sajob%type,
 v_sasex sawon.sasex%type,
 v_dname dept.dname%type)

is
v_deptno dept.deptno%type;
v_comm sawon.comm%type;
v_samgr sawon.samgr%type;
v_sabun sawon.sabun%type;

begin
select deptno into v_deptno from dept where dname=v_dname; --부서번호

if v_dname='영업부' then v_sabun :=3;
elsif v_dname='총무부' then v_sabun :=10;
elsif v_dname='전산부' then v_sabun :=6;
end if;
 
if v_sajob='대리' then v_comm := v_sapay*0.1;
elsif v_sajob='과장' then v_comm := v_sapay*0.15;
elsif v_sajob='부장' then v_comm := v_sapay*0.2;
else v_comm := 0;
end if;

update sawon set 
sapay=v_sapay,
deptno=v_deptno,
sajob=v_sajob,
comm=v_comm,
samgr=v_samgr
where sabun=v_sabun;

commit;
end;
/
 
------------------------------------
 

 
------------------------------------
 
// 연습문제 3.

 
// 3.  내가 한 풀이

/*
해당 입사년도에 입사한 사원의 인원수와 평균급여를 출력하는 procedure

show error procedure p_result_output

variable result varchar2(100) --외부변수 선언
exec p_state(1990, :result) --프로시져 호출
print result --변수 출력
*/

create or replace procedure p_state
(year in number,
output out varchar2)
is
cnt number(3); -- 인원수
pay_avg number(10, 2); -- 평균급여

begin
select count(*), avg(sapay) into cnt, pay_avg from sawon where to_char(sahire, 'yyyy')=year;  
output := '입사년도: ' || year || ', 인원수: ' || cnt || ', 평균급여: ' || pay_avg;
end;
/

 
------------------------------------
 

 
// 예시

 
------------------------------------
 
// 연습문제 4.
/* 
col goname format a10

show error function f_sex

-- 이름의 성을 제외한 이름만 추출하는 함수

create or replace function f_name
(name varchar2)
return varchar2

is
begin
return substr(name, 2, 4);

col name format a10

-- 문제 : 주민번호 형태의 데이터를 가지고 성별을 반환하는 함수
-- select goname, f_sex(gojumin) "성별" from gogek; --호출형태  
*/
 
// 4.  내가 한 풀이
create or replace function f_sex
(gojumin varchar2)
return varchar2
is
v_gender varchar2(10);
begin

v_gender := case when substr(gojumin, 8 ,1)=1 then '남자' else '여자' end;

return v_gender;

end;
/
 
------------------------------------
 
// 연습문제 5.
/* 
col goname format a10

show error function f_sex

-- 이름의 성을 제외한 이름만 추출하는 함수

create or replace function f_name
(name varchar2)
return varchar2

is
begin
return substr(name, 2, 4);

col name format a10

-- 문제 : 숫자형태의 데이터를 가지고 100의 '*' 하나를 반환하는 함수
-- select saname, f_star(sapay) 급여현황 from sawon; --호출형태
*/
 
// 5.  내가 한 풀이
create or replace function f_star
(star number)
return varchar2
is
v_star varchar2(1000);
begin

v_star := lpad('*', (star/100), '*');

return v_star;

end;
/
 
------------------
 
// 4번 5번 선생님이 정리해주신 풀이
 

 
------------------------------------
 
<선생님이 정리해주신>
 
--ed14
 
***  Procedure(프로시저)
--자주 실행되는 SQL & PL/SQL을 미리 컴파일 -> 호출(실행)만 해서 사용하는 객체
--목적 
데이터 처리(DML) -> 대상 테이블이 반드시 존재***
성능 향상(속도 빠름) -> 한 번의 컴파일로 호출만 해서 사용
메모리 절약 -> 동일한 프로시져를 여러 사용자가 호출하더라도 하나의 메모리 사용

--테이블이 아닌 프로시저 단위의 사용권한을 부여하므로 보안성을 강화
grant execute on 프로시져 to ~

--파라미터 변수 mode(type만 지정!->길이는 지정 안됨)
-in mode : 호출될 때 값을 받는 변수 ***
-out mode : 프로시져가 호출된 후 결과값을 호출한 곳에 넘겨주는 변수
-in out mode : 호출할때 값이 들어가서 실행된 결과는 넘겨주는 변수

--형식
create or repalce procedure pro_name
(파라미터변수 [(in) | out] type,...)
is
--지역 변수 선언;
begin

end;
/
 
--ed15
 
drop table sawon_ex;
create table sawon_ex as select * from sawon;

create or replace procedure p_sawon_del
( v_bun sawon_ex.sabun%type )
is
begin
delete sawon_ex where sabun=v_bun;
end;
/

-- 호출형태
exec p_sawon_del(15)
 
--ed16
 
--사원 테이블에 사원 정보를 입력하는 프로시져
--사번은 시퀀스를 이용하고(201~299, 2씩증가)
--입사일은 현재날짜로 입력되고,
--커미션은 직책 조건에 따라 입력(직책이 대리 -> 급여의 10%, 과장 -> 급여의 15%, 부장-> 급여의 20%, 그 외의 직책-> 0)
--관리자번호는 부서가 영업부이면, 3번 사번을 입력하고, 총무부이면 10번 사번을 입력하고, 전산부이면 6번 사번을 입력되도록 한다.

--호출형태
--exec p_sawon_in('홍동우', 3000, '대리', '남자', '영업부')

-- 시퀀스 생성
create sequence s_sawon_sabun
increment by 2
start with 201
maxvalue 299;

create or replace procedure p_sawon_in(
    v_saname sawon.saname%type,
    v_sapay sawon.sapay%type,
    v_sajob sawon.sajob%type,
    v_sasex sawon.sasex%type,
    v_dname dept.dname%type )
is
--    dname_dept dept.deptno%type;
--    v_comm sawon.comm%type;
    v_samgr sawon.samgr%type;
begin
--   select deptno into dname_dept from dept where dname = v_dname;
   
--   if v_sajob = '대리' then
--      v_comm := v_sapay*0.1;
--   elsif v_sajob = '과장' then
--      v_comm := v_sapay*0.15;
--   elsif v_sajob = '부장' then
--      v_comm := v_sapay*0.20;
--   end if;

   if v_dname = '영업부' then
      v_samgr := 3;
   elsif v_dname = '총무부' then
      v_samgr := 10;
   elsif v_dname = '전산부' then
      v_samgr := 6;
   end if;

   insert into sawon (sabun, saname, sapay, sajob, sasex, deptno, comm, samgr, sahire)
--      values (s_sawon_sabun.nextval, v_saname, v_sapay, v_sajob,v_sasex, dname_dept, v_comm, v_samgr, sysdate);
   values( s_sawon_sabun.nextval, 
v_saname, 
v_sapay, 
v_sajob,
v_sasex,
(select deptno from dept where dname=v_dname),
(case v_sajob when '대리' then v_sapay*0.1 when '과장' then v_sapay*0.14 when '부장' then v_sapay*0.2 else 0 end),
v_samgr,
sysdate);

   commit;
end;
/

--show error procedure 프로시져명
--user_errors 뷰 : object_type 컬럼에서 객체지정 필요
--user_objects 뷰 : 객체의 정보
 
--ed16_1
 
set verify off

accept name prompt '이름입력->'
accept pay prompt '급여입력->'
accept job prompt '직책입력->'
accept sex prompt '성별입력->'
accept dname prompt '부서명입력->'

begin
p_sawon_in('&name', &pay, '&job', '&sex', '&dname');
end;
/
 
--ed17
 
--사원 테이블에 사원 정보를 변경하는 프로시져
--호출형태
--exec p_sawon_up(201, 3500, '과장', '관리부') ->201번 사원의 정보를 지정한 값들로 변경

--커미션은 직책의 조건에 따라 입력(직책이 대리 -> 급여의 10%, 과장 -> 급여의 15%, 부장-> 급여의 20%, 그 외의 직책-> 0)
--관리자번호는 부서가 영업부이면, 3번 사번을 입력하고, 총무부이면 10번 사번을 입력하고, 전산부이면 6번 사번을 입력되도록 한다.

create or replace procedure p_sawon_up(
    v_sabun sawon.sabun%type,
    v_sapay sawon.sapay%type,
    v_sajob sawon.sajob%type,
    v_dname dept.dname%type )
is
begin
   select deptno into v_deptno from dept where dname = v_dname;  --부서번호

   if (v_dname = '영업부') then v_mgr := 3;
   elsif (v_dname = '총무부') then v_mgr := 10;
   elsif (v_dname = '전산부') then v_mgr := 6;
   end if;

   if (v_job = '대리') then v_comm := v_sal*0.1;
   elsif (v_job = '과장') then v_comm := v_sal*0.15;
   elsif (v_job = '부장') then v_comm := v_sal*0.20;
   else v_comm := 0;
   end if;

   update sawon set  
      sapay = v_sal,
      deptno = v_deptno,
      sajob = v_job,
      comm = v_comm,
      samgr = v_mgr
      where sabun = v_bun;

-- update sawon
-- set  sapay = v_sapay,
-- sajob = v_sajob,
-- deptno = (select deptno from dept where dname=v_dname),
-- comm = (case v_sajob when '대리' then v_sapay*0.1 when '과장' then v_sapay*0.15 when '부장' then v_sapay*0.2 else 0 end),
-- samgr = (case v_dname when '영업부' then 3 when '총무부' then 10 when '전산부' then 6 end)
-- where sabun=v_sabun;
commit;

end;
/
 
--ed18
 
--out mode가 있는 프로시져

--사번을 가지고 이름을 리턴해주는 프로시져를 생성

create or replace procedure p_name_output
( v_bun         sawon.sabun%type,
  v_name out sawon.saname%type )
is
begin
select saname into v_name from sawon where sabun=v_bun;
end;
/

-- 호출형태
SQL> variable name varchar2(100) --1. 외부변수 선언
SQL> exec p_name_output(12, :name) --2. 프로시져 호출

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> print name --3. 변수 출력

NAME
-------------------------------------------------
류명한
 
--ed19
 
--해당 입사년도에 입사한 사원의 인원수와 평균급여를 출력하는 프로시져

--호출형태
--variable result varchar2(100)
--exec p_state(1990, :result)
--print result // [1990년 입사한 사원은 XX명, 평균급여는 XXXX이다] 형태로 출력


create or replace procedure p_state
(
year    in   number,
output out varchar2
)
is
cnt number(3);
pay_avg number(10, 2);
begin
select count(*), avg(sapay) into cnt, pay_avg from sawon where to_char(sahire, 'yyyy') = year;
output := '입사년도 : ' || year || ', 인원수 : ' || cnt || ', 평균급여 : ' || pay_avg;
end;
/
 
--ed20
 
*** Function(함수)
-- 반복되는 복잡하고 어려운 수식을 미리 생성하여 호출만 해서 사용하기 위한 객체
-- 데이터 조작(DML) 목적이 아니므로 대상 테이블이 없다*** - insert를 제외한 DML사용 불가!!
-- 함수 사용 가능한 모든 곳에서 호출 가능하므로 문장의 일부처럼 사용 가능
-- in mode만 사용(반드시 리턴값이 1개 존재하니까)

-- 형식 
create or replace function func_name
(파라미터변수 type,...)
return type -- 길이지정 안함
is
-- 지역변수 선언;
begin
명령;
return [값|변수];
end;
/
 
--ed21
 
--이름의 성을 제외한 이름만 추출하는 함수

create or replace function f_name
( name varchar2 )
return varchar2
is
begin
return substr(name, 2, 4);
end;
/

--호출형태
select saname, f_name(saname) name from sawon;
select goname, f_name(goname) name from gogek;
 
--ed22
 
--주민번호 형태의 데이터를 가지고 성별을 반환하는 함수
--호출형태
--select goname, f_sex(gojumin) "성별" from gogek;

create or replace function f_sex
(v_jumin varchar2)
return varchar2
is
sex_num number(1);
begin
sex_num := substr(v_jumin, 8, 1);
if sex_num = 1 or sex_num = 3 then return '남자';
else return '여자';
end if;
end;
/


--숫자형태의 데이터를 가지고 100의 '*'하나를 반환하는 함수
--호출형태
--select saname, f_star(sapay) 급여현황 from sawon;

create or replace function f_graph
(v_sapay number)
return varchar2
is
begin
return lpad('*' , v_sapay/100, '*');
end;
/

------------------------------------
 
<오늘 숙제>

<내가 한 풀이>
 
1. 날짜 형태의 값을 가지고 [xx년 xx개월]형태로 기간을 반환하는 함수를 만들어라.
-- 호출형태 col star format a50 col 근무기간 format a20 
-- select saname, sapay, f_graph(sapay) star, sahire, f_gigan(sahire) 근무기간 from sawon; 

create or replace function f_gigan
          (hire_date date)
          return varchar2
is
begin
          return floor(months_between(sysdate, hire_date)/12) || ' 년  ' ||
          floor(mod(months_between(sysdate, hire_date),12)) || ' 개월';
end;
/

2. 날짜에 년수, 개월수, 날수를 매개변수로 보내 더하는 함수를 생성하라 
-- (입사일로부터 년 수/개월/일 을 더해서 출력하는 함수)
-- 입사일로부터 3년 1개월 3일 더한값 출력 
-- 적용 예) select saname, sahire, add_date(sahire, 3, 1, 3) "심사일" from sawon; 

create or replace function add_date (
    v_date date,
    v_year number,
    v_mon number,
    v_day number
)
return date

is
begin
    return add_months(v_date, v_year*12+v_mon)+v_day;
end;
/

3. 주민번호를 가지고 나이를 계산하는 함수를 생성하라. 
--적용 예) select goname, gojumin, f_jumin_age(gojumin) age from gogek;

create or replace function f_jumin_age 
   (birth varchar2)
   return number
is
   v_age number;
begin
   if substr(birth, 8, 1) in (1, 2) then 
      v_age := to_number(to_char(sysdate, 'yyyy')) - (substr(birth, 1, 2)+1900);
      else v_age := to_number(to_char(sysdate, 'yyyy')) - (substr(birth, 1, 2)+2000);
   end if;

   return v_age;
end;
/

 

----------------------------

 

<오늘 숙제>

<선생님이 정리해주신 풀이>

 

1. 날짜 형태의 값을 가지고 [xx년 xx개월]형태로 기간을 반환하는 함수를 만들어라.
-- 호출형태 col star format a50 col 근무기간 format a20 
-- select saname, sapay, f_graph(sapay) star, sahire, f_gigan(sahire) 근무기간 from sawon; 

 

create or replace function f_gigan
   (v_date date)
   return varchar2
is
begin
   return floor(months_between(sysdate, v_sahire)/12) || '년 ' ||  floor(mod(months_between(sysdate, v_sahire), 12)) || '개월';
end;
/

2. 날짜에 년수, 개월수, 날수를 매개변수로 보내 더하는 함수를 생성하라 
-- (입사일로부터 년 수/개월/일 을 더해서 출력하는 함수)
-- 입사일로부터 3년 1개월 3일 더한값 출력 
-- 적용 예) select saname, sahire, add_date(sahire, 3, 1, 3) "심사일" from sawon; 

create or replace function add_date(
      v_hire date,
      year number,
      month number,
      day number)
      return date
is
begin   
   return add_months(v_hire,((year*12)+month))+day;
end;
/

3. 주민번호를 가지고 나이를 계산하는 함수를 생성하라. 
--적용 예) select goname, gojumin, f_jumin_age(gojumin) age from gogek;

create or replace function f_jumin_age(
      jumin varchar2)
      return varchar2
is
   cen number(2);
   year number(4);
   go_year number(2);
begin
   cen := substr(jumin,8,1);  -- 1900년생, 2000년생 구별하기 위한 데이터
   year := to_char(sysdate,'yyyy'); -- 현재의 년도 구한 데이터
   go_year := substr(jumin,1,2); -- 년생 구하기

   if(cen = 1 or cen = 2) then
      return year-(1900+go_year)||'살';
   else
      return year-(2000+go_year)||'살';
   end if;
end;
/