-- 주민번호를 입력받아서 성별을 알려주는 함수 func_gender( 주민번호 )를 생성하기 --
[문법]
create or replace function 함수명
(파라미터변수명 IN 파라미터변수의타입)
return 리턴되어질타입
is
변수선언;
begin
실행문;
return 리턴되어질값;
end 함수명;
Function 생성하기
create or replace function func_gender
( p_jubun IN varchar2 ) -- varchar2(13)와 같이 자리수를 쓰면 오류 발생.!
return varchar2 -- varchar2(10)와 같이 자리수를 쓰면 오류 발생.!
is
v_result varchar2(6); -- 여기는 자리수를 써야 한다.
begin
select case when substr(p_jubun, 7, 1) in ('2','4') then '여' else '남' end
into
v_result
from dual;
return v_result;
end func_gender;
-- Function FUNC_GENDER이(가) 컴파일되었습니다.
생성된 Function 사용하기
select func_gender('9007201234567'), func_gender('9007202234567')
, func_gender('0107203234567'), func_gender('0107204234567')
from dual;
-- 남 여 남 여
select employee_id as 사원번호, first_name || ' ' || last_name as 사원명
, jubun as 주민번호
, func_gender(jubun) as 성별
from employees;
-- 여성만 보고싶을 때
select employee_id as 사원번호, first_name || ' ' || last_name as 사원명
, jubun as 주민번호
, func_gender(jubun) as 성별
from employees
where func_gender(jubun) = '여';
생성된 함수 조회하기
select *
from user_source
where type = 'FUNCTION' and name = 'FUNC_GENDER';
-- 나이 구하는 함수 생성하기
create or replace function func_age
(p_jubun IN varchar2)
return number
is
v_age number(3);
begin
v_age := extract(year from sysdate) - ( to_number(substr(p_jubun, 1, 2)) + case when substr(p_jubun, 7, 1) in ('1','2') then 1900 else 2000 end ) + 1;
return v_age;
end func_age;
select func_age('9007131234567')
from dual;
-- 33
-- 정년퇴직일 구하기
create or replace function func_retirement_day
( p_jubun IN varchar2 )
return date
is
v_retirement_day date;
begin
select last_day ( to_date ( to_char( add_months(sysdate, (63-func_age(p_jubun))*12 ), 'yyyy') ||
case when substr( p_jubun, 3, 2 ) between '03' and '08' then '-08-01' else '-02-01' end
, 'yyyy-mm-dd') )
into v_retirement_day
from dual;
return v_retirement_day;
end func_retirement_day;
--- [퀴즈] ---
employees 테이블에서 모든 사원들에 대해
사원번호, 사원명, 주민번호, 성별, 현재나이, 월급, 입사일자, 정년퇴직일, 정년까지_근무할_개월수
여기서 정년퇴직일이라 함은
해당 사원의 생월이 3월에서 8월에 태어난 사람은
해당사원의 나이(한국나이)가 63세가 되는 년도의 8월말일(8월 31일)로 하고,
해당사원의 생월이 9월에서 2월에 태어난 사람은
해당사원의 나이(한국나이)가 63세가 되는 년도의 2월말일(2월 28일 또는 2월 29일)로 한다.
select employee_id as 사원번호
, first_name || ' ' || last_name as 사원명
, jubun as 주민번호
, func_gender(jubun) as 성별
, func_age(jubun) as 현재나이
, nvl(salary + (salary * commission_pct), salary) as 월급
, hire_date as 입사일자
, func_retirement_day(jubun) as 정년퇴직일
, trunc( months_between(func_retirement_day(jubun), hire_date) ) as 정년까지근무할개월수
, trunc( trunc( months_between(func_retirement_day(jubun), hire_date) )/12 ) * nvl(salary + (salary * commission_pct), salary) as 퇴직금
-- 정년까지근무할개월수/12 * 월급
from employees;
-- [퀴즈] 아래와 같은 결과물이 나오도록 프로시저 pcd_employees_info 를 생성하기
-- 성별과 나이는 위에서 만든 함수를 사용하기
/*
------------------------------------------------------
사원번호 부서명 사원명 입사일자 성별 나이
------------------------------------------------------
101 ... ... ... ... ...
*/
[내 풀이]
create or replace procedure pcd_employees_info
(p_employee_id IN number)
is
v_employee_id number(5);
v_department_name varchar2(50);
v_ename varchar2(50);
v_hire_date varchar2(20);
v_gender varchar2(10);
v_age number(3);
begin
select employee_id
, department_name
, first_name || ' ' || last_name
, hire_date
, func_gender(jubun)
, func_age(jubun)
INTO
v_employee_id, v_department_name, v_ename, v_hire_date, v_gender, v_age
from employees E left join departments D
ON E.department_id = D.department_id
where E.employee_id = p_employee_id;
dbms_output.put_line( lpad('-',55,'-') );
dbms_output.put_line( ' 사원번호 부서명 사원명 입사일자 성별 나이' );
dbms_output.put_line( lpad('-',55,'-') );
dbms_output.put_line( ' ' || v_employee_id || ' ' || v_department_name || ' ' || v_ename || ' ' || v_hire_date || ' ' || v_gender || ' ' || v_age );
end pcd_employees_info;
[선생님 풀이] - 이 방법이 속도면에서 더 나음
-- 이 구문을 활용
with E as
(
select employee_id
, first_name || ' ' || last_name as ename
, hire_date
, func_gender(jubun)
, func_age(jubun)
from employees
where employee_id = 178
)
,
D as
(
select department_id, department_name
from departments
)
select E.employee_id, D.department_name, ename, e.hire_date, e.gender, e.age
from E left join D
on e.department_id = d.department_id;
create or replace procedure pcd_employees_info_2
( p_employee_id IN employees.employee_id%type )
is
v_employee_id employees.employee_id%type;
v_department_name departments.department_name%type;
v_ename varchar2(40);
v_hire_date employees.hire_date%type;
v_gender varchar2(6);
v_age number(3);
begin
with E as
(
select employee_id
, first_name || ' ' || last_name as ename
, hire_date
, func_gender(jubun) as gender
, func_age(jubun) as age
, department_id
from employees
where employee_id = p_employee_id
)
,
D as
(
select department_id, department_name
from departments
)
select E.employee_id, D.department_name, ename, e.hire_date, e.gender, e.age
INTO
v_employee_id, v_department_name, v_ename, v_hire_date, v_gender, v_age
from E left join D
on e.department_id = d.department_id;
dbms_output.put_line( lpad('-',55,'-') );
dbms_output.put_line( ' 사원번호 부서명 사원명 입사일자 성별 나이' );
dbms_output.put_line( lpad('-',55,'-') );
dbms_output.put_line( ' ' || v_employee_id || ' ' || v_department_name || ' ' || v_ename || ' ' || v_hire_date || ' ' || v_gender || ' ' || v_age );
end pcd_employees_info_2;
exec pcd_employees_info_2(234234);
/*
오류 보고 -
ORA-01403: 데이터를 찾을 수 없습니다.
*/
-- !!!! 프로시저에서 데이터(행)가 없을 경우 "no data found" 라는 오류가 발생한다. !!!! --
-- no_data_found 은 오라클에서 데이터가 존재하지 않을 경우 발생하는 오류임.
-- [데이터(행)가 없을 경우 해결책]
-- ==> 예외절(Exception) 처리를 해주면 된다.
create or replace procedure pcd_employees_info_2
( p_employee_id IN employees.employee_id%type )
is
v_employee_id employees.employee_id%type;
v_department_name departments.department_name%type;
v_ename varchar2(40);
v_hire_date employees.hire_date%type;
v_gender varchar2(6);
v_age number(3);
begin
with E as
(
select employee_id
, first_name || ' ' || last_name as ename
, hire_date
, func_gender(jubun) as gender
, func_age(jubun) as age
, department_id
from employees
where employee_id = p_employee_id
)
,
D as
(
select department_id, department_name
from departments
)
select E.employee_id, D.department_name, ename, e.hire_date, e.gender, e.age
INTO
v_employee_id, v_department_name, v_ename, v_hire_date, v_gender, v_age
from E left join D
on e.department_id = d.department_id;
dbms_output.put_line( lpad('-',55,'-') );
dbms_output.put_line( ' 사원번호 부서명 사원명 입사일자 성별 나이' );
dbms_output.put_line( lpad('-',55,'-') );
dbms_output.put_line( ' ' || v_employee_id || ' ' || v_department_name || ' ' || v_ename || ' ' || v_hire_date || ' ' || v_gender || ' ' || v_age );
EXCEPTION
WHEN no_data_found THEN -- no_data_found 은 오라클에서 데이터가 존재하지 않을 경우 발생하는 오류임.
dbms_output.put_line('>> 사원번호 ' || p_employee_id || ' 은 존재하지 않습니다. <<');
end pcd_employees_info_2;
-- Procedure PCD_EMPLOYEES_INFO_2이(가) 컴파일되었습니다.'SQL' 카테고리의 다른 글
| ===== ***** 반복문 ***** ===== (0) | 2022.07.13 |
|---|---|
| 제어문(IF문) (0) | 2022.07.13 |
| PL/SQL(Procedure Language / Structured Query Language) (0) | 2022.07.12 |
| Constraint(제약조건), INDEX(인덱스, 색인) (0) | 2022.07.12 |
| 시노님(Synonym, 동의어) (0) | 2022.07.12 |