oracle procedure cursor 例子

--1、if
   IF xx==1 WHEN
     BEGIN
      -- do something ...
     END;
   END IF;

--2、while
   WHILE xx>4 LOOP
      BEGIN
      -- do something
      END;
   END LOOP;

--3、for in - cursor
   CURSOR cur IS SELECT * FROM tableName ;
   - -
   ...
   IS
   CURSOR cur IS SELECT * FROM xxtable;
   BEGIN
      FOR cur_result IN cur LOOP
        BEGIN
            v_sum := cur_result.colName1 + cur_result.colName2;
        END;
      END LOOP;
   END;
   --
   CURSOR c_user(c_id NUMBER) IS SELECT NAME FROM xxtable WHERE xxx=c_id;
   OPEN c_user(变量值)
   LOOP
      FETCH c_user INTO v_name;
      EXIT FETCH c_user%NOTFOUND;
      --dosomething ...
   END LOOP;

--4、select ... into...
   BEGIN
     SELECT col1,col2 INTO xx1,xx2 FROM xxtable WHERE xxx ;
   EXCEPTION
   WHEN no_data_found THEN
        xxx
   END;
--5、test
/**
   *******************************************************
*/
-- 1.清空临时表 lock_test 数据 
-- CREATE TABLE lock_test AS SELECT * FROM lock_;.
CREATE OR REPLACE PROCEDURE procedure_02
  AS
      CURSOR cur SELECT * FROM LOCK_INFO;
  BEGIN
      FOR cur_result IN cur LOOP
          BEGIN
               dbms_output.put_line(cur_result.id||cur_result.lock_remark);
          END;
      END LOOP;
  END;
-- package 放返回游标 （ REF_CURSOR  OUT PKG_RDS.T_RETDATASET）
  CREATE OR REPLACE PACKAGE PKG_RDS AS
  TYPE t_RetDataSet IS REF CURSOR;
  END pkg_RDS;
-- 游标 01
CREATE OR REPLACE PROCEDURE PROCEDURE_02
 AS
  CURSOR CUR IS
    SELECT * FROM LOCK_INFO;
BEGIN
  FOR CUR_RESULT IN CUR LOOP
    INSERT INTO lock_test (id)VALUES(CUR_RESULT.id);
    COMMIT;
  END LOOP;
END PROCEDURE_02;
-- 游标 02
CREATE OR REPLACE PROCEDURE PROCEDURE_03 AS
  V_ID   LOCK_INFO.ID%TYPE;
  V_NAME LOCK_INFO.LOCK_REMARK%TYPE;
  CURSOR CUR IS
    SELECT L.ID, L.LOCK_REMARK FROM LOCK_INFO L;
BEGIN
  OPEN CUR;
  LOOP
    FETCH CUR
      INTO V_ID, V_NAME;
    EXIT WHEN CUR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('insert into ..' || V_ID || ' - ' || V_NAME);
    INSERT INTO LOCK_TEST (ID, LOCK_REMARK) VALUES (V_ID, V_NAME);
    COMMIT;
  END LOOP;
END PROCEDURE_03;

--