cursor c1 is
select distinct a.partno
from stock_transaction a, item_master b
where a.partno=b.partno
and b.item_type='G'
and b.group_code in ('1','2')
and to_char(a.create_date,'yyyymm')=:yymm;
for c in cl loop
begin
SELECT SUM(A.ORD_QTY) into hua_qty
FROM SO_MASTER B,SO_DETAIL A
WHERE A.SO_NO=B.SO_NO
AND B.CUSTOMER='00031'
AND TO_CHAR(B.ISSUE_DATE,'YYYYMM')=:yymm
and partno=c.partno;
exception when no_data_found then
hua_qty:=0;
end;
end loop;
简单的说明一下,上面声明了一个游标,然后再根据这个游标循环,由于在下面的这个表格中可能存在不符合该条件的值,所以就加上了
exception when no_data_found 这个语句,但现在的问题是就是符合该条件的值也是等于0了,请问这是怎么回事.
select distinct a.partno
from stock_transaction a, item_master b
where a.partno=b.partno
and b.item_type='G'
and b.group_code in ('1','2')
and to_char(a.create_date,'yyyymm')=:yymm;
for c in cl loop
begin
SELECT SUM(A.ORD_QTY) into hua_qty
FROM SO_MASTER B,SO_DETAIL A
WHERE A.SO_NO=B.SO_NO
AND B.CUSTOMER='00031'
AND TO_CHAR(B.ISSUE_DATE,'YYYYMM')=:yymm
and partno=c.partno;
exception when no_data_found then
hua_qty:=0;
end;
end loop;
简单的说明一下,上面声明了一个游标,然后再根据这个游标循环,由于在下面的这个表格中可能存在不符合该条件的值,所以就加上了
exception when no_data_found 这个语句,但现在的问题是就是符合该条件的值也是等于0了,请问这是怎么回事.
cursor c1 is
select distinct a.partno
from stock_transaction a, item_master b
where a.partno=b.partno
and b.item_type='G'
and b.group_code in ('1','2')
and to_char(a.create_date,'yyyymm')=:yymm;
for c in cl loop
begin
SELECT SUM(A.ORD_QTY) into hua_qty
FROM SO_MASTER B,SO_DETAIL A
WHERE A.SO_NO=B.SO_NO
AND B.CUSTOMER='00031'
AND TO_CHAR(B.ISSUE_DATE,'YYYYMM')=:yymm
and partno=c.partno;
end;
end loop;
exception when no_data_found then
hua_qty:=0;
cursor c1 is
select distinct a.partno
from stock_transaction a, item_master b
where a.partno=b.partno
and b.item_type='G'
and b.group_code in ('1','2')
and to_char(a.create_date,'yyyymm')=:yymm;
begin--过程/函数体开始处
for c in cl loop
SELECT SUM(A.ORD_QTY) into hua_qty
FROM SO_MASTER B,SO_DETAIL A
WHERE A.SO_NO=B.SO_NO
AND B.CUSTOMER='00031'
AND TO_CHAR(B.ISSUE_DATE,'YYYYMM')=:yymm
and partno=c.partno;
end loop;
exception when no_data_found then
hua_qty:=0;
end;--过程/函数体结束处
FOR i IN (1..5) LOOP
如果中间产生异常如何继续循环,而不是碰到异常就退出?
END LOOP;
--------实际应用代码功能如下------------------
declare
str_sql varchar2(2000);
cursor get_obj is
select object_type, object_name from USER_OBJECTS
where status = 'INVALID'
and object_type in('PACKAGE BODY','VIEW');
begin
for rec_ in get_obj loop
if (rec_.object_type = 'VIEW') then
str_sql := 'ALTER VIEW "'|| rec_.object_name ||'" COMPILE';
else
str_sql := 'ALTER PACKAGE "'|| rec_.object_name ||'" COMPILE BODY';
end if;
execute immediate str_sql;
---本来我想把Exception放到这个位置的,结果执行时就是出错
end loop;
EXCEPTION WHEN OTHERS THEN--放在这不出错但不是我想要的效果,只要中间有一个异常整体
--结束了,我是想要碰到异常继续下一个循环而不是退出.
dbms_output.put_line(sqlerrm);
end;
建议用if is null 判断;
yhcustc 的问题我改了一下看看行么,基本skip机能用块来实现。
DECLARE
STR_SQL VARCHAR2(2000);
CURSOR GET_OBJ IS
SELECT OBJECT_TYPE, OBJECT_NAME
FROM USER_OBJECTS
WHERE STATUS = 'INVALID '
AND OBJECT_TYPE IN ('PACKAGE BODY ', 'VIEW ');
BEGIN
FOR REC_ IN GET_OBJ LOOP
BEGIN
IF (REC_.OBJECT_TYPE = 'VIEW ') THEN
STR_SQL := 'ALTER VIEW " ' || REC_.OBJECT_NAME || ' " COMPILE ';
ELSE
STR_SQL := 'ALTER PACKAGE " ' || REC_.OBJECT_NAME ||
' " COMPILE BODY ';
END IF;
EXECUTE IMMEDIATE STR_SQL;
---本来我想把Exception放到这个位置的,结果执行时就是出错
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
--放在这不出错但不是我想要的效果,只要中间有一个异常整体
--结束了,我是想要碰到异常继续下一个循环而不是退出.
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;