create or replace function GET_PRODUCT_ID_BY_NAME(productName varchar2)
  return number is
  pragma autonomous_transaction;

  mResult number;      -- 返回结果ID
  countResult number;  -- 根据名称查询结果
  tempVar varchar2(200);-- 临时处理字符
begin
  tempVar := trim(productName);

    select count(TPI.ID)
      into countResult
      from TBL_PRODUCT_INFO TPI
      where TPI.PRODUCT_NAME = tempVar;

    if (countResult = 0) then -- 产品不存在
      select seq_tbl_product_info.Nextval into mResult from dual;

        insert into TBL_PRODUCT_INFO
          (ID, PRODUCT_NAME)
        values
          (mResult, tempVar);
        commit;

    else                       -- 产品已存在

      select TPI.ID
        into mResult
        from TBL_PRODUCT_INFO TPI
       where TPI.PRODUCT_NAME = tempVar;

    end if;

    return(mResult);
  end;

?