把select name into sname from test_tb where id='1'; 改为update sname=name from test_tb where id='1'; 或者insert into test_tb (sname) values(select name from test_tb ) where id='1';
应该是FUNCTION里不能对表进行DML操作,不过我这边现在没法测试.
FUNCTION里是可以用DML的。 V_Lucky(Lucky),哪有這樣的INSERT語句?
我测试过了你的fuction没问题啊 测试环境:solaris 2.7 oralce 8i
不过当test_tb 里面id = 1的行多于一行当然汇报错ORA-01422: exact fetch returns more than requested number of rows
mackytan的意思是這樣: 類似SELECT lqtest FROM dual;語句引起ORA-14551函數是沒有問題的。
我在ORACLE7.2上做的测试: SQL> create table test_tb (id varchar2(10),name varchar2(100)); Table created. SQL> insert into test_tb 2 values ('1','name1'); 1 row created. SQL> commit; Commit complete. SQL> create or replace function lqtest return varchar2 2 as 3 sname varchar2(100); 4 begin 5 select name into sname from test_tb where id='1'; 6 insert into test_tb(id,name) values('2','test'); 7 return '1'; 8 end ; 9 / Function created. SQL> show error; No errors. SQL> select lqtest from dual; select lqtest from dual * ERROR at line 1: ORA-06571: Function LQTEST does not guarantee not to update database这说明函数本身是对的. 但是如果在SELECT中用到这个函数的话,就汇报错. 要让一个函数能在SELECT使用,必须遵循几个原则(用PRAGMA RESTRICT_REFERENCES (function_name, WNDS [, WNPS] [, RNDS] [, RNPS]);)在包头中申明) : WNDS means "writes no database state" (does not modify database tables). WNPS means "writes no package state" (does not change the values of packaged variables). RNDS means "reads no database state" (does not query database tables). RNPS means "reads no package state" (does not reference the values of packaged variables).
改为update sname=name from test_tb where id='1';
或者insert into test_tb (sname) values(select name from test_tb ) where id='1';
V_Lucky(Lucky),哪有這樣的INSERT語句?
测试环境:solaris 2.7 oralce 8i
類似SELECT lqtest FROM dual;語句引起ORA-14551函數是沒有問題的。
SQL> create table test_tb (id varchar2(10),name varchar2(100));
Table created.
SQL> insert into test_tb
2 values ('1','name1');
1 row created.
SQL> commit;
Commit complete.
SQL> create or replace function lqtest return varchar2
2 as
3 sname varchar2(100);
4 begin
5 select name into sname from test_tb where id='1';
6 insert into test_tb(id,name) values('2','test');
7 return '1';
8 end ;
9 /
Function created.
SQL> show error;
No errors.
SQL> select lqtest from dual;
select lqtest from dual
*
ERROR at line 1:
ORA-06571: Function LQTEST does not guarantee not to update database这说明函数本身是对的. 但是如果在SELECT中用到这个函数的话,就汇报错.
要让一个函数能在SELECT使用,必须遵循几个原则(用PRAGMA RESTRICT_REFERENCES (function_name, WNDS [, WNPS] [, RNDS] [, RNPS]);)在包头中申明) :
WNDS means "writes no database state" (does not modify
database tables).
WNPS means "writes no package state" (does not change the
values of packaged variables).
RNDS means "reads no database state" (does not query database
tables).
RNPS means "reads no package state" (does not reference the
values of packaged variables).