oracle 函数插入操作,Oracle函数内部运用insert插入

Oracle函数内部使用insert插入

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;