问题：写一个函数，函数中要更新数据库中的某个字段，函数在使用时格式是：select funcName() from dual; 那么此函数中如果含有update语句
函数：
create or replace function CalABGrpRearBounty()
return number is Result number;
begin
update larearrelation set rearcommflag = 2 where rearcommflag = 1;
commit;
end;
报：ORA-14551,不能在查询语句中执行dml语句
开始以为函数本身有问题，其实不然，是因为select funcName() from dual; 这个语句的问题
解决：函数中使用自治事务。
自治事务实现的功能是自治事务的提交或回滚不影响当前的事务。同时自治事务也是看不到当前会话所做的未提交的修改的。
create or replace function CalABGrpRearBounty(tAgentCode laagent.agentcode%type,
tAgentGrade latree.agentgrade%type
)
return number is pragma autonomous_transaction;
mResult number;
mAgentCode laagent.agentcode%type;
mFYCMin lawageradix.fycmin%type; -- 非本人推荐的奖金数量
mFYCMax lawageradix.fycmax%type; -- 本人推荐的奖金数量
mRecommendCount number; -- 推荐次数
mRearCommFlag number;
begin
mResult := 0;
-- 查询育成奖金标准
select FYCMin into mFYCMin from lawageradix where agentgrade = tAgentGrade and wagecode = 'WA0009' and branchtype = 2;
if(mFYCMin is not null and mFYCMin > 0)
then
begin
select FYCMax into mFYCMax from lawageradix where agentgrade = tAgentGrade and wagecode = 'WA0009' and branchtype = 2;
-- 查询被育成人，存在育成关系，且未提佣，且计算日期在育成止期后
-- rearcommflag=1:未进行佣金计算；rearcommflag=2：已进行过佣金计算；rearcommflag=3：已审核。
declare cursor rearListCursor is
select agentcode from larearrelation where rearagentcode = tAgentCode and rearflag = 1 and (rearcommflag = 1 or rearcommflag = 2);
begin
open rearListCursor;
loop
fetch rearListCursor into mAgentCode;
exit when rearListCursor%notfound;
-- 查询育成人是否推荐人
select count(*) into mRecommendCount from larecomrelation where agentcode = mAgentCode and recomagentcode = tAgentCode
and recomflag = 1;
if(mRecommendCount = 1)
then
mResult := mResult + mFYCMax;
else
mResult := mResult + mFYCMin;
end if;
-- 更新rearcommflag=2，表示已计算过佣金
select rearcommflag into mRearCommFlag from larearrelation where rearagentcode = tAgentCode and agentcode = mAgentCode and rearflag = 1;
if(mRearCommFlag = 1)
then
begin
update larearrelation set rearcommflag = 2 where rearcommflag = 1 and rearagentcode = tAgentCode and agentcode = mAgentCode and rearflag = 1 ;
commit;
end;
end if;
end loop;
close rearListCursor;
end;
end;
end if;
return(mResult);
end;
以下摘自：作者: 杨廷琨, 出处:blog, 责任编辑: 王晓晨, 2008-08-04 09:31
http://database.ctocio.com.cn/tips/254/8246254.shtml
这两天看了11g的CONCEPT文档的事务部分，发现自治事务还有一些以前没有注意到的地方，这里简单总结一下。
这一篇简单描述一下自治事务可以使用的位置。
自治事务可以使用在以下的位置：
存储过程和函数;
本地过程和函数;
包;
对象方法;
匿名块的最顶层。
其中过程、函数和包中使用自治事务很常见，大部分自治事务的情况都属于这种情况。由于对象相对使用的比较少，所以对象方法中的自治事务也是比较少见的，不过由于对象中的方法和包中的存储过程、函数十分相似，所以这种情况也是情理之中的。
剩下的两种情况，是以前所不了解的。从来没有想过对于本地过程和函数也是可以定义为自治事务的，不过本地过程定义为自治事务是十分方便的功能，使得过程中需要用到自治事务的功能时，不必再额外定义一个自治事务的存储过程，只需要在当前的过程中定义一个自治的本地过程就可以了。
一个简单的例子如下：
| SQL> CREATE TABLE T_AUTO_TRANS (ID NUMBER, NAME VARCHAR2(30)); |
表已创建。
| SQL> CREATE TABLE T_LOG (ID NUMBER, NAME VARCHAR2(30)); |
表已创建。
下面建立一个LOCAL PROCEDURE的自治事务的例子：
| SQL> DECLARE 2 PRAGMA AUTONOMOUS_TRANSACTION; 3 BEGIN 4 INSERT INTO T_LOG VALUES (1, 'AUTONOMOUS_TRANSACTION'); 5 COMMIT; 6 END; 7 / |
PL/SQL 过程已成功完成。
| SQL> SELECT * FROM T_AUTO_TRANS; ID NAME ---------- ------------------------------ 1 TEST SQL> SELECT * FROM T_LOG; ID NAME ---------- ------------------------------ 1 AUTONOMOUS_TRANSACTION 1 AUTONOMOUS_TRANSACTION SQL> ROLLBACK; |
回退已完成。
| SQL> SELECT * FROM T_AUTO_TRANS; |
| SQL> SELECT * FROM T_LOG; ID NAME ---------- ------------------------------ 1 AUTONOMOUS_TRANSACTION 1 AUTONOMOUS_TRANSACTION |
这种情况的意义没有本地过程的大，不过对于编写包含大量的SQL和PL/SQL的sql文件还是有不少帮助的。
Oracle 11g自治事务更像是在单独的会话中执行，它的事务状态不会影响当前的事务，它也不会看到当前事务没有提交的修改。
<script language=Javascript> </script>
自治事务实现的功能是自治事务的提交或回滚不影响当前的事务。同时自治事务也是看不到当前会话所做的未提交的修改的。
| SQL> TRUNCATE TABLE T_AUTO_TRANS; |
表被截断。
| SQL> CREATE OR REPLACE PROCEDURE P_TEST AS 2 BEGIN 3 INSERT INTO T_AUTO_TRANS VALUES (2, 'TEST'); 4 FOR I IN (SELECT COUNT(*) CNT FROM T_AUTO_TRANS) LOOP 5 DBMS_OUTPUT.PUT_LINE('P_TEST:' || I.CNT); 6 END LOOP; 7 END; 8 / |
过程已创建。
| SQL> CREATE OR REPLACE PROCEDURE P_TEST_AUTO AS 2 PRAGMA AUTONOMOUS_TRANSACTION; 3 BEGIN 4 INSERT INTO T_AUTO_TRANS VALUES (2, 'TEST'); 5 FOR I IN (SELECT COUNT(*) CNT FROM T_AUTO_TRANS) LOOP 6 DBMS_OUTPUT.PUT_LINE('P_TEST_AUTO:' || I.CNT); 7 END LOOP; 8 COMMIT; 9 END; 10 / |
过程已创建。
| SQL> INSERT INTO T_AUTO_TRANS VALUES (1, 'TEST'); |
已创建 1 行。
| SQL> SET SERVEROUT ON SQL> EXEC P_TEST P_TEST:2 |
PL/SQL 过程已成功完成。
| SQL> EXEC P_TEST_AUTO P_TEST_AUTO:1 |
PL/SQL 过程已成功完成。
可以看到，对于自治事务是看不到当前会话所作的未提交的修改的。
| SQL> EXEC P_TEST P_TEST:4 |
PL/SQL 过程已成功完成。
而对于当前会话，由于自治事务的操作已经提交，因此之后的查询是可以看到自治事务所进行的修改的。
自治事务看不到当前会话的修改，那么自治事务调用的过程是否能看到自治事务的修改呢：
| SQL> TRUNCATE TABLE T_AUTO_TRANS; |
表被截断。
| SQL> CREATE OR REPLACE PROCEDURE P_AUTO_TRANS AS 2 PRAGMA AUTONOMOUS_TRANSACTION; 3 BEGIN 4 INSERT INTO T_AUTO_TRANS VALUES (1, 'TEST'); 5 INSERT INTO T_AUTO_TRANS VALUES (2, 'TEST'); 6 FOR I IN (SELECT COUNT(*) CNT FROM T_AUTO_TRANS) LOOP 7 DBMS_OUTPUT.PUT_LINE('P_AUTO_TRANS:' || I.CNT); 8 END LOOP; 9 P_TEST; 10 P_TEST_AUTO; 11 COMMIT; 12 END; |
过程已创建。
| SQL> INSERT INTO T_AUTO_TRANS VALUES (1, 'TEST'); |
已创建 1 行。
| SQL> EXEC P_AUTO_TRANS P_AUTO_TRANS:2 P_TEST:3 P_TEST_AUTO:1 PL/SQL |
过程已成功完成。
建立一个自治事务，分别调用一个普通过程和一个声明了自治事务的过程，结果可以看到，自治事务调用普通过程，可以看到自治事务所进行的修改，而对于自治事务调用的自治事务过程，是看不到自治事务所进行的修改的。
从这一点看，自治事务更像是在单独的会话中执行，它的事务状态不会影响当前的事务，它也不会看到当前事务没有提交的修改。
总结：要想在SELECT语句中对表进行修改，可以使用自治事务（Autonomous Transactions，AT）。自治事务（Autonomous Transactions）是由另外一个事务调用的，这个叫事务做主事务（Main Transaction，MT）。自治事务（Autonomous Transactions）的提交（commit）和回滚（rollback）并不影响主事务（MT）。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
