ORACLE之存储过程PROCEDURE
用到游标的存储过程CREATE OR REPLACE PROCEDURE P_TESTIS--带参数的显示游标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;
页:
[1]