编号生成器Ejb3.0

/*
功能:编号生成器设计初探
环境:JDEVELOPER+STRUTS+EJB+ORACLE
作者:chinayaosir
博客:http://blog.csdn.net/chinayaosir
*/
/****************************************************************************/
/*1.ORACLE函数清单*/
TO_CHAR(sysdate,'YY-MM-DD') //DATE   TO STRING
TO_CHAR(1234,'9999')        //NUMBER TO STRING   
TO_NUMBER(STRING)           //STRING TO NUMBER
SUBSTR(STRING,BEGIN,COUNT)  //GET SUB STRING IN STRING
LENGTH(String)            //GET LENGTH OF STRING
/****************************************************************************/
/*2.ORACLE函数清单应用例子*/
select TO_CHAR(sysdate,'YYYY-MM-DD') as today,
substr(TO_CHAR(sysdate,'YYYY-MM-DD'),1,4) AS year,
LENGTH(TO_CHAR(sysdate,'YYYY-MM-DD')) as length,
TO_CHAR(123456789,'999999999') as numberno
from DUAL;

/****************************************************************************/
/*3.表数据定义语句DDL*/
/*I_GET_ITEMCODE    ITEMCODE算号器*/
drop table bab.I_GET_ITEMCODE;
create table bab.I_GET_ITEMCODE
(
ITEMCODE    VARCHAR2(10)    not null,
ITEMNUMBER      VARCHAR2(20)    not null,
CREATE_DATE    DATE        null,
FLAG        VARCHAR2(1)    null
)tablespace MTYBAB;
/*添加主键约束*/
alter table bab.I_GET_ITEMCODE
add constraint I_GET_ITEMCODE_PK primary key(ITEMCODE);   
/*添加唯一约束*/
alter table bab.I_GET_ITEMCODE
Add   constraint  I_GET_ITEMCODE_UK  unique(ITEMNUMBER);
/****************************************************************************/
/*4.定义3个函数*/
4.1 F_GET_ITEMNUMBER()功能是输入itemcode和flag,  返回itemnumber
4.2 F_GET_ITEMCODE()功能是输入itemnumber和flag,返回itemcode
当flag为'F',在I_GET_ITEMCODE表只查询数据而不插入数据!
当flag为'T',在I_GET_ITEMCODE表查询数据不存在时插入数据!调用I_NEW_ITEMCODE()完成!
4.3 F_NEW_ITEMCODE()功能是ITEMCODE编号产生器
/***********************************************/
/*4.1.1 函数F_GET_ITEMNUMBER()代码*/
CREATE OR REPLACE function F_GET_ITEMNUMBER
(itemcode1 in varchar2,itemflag in varchar2)
RETURN varchar2
IS
    itemnumber1 varchar2(20);
BEGIN
    itemnumber1 := 'T';
    select ITEMNUMBER into itemnumber1
    from BAB.I_GET_ITEMCODE
    where ITEMCODE = itemcode1 and FLAG = itemflag;
    return itemnumber1;

END;
/*4.1.2 函数F_GET_ITEMNUMBER()测试*/
SELECT F_GET_ITEMNUMBER(ITEMCODE,'T'),ITEMCODE FROM I_GET_ITEMCODE;

/***********************************************/
/*4.2.1 函数F_GET_ITEMCODE()代码*/
CREATE OR REPLACE function F_GET_ITEMCODE
(itemnumber1 in varchar2,itemflag in varchar2)
return varchar2
IS
    itemcode1 varchar2(10);
    findrow number(5,0);
BEGIN
    itemcode1:='';   
    findrow:=0;   
    select count(*)  into findrow  from BAB.I_GET_ITEMCODE where ITEMNUMBER = itemnumber1;
    if findrow > 0  then
        select ITEMCODE into itemcode1  from BAB.I_GET_ITEMCODE where ITEMNUMBER = itemnumber1;
    end if;
    if itemflag = 'T'  then
       itemcode1 :=F_NEW_ITEMCODE(itemnumber1,'T');
    end if;
    return itemcode1;
END;

/*4.2.2 函数F_GET_ITEMCODE()测试PL/SQL环境*/
SELECT F_GET_ITEMCODE(ITEMNUMBER,'F') AS ITEMCODE,ITEMNUMBER FROM I_GET_ITEMCODE;
/

DECLARE
ITEMCODE varchar2(10);
BEGIN
ITEMCODE:=F_GET_ITEMCODE('BX80315GB','T');
DBMS_OUTPUT.PUT_LINE('ITEMCODE= ' ||ITEMCODE);
END;
/
/***********************************************/
/*4.3.1 函数F_NEW_ITEMCODE()代码*/
CREATE OR REPLACE function F_NEW_ITEMCODE
(itemnumber1 in varchar2,itemflag in varchar2)
RETURN varchar2
IS
    findrow number(5,0);
        firstchar varchar2(1);
    ctemp1 varchar2(2);
    ctemp2 varchar2(2);
    ctemp3 varchar2(5);
    itemp3 number(5,0);    
    ctempno varchar2(10);
BEGIN
    ctempno :='T';
    itemp3    :=1 ;
    findrow :=0;
    select upper(substr(value,1,1))  into firstchar
    from   emg_systemtable  
    where parameter='ITEMBASIC_FIRST_CHAR';
       
    ctemp1:=substr(TO_CHAR(sysdate,'YY-MM-DD'),1,2);
    ctemp2:=substr(TO_CHAR(sysdate,'YY-MM-DD'),4,2);
   
    LOCK TABLE  bab.I_GET_ITEMCODE IN SHARE MODE;
    select max(ITEMCODE)   into ctempno
    from BAB.I_GET_ITEMCODE
    where  (substr(ITEMCODE,2,2)=ctemp1) and   (substr(ITEMCODE,4,2)=ctemp2) ;
       
    if ctempno != 'T' then 
             begin  
                    itemp3    := TO_NUMBER(substr(ctempno,6,5)) ;
            itemp3  := itemp3 + 1.0;
            end;
        end if;       

    ctemp3 :=ltrim(to_char(itemp3,'99999'));
    ctemp3 :=LPAD(ctemp3,5,'0');              
     ctempno := firstchar||ctemp1||ctemp2||ctemp3;

    select count(*)    into findrow
    from BAB.I_GET_ITEMCODE
    where  itemnumber=itemnumber1;
      if findrow =0 then
    begin

        insert into I_GET_ITEMCODE(ITEMCODE,ITEMNUMBER,CREATE_DATE,FLAG) 
            values(ctempno,itemnumber1,sysdate,itemflag);  
            commit;
    end;
    end if;
    return ctempno;
END;

/*4.3.2 函数F_NEW_ITEMCODE()测试PL/SQL环境*/
DECLARE
ITEMCODE varchar2(10);
BEGIN
ITEMCODE:=F_NEW_ITEMCODE('BX80315US','T');
DBMS_OUTPUT.PUT_LINE('ITEMCODE= ' ||ITEMCODE);
END;

/*************************************************************************/
/*5.EJB调用函数(环境JDEVELOPER EJB3.0)*/
public String GetItemCodeByItem(String itemnumber){
         String GetItemCode = "";
         uow = this.sessionFactory.acquireUnitOfWork();
             try {
                StoredFunctionCall functionCall = new StoredFunctionCall();
                functionCall.setProcedureName("F_GET_ITEMCODE");
                functionCall.addNamedArgumentValue("itemnumber1",itemnumber);
                functionCall.addNamedArgumentValue("itemflag","T");
                functionCall.setResult("F_GET_ITEMCODE", String.class);
                ValueReadQuery query = new ValueReadQuery();
                query.setCall(functionCall);
                GetItemCode = (String) uow.executeQuery(query);
                uow.release();
             }
             catch (Exception e) {
                 GetItemCode=e.toString();
             }
         return itemcode;
}
/*************************************************************************/
/*6.ACTION调用EJB省略*/