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_02AS 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_CURSOROUT PKG_RDS.T_RETDATASET）CREATE OR REPLACE PACKAGE PKG_RDS ASTYPE t_RetDataSet IS REF CURSOR;END pkg_RDS;-- 游标 01CREATE OR REPLACE PROCEDURE PROCEDURE_02 ASCURSOR CUR IS SELECT * FROM LOCK_INFO;BEGINFOR 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 ASV_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;BEGINOPEN 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;--
页:
[1]