oracle 궁금했던거

테이블에 값을 insert 할 때 

 

다른 테이블의 한 컬럼의 값을 update 할 수 있을까?

 

/* 유저 테이블 추가 */ 
CREATE TABLE USER_TABLE 
( 
NUM NUMBER(4) NOT NULL, 
USER_ID VARCHAR2(20) NOT NULL, 
USER_PW VARCHAR2(20) NOT NULL, 
USER_STATUS VARCHAR2(4) DEFAULT 'Y' NOT NULL, 
SINGUP_DATE DATE DEFAULT SYSDATE NOT NULL, 
CONSTRAINT USER_TABLE PRIMARY KEY(NUM) 
); 

COMMENT ON TABLE USER_TABLE IS '회원정보'; 
COMMENT ON COLUMN USER_TABLE.NUM IS '회원번호'; 
COMMENT ON COLUMN USER_TABLE.USER_ID IS '회원ID'; 
COMMENT ON COLUMN USER_TABLE.USER_PW IS '회원PW'; 
COMMENT ON COLUMN USER_TABLE.USER_STATUS IS '회원상태'; 
COMMENT ON COLUMN USER_TABLE.SINGUP_DATE IS '가입날짜'; 

INSERT INTO USER_TABLE ( NUM, USER_ID, USER_PW ) VALUES ( 1, 'user_1', '비밀이지롱!' ); 
INSERT INTO USER_TABLE ( NUM, USER_ID, USER_PW ) VALUES ( 2, 'user_2', '비밀이지롱!!' ); 
INSERT INTO USER_TABLE ( NUM, USER_ID, USER_PW ) VALUES ( 3, 'user_3', '비밀이지롱!!!' ); 
INSERT INTO USER_TABLE ( NUM, USER_ID, USER_PW ) VALUES ( 4, 'user_4', '비밀이지롱!!!!' ); 
INSERT INTO USER_TABLE ( NUM, USER_ID, USER_PW ) VALUES ( 5, 'user_5', '비밀이지롱!!!!!' ); 

SELECT * FROM USER_TABLE; 


/* 밴 테이블 추가 */ 
CREATE TABLE USER_BAN_TABLE 
( 
NUM NUMBER(4) NOT NULL, 
USER_ID VARCHAR2(20) NOT NULL, 
BAN_TITLE VARCHAR2(20) NOT NULL, 
MASTER_ID VARCHAR2(20) NOT NULL, 
USER_STATUS VARCHAR2(4) DEFAULT 'Y' NOT NULL, 
BAN_DATE DATE DEFAULT SYSDATE NOT NULL, 
CONSTRAINT USER_BAN_TABLE PRIMARY KEY(NUM) 
); 

COMMENT ON TABLE USER_BAN_TABLE IS '밴정보'; 
COMMENT ON COLUMN USER_BAN_TABLE.NUM IS '밴번호'; 
COMMENT ON COLUMN USER_BAN_TABLE.USER_ID IS '밴당한사람'; 
COMMENT ON COLUMN USER_BAN_TABLE.BAN_TITLE IS '밴한이유'; 
COMMENT ON COLUMN USER_BAN_TABLE.MASTER_ID IS '밴한사람'; 
COMMENT ON COLUMN USER_BAN_TABLE.USER_STATUS IS '회원상태'; 
COMMENT ON COLUMN USER_BAN_TABLE.BAN_DATE IS '밴한날짜'; 

SELECT * FROM USER_BAN_TABLE; 

/* 여기서 궁금한점 */ 
/* 밴 목록에 회원을 밴할 때 */ 
/* 회원 테이블의 유저 상태도 변하게 */ 
/* 할 수 있을까 ?!?!?!? */ 

INSERT INTO USER_TABLE ( NUM, USER_ID, BAN_TITLE, MASTER_ID, USER_STATUS ) VALUES ( 1, 'user_1', '넌 밴이야', 'admin', 'Y' ); 

한번 해보자..

 

 

------ 2020 - 03 - 22

 

mapper단에서 insert랑 update 두개를 만든다음에

 

controller단에서 api를 호출할때 두개 동시에 호출했다.