np.woongsun.com_ODOUNG.sql

-- 회원의 주소를 저장할 수 있다. 한명의 회원 하나의 주소를 가진다(1:1), 총 7개의 추가 필드를 소유한다.
-- 회원의 이메일을 저장할 수 있다. 유일한 값
-- 회원의 이메일이 본인 소유인 것을 인증한다.

--SELECT * FROM TBL_MEMBER;
--
--UPDATE TBL_MEMBER SET AUTH = 0;
--
--COMMIT;
--
--SELECT 
--    '<input type="hidden" name="' ||
--    LOWER(COLUMN_NAME) || 
--    '" id= "' ||
--    LOWER(COLUMN_NAME) || 
--    '">'
--FROM USER_TAB_COLUMNS a
--WHERE TABLE_NAME = 'TBL_MEMBER'
--order by column_id;
--
--
--SELECT 
--    'v_' || COLUMN_NAME || 'TBL_MEMBER.' || COLUMN_NAME || '%TYPE,'
--FROM USER_TAB_COLUMNS a
--WHERE TABLE_NAME = 'TBL_MEMBER'
--order by column_id;
--
--INSERT INTO TBL_MEMBER (
--A,
--B,
--C,
--D
--) 
--    VALUES (
--    1,
--    2,
--    3,
--    4
--);
--
--SELECT 
--    LOWER(COLUMN_NAME) || ',',
--    column_id + 1
--FROM USER_TAB_COLUMNS a
--WHERE TABLE_NAME = 'TBL_MEMBER'
--order by column_id;
--
--SELECT A FROM
--(
--
--SELECT 'INSERT INTO TBL_MEMBER (' AS A, 1 FROM DUAL UNION
--SELECT ') VALUES (' , 100 FROM DUAL UNION
--SELECT ');', 1000 FROM DUAL UNION
--SELECT * FROM (SELECT 
--    UPPER(COLUMN_NAME) || ',',
--    column_id + 1
--FROM USER_TAB_COLUMNS a
--WHERE TABLE_NAME = 'TBL_MEMBER'
--order by column_id) UNION
--SELECT * FROM (SELECT 
--     '? ,',
--    column_id + 100
--FROM USER_TAB_COLUMNS a
--WHERE TABLE_NAME = 'TBL_MEMBER'
--order by column_id)
--ORDER BY 2);
--
----CREATE OR REPLACE PROCEDURE PROC_INSERT_MEMBER(
----    v_ID TBL_MEMBER.ID%TYPE,
----    v_PW TBL_MEMBER.PW%TYPE,
----    v_NAME TBL_MEMBER.NAME%TYPE,
----    v_SI TBL_MEMBER.SI%TYPE,
----    v_SGG TBL_MEMBER.SGG%TYPE,
----    v_EMD TBL_MEMBER.EMD%TYPE,
----    v_ROADADDR TBL_MEMBER.ROADADDR%TYPE,
----    v_ADDRDETAIL TBL_MEMBER.ADDRDETAIL%TYPE,
----    v_ZIPNO TBL_MEMBER.ZIPNO%TYPE,
----    v_ROADFULLADDR TBL_MEMBER.ROADFULLADDR%TYPE,
----    v_JIBUNADDR TBL_MEMBER.JIBUNADDR%TYPE,
----    v_EMAIL TBL_MEMBER.EMAIL%TYPE,
----    v_AUTH TBL_MEMBER.AUTH%TYPE,
----    v_AUTH_TOKEN TBL_MEMBER.AUTH_TOKEN%TYPE
----) IS
----BEGIN
----    INSERT INTO TBL_MEMBER (
----    id, pw, name, si, sgg, emd, roadaddr, addrdetail, zipno, roadfulladdr, jibunaddr, email)
----VALUES (v_id, v_pw, v_name, v_si, v_sgg, v_emd, v_roadaddr, v_addrdetail, v_zipno, v_roadfulladdr, v_jibunaddr, v_email);
----
----END;
----/
----
----INSERT INTO TBL_MEMBER (
----    id, pw, name, si, sgg, emd, roadaddr, addrdetail, zipno, roadfulladdr, jibunaddr, email)
----VALUES (v_id, v_pw, v_name, v_si, v_sgg, v_emd, v_roadaddr, v_addrdetail, v_zipno, v_roadfulladdr, v_jibunaddr, v_email);
--
----INSERT INTO TBL_MEMBER (
----ID,
----PW,
----NAME,
----SI,
----SGG,
----EMD,
----ROADADDR,
----ADDRDETAIL,
----ZIPNO,
----ROADFULLADDR,
----JIBUNADDR,
----EMAIL,
----AUTH,
----AUTH_TOKEN,
----) VALUES (
----
----);
--
--CREATE OR REPLACE PROCEDURE PROC_INSERT_MEMBER(
--    V_ID TBL_MEMBER.ID%TYPE,
--    V_PW TBL_MEMBER.PW%TYPE,
--    V_NAME TBL_MEMBER.NAME%TYPE,
--    V_SI TBL_MEMBER.SI%TYPE,
--    V_SSG TBL_MEMBER.SSG%TYPE,
--    V_EMD TBL_MEMBER.EMD%TYPE,
--    V_ROADADDR TBL_MEMBER.ROADADDR%TYPE,
--    V_ADDRDETAIL TBL_MEMBER.ADDRDETAIL%TYPE,
--    V_ZIPNO TBL_MEMBER.ZIPNO%TYPE,
--    V_ROADFULLADDR TBL_MEMBER.ROADFULLADDR%TYPE,
--    V_JIBUNADDR TBL_MEMBER.JIBUNADDR%TYPE,
--    V_EMAIL TBL_MEMBER.EMAIL%TYPE,
--    V_AUTH TBL_MEMBER.AUTH%TYPE,
--    V_AUTH_TOKEN TBL_MEMBER.AUTH_TOKEN%TYPE
--) IS
--BEGIN
--    INSERT INTO TBL_MEMBER (ID, PW, NAME, SI, SSG, EMD, ROADADDR, ADDRDETAIL, ZIPNO, ROADFULLADDR, JIBUNADDR, EMAIL, AUTH, AUTH_TOKEN) 
--    VALUES (V_ID, V_PW, V_NAME, V_SI, V_SSG, V_EMD, V_ROADADDR, V_ADDRDETAIL, V_ZIPNO, V_ROADFULLADDR, V_JIBUNADDR, V_EMAIL, V_AUTH, V_AUTH_TOKEN);
--    COMMIT;
--END;
--/
--
--INSERT INTO TBL_MEMBER (ID, PW, NAME, SI, SSG, EMD, ROADADDR, ADDRDETAIL, ZIPNO, ROADFULLADDR, JIBUNADDR, EMAIL, AUTH, AUTH_TOKEN) 
--VALUES (V_ID, V_PW, V_NAME, V_SI, V_SSG, V_EMD, V_ROADADDR, V_ADDRDETAIL, V_ZIPNO, V_ROADFULLADDR, V_JIBUNADDR, V_EMAIL, V_AUTH, V_AUTH_TOKEN);

CREATE OR REPLACE PROCEDURE PROC_INSERT_MEMBER(
    V_ID TBL_MEMBER.ID%TYPE,
    V_PW TBL_MEMBER.PW%TYPE,
    V_NAME TBL_MEMBER.NAME%TYPE,
    V_SI TBL_MEMBER.SI%TYPE,
    V_SGG TBL_MEMBER.SGG%TYPE,
    V_EMD TBL_MEMBER.EMD%TYPE,
    V_ROADADDR TBL_MEMBER.ROADADDR%TYPE,
    V_ADDRDETAIL TBL_MEMBER.ADDRDETAIL%TYPE,
    V_ZIPNO TBL_MEMBER.ZIPNO%TYPE,
    V_ROADFULLADDR TBL_MEMBER.ROADFULLADDR%TYPE,
    V_JIBUNADDR TBL_MEMBER.JIBUNADDR%TYPE,
    V_EMAIL TBL_MEMBER.EMAIL%TYPE
) IS
BEGIN
    INSERT INTO TBL_MEMBER (ID, PW, NAME, SI, SGG, EMD, ROADADDR, ADDRDETAIL, ZIPNO, ROADFULLADDR, JIBUNADDR, EMAIL) 
    VALUES (V_ID, V_PW, V_NAME, V_SI, V_SGG, V_EMD, V_ROADADDR, V_ADDRDETAIL, V_ZIPNO, V_ROADFULLADDR, V_JIBUNADDR, V_EMAIL);
    
    COMMIT;
END;
/

INSERT INTO TBL_MEMBER (ID, PW, NAME, SI, SSG, EMD, ROADADDR, ADDRDETAIL, ZIPNO, ROADFULLADDR, JIBUNADDR, EMAIL, AUTH, AUTH_TOKEN) 
VALUES (V_ID, V_PW, V_NAME, V_SI, V_SSG, V_EMD, V_ROADADDR, V_ADDRDETAIL, V_ZIPNO, V_ROADFULLADDR, V_JIBUNADDR, V_EMAIL, V_AUTH, V_AUTH_TOKEN);

SELECT * FROM TBL_MEMBER;