오라클 패키지

//*****************************************
-- 패키지
  -- 타입, 프로그램객체, 프로시져, 함수등을 논리적으로 묶어 놓은것

-- 작성 방법
   1. 패키지 명세
     CREATE OR REPLACE PACKAGE 패키지명 IS
 FUNCTION 함수명([인수]) RETURN 리턴타입;
 PROCEDURE 프로시져명([인수]);
    :
     END 패키지명;
     /

   2. 패키지 몸체 구현
     CREATE OR REPLACE PACKAGE BODY 패키지명 IS
 FUNCTION 함수명([인수]) RETURN 리턴타입
 IS
    [변수선언]
 BEGIN
    함수몸체구현
    RETURN 리턴값;
 END;

 PROCEDURE list_insa([인수])
 IS
    [변수선언]
 BEGIN
    프로시져몸체구현
 END;
    :
    END 패키지명;
    /

-----------------------------------------------------
-- 1. 패키지 명세
CREATE OR REPLACE PACKAGE pack_insa IS
 FUNCTION fngender(ssn IN VARCHAR2)
                        RETURN VARCHAR2;
 PROCEDURE list_insa(pNum IN NUMBER);
END pack_insa;
/

-- 2. 패키지 몸체 구현
CREATE OR REPLACE PACKAGE BODY pack_insa IS
 FUNCTION fngender(ssn IN VARCHAR2)
                        RETURN VARCHAR2
 IS
  s VARCHAR2(10) := '여자';
 BEGIN
  IF SUBSTR(ssn, 8, 1) IN ('1', '3') THEN
      s := '남자'; 
  END IF;
  RETURN s;
 END;

 PROCEDURE list_insa(pNum IN NUMBER)
 IS
  vName VARCHAR2(20);
  vSsn    VARCHAR2(20);
  vGender VARCHAR2(10);
 BEGIN
  SELECT name, ssn, fngender(ssn) gender INTO vName, vSsn, vGender FROM insa WHERE num=pNum;
  DBMS_OUTPUT.PUT_LINE(vName || '  ' || vSsn || '  ' || vGender);
 END;
END pack_insa;
/

EXEC pack_insa.list_insa(1001);

-- 오버로딩
CREATE OR REPLACE PACKAGE pack_insa IS
 FUNCTION fngender(ssn IN VARCHAR2)
                        RETURN VARCHAR2;
 PROCEDURE list_insa(pNum IN NUMBER);
 PROCEDURE list_insa(pName IN VARCHAR2);
END pack_insa;
/

CREATE OR REPLACE PACKAGE BODY pack_insa IS
 FUNCTION fngender(ssn IN VARCHAR2)
                        RETURN VARCHAR2
 IS
  s VARCHAR2(10) := '여자';
 BEGIN
  IF SUBSTR(ssn, 8, 1) IN ('1', '3') THEN
   s := '남자'; 
  END IF;
  RETURN s;
 END;

 PROCEDURE list_insa(pNum IN NUMBER)
 IS
  vName VARCHAR2(20);
  vSsn VARCHAR2(20);
  vGender VARCHAR2(10);
 BEGIN
  SELECT name, ssn, fngender(ssn) gender INTO vName, vSsn, vGender FROM insa WHERE num=pNum;
  DBMS_OUTPUT.PUT_LINE(vName || '  ' || vSsn || '  ' || vGender);
 END;

 PROCEDURE list_insa(pName IN VARCHAR2)
 IS
     CURSOR cur_insa IS
         SELECT name, ssn, fngender(ssn) gender  FROM insa WHERE name LIKE '%' || pName || '%';
 BEGIN
     FOR rec IN cur_insa LOOP
       DBMS_OUTPUT.PUT_LINE(rec.name || '  ' || rec.ssn || '  ' || rec.gender);
     END LOOP;
 END;
END pack_insa;
/

EXEC pack_insa.list_insa(1001);
EXEC pack_insa.list_insa('김');