[펌] Oracle CURD Package Sample

출처 : http://blog.beany.co.kr/archives/485

예전에 테스트로 만들어 본 Oracle CURD Package Sample

• Package 정의

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

CREATE OR REPLACE PACKAGE SP_CMM_DEVL_0170 AS TYPE REF_TYPE IS REF CURSOR;   -- 다중 반환 PROCEDURE MULTI_RETURN (                 p_search_str IN  VARCHAR2                ,p_return_1   OUT REF_TYPE                ,p_return_2   OUT VARCHAR2               );   -- 목록 PROCEDURE LST (                 p_search_str IN  VARCHAR2                ,p_return     OUT REF_TYPE               );   -- 입력 PROCEDURE INS (                 p_word_kor  IN VARCHAR2                ,p_abbr_nm   IN VARCHAR2                ,p_word_eng  IN VARCHAR2                ,p_word_desc IN VARCHAR2                ,p_reg_id    IN VARCHAR2                ,p_reg_ip    IN VARCHAR2                ,p_reg_dt    IN VARCHAR2               );   -- 수정 PROCEDURE UPD (                 p_seq       IN NUMBER                ,p_word_kor  IN VARCHAR2                ,p_abbr_nm   IN VARCHAR2                ,p_word_eng  IN VARCHAR2                ,p_word_desc IN VARCHAR2                ,p_upd_id    IN VARCHAR2                ,p_upd_ip    IN VARCHAR2                ,p_upd_dt    IN VARCHAR2               );   -- 삭제 PROCEDURE DEL (                 p_seq IN NUMBER               );   END SP_CMM_DEVL_0170; /

• Package Body 구현

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143

CREATE OR REPLACE PACKAGE BODY SP_CMM_DEVL_0170 IS   -- 다중 반환 PROCEDURE MULTI_RETURN (                 p_search_str IN  VARCHAR2                ,p_return_1   OUT REF_TYPE                ,p_return_2   OUT VARCHAR2 ) IS     v_query   VARCHAR2(1000);     v_message VARCHAR2(2000); BEGIN           v_query := 'SELECT  SEQ                        ,WORD_KOR                        ,ABBR_NM                        ,WORD_ENG                        ,WORD_DESC                        ,REG_ID                        ,REG_IP                        ,REG_DT                        ,UPD_ID                        ,UPD_IP                        ,UPD_DT                 FROM   SAMPLE_DIC                WHERE  1 = 1';       IF (LENGTH(p_search_str) > 0) THEN         v_query := v_query || ' AND WORD_KOR LIKE ''' || p_search_str || '%''';     END IF;           v_query := v_query || ' ORDER BY SEQ';           OPEN p_return_1 FOR v_query;           v_message  := 'Procedure 반환값!!';     p_return_2 := v_message; END MULTI_RETURN;   -- 목록 PROCEDURE LST (                 p_search_str IN  VARCHAR2                ,p_return     OUT REF_TYPE ) IS     v_query VARCHAR2(1000); BEGIN           v_query := 'SELECT  SEQ                        ,WORD_KOR                        ,ABBR_NM                        ,WORD_ENG                        ,WORD_DESC                        ,REG_ID                        ,REG_IP                        ,REG_DT                        ,UPD_ID                        ,UPD_IP                        ,UPD_DT                 FROM   SAMPLE_DIC                WHERE  1 = 1';       IF (LENGTH(p_search_str) > 0) THEN         v_query := v_query || ' AND WORD_KOR LIKE ''' || p_search_str || '%''';     END IF;           v_query := v_query || ' ORDER BY SEQ';           OPEN p_return FOR v_query; END LST;   -- 입력 PROCEDURE INS (                 p_word_kor  IN VARCHAR2                ,p_abbr_nm   IN VARCHAR2                ,p_word_eng  IN VARCHAR2                ,p_word_desc IN VARCHAR2                ,p_reg_id    IN VARCHAR2                ,p_reg_ip    IN VARCHAR2                ,p_reg_dt    IN VARCHAR2 ) IS BEGIN     INSERT INTO SAMPLE_DIC (                              SEQ                             ,WORD_KOR                             ,ABBR_NM                             ,WORD_ENG                             ,WORD_DESC                             ,REG_ID                             ,REG_IP                             ,REG_DT                            )     VALUES (              SAMPLE_DIC_SEQ.NEXTVAL             ,p_word_kor             ,p_abbr_nm             ,p_word_eng             ,p_word_desc             ,p_reg_id             ,p_reg_ip             ,p_reg_dt            ); END INS;   -- 수정 PROCEDURE UPD (                 p_seq       IN NUMBER                ,p_word_kor  IN VARCHAR2                ,p_abbr_nm   IN VARCHAR2                ,p_word_eng  IN VARCHAR2                ,p_word_desc IN VARCHAR2                ,p_upd_id    IN VARCHAR2                ,p_upd_ip    IN VARCHAR2                ,p_upd_dt    IN VARCHAR2 ) IS BEGIN     UPDATE  SAMPLE_DIC     SET     WORD_KOR  = p_word_kor            ,ABBR_NM   = p_abbr_nm            ,WORD_ENG  = p_word_eng            ,WORD_DESC = p_word_desc            ,UPD_ID    = p_upd_id            ,UPD_IP    = p_upd_ip            ,UPD_DT    = p_upd_dt     WHERE  SEQ        = p_seq; END UPD;   -- 삭제 PROCEDURE DEL (                 p_seq IN NUMBER ) IS BEGIN     DELETE     FROM SAMPLE_DIC     WHERE SEQ = p_seq; END DEL;   END SP_CMM_DEVL_0170; /