我收到此错误:
cannot perform a DML operation inside a query
当我尝试执行查询时
select st_atten_up(1,7) from dual;
代码如下。
create or replace FUNCTION st_atten_up(stu_id IN student_info.id%type,app_mon IN student_attendence.month%type)
RETURN NUMBER
IS
att1 NUMBER;
BEGIN SELECT ATTENDANCE into att1 FROM student_attendence
WHERE student_attendence.id = stu_id and student_attendence.month = app_mon;
att1 := att1 + 1;
UPDATE student_attendence SET ATTENDANCE = att1
where id = stu_id and month = app_mon;
return att1;
END;
提前致谢。
如果调用的函数已声明为PRAGMA AUTONOMOUS_TRANSACTION
(Link),则可以从技术上在select中执行DML 。然而,这是很少从内执行DML一个好主意,SELECT
陈述了许多很好的理由不过,要回答你的问题,你可以写你的函数(包括变异表,性能下降。) PRAGMA
:
create or replace FUNCTION st_atten_up(stu_id IN student_info.id%type,app_mon IN student_attendence.month%type)
RETURN NUMBER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
att1 NUMBER;
BEGIN SELECT ATTENDANCE into att1 FROM student_attendence
WHERE student_attendence.id = stu_id and student_attendence.month = app_mon;
att1 := att1 + 1;
UPDATE student_attendence SET ATTENDANCE = att1
where id = stu_id and month = app_mon;
return att1;
END;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句