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;-- 游标 01CREATE 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;-- 游标 02CREATE 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;--