oracle procedure cursor 例子

nedrukxhxl

--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;
--