oracle 中怎样执行自定义函数（函数中有修改数据库的操作）

[houwenlong789]

最近写了一个函数，函数中有修改数据库中表的数据操作，函数原型为：

create or replace
FUNCTION SYS_FILL_GS 
(
    evaluationSet in number
)

假如我现在执行 select SYS_FILL_GS(32783) from dual;
则会出现：
Error report:
SQL Error: ORA-14551: 无法在查询中执行 DML 操作
ORA-06512: 在"KFEPMS.SYS_FILL_GS", line 17
14551. 00000 - "cannot perform a DML operation inside a query "
*Cause: DML operation like insert, update, delete or select-for-update
cannot be performed inside a query or under a PDML slave.
*Action: Ensure that the offending DML operation is not performed or
use an autonomous transaction to perform the DML operation within
the query or PDML slave.
这个问题怎样解决

[zhangwonderful]

把sql语句完整粘贴出来看看

[zhangwonderful]

function中一般不出现delete,insert,update等语句。如果要对表进行delete,insert,update，用存储过程或触发器即可。

[清风智语]

DML在函数中可以直接操作数据库吗？

[houwenlong789]

答案找到了，遇到相同问题的可以查看这篇文章http://www.cnblogs.com/pengyq/archive/2008/11/26/1341656.html

[houwenlong789]

[Quote=引用 3 楼 robinson_0612 的回复:]

DML在函数中可以直接操作数据库吗？
[/Quote] 答案是可以的

[心中的彩虹]

函数的全部代码 ,怎么没有返回

create or repacle function fun1(vn in varchar2) return varchar2
as
v varchar2;
begin
......

return(v);
end;

[houwenlong789]

create or replace
FUNCTION SYS_FILL_GS 
(
    evaluationSet in number
)
RETURN VARCHAR2 AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_test number(8);
v_masterCounts number(3) ;--部门主管个数
v_lastOfEvaluationSet date ;
v_index number(3);-- for循环指针
v_currentMasterID varchar2(200);--当前部门主管id

BEGIN
  select evaluation_date into v_lastOfEvaluationSet from dep_evaluation_set where PK_auto_ID=evaluationSet;
  select count(*) into v_test from dep_GS_examin_sup where FK_dep_evaluation_set_ID = evaluationSet;
  if v_test = 0 then
        -- 1插入督查督导的所有内容（行政正职，书记，主管领导打分表）
         insert into dep_GS_examin_sup (FK_sup_record_ID,FK_dep_evaluation_set_ID)
            select r.PK_auto_ID, evaluationSet
                from sup_record r,sup_Info i
                    where r.FK_SPI_ID = i.PK_SUP_ID and Is_GSProject  = 1 and 
                     last_day(workOut_date) = last_day(v_lastOfEvaluationSet);
      --2 插入月综合计划的所有内容（行政正职，书记，主管领导打分表）
         insert into dep_GS_examin_complex (FK_complex_plan_detail_ID,FK_dep_evaluation_set_ID)
             select d.PK_auto_ID ,evaluationSet
                  from complex_plan_detail d,complex_month_plan c
                     where d.FK_complex_ID = c.PK_auto_ID and is_GS_plan = 1
                         and finish_state_check=3 and last_day(d.end_date) =last_day(v_lastOfEvaluationSet);
      --3其它领导打分表打分表 的插入
        select count(*) into v_masterCounts from (select distinct(FK_dpt_Master) from department);
        for v_index in 1..v_masterCounts loop
          --选出一个主管领导
           select FK_dpt_Master into  v_currentMasterID from 
               (select  FK_dpt_Master,rownum rn  from ( select distinct(FK_dpt_Master) from department)) 
                     where rn = v_index;
          --插入这个主管领导可以打分的督查督导内容
          insert into dep_GS_other_charger_sup(FK_dep_evaluation_set_ID,FK_employee_ID,score,FK_sup_record_ID)
           select evaluationSet,v_currentMasterID,80,r.PK_auto_ID
              from sup_record r,sup_Info i
                where r.FK_SPI_ID = i.PK_SUP_ID and Is_GSProject  = 1 and  
                  SYS_MASTER_CAN_SCORE_BY_ID(v_currentMasterID,i.cooperate_Dep) > 0
                  and  last_day(workOut_date) = last_day(v_lastOfEvaluationSet);
                  
         -- 插入这个主管领导可以打分的月综合计划
          insert into dep_GS_other_charger_complex(FK_dep_evaluation_set_ID,FK_employee_ID,score,FK_complex_plan_detail_ID)
           select evaluationSet,v_currentMasterID,80 ,d.PK_auto_ID
                      from complex_plan_detail d,complex_month_plan c
                         where d.FK_complex_ID = c.PK_auto_ID and is_GS_plan = 1 and SYS_MASTER_CAN_SCORE_By_Name(v_currentMasterID,d.cooperate_dept)>0
                             and finish_state_check=3 and last_day(d.end_date) =(v_lastOfEvaluationSet);
        end loop;
      
       -- 插入月综合计划的所有内容（其它领导打分表打分表）
    
  end if;
  COMMIT;
  RETURN NULL;
END SYS_FILL_GS;

这次可以用的代码 ，假如大家遇到这种问题，可以参看以下，假如，函数中有修改数据库的操作，只需要加
PRAGMA AUTONOMOUS_TRANSACTION;
和 COMMIT;

[q4383194]

我执行包里面的函数 ，提示没有对应函数或过程。