求高人解答
为什么在oracle中,函数不能修改数据啊,这个有例外吗
[解决办法]
谁说的?
[TEST@myoracle] SQL>desc test 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- 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-30002 2011-08-01 2011-08-31002 2011-09-01 2011-09-30003 2011-08-01 2011-08-31003 2011-09-01 2011-09-30003 2011-10-01 2011-10-31001 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-30002 2011-08-01 2011-08-31002 2011-09-01 2011-09-30002 2011-08-01 2011-08-31002 2011-09-01 2011-09-30002 2011-10-01 2011-10-31002 2011-08-01 2011-12-31已选择7行。
[解决办法]
你说的是更新数据么?完全可以啊,只需要保证有返回值。
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;