Oracle函数内部使用insert插入
create or replace function GET_PRODUCT_ID_BY_NAME(productName varchar2)return number ispragma autonomous_transaction;mResult number; -- 返回结果IDcountResult number;-- 根据名称查询结果tempVar varchar2(200);-- 临时处理字符begintempVar := 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;
页:
[1]