1、过程主体
--存储过程循环 ,SELECT INTO 是隐式游标只能返回一行 CREATE OR REPLACE PROCEDURE P_LOOP_PR ( P_TIME NUMBER) IS A_OVER NUMBER; A_NAME VARCHAR2(20); BEGIN FOR I IN 1..10 LOOP SELECT A.PRODUCT_NUM*A.PRODUCT_NUM,A.PRODUCT_NAME INTO A_OVER,A_NAME FROM PRODUCT_RELEASE A WHERE A.PUT_TIME=P_TIME ORDER BY A.PRODUCT_ID; DBMS_OUTPUT.PUT_LINE('商品名称:'||A_NAME||','||'商品总价:'||A_OVER); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND'); END P_LOOP_PR;
2、调用
--调用 DECLARE BEGIN P_LOOP_PR('20181220'); END;
3、存储过程用游标实现返回多行
----存储过程,游标实现 要求要返回多行, CREATE OR REPLACE PROCEDURE P_LOOP_PR ( P_TIME NUMBER) IS A_OVER NUMBER; A_NAME VARCHAR2(20); CURSOR PL_CURSOR IS SELECT A.PRODUCT_NUM*A.PRODUCT_NUM,A.PRODUCT_NAME FROM PRODUCT_RELEASE A WHERE A.PUT_TIME=P_TIME ORDER BY A.PRODUCT_ID; BEGIN OPEN PL_CURSOR; FOR I IN 1..8 LOOP FETCH PL_CURSOR INTO A_OVER,A_NAME; DBMS_OUTPUT.PUT_LINE('商品名称:'||A_NAME||','||'商品总价:'||A_OVER); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND'); END P_LOOP_PR;