문자포함 자동채번 쿼리

CREATE TABLE ORDERMASTER

(

ORDERNO VARCHAR2 (30) NOT NULL,

MODELCD NUMBER NOT NULL,

ORDERQTY NUMBER NOT NULL,

ORDERDT DATE,

CUSTOMERID VARCHAR2 (30)

)

​

ALTER TABLE SCOTT.ORDERMASTER ADD(

CONSTRAINT PK_ORDERMASTER PRIMARY KEY (ORDERNO));

 

insert into OrderMaster (orderno,MODELCD,orderqty,orderdt) values('20200626K001',1,1,'20200626');

insert into OrderMaster (orderno,MODELCD,orderqty,orderdt) values('20200627K001',1,1,'20200627');

insert into OrderMaster (orderno,MODELCD,orderqty,orderdt) values('202006260001',1,1,'20200626');

insert into OrderMaster (orderno,MODELCD,orderqty,orderdt) values('202006260002',1,1,'20200626');

insert into OrderMaster (orderno,MODELCD,orderqty,orderdt) values('202006270001',1,1,'20200627');

​

SELECT *

FROM OrderMaster;

​

DELETE FROM ORDERMASTER;

​

--20200627K001

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') || 'K' || LPAD(NVL(MAX(SUBSTR(ORDERNO, 10)),0)+1,3,'00')

FROM ORDERMASTER

WHERE SUBSTR(ORDERNO, 0,8) = TO_CHAR(SYSDATE, 'YYYYMMDD');

​

--20200626 0001 성공

SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD') || NVL(TO_CHAR(MAX(SUBSTR(ORDERNO,9))), '0000'))+1

FROM ORDERMASTER

WHERE SUBSTR(ORDERNO, 0,8) = TO_CHAR(SYSDATE, 'YYYYMMDD');

​

--20200626 0001 성공

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') || LPAD(NVL(MAX(SUBSTR(ORDERNO, 10)),0)+1,4,'000')

FROM ORDERMASTER

WHERE SUBSTR(ORDERNO, 0,8) = TO_CHAR(SYSDATE, 'YYYYMMDD');