ORACLE???????????к?????
?????????С??? ???????[ 2016/10/9 13:46:54 ] ????????????? Oracle
???????????У?????????????к?????????????????????????к?????????
??????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??
??????
???·???
??????????????????
2023/3/23 14:23:39???д?ò??????????
2023/3/22 16:17:39????????????????????Щ??
2022/6/14 16:14:27??????????????????????????
2021/10/18 15:37:44???????????????
2021/9/17 15:19:29???·???????·
2021/9/14 15:42:25?????????????
2021/5/28 17:25:47??????APP??????????
2021/5/8 17:01:11