함수 만들기
-- func_age 함수
create or replace function func_age
(p_jubun IN varchar2) -- varchar2(13) 와 같이 자리수를 쓰면 안됨(오류)
return number -- varchar2(6)와 같이 자리수를 쓰면 안됨(오류)
is
v_age number(3); -- !! 변수선언 시에는 자리수를 써야 한다. !!
begin
select case when to_date(to_char(sysdate, 'yyyy') || substr(p_jubun, 3, 4), 'yyyymmdd') - to_date(to_char(sysdate, 'yyyymmdd'), 'yyyymmdd') > 0
then 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
else 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 )
end
INTO
v_age -- v_result에 넣겠다. 즉 변수선언 해야한다.
from dual;
return v_age;
end func_age;
create or replace function func_gender
(p_jubun IN varchar2) -- varchar2(13) 와 같이 자리수를 쓰면 안됨(오류)
return varchar2 -- varchar2(6)와 같이 자리수를 쓰면 안됨(오류)
is
v_result varchar2(6); -- 변수선언 시에는 자리수를 써야 한다.
begin
select case when substr(p_jubun, 7, 1) in('1','3') then '남' else '여' end -- 이 값을
INTO
v_result -- v_result에 넣겠다. 즉 변수선언 해야한다.
from dual;
return v_result;
end func_gender;
--------------------정년퇴직일을 구해주는 함수 만들기------------------------------
/*
여기서 정년퇴직일이라 함은
해당 사원의 생월이 3월에서 8월에 태어난 사람은
해당사원의 나이가 63세가 되는 년도의 8월말일(8월 31일)로 하고,
해당사원의 생월이 9월에서 2월에 태어난 사람은
해당사원의 나이가 63세가 되는 년도의 2월말일(2월 28일 또는 2월 29일)로 한다.
*/
create or replace function func_retirement_day
(p_jubun IN varchar2)
return date
is -- 선언부
v_retirement_day date;
begin -- 실행부
-- 63 - 현재나이 를 구한 후 addmonth는 sysdate(현재날짜) + 나이 * 12(개월)이다. 개월을 더하기 때문에 년수를 개월로 반환-> 1=>12개월
select last_day(to_date(to_char(add_months(sysdate,(63 - func_age('9606192111111') ) * 12), 'yyyy') ||
case when substr('9606192111111',3,2) between '03' and '08' then '-08-01' else '-02-01' end,'yyyy-mm-dd'))
into v_retirement_day -- select된걸 v_retirement_day에 넣음
from dual;
return v_retirement_day;
end func_retirement_day;
select func_retirement_day('620101234567')
from dual;
예외처리 프로시저 만들기
begin 실행부에
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('>> 사원번호 ' || p_employee_id || '은 존재하지 않습니다.<<');
로 예외처리를 넣는다. /*
------------------------------------------------------------
사원번호 부서명 부서장명 사원명 입사일자 성별 나이
------------------------------------------------------------
101 ..... ...... ....... .... ... ...
*/
create or replace procedure pcd_employees_info
(p_employee_id in employees.employee_id%type)
is
v_employee_id employees.employee_id%type;
v_deptname departments.department_name%type;
v_mgrname varchar2(30);
v_empname varchar2(30);
v_hire_date varchar2(10);
v_gender varchar2(6);
v_age number(3);
begin
with v as
(
select
A.department_id,
department_name as dept_name,
first_name || ' ' || last_name as dept_king_name
from departments A join employees B
ON A.manager_id = B.employee_id
)
select
e.employee_id as 사원번호,
nvl(dept_name, ' ') AS 부서명,
nvl(dept_king_name,' ') AS 부서장명,
e.first_name || ' ' || e.last_name as 사원명,
to_char(hire_date, 'yyyy-mm-dd') as 입사일자,
func_gender(jubun) AS 성별,
func_age(jubun) AS 나이
INTO
v_employee_id, v_deptname, v_mgrname, v_empname, v_hire_date, v_gender, v_age
from employees e left join v
ON v.department_id = e.department_id
WHERE e.employee_id = p_employee_id;
dbms_output.put_line( lpad('-',60,'-') );
dbms_output.put_line( '사원번호 부서명 부서장명 사원명 입사일자 성별 나이' );
dbms_output.put_line( lpad('-',60,'-') );
dbms_output.put_line( v_employee_id || ' ' ||
v_deptname || ' ' ||
v_mgrname || ' ' ||
v_empname || ' ' ||
v_hire_date || ' ' ||
v_gender || ' ' ||
v_age );
EXCEPTION -- 예외처리
WHEN no_data_found THEN
dbms_output.put_line('>> 사원번호 ' || p_employee_id || '은 존재하지 않습니다.<<');
end pcd_employees_info;
exec pcd_employees_info(101);
exec pcd_employees_info(337);
사용자 정의 예외처리
------------ ***** 사용자 정의 예외절(EXCEPTION) ***** ----------------
예외절 = 오류절
※ 형식
exception
when 익셉션이름1 [or 익셉션이름2] then
실행문장1;
실행문장2;
실행문장3;
when 익셉션이름3 [or 익셉션이름4] then
실행문장4;
실행문장5;
실행문장6;
when others then
실행문장7;
실행문장8;
실행문장9;
------------------------------------------------------------------
/*
=== tbl_member_test1 테이블에 insert 할 수 있는 요일명과 시간을 제한해 두겠습니다. ===
tbl_member_test1 테이블에 insert 할 수 있는 요일명은 월,화,수,목,금 만 가능하며
또한 월,화,수,목,금 중에 오후 2시 부터 오후 5시 이전까지만(오후 5시 정각은 안돼요) insert 가 가능하도록 하고자 한다.
만약에 insert 가 불가한 요일명(토,일)이거나 불가한 시간대에 insert 를 시도하면
'영업시간(월~금 15:00 ~ 16:59:59 까지) 아니므로 입력불가함!!' 이라는 오류메시지가 뜨도록 한다.
*/
SELECT TO_CHAR(sysdate,'d') -- sysdate의 주의 일요일부터(지금은 2024/3/4) sysdate(지금은 2024/3/4) 까지 며칠째인지를 알려주는 것(요일을 알 수 있음)
-- '1'(일요일) '2'(월요일) '3'(화요일) '4'(수요일) '5'(목) '6'(금) '7'(토)
FROM dual;
create or replace procedure pcd_tbl_member_test1_insert
(p_userid IN tbl_member_test1.userid%type
,p_passwd IN tbl_member_test1.passwd%type
,p_name IN tbl_member_test1.name%type)
is
v_passwd_length number(2);
v_ch varchar2(1);
v_flag_alphabet number(1) := 0;
v_flag_number number(1) := 0;
v_flag_special number(1) := 0;
error_insert exception;
error_dayTime exception;
begin
-- 입력(insert)이 불가한 요일명과 시간대를 알아봅니다. --
if( to_char(sysdate, 'd') in('1','7') OR
to_number(to_char(sysdate, 'hh24')) < 14 OR
to_number(to_char(sysdate, 'hh24')) > 16 ) then
raise error_dayTime;
else -- 입력(insert)이 가능한 요일명과 시간대 이라면 암호를 검사하겠다.
v_passwd_length := length(p_passwd);
if( v_passwd_length < 5 or v_passwd_length > 20 ) then
raise error_insert; -- 사용자가 정의하는 예외절(exception)을 구동시켜라.
else
for i in 1..v_passwd_length loop
v_ch := substr(p_passwd, i, 1);
if(v_ch between 'A' and 'Z') OR (v_ch between 'a' and 'z') then -- 영문자 이라면
v_flag_alphabet := 1;
elsif(v_ch between '0' and '9') then -- 숫자 이라면
v_flag_number := 1;
else -- 특수문자이라면
v_flag_special := 1;
end if;
end loop; -- end of for loop
if(v_flag_alphabet * v_flag_number * v_flag_special = 1) then
insert into tbl_member_test1(userid, passwd, name) values(p_userid, p_passwd, p_name);
else
raise error_insert; -- 사용자가 정의하는 예외절(exception)을 구동시켜라.
end if;
end if;
end if;
exception
when error_dayTime then
raise_application_error(-20003, '>> 영업시간(월~금 14:00 ~ 16:59:59 까지)이 아니므로 입력불가함!! <<');
when error_insert then
raise_application_error(-20002, '>> 암호는 최소 5글자 이상이면서 영문자 및 숫자 및 특수기호가 혼합되어져야 합니다. <<');
end pcd_tbl_member_test1_insert;
-- Procedure PCD_TBL_MEMBER_TEST1_INSERT이(가) 컴파일되었습니다.
exec pcd_tbl_member_test1_insert('eomjh','qwer1234$','엄정화');
/*
오류 보고 -
ORA-20003: >> 영업시간(월~금 14:00 ~ 16:59:59 까지)이 아니므로 입력불가함!! <<
*/
/*
-- 현재시각은 월요일 오후 2시 1분이다 (이거 시간대가 맞아야되므로 테스트할때는
if( to_char(sysdate, 'd') in('1','7') OR
to_number(to_char(sysdate, 'hh24')) < 14 OR
to_number(to_char(sysdate, 'hh24')) > 16 ) then
raise error_dayTime;
이 부분의 시간대 수정
*/
exec pcd_tbl_member_test1_insert('eomjh','qwer1234$','엄정화');
-- PL/SQL 프로시저가 성공적으로 완료되었습니다.
commit;
select *
from tbl_member_test1;
/*
hongkd qwer1234$ 홍길동
eomjh qwer1234$ 엄정화
*/
exec pcd_tbl_member_test1_insert('hongkd','qwer1234$','홍길동');
--> 정상적으로 insert 되어진다.
exec pcd_tbl_member_test1_insert('eomjh','a3$','유관순');
--> 오류메시지 -20002 '암호는 최소 5글자 이상이면서 영문자 및 숫자 및 특수기호가 혼합되어져야 합니다.' 이 뜬다. 그러므로 insert 가 안되어진다.
exec pcd_tbl_member_test1_insert('eomjh','abc1234','유관순');
--> 오류메시지 -20002 '암호는 최소 5글자 이상이면서 영문자 및 숫자 및 특수기호가 혼합되어져야 합니다.' 이 뜬다. 그러므로 insert 가 안되어진다.
개발하는 심심이