본문 바로가기
ORACLE/PL/SQL

프로시저(Procedure) 실습 - 주민등록번호 검사기 (out 파라미터)

zammanza 2013. 2. 22. 17:36

1. 내가 만든 Ver

--Anonymous Procedure

--주민등록번호 검사기

set serveroutput on

set verify off

 

accept p_preidnum prompt '주민등록번호 앞자리 : '

accept p_nextidnum prompt '주민등록번호 앞자리 : '

 

declare

    --앞자리 입력받은 숫자 저장할 변수

    preidnum varchar2(6);

    --뒷자리 입력받은 숫자 저장할 변수

    nextidnum varchar2(7);

 

    --앞자리 숫자 Table

    type preidnum_table is table of number

    index by binary_integer;

    --뒷자리 숫자 Table

    type nextidnum_table is table of number

    index by binary_integer;

   

    pre_tab    preidnum_table;

    next_tab   nextidnum_table;

   

    --나눗셈에 사용

    pre_multi number :=2;

    next_multi number :=2;

   

    --합계 저장변수

    sumnum number := 0;

  

begin

    --치환변수 사용하여 입력받은 값 저장

    preidnum := &p_preidnum;

    nextidnum := &p_nextidnum;

 

    for cnt in 1..6 loop

        pre_tab(cnt) := to_number(substr(preidnum,cnt,1));

        pre_tab(cnt) := pre_tab(cnt) * pre_multi;

        pre_multi := pre_multi + 1;

        dbms_output.put_line(pre_tab(cnt));

        sumnum := sumnum + pre_tab(cnt);

    end loop;

   

    for cnt in 1..6 loop

        if pre_multi < 10 then

            next_tab(cnt) := to_number(substr(nextidnum,cnt,1));

            next_tab(cnt) := next_tab(cnt) * pre_multi;

                 sumnum := sumnum + next_tab(cnt);

            pre_multi := pre_multi + 1;

            dbms_output.put_line(next_tab(cnt));

         else

            next_tab(cnt) := to_number(substr(nextidnum,cnt,1));

            next_tab(cnt) := next_tab(cnt) * next_multi;

            sumnum := sumnum + next_tab(cnt);

                 next_multi := next_multi + 1;

            dbms_output.put_line(next_tab(cnt));

         end if;

    end loop;   

 

    dbms_output.put_line(sumnum);

    sumnum := mod((11- (mod(sumnum, 11))),10);

    dbms_output.put_line(sumnum);

   

    if sumnum = substr(nextidnum,7,1) then

        dbms_output.put_line('올바른 주민등록번호입니다.');

    else

        dbms_output.put_line('잘못된 주민등록번호입니다.');

    end if;

   

end;

/

set verify on

set serveroutput off

 

 

 

 

 

2. 강사님 Ver

set verify off

set serveroutput on

 

accept p_name prompt '이름을 입력하시오 : '

accept p_jumin1 prompt '주민등록번호(앞자리)를 입력하시오 : '

accept p_jumin2 prompt '주민등록번호(뒤자리)를 입력하시오 : '

 

declare

             v_name varchar2(10) := '&p_name';

             v_jumin1 varchar2(10) := trim('&p_jumin1');

             v_jumin2 varchar2(10) := trim('&p_jumin2');

             v_error number := 1;

             v_tot number := 0;

             v_num01 number := 0;

             v_num02 number := 0;

             v_num03 number := 0;

             v_num04 number := 0;

             v_num05 number := 0;

             v_num06 number := 0;

             v_num07 number := 0;

             v_num08 number := 0;

             v_num09 number := 0;

             v_num10 number := 0;

             v_num11 number := 0;

             v_num12 number := 0;

             v_num13 number := 0;

            

begin

 

             if length(v_name) > 0 then

                           if length(v_jumin1) = 6 and length(v_jumin2) = 7 then

                                        v_num01 := to_number(substr(v_jumin1, 1, 1));

                                        v_num02 := to_number(substr(v_jumin1, 2, 1));

                                        v_num03 := to_number(substr(v_jumin1, 3, 1));

                                        v_num04 := to_number(substr(v_jumin1, 4, 1));

                                        v_num05 := to_number(substr(v_jumin1, 5, 1));

                                        v_num06 := to_number(substr(v_jumin1, 6, 1));

                                       

                                        v_num07 := to_number(substr(v_jumin2, 1, 1));

                                        v_num08 := to_number(substr(v_jumin2, 2, 1));

                                        v_num09 := to_number(substr(v_jumin2, 3, 1));

                                        v_num10 := to_number(substr(v_jumin2, 4, 1));

                                        v_num11 := to_number(substr(v_jumin2, 5, 1));

                                        v_num12 := to_number(substr(v_jumin2, 6, 1));

                                        v_num13 := to_number(substr(v_jumin2, 7, 1));

                                       

                                        v_tot := v_num01*2 + v_num02*3 + v_num03*4 + v_num04*5 + v_num05*6 + v_num06*7;

                                        v_tot := v_tot + v_num07*8 + v_num08*9 + v_num09*2 + v_num10*3 + v_num11*4 + v_num12*5;

                                        v_tot := v_tot mod 11;

                                        v_tot := 11 - v_tot;

                                       

                                        if v_tot = v_num13 then

                                                     v_error := 0;

                                        else

                                                     v_error := 1;

                                        end if;

                           else

                                        v_error := 2;

                           end if;

             else

                           v_error := 3;

             end if;

 

             dbms_output.put_line('-----------------------------------------------');

             dbms_output.put_line('-----------------------------------------------');

 

             if v_error = 0 then

                           dbms_output.put_line('이 름 : ' || v_name);

                           dbms_output.put_line('생년월일 : ' || v_jumin1);

                           dbms_output.put_line('00년 이후출생여부 : ');

                           dbms_output.put_line('검사여부 :  정확합니다.');

             elsif v_error = 1 then

                           dbms_output.put_line('이 름 : ' || v_name);

                           dbms_output.put_line('생년월일 : ' || v_jumin1);

                           dbms_output.put_line('00년 이후출생여부 : ');

                           dbms_output.put_line('검사여부 :  부정확합니다.');

             elsif v_error = 2 then

                           dbms_output.put_line('Error2 : 주민등록번호의 길이가 정확하지 않습니다.');

             elsif v_error = 3 then

                           dbms_output.put_line('Error3 : 이름이 입력되지 않았습니다.');

             else

                           dbms_output.put_line('Error4 : 기타에러 입니다.');

             end if;

 

end;

/

 

set verify on

set serveroutput off

 

 

 

 

 

3. 강사님 Ver (명명 프로시저)

create or replace procedure jumincheck

--주민등록번호 검사기

--명명 프로시져

(

    v_name in varchar2,

    v_jumin1 in varchar2,

    v_jumin2 in varchar2

)

is

--    v_name varchar2(10) := '&p_name';

--    v_jumin1 varchar2(10) := trim('&p_jumin1');

--    v_jumin2 varchar2(10) := trim('&p_jumin2');

    v_error number := 1;

    v_tot number := 0;

    v_num01 number := 0;

    v_num02 number := 0;

    v_num03 number := 0;

    v_num04 number := 0;

    v_num05 number := 0;

    v_num06 number := 0;

    v_num07 number := 0;

    v_num08 number := 0;

    v_num09 number := 0;

    v_num10 number := 0;

    v_num11 number := 0;

    v_num12 number := 0;

    v_num13 number := 0;

   

begin

    DBMS_OUTPUT.ENABLE;

 

    if length(v_name) > 0 then

        if length(v_jumin1) = 6 and length(v_jumin2) = 7 then

            v_num01 := to_number(substr(v_jumin1, 1, 1));

            v_num02 := to_number(substr(v_jumin1, 2, 1));

            v_num03 := to_number(substr(v_jumin1, 3, 1));

            v_num04 := to_number(substr(v_jumin1, 4, 1));

            v_num05 := to_number(substr(v_jumin1, 5, 1));

            v_num06 := to_number(substr(v_jumin1, 6, 1));

           

            v_num07 := to_number(substr(v_jumin2, 1, 1));

            v_num08 := to_number(substr(v_jumin2, 2, 1));

            v_num09 := to_number(substr(v_jumin2, 3, 1));

            v_num10 := to_number(substr(v_jumin2, 4, 1));

            v_num11 := to_number(substr(v_jumin2, 5, 1));

            v_num12 := to_number(substr(v_jumin2, 6, 1));

            v_num13 := to_number(substr(v_jumin2, 7, 1));

           

            v_tot := v_num01*2 + v_num02*3 + v_num03*4 + v_num04*5 + v_num05*6 + v_num06*7;

            v_tot := v_tot + v_num07*8 + v_num08*9 + v_num09*2 + v_num10*3 + v_num11*4 + v_num12*5;

            v_tot := v_tot mod 11;

            v_tot := 11 - v_tot;

            v_tot := v_tot mod 10;

           

            if v_tot = v_num13 then

                v_error := 0;

            else

                v_error := 1;

            end if;

        else

            v_error := 2;

        end if;

    else

        v_error := 3;

    end if;

 

    dbms_output.put_line('-----------------------------------------------');

    dbms_output.put_line('-----------------------------------------------');

 

    if v_error = 0 then

        dbms_output.put_line('이 름 : ' || v_name);

        dbms_output.put_line('생년월일 : ' || v_jumin1);

        dbms_output.put_line('00년 이후출생여부 : ');

        dbms_output.put_line('검사여부 :  정확합니다.');

    elsif v_error = 1 then

        dbms_output.put_line('이 름 : ' || v_name);

        dbms_output.put_line('생년월일 : ' || v_jumin1);

        dbms_output.put_line('00년 이후출생여부 : ');

        dbms_output.put_line('검사여부 :  부정확합니다.');

    elsif v_error = 2 then

        dbms_output.put_line('Error2 : 주민등록번호의 길이가 정확하지 않습니다.');

    elsif v_error = 3 then

        dbms_output.put_line('Error3 : 이름이 입력되지 않았습니다.');

    else

        dbms_output.put_line('Error4 : 기타에러 입니다.');

    end if;

 

end;

 

 

 

 

 

 

4. 강사님 Ver (명명 프로시저, out 파라미터 사용)

create or replace procedure jumincheck2

--주민등록번호 검사기

--명명 프로시져

--out 파라미터(out parameter) 사용

(

    v_name in varchar2,

    v_jumin1 in varchar2,

    v_jumin2 in varchar2,

    v_result out varchar2

)

is

--    v_name varchar2(10) := '&p_name';

--    v_jumin1 varchar2(10) := trim('&p_jumin1');

--    v_jumin2 varchar2(10) := trim('&p_jumin2');

    v_error number := 1;

    v_tot number := 0;

    v_num01 number := 0;

    v_num02 number := 0;

    v_num03 number := 0;

    v_num04 number := 0;

    v_num05 number := 0;

    v_num06 number := 0;

    v_num07 number := 0;

    v_num08 number := 0;

    v_num09 number := 0;

    v_num10 number := 0;

    v_num11 number := 0;

    v_num12 number := 0;

    v_num13 number := 0;

   

begin

    if length(v_name) > 0 then

        if length(v_jumin1) = 6 and length(v_jumin2) = 7 then

            v_num01 := to_number(substr(v_jumin1, 1, 1));

            v_num02 := to_number(substr(v_jumin1, 2, 1));

            v_num03 := to_number(substr(v_jumin1, 3, 1));

            v_num04 := to_number(substr(v_jumin1, 4, 1));

            v_num05 := to_number(substr(v_jumin1, 5, 1));

            v_num06 := to_number(substr(v_jumin1, 6, 1));

           

            v_num07 := to_number(substr(v_jumin2, 1, 1));

            v_num08 := to_number(substr(v_jumin2, 2, 1));

            v_num09 := to_number(substr(v_jumin2, 3, 1));

            v_num10 := to_number(substr(v_jumin2, 4, 1));

            v_num11 := to_number(substr(v_jumin2, 5, 1));

            v_num12 := to_number(substr(v_jumin2, 6, 1));

            v_num13 := to_number(substr(v_jumin2, 7, 1));

           

            v_tot := v_num01*2 + v_num02*3 + v_num03*4 + v_num04*5 + v_num05*6 + v_num06*7;

            v_tot := v_tot + v_num07*8 + v_num08*9 + v_num09*2 + v_num10*3 + v_num11*4 + v_num12*5;

            v_tot := v_tot mod 11;

            v_tot := 11 - v_tot;

            v_tot := v_tot mod 10;

           

            if v_tot = v_num13 then

                v_error := 0;

            else

                v_error := 1;

            end if;

        else

            v_error := 2;

        end if;

    else

        v_error := 3;

    end if;

 

 

    if v_error = 0 then

        v_result := '검사여부 :  정확합니다.';

    elsif v_error = 1 then

        v_result := '검사여부 :  부정확합니다.';

    elsif v_error = 2 then

        v_result := 'Error2 : 주민등록번호의 길이가 정확하지 않습니다.';

    elsif v_error = 3 then

        v_result := 'Error3 : 이름이 입력되지 않았습니다.';

    else

        v_result := 'Error4 : 기타에러 입니다.' ;

    end if;

 

end;

 

 

 

 

실행

 

 

 

 

저작자표시 (새창열림)

'ORACLE > PL/SQL' 카테고리의 다른 글

PL/SQL 커서(cursor) -암시적 커서, 명시적 커서(선언, 비선언, 파라미터)-  (0) 2013.02.22
Pl/SQL 예외처리(Exception) 기본 실습 (미리 정의된 예외, 사용자 정의 예외)  (0) 2013.02.22
명명 프로시저(Procedure) 기본 이론 & 생성 실습  (0) 2013.02.22
PL/SQL 제어문 [반복제어(for loop, loop, while)/조건제어(if)]  (0) 2013.02.22
PL/SQL insert문 사용 실습  (0) 2013.02.22

'ORACLE/PL/SQL' Related Articles

티스토리툴바