最近写了一个函数,函数中有修改数据库中表的数据操作,函数原型为: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.
这个问题怎样解决
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.
这个问题怎样解决
create or repacle function fun1(vn in varchar2) return varchar2
as
v varchar2;
begin
......return(v);
end;
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);--当前部门主管idBEGIN
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;