根据以下要求编写函数：将scott.emp表中工资低于平均工资的职工工资加上200，并返回修改了工资的总人数。pl/sql中有更新的操作，
环境
oracle 11.2.0 + sql plus
问题
根据以下要求编写函数：将scott.emp表中工资低于平均工资的职工工资加上200，并返回修改了工资的总人数。pl/sql中有更新的操作，执行此函数报如下错误：ora-16551: 无法在查询中执行 dml 操作。
解决
在声明函数时加上： pragma autonomous_transaction; 并在执行完dml后commit。
操作日志
--登录到oracle
c:\users\wentasy>sqlplus wgb
sql*plus: release 11.2.0.1.0 production on 星期六 6月 29 15:32:21 2013
copyright (c) 1982, 2010, oracle. all rights reserved.
输入口令:
连接到:
oracle database 11g enterprise edition release 11.2.0.1.0 - production
with the partitioning, olap, data mining and real application testing options
--编写函数
sql> create or replace function raise_sal
2 return number
3 is
4 v_num number:=0;
5 v_avg emp.sal%type;
6 begin
7 select avg(sal) into v_avg from emp;
8 update emp set sal=sal+200 where sal 9 v_num:=sql%rowcount;
10 return v_num;
11 end raise_sal;
12 /
函数已创建。
--调用函数，出现错误
sql> select raise_sal() from dual;
select raise_sal() from dual
*
第 1 行出现错误:
ora-14551: 无法在查询中执行 dml 操作
ora-06512: 在 wgb.raise_sal, line 8
--加上pragma autonomous_transaction和commit。
sql> create or replace function raise_sal
2 return number
3 is
4 pragma autonomous_transaction;
5 v_num number:=0;
6 v_avg emp.sal%type;
7 begin
8 select avg(sal) into v_avg from emp;
9 update emp set sal=sal+200 where sal 10 v_num:=sql%rowcount;
11 commit;
12 return v_num;
13 end raise_sal;
14 /
函数已创建。
--验证第一步：查询薪水平均值
sql> select avg(sal) from emp;
avg(sal)
----------
2543.75
--验证第二步：查询薪水比平均薪水低的员工的总数
sql> select count(sal) from emp where sal
count(sal)
----------
8
--验证第三步：查询数据
sql> select ename, sal from emp;
ename sal
---------- ----------
smith 1600
allen 2400
ward 2050
jones 2975
martin 2050
blake 2850
clark 2450
king 5000
turner 2300
james 1750
ford 3000
ename sal
---------- ----------
miller 2100
已选择12行。
--验证第四步：调用函数，如果为8，则实现功能
sql> select raise_sal() from dual;
raise_sal()
-----------
8
--验证第五步：再次查询表数据
sql> select ename, sal from emp;
ename sal
---------- ----------
smith 1800
allen 2600
ward 2250
jones 2975
martin 2250
blake 2850
clark 2650
king 5000
turner 2500
james 1950
ford 3000
ename sal
---------- ----------
miller 2300
已选择12行。
参考资料
ora-14551: 无法在查询中执行 dml 操作
引用文字——更好的理解自治事务
数据库事务是一种单元操作，要么是全部操作都成功，要么全部失败。在oracle中，一个事务是从执行第一个数据管理语言（dml）语句开始，直到执行一个commit语句，提交保存这个事务，或者执行一个rollback语句，放弃此次操作结束。事务的“要么全部完成，要么什么都没完成”的本性会使将错误信息记入数据库表中变得很困难，因为当事务失败重新运行时，用来编写日志条目的insert语句还未完成。针对这种困境，oracle提供了一种便捷的方法，即自治事务。自治事务从当前事务开始，，在其自身的语境中执行。它们能独立地被提交或重新运行，而不影响正在运行的事务。正因为这样，它们成了编写错误日志表格的理想形式。在事务中检测到错误时，您可以在错误日志表格中插入一行并提交它，然后在不丢失这次插入的情况下回滚主事务。因为自治事务是与主事务相分离的，所以它不能检测到被修改过的行的当前状态。这就好像在主事务提交之前，它们一直处于单独的会话里，对自治事务来说，它们是不可用的。然而，反过来情况就不同了：主事务能够检测到已经执行过的自治事务的结果。要创建一个自治事务，您必须在匿名块的最高层或者存储过程、函数、数据包或触发的定义部分中，使用pl/sql中的pragma autonomous_transaction语句。在这样的模块或过程中执行的sqlserver语句都是自治的。触发无法包含commit语句，除非有pragma autonomous_transaction标记。但是，只有触发中的语句才能被提交，主事务则不行。