???????????У?????????????к?????????????????????????к?????????
??????1??????????????к?????????￡?
--?????????
create table S_AUTOCODE
(
pk1           VARCHAR2(32) primary key??
atype         VARCHAR2(20) not null??
owner         VARCHAR2(10) not null??
initcycle     CHAR(1) not null??
cur_sernum    VARCHAR2(50) not null??
zero_flg      VARCHAR(2) not null??
sequencestyle VARCHAR2(50)??
memo          VARCHAR2(60)
);
-- Add comments to the columns
comment on column S_AUTOCODE.pk1  is '????';
comment on column S_AUTOCODE.atype  is '???к?????';
comment on column S_AUTOCODE.owner  is '???к???????';
comment on column S_AUTOCODE.initcycle  is '???к????';
comment on column S_AUTOCODE.cur_sernum  is '???к?';
comment on column S_AUTOCODE.zero_flg  is '???к????';
comment on column S_AUTOCODE.sequencestyle  is '???к????';
comment on column S_AUTOCODE.memo  is '???';
-- Create/Recreate indexes
create index PK_S_AUTOCODE on S_AUTOCODE (ATYPE?? OWNER);
??????2????????????????磺
????insert into s_autocode (PK1?? ATYPE?? OWNER?? INITCYCLE?? CUR_SERNUM?? ZERO_FLG?? SEQUENCESTYLE?? MEMO)
????values ('0A772AEDFBED4FEEA46442003CE1C6A6'?? 'ZDBCONTCN'?? '012805'?? '1'?? '200000'?? '7'?? '$YEAR$??$ORGAPP$?????$SER$??'?? '????????????');
??????3??????????к??????????
??????????????SF_SYS_GEN_AUTOCODE
CREATE OR REPLACE FUNCTION SF_SYS_GEN_AUTOCODE(
I_ATYPE IN VARCHAR2??  /*???????*/
I_OWNER IN VARCHAR2   /*??????????*/
) RETURN VARCHAR2 IS
/**************************************************************************************************/
/* PROCEDURE NAME : SF_SYS_GEN_AUTOCODE                                                           */
/* DEVELOPED BY   : WANGXF                                                                        */
/* DESCRIPTION    : ????????????????????к?                                                    */
/* DEVELOPED DATE : 2016-10-08                                                                    */
/* CHECKED BY     :                                                                               */
/* LOAD METHOD    : F1-DELETE INSERT                                                              */
/**************************************************************************************************/
O_AUTOCODE VARCHAR2(100);                        /*????????к?*/
V_INITCYCLE S_AUTOCODE.INITCYCLE%TYPE;       /*???к????*/
V_CUR_SERNUM S_AUTOCODE.CUR_SERNUM%TYPE;     /*???к?*/
V_ZERO_FLAG S_AUTOCODE.ZERO_FLG%TYPE;        /*???к????*/
V_SEQUENCESTYLE S_AUTOCODE.SEQUENCESTYLE%TYPE;/*???к????*/
V_SEQ_NUM VARCHAR2(100);                     /*???????к?*/
V_DATE_YEAR CHAR(4);                         /*??????2016*/
V_DATE_YEAR_MONTH CHAR(6);                   /*????·????201610*/
V_DATE_DATE CHAR(8);                         /*????·??????20161008*/
V_DATE_DATE_ALL CHAR(14);                    /*??????????У???20161008155732*/
/*
??????????У?
$YEAR$ --> ???
$YEAR_MONTH$ --> ???+?·??????????
$DATE$ --> ???+?·?+?????????????
$DATE_ALL$ --> ?????????????????
$ORGAPP$ --> ??????
$SER$ --> ??????к?
*/
--????????????????DML??????
Pragma Autonomous_Transaction;
BEGIN
-- ????????????????к?????
SELECT T.INITCYCLE??
T.CUR_SERNUM??
T.ZERO_FLG??
T.SEQUENCESTYLE
INTO V_INITCYCLE??V_CUR_SERNUM??V_ZERO_FLAG??V_SEQUENCESTYLE
FROM S_AUTOCODE T WHERE T.ATYPE=I_ATYPE AND T.OWNER=I_OWNER ;
--????????????
SELECT
TO_CHAR(SYSDATE??'yyyy')??
TO_CHAR(SYSDATE??'yyyyMM')??
TO_CHAR(SYSDATE??'yyyyMMdd')??
TO_CHAR(SYSDATE??'yyyyMMddHH24MISS')
INTO  V_DATE_YEAR??V_DATE_YEAR_MONTH??V_DATE_DATE??V_DATE_DATE_ALL
FROM DUAL;
-- ???????
O_AUTOCODE := REPLACE(V_SEQUENCESTYLE??'$YEAR$'??V_DATE_YEAR);
O_AUTOCODE := REPLACE(O_AUTOCODE??'$YEAR_MONTH$'??V_DATE_YEAR_MONTH);
O_AUTOCODE := REPLACE(O_AUTOCODE??'$DATE$'??V_DATE_DATE);
O_AUTOCODE := REPLACE(O_AUTOCODE??'$DATE_ALL$'??V_DATE_DATE_ALL);
--?????????
O_AUTOCODE := REPLACE(O_AUTOCODE??'$ORGAPP$'??I_OWNER);
--??????
V_SEQ_NUM := TO_CHAR(TO_NUMBER(V_CUR_SERNUM)+TO_NUMBER(V_INITCYCLE));
--??д??????к???????ζ??????
UPDATE S_AUTOCODE T SET T.CUR_SERNUM=V_SEQ_NUM WHERE T.ATYPE=I_ATYPE AND T.OWNER=I_OWNER ;
--???????????油0
IF LENGTH(V_SEQ_NUM) < TO_NUMBER(V_ZERO_FLAG)
THEN
/*
LOOP
V_SEQ_NUM := '0'||V_SEQ_NUM;
EXIT WHEN LENGTH(V_SEQ_NUM) = TO_NUMBER(V_ZERO_FLAG);
END LOOP;
*/
V_SEQ_NUM := LPAD(V_SEQ_NUM??TO_NUMBER(V_ZERO_FLAG)??'0');
END IF;
O_AUTOCODE := REPLACE(O_AUTOCODE??'$SER$'??V_SEQ_NUM);
COMMIT;
RETURN O_AUTOCODE;
EXCEPTION
--?????ж??????????????ERROR?
WHEN NO_DATA_FOUND THEN
ROLLBACK;
DBMS_OUTPUT.put_line('there is no config as you need...');
RETURN 'ERROR';
END SF_SYS_GEN_AUTOCODE;
??????4???????
??????????$YEAR$??$ORGAPP$?????$SER$??
????SELECT SF_SYS_GEN_AUTOCODE('ZDBCONTCN'??'012805') FROM DUAL;
??????5?? ???
????2016??012805?????0200001??