ORACLE: SELECT语句里调用函数使用DML语句进行Update/Insert/Delete操作

FUNCTION get_tg_dop_reserved(
         dop_id_  IN NUMBER) RETURN VARCHAR2
IS
  PRAGMA AUTONOMOUS_TRANSACTION; /* 注意：这句话必须写 */
  dummy_   NUMBER;
  CURSOR c1 IS
       SELECT 1
       FROM DOP_HEAD a
       WHERE a.dop_id = dop_id_
       AND a.objstate NOT IN ('Removed','Closed','Created');
 
BEGIN
 
  OPEN c1;
  FETCH c1 INTO  dummy_;
  CLOSE c1;
  IF dummy_ IS NOT NULL THEN
     tg_imp001_api.tg_dop_reserved(dop_id_);
     COMMIT; /* 注意：如果做了DML，一定要提交或回滚 */
     RETURN '預留成功！';
  END IF;
 
  RETURN '沒找到該DOP！';
      
END get_tg_dop_reserved; 

 

以上声明后就可以使用SELECT语句了

SELECT get_tg_dop_reserved（268） “结果"

FROM dual;

 

 

转载于:https://www.cnblogs.com/Jun2009/archive/2010/08/14/1799468.html