ORACLE之存储过程PROCEDURE

用到游标的存储过程

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;