[Oracle] 기본 CRUD 구문

자주사용하는데도 매번 스펠링이 틀릴까봐 걱정되는 CRUD 구문 정리

- C (Create)

CREATE TABLE TB_MEMBER (
  USERNO      VARCHAR2(20)    NOT NULL
  , USERID    VARCHAR2(20)    NOT NULL
  , USERPW    VARCHAR2(200)   NOT NULL
  , NAME      VARCHAR2(20)
  , PHONE     VARCHAR2(13)
  , TYPE      VARCHAR2(5)
  , CREATED   DATE            DEFAULT SYSDATE
  , UPDATED   DATE            DEFAULT SYSDATE
)

-- 기본. 모든 컬럼 값을 순서대로 넣기
INSERT INTO TB_MEMBER 
  VALUES( PKG.GET_SEQ, 'hjgong', PKG.ENCRYPT('Password1234'), 'Hailey', '010-0000-0000', 'A', SYSDATE, SYSDATE )
;
-- 필요한 컬럼의 값만 넣기
INSERT INTO TB_MEMBER ( USERNO, USERID, USERPW, NAME)
  VALUES( PKG.GET_SEQ, 'hjgong', PKG.ENCRYPT('Password1234'), 'Hailey' )
;
-- 다른 테이블의 데이터를 넣기
INSERT INTO TB_MEMBER ( USERNO, USERID, USERPW, NAME)
  SELECT PKG.GET_SEQ USERNO , ID, PASSWORD, USER FROM TB_OTHER_MEMBER WHERE LAST_UPDATE > SYSDATE-7
;

 

- R (Read)

SELECT * FROM TB_MEMBER WHERE TYPE='A' ORDER BY USERNO;

 

- U (Update)

UPDATE TB_MEMBER M SET M.PHONE='010-0000-0000', M.UPDATED = SYSDATE WHERE M.USERNO = ? ;

 

- D (Delete)

DELETE FROM TB_MEMBER WHERE USERNO = ? ;