ORACLE之存储过程PROCEDURE

娅宁2959

用到游标的存储过程

CREATE OR REPLACE PROCEDURE P_TEST
IS
--带参数的显示游标
CURSOR C_TEST(TEST_STS VARCHAR2) IS
SELECT T.ID, T.NAME
FROM TABLENAME T
WHERE T.STS=TEST_STS;
--声明对应游标结果的变量
V_TEST_ID TABLENAME.ID%TYPE;
V_TEST_NAME TABLENAME.NAME%TYPE;
--自定义变量
V_STS_A CHAR(1);
--程序开始
BEGIN
--赋值
V_STS_A := 'A';
--打开游标
OPEN C_ORDER(V_STS_A);
--循环
LOOP
--将查询出来的记录值放到变量里边
FETCH C_ORDER
INTO V_TEST_ID, V_TEST_NAME;
--判断是否跳出循环
EXIT WHEN C_ORDER%NOTFOUND;
INSERT INTO OTHERTABLE(ID, TABLENAME_ID, NAME)
VALUES(OTHERTABLE_ID_SEQ.NEXTVAL, V_TEST_ID, V_TEST_NAME);
END LOOP;  
CLOSE C_ORDER;
END;