DECLARE
TYPE CURRENT_INV_TYPE IS RECORD(
CURRENT_ID TEST2.C1%TYPE,
CURRENT_NAME TEST2.C2%TYPE,
CURRENT_DATE TEST3.BIRTHDAY%TYPE);
CURRENT_RECORD CURRENT_INV_TYPE;
TYPE SETTLE_TYPE IS TABLE OF TEST2%ROWTYPE;
V_SS SETTLE_TYPE;
V_MIN_WEIGHT NUMBER := 2.514;
V_EXIT NUMBER := 11;
BEGIN
--如果查询不到数据 into 处会报 no data found 异常 ,如果查询到多条数据会报ORA-01422 exact fetch returns more than requested number of rows异常
SELECT T.C1, T.C2, T.C3
INTO CURRENT_RECORD
FROM TEST2 T
WHERE ROWNUM = 1;
DBMS_OUTPUT.put_line('input:' || SQL%ROWCOUNT);
IF CURRENT_RECORD.CURRENT_ID = 11 THEN
DBMS_OUTPUT.put_line('input:' || CURRENT_RECORD.CURRENT_ID);
END IF;
--此处into前的列数必须和v_ss 的列数相同
SELECT T.C1, T.C2, SYSDATE BULK COLLECT INTO V_SS FROM TEST2 T;
FOR R IN V_SS.FIRST .. V_SS.LAST LOOP
IF V_MIN_WEIGHT < V_SS(R).C1 THEN
DBMS_OUTPUT.put_line('MIN_WEIGHT:' || V_SS(R).C1);
END IF;
DBMS_OUTPUT.put_line('Loop!');
IF V_EXIT = V_SS(R).C1 AND V_SS(R).C2 = 'S' THEN
DBMS_OUTPUT.put_line('退出!');
EXIT; --退出循环
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLCODE || ':' || SQLERRM);
ROLLBACK;
END;