你说的是更新数据么?完全可以啊,只需要保证有返回值。create or replace function f_update(p_id IN NUMBER) return number is Result number; begin UPDATE a SET a_name='Tt' WHERE a_id=p_id; RETURN p_id; end f_update;
lz的意思可能是select 的时候调用function的时候不能更新数据. 借用2楼代码说下: 如果执行 select my_fun from dual 会报ora-14552,ora-14551错误。如果想在function进行dml语句,需要加自治事务。CREATE OR REPLACE FUNCTION my_fun RETURN VARCHAR2 IS --开启自治事务 PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO ws (code) VALUES('12321'); COMMIT; RETURN 0; EXCEPTION WHEN OTHERS THEN ROLLBACK; RETURN 1; END;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
BH VARCHAR2(30)
YXQ_Q VARCHAR2(10)
YXZ_Q VARCHAR2(10)[TEST@myoracle] SQL>select * from test;BH YXQ_Q YXZ_Q
------------------------------ ---------- ----------
001 2011-08-01 2011-12-30
002 2011-08-01 2011-08-31
002 2011-09-01 2011-09-30
003 2011-08-01 2011-08-31
003 2011-09-01 2011-09-30
003 2011-10-01 2011-10-31
001 2011-08-01 2011-12-31已选择7行。[TEST@myoracle] SQL>CREATE OR REPLACE FUNCTION my_fun
2 RETURN VARCHAR2
3 IS
4 BEGIN
5 UPDATE TEST SET bh='002';
6 COMMIT;
7 RETURN 0;
8 EXCEPTION
9 WHEN OTHERS THEN
10 RETURN 1;
11 END;
12 /函数已创建。
[TEST@myoracle] SQL>declare
2 o_ret varchar2(10);
3 begin
4 o_ret := my_fun;
5 end;
6 /PL/SQL 过程已成功完成。[TEST@myoracle] SQL>select * from test;BH YXQ_Q YXZ_Q
------------------------------ ---------- ----------
002 2011-08-01 2011-12-30
002 2011-08-01 2011-08-31
002 2011-09-01 2011-09-30
002 2011-08-01 2011-08-31
002 2011-09-01 2011-09-30
002 2011-10-01 2011-10-31
002 2011-08-01 2011-12-31已选择7行。
Result number;
begin
UPDATE a
SET a_name='Tt'
WHERE a_id=p_id;
RETURN p_id;
end f_update;
借用2楼代码说下:
如果执行 select my_fun from dual 会报ora-14552,ora-14551错误。如果想在function进行dml语句,需要加自治事务。CREATE OR REPLACE FUNCTION my_fun
RETURN VARCHAR2
IS
--开启自治事务
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ws (code) VALUES('12321');
COMMIT;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RETURN 1;
END;