极其离奇的问题，大家近来看看，解决了100分相送！！

[waxle]

救命啊，触发器中是否可以使用循环结构？？
这是我用循环结构写的触发器。
CREATE OR REPLACE TRIGGER UPDATERESULTD AFTER INSERT ON RESULTR
FOR EACH ROW
DECLARE
LOOP1 INTEGER;
SECNODE VARCHAR2(10);
RATIODR NUMBER(8,3);
BEGIN
SELECT COUNT(*) INTO LOOP1 FROM RELATION_DR WHERE FIRNODE=:NEW.FIRNODE;
FOR I IN 1..LOOP1 LOOP
SELECT SECNODE INTO SECNODE FROM ((SELECT SECNODE FROM RELATION_DR WHERE FIRNODE=:NEW.FIRNODE AND ROWNUM<(I+1)) MINUS (SELECT SECNODE FROM RELATION_DR WHERE FIRNODE=:NEW.FIRNODE AND ROWNUM<I)) WHERE ROWNUM=1;
SELECT RATIODR INTO RATIODR FROM ((SELECT RATIODR FROM RELATION_DR WHERE FIRNODE=:NEW.FIRNODE AND ROWNUM<(I+1)) MINUS (SELECT RATIODR FROM RELATION_DR WHERE FIRNODE=:NEW.FIRNODE AND ROWNUM<I)) WHERE ROWNUM=1;
INSERT INTO RESULTD VALUES(:NEW.YR,SECNODE,:NEW.WATER_QR*RATIODR);
END LOOP;
END;
/

这是相关的三个表建表的语句
CREATE TABLE RESULTR(YR CHAR(10),FIRNODE CHAR(10),WATER_QR NUMBER(8,3));
CREATE TABLE RESULTD(YR CHAR(10),SECNODE CHAR(10),WATER_QD NUMBER(8,3));
CREATE TABLE RELATION_DR(FIRNODE CHAR(10),SECNODE CHAR(10),RATIODR NUMBER(5,2));

但是在使用的时候有些插入出错了，出错信息如下：
ERROR 位于第 1 行:
ORA-01403: 未找到数据
ORA-06512: 在"LRH.UPDATERESULTD", line 9
ORA-04088: 触发器 'LRH.UPDATERESULTD' 执行过程中出错

大虾们救命啊，是不是不能用循环结构，能不能提示一下或帮忙改一下，谢谢！！！


我的三个表格的数据吧。
第一个关系表：
SQL> select * from relation_dr;

FIRNODE SECNODE RATIODR
---------- ---------- ----------
HETIAN P01 .6
HETIAN P02 .3
HETIAN P03 .1
AKESHU P04 .5
AKESHU P05 .3
AKESHU P06 .05
AKESHU P07 .15
NONGYISHI P08 .25
NONGYISHI P09 .35
NONGYISHI P10 .15
NONGYISHI P11 .25

FIRNODE SECNODE RATIODR
---------- ---------- ----------
KUELE P12 1

然后我插入以下语句时出错，好像只有HETIAN和AKESHU可以插入2条或以上数据，其他都不可以了，似乎这两个值已经注册了一样，不知道什么原因。
SQL> INSERT INTO RESULTR VALUES(2002,'HETIAN',500);

已创建 1 行。

SQL> INSERT INTO RESULTR VALUES(2002,'AKESHU',550);

已创建 1 行。

SQL> INSERT INTO RESULTR VALUES(2002,'NONGYISHI',400);
INSERT INTO RESULTR VALUES(2002,'NONGYISHI',400)
*
ERROR 位于第 1 行:
ORA-01403: 未找到数据
ORA-06512: 在"LRH.UPDATERESULTD", line 9
ORA-04088: 触发器 'LRH.UPDATERESULTD' 执行过程中出错


SQL> INSERT INTO RESULTR VALUES(2002,'KUELE',400);

已创建 1 行。

但是如果把RELATION_DR中NONGYISHI的条数改成1条，即删除3条数据。
如下语句：
SQL> delete from relation_dr where secnode IN ('P09','P10','P11');

已删除3行。

SQL> INSERT INTO RESULTR VALUES(2002,'NONGYISHI',400);

已创建 1 行。
即插入了数值。好像要加入其他的FIRNODE也是一样的，在RELATION_D不能超过一条数据，否则出错。不知道具体什么原因，大虾们救命啊。

而且非常奇怪的是当我重新建一个触发器，更换表名之后也是只有HETIAN和AKESHU这两项在RELATION_DR表能超过两行的数据，其他都不可以，好像出现触发器的系统记忆功能一样，不知道怎么回事啊，感觉非常离奇。敬请哪位大虾相告，多谢多谢！！
解决必定加分100。

[dinya2003]

CREATE OR REPLACE TRIGGER UPDATERESULTD
AFTER INSERT ON RESULTR
FOR EACH ROW
DECLARE
LOOP1 INTEGER;
SECNODE VARCHAR2(10);
RATIODR NUMBER(8,3);
BEGIN
SELECT COUNT(*) INTO LOOP1 FROM RELATION_DR WHERE FIRNODE=:NEW.FIRNODE;
FOR I IN 1..LOOP1 LOOP
SELECT SECNODE INTO SECNODE FROM ((SELECT SECNODE FROM RELATION_DR WHERE FIRNODE=:NEW.FIRNODE AND ROWNUM<(I+1)) MINUS (SELECT SECNODE FROM RELATION_DR WHERE FIRNODE=:NEW.FIRNODE AND ROWNUM<I)) WHERE ROWNUM=1;
SELECT RATIODR INTO RATIODR FROM ((SELECT RATIODR FROM RELATION_DR WHERE FIRNODE=:NEW.FIRNODE AND ROWNUM<(I+1)) MINUS (SELECT RATIODR FROM RELATION_DR WHERE FIRNODE=:NEW.FIRNODE AND ROWNUM<I)) WHERE ROWNUM=1;

--增加以下部分判断是否有值,有可能select中没有值;
IF SECNODE IS NULL THEN
SECNODE:='没有值'||:NEW.YR;
END IF;
IF RATIODR IS NULL THEN
RATIODR:=0;
END IF;
-------------------------------------
INSERT INTO RESULTD VALUES(:NEW.YR,SECNODE,:NEW.WATER_QR*RATIODR);
END LOOP;
END;

[waxle]

但是那个RELATION_DR中的数值你也看到了，有NONGYISHI的值啊，不可能会没有值啊，数据结构都是一样的啊，为什么NONGYISHI就不可以呢？？我的流程是这样的。
当插入数值到RESULTR时，根据插入RESULT_DR中的FIRNODE看看有几个SECNODE，有几个SECNODE就循环几次(loop1次)，然后根据插入到RESULT_DR中的FIRNODE查询对应的SECNODE和RATIODR，然后写入到RESULTD中，即写入YR，SECNODE，以及根据比例(RATIODR)得到的WATER_DR。
当插入NONGYISHI是，按照流程应该时。
察看RELATION_DR表，然后可以得到loop1=4 因为有4条记录。
然后i=1根据我的记录则可以找到第一条记录。怎么可能没有记录呢？？
大虾麻烦帮忙仔细看看请指教。而且我更换用户结果还是一样的。

[waxle]

怎么没有人回啊，自己顶一个！！！

[EnjoyLift]

ORA-01403 no data found
Topics
What is this error?
How to fix it
1-August-2001
Author: Kavitha Soundararajan


--------------------------------------------------------------------------------

What causes this error?
This error occurs when a SQL statement, written within a PL/SQL block, does not fetch any data.


Back to top of file
--------------------------------------------------------------------------------

How to fix it
The easiest fix would be is to handle the error in the PL/SQL block

When a SQL statement is written within a PL/SQL block, enclose the SQL with a BEGIN and END statement. Handle the exception and raise a user-friendly message or handle the rest of the processing.

Eg:

CREATE OR REPLACE PROCEDURE test_proc (p_empno IN NUMBER) IS
l_empname VARCHAR2(50);
BEGIN

SELECT empname
INTO l_empname
FROM emp
WHERE empno = p_empno;

IF l_empname = 'Sarah Jones' THEN
INSERT INTO empresult values ('105', 'Found Sarah Jones');
END IF;
END;
/
The above procedure has not handled the error that would be raised if the select statement did not find the specified empno. See the following for the error raised:

SQL> exec test_proc (1)
begin test_proc (1); end;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "TAROT.TEST_PROC", line 4
ORA-06512: at line 1

If you want to procedure to execute properly without errors on the screen, then you would have to handle the error.

The Exception needs to be controlled by adding an exception handler to the code. The above code has been modified to handle an exception NO_DATA_FOUND. This is the name of the exception that the error relates to.

CREATE OR REPLACE PROCEDURE test_proc (p_empno IN NUMBER) IS
l_empname VARCHAR2(50);
BEGIN

SELECT empname
INTO l_empname
FROM emp
WHERE empno = p_empno;

IF l_empname = 'Sarah Jones' THEN
INSERT INTO empresult values ('105', 'Found Sarah Jones');
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO empresult values(p_empno, 'Did not find Sarah Jones');
END;
/

If you execute the above procedure now, this is what you would see:

SQL> exec test_proc (1)

PL/SQL procedure successfully completed.

SQL> select *
2 from empresult;

EMPNO EMPNAME
--------- --------------------------------------------------
1 Did not find Sarah Jones

As the exception was handled, a row was inserted into the empresult table with the error message you specified.

When the exception is raised, the control jumps from the select statement to the exception handler routine. Any code after the Select will not get executed if an exception has been raised. But if you do have some code that needs to be executed after the select has been executed, irrespective of whether the select was successful or not, then you would need to nest the begin and end statements.

CREATE OR REPLACE PROCEDURE test_proc (p_empno IN NUMBER) IS
l_empname VARCHAR2(50);
l_count NUMBER;
BEGIN
BEGIN
SELECT empname
INTO l_empname
FROM emp
WHERE empno = p_empno;

IF l_empname = 'Sarah Jones' THEN
INSERT INTO empresult values ('105', 'Found Sarah Jones');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO empresult values(p_empno, 'Did not find Sarah Jones');
END;

l_count := 1;
-- More statements
-- And more
END;
/

The procedure has been changed to NEST the BEGIN and END statements. So, if the SELECT statement fails, the control will enter the exception handler and then proceed on to the next line which is l_count := 1 statement.

It is always best to have separate BEGIN and END statements for every SELECT written in your PL/SQL block, which enables you to raise SELECT-senstitive error messages.

Please do not use SQL%NOTFOUND to check for this error. When the NO_DATA_FOUND exception is raised, then the program will look for the Exception handler. If it does not find one, then the error message is thrown to the screen.

[EnjoyLift]

问题的关键在于你没有处理NO_DATA_FOUND异常（只要你的SELECT语句没有数据返回就会引起这个异常），至于是不是SELECT能不能查到数据很容易测试，由于你这个触发器是数据插入后执行的，所以可以在遇到异常后把你的这个触发器禁止，然后执行你的触发器里的SELECT语句看是不是有没有数据返回的SELECT语句，还有你的SQL语句的书写没有缩进，太难看了