[Oracle] days17 -저장함수

저장프로시저 예제연습

 

회원가입 아이디 중복체크 -- 0사용가능 1사용불가능 

create or replace procedure up_idCheck
(
    pempno in emp.empno%type --id
    , pempnoCheck out number --사용가능하면 0 불가능 1  출력하는 매개변수
) 
is 
begin
    select count(*) into pempnoCheck
    from emp 
    where empno=empno;
end;

--위 프로시저 체크 
declare 
vempnoCheck number;
begin
    up_idCheck(12323,vempnoCheck);
    dbms_output.put_line(vempnoCheck);
end;

declare 
vempnoCheck number;
begin
     up_idcheck(9999, vempnoCheck);
    if(vempnoCheck=1) then 
    dbms_output.put_line('사용중');
    else 
    dbms_output.put_line('사용가능');
    end if;
end;

 

 

회원가입 한후에 id/pwd입력하고 로그인 (인증)
로그인 성공, 실패 (id x , pwd x)

create or replace procedure up_logon
(
    pempno in emp.empno%type --id
    , pename in emp.ename%type --비밀번호 대신에 사용 
    , plogonCheck out number --로그인가능하면 0 1(id 존재x) -1 (id존재하지만 비번 틀림) 
) 
is 
vename emp.ename%type;
begin
    select count(*) into  plogonCheck --id 존재하지 않으면 0
    from emp 
    where empno=empno;
    --id가 존재하면, 비밀번호가 맞는지 체크  
    if plogonCheck = 1 then 
        select ename into vename
        from emp
        where empno=pempno;
         
        --id존재, 비빌번호 일치 
        if vename=pename then
       plogonCheck := 0; --로그인성공 
        else  --id 존재, 비밀번호x 
        plogonCheck:= -1; --로그인 실패 
        end if;
else 
    plogonCheck := 1;
    end if; 
end;


declare 
vlogonCheck number;
begin
     up_logon(1111,  'kenik', vlogonCheck); --1이 나오도록 처리 
end;

 

 

저장함수

 

ssn주민등록번호를 파라미터로 넘겨주면 남자/여자로 반환하는 저장함수 
저장함수= 저장 프로시저 같지만 차이점: 저장함수는 리턴값이 있다. 

 

 

형식

    create [or replace] function [함수명]
      (argument1 [mode] date_type1,
       argument2 [mode] date_type2,
        .........
       return data_type;
      IS [AS]
      BEGIN
        ..........
        return(변수);
      EXCEPTION
        .........
      END;


삭제 
       DROP function [함수명];

--강사님이 적으신 저장함수 형식 
create or replace function up_logon
()
return 리턴자료형 
is 
begin 
return (리턴값);
return 리턴값; 
end;

 

주민등록번호를 받아서 성별을 반환하는 함수 

create or replace function uf_Gender
(
    prrn varchar2
)
return varchar2
is 
    vgender varchar2(6) := '여자'; --남,여를 변수
begin
    if mod(substr(prrn,8,1),2)=1 then
        vgender := '남자';
    end if;
    return vgender;
--exception
end;

select num,name,ssn,uf_gender(ssn) gender 
from insa;

uf_sum(10) 1~10까지 합을 반환 

 

create or replace function uf_sum
(   pn number
 )
return number
is
vsum number := 0;
vstart number := 1;
vmax number := pn; 
begin
    if pn< 1 then 
    vmax := 1;  
   
    end if; 
    for i in vstart..vmax
    loop
        vsum := vsum+i;
    end loop;
    return vsum;
end;

select uf_sum(10)
from dual;

create or replace function uf_sum2
(   pn number
 )
return number
is
vsum number := 0;
begin
    for i   in  reverse 1..pn
    loop
        vsum := vsum+i;
    end loop;
    return vsum;
end;

select uf_sum2(10)
from dual;

 

 

주민등록번호를 입력받아서 생년월일(yyyy.mm.dd) 반환하는 함수 uf_birth

주민등록번호를 입력받아서 만나이 반환하는 함수 

create or replace function uf_birth 
(  prrn varchar2 --주민드옭번호 입력받을 변수 
)
return varchar2
is
--바로 yyyy.mm.dd 형식으로 가져올 수 없어서 변수 선언 
    vbirth varchar2(20);
    vgender number(1);
    vcentry varchar2(20); 
    vrrn6 varchar2(6);
begin 
--바로 yyyy.mm.dd 형식으로 가져올 수 없어서 변수 선언 
--1800.1900.2000 다르기때문에 성별을 가져와야 한다. 
   vrrn6 := substr(prrn,0,6);
   vgender := substr(prrn, -7,1);
  
  vcentry := case 
        when vgender in (1,2,5,6) then 19
        when vgender in (3,4,7,8) then 20
        else 18
   end;
  vbirth := to_char(to_date(concat(vcentry, vrrn6)), 'YYYY.MM.dd DAY');
return vbirth ;
end;

select name, ssn,uf_birth(ssn)
from insa;

 

만나이 계산

CREATE OR REPLACE FUNCTION uf_age
(
  prrn VARCHAR2
)
RETURN NUMBER 
IS
   vischeck number(1);
   vt_year  number(4);
   vb_year  number(4);
   vage     number(3);
BEGIN
   vischeck :=  SIGN(  TRUNC( SYSDATE ) -  TO_DATE(  SUBSTR( prrn, 3,4), 'MMDD') );
   vt_year  := TO_CHAR( SYSDATE  , 'YYYY');
   vb_year  := CASE  
                 WHEN SUBSTR( prrn, 8, 1 ) IN (1,2,5,6) THEN '1900' + SUBSTR( prrn, 1,2)
                 WHEN SUBSTR( prrn, 8, 1 ) IN (3,4,7,8) THEN '2000' + SUBSTR( prrn, 1,2)
                 ELSE                                       '1800'  + SUBSTR( prrn, 1,2)
             END;
   vage     :=  CASE  VISCHECK
                WHEN -1 THEN  -- 생일 안지난것
                 vt_year - vb_year-1
                ELSE   -- 0, 1
                 vt_year - vb_year
            END  ;        
   RETURN vage;         
--EXCEPTION
END;
-- Function UF_AGE이(가) 컴파일되었습니다.
--
SELECT name, ssn, UF_AGE(ssn) age
FROM insa;

저장 프로시저 mod: in, out, in out 입출력용 파라미터 (매개변수) 
전화번호 8765-8652  앞자리 8765만 출력

create or replace procedure up_tel
(   --입출력용
    pphone in out varchar2
)
is
begin
    --입력을 받아와서 다시 담으면 출력이 됨 
    pphone := substr(pphone,0,4); 
--exception
end;

--테스트 
DECLARE
  vphone VARCHAR2(9) := '8765-3821';
BEGIN
    up_tel(vphone);
    dbms_output.put_line(vphone);
END;