TYPE LIST_CURSER is ref cursor;
TEMP_ID_LIST LIST_CURSER;
TEMP_ID TM_PARENT.TEMP_ID%TYPE;
V_QUERY_ID_SQL VARCHAR2(4000);
V_DNS VARCHAR2(20);
V_USER_NAME VARCHAR2(100);
V_STUDENT_ID NUMBER(20);
V_RELATION VARCHAR2(50);
V_LINK_DN VARCHAR2(50);
V_ADDR VARCHAR2(300);
V_REMARK VARCHAR2(300);
V_PARENT_ID NUMBER(20);
V_RETURN NUMBER(20);
begin
V_QUERY_ID_SQL = 'select TEMP_ID from table';
OPEN TEMP_ID_LIST FOR V_QUERY_ID_SQL;
LOOP
exit when TEMP_ID_LIST%notfound;
fetch TEMP_ID_LIST INTO TEMP_ID;
SELECT TCPT.STUDENT_ID, TCPT.DN, TCPT.USER_NAME, TCPT.RELATION,
TCPT.FAMILY_PHONE, TCPT.FAMILY_ADDR,TCPT.REMARK
INTO V_STUDENT_ID,V_DNS,V_USER_NAME,V_RELATION,V_LINK_DN,V_ADDR,V_REMARK
FROM TM_PARENT TCPT
WHERE TCPT.TEMP_ID = TEMP_ID;
p_create_parent(V_STUDENT_ID,V_DNS,V_USER_NAME,V_RELATION,V_LINK_DN,V_ADDR,V_REMARK,V_PARENT_ID,V_RETURN);
V_RETTEXT:= 'V_PARENT_ID = ' || V_PARENT_ID || 'V_RETURN' || V_RETURN;
END LOOP;
CLOSE TEMP_ID_LIST; EXCEPTION
WHEN OTHERS THEN
V_RETTEXT:= '-1';
进入游标里面的SELECT TCPT.STUDENT_ID, TCPT.DN, TCPT.USER_NAME, TCPT.RELATION
就跳入异常了,求指教
1,先检查你的SQL 是否能查询出数据
2. 再你的变量定义的类型和长度是否有问题,
你这样写是肯定一遍历游标就进入异常,一开始聚符合这个条件所以就退出了exit when TEMP_ID_LIST%notfound;[/fetch TEMP_ID_LIST INTO TEMP_ID;
SELECT TCPT.STUDENT_ID, TCPT.DN, TCPT.USER_NAME, TCPT.RELATION,
TCPT.FAMILY_PHONE, TCPT.FAMILY_ADDR,TCPT.REMARK
INTO V_STUDENT_ID,V_DNS,V_USER_NAME,V_RELATION,V_LINK_DN,V_ADDR,V_REMARK
FROM TM_PARENT TCPT
WHERE TCPT.TEMP_ID = TEMP_ID;
p_create_parent(V_STUDENT_ID,V_DNS,V_USER_NAME,V_RELATION,V_LINK_DN,V_ADDR,V_REMARK,V_PARENT_ID,V_RETURN);
V_RETTEXT:= 'V_PARENT_ID = ' || V_PARENT_ID || 'V_RETURN' || V_RETURN;
exit when TEMP_ID_LIST%notfound;
你根本没有明白我的意思 不管TEMP_ID_LIST 都会执行exit when TEMP_ID_LIST%notfound;
首先这个条件TEMP_ID_LIST%notfound就是为真;
OPEN TEMP_ID_LIST FOR V_QUERY_ID_SQL;
LOOP
fetch TEMP_ID_LIST INTO TEMP_ID;
IF TEMP_ID IS NOT NULL AND LENGTH(TEMP_ID) <> 0 THEN
SELECT TCPT.STUDENT_ID, TCPT.DN, TCPT.USER_NAME, TCPT.RELATION,
TCPT.FAMILY_PHONE, TCPT.FAMILY_ADDR,TCPT.REMARK
INTO V_STUDENT_ID,V_DNS,V_USER_NAME,V_RELATION,V_LINK_DN,V_ADDR,V_REMARK
FROM TM_PARENT TCPT
WHERE TCPT.TEMP_ID = TEMP_ID;
p_create_parent(V_STUDENT_ID,V_DNS,V_USER_NAME,V_RELATION,V_LINK_DN,V_ADDR,V_REMARK,V_PARENT_ID,V_RETURN);
V_RETTEXT:= 'V_PARENT_ID = ' || V_PARENT_ID || 'V_RETURN' || V_RETURN;
END IF;
exit when TEMP_ID_LIST%notfound;
END LOOP;
CLOSE TEMP_ID_LIST;
LS大大的意思是这样?
可是我这里可以进到if里面一走到这个select 就跳到了异常
继续求教
SELECT TCPT.STUDENT_ID, TCPT.DN, TCPT.USER_NAME, TCPT.RELATION,
TCPT.FAMILY_PHONE, TCPT.FAMILY_ADDR,TCPT.REMARK
INTO V_STUDENT_ID,V_DNS,V_USER_NAME,V_RELATION,V_LINK_DN,V_ADDR,V_REMARK
FROM TM_PARENT TCPT
WHERE TCPT.TEMP_ID = TEMP_ID and rownum=1;
exit when TEMP_ID_LIST%notfound;
颠倒下.