贴个例子你看一下 CREATE OR REPLACE PROCEDURE PR_AF_MENU_IN" ( ag_lmenu_id IN tsecmenu.lmenu_id%TYPE, ag_mmenu_id IN tsecmenu.mmenu_id%TYPE, ag_insert_date tsecmenu.insert_date%TYPE, ag_insert_id IN tsecmenu.insert_id%TYPE ) IS s_sno tsecpergrade.sno%TYPE; CURSOR cur_sno IS SELECT DISTINCT sno FROM tsecpergrade; BEGIN OPEN cur_sno; LOOP FETCH cur_sno INTO s_sno; EXIT WHEN cur_sno%NOTFOUND; INSERT INTO tsecpergrade (sno, lmenu_id, mmenu_id, mmenu_grade, re, insert_date, insert_id, modify_date, modify_id ) VALUES (s_sno, ag_lmenu_id, ag_mmenu_id, '99', '', ag_insert_date, ag_insert_id, ag_insert_date, ag_insert_id ); END LOOP; CLOSE cur_sno; END; /
这是个很简单的存储过程CURSOR cur_sno IS SELECT DISTINCT sno FROM tsecpergrade;//定义游标begin//存储过程开始 OPEN cur_sno;打开游标
LOOP FETCH cur_sno INTO s_sno;//游标循环赋值给变量 EXIT WHEN cur_sno%NOTFOUND;//游标到头跳出循环 //下面是执行相应的操作 INSERT INTO tsecpergrade (sno, lmenu_id, mmenu_id, mmenu_grade, re, insert_date, insert_id, modify_date, modify_id ) VALUES (s_sno, ag_lmenu_id, ag_mmenu_id, '99', '', ag_insert_date, ag_insert_id, ag_insert_date, ag_insert_id ); END LOOP; CLOSE cur_sno; 关闭游标 end; 存储过程结束
CREATE OR REPLACE PROCEDURE PR_AF_MENU_IN" (
ag_lmenu_id IN tsecmenu.lmenu_id%TYPE,
ag_mmenu_id IN tsecmenu.mmenu_id%TYPE,
ag_insert_date tsecmenu.insert_date%TYPE,
ag_insert_id IN tsecmenu.insert_id%TYPE
)
IS
s_sno tsecpergrade.sno%TYPE; CURSOR cur_sno
IS
SELECT DISTINCT sno
FROM tsecpergrade;
BEGIN
OPEN cur_sno; LOOP
FETCH cur_sno
INTO s_sno; EXIT WHEN cur_sno%NOTFOUND; INSERT INTO tsecpergrade
(sno, lmenu_id, mmenu_id, mmenu_grade, re,
insert_date, insert_id, modify_date, modify_id
)
VALUES (s_sno, ag_lmenu_id, ag_mmenu_id, '99', '',
ag_insert_date, ag_insert_id, ag_insert_date, ag_insert_id
);
END LOOP; CLOSE cur_sno;
END;
/
IS
SELECT DISTINCT sno
FROM tsecpergrade;//定义游标begin//存储过程开始
OPEN cur_sno;打开游标
LOOP
FETCH cur_sno
INTO s_sno;//游标循环赋值给变量 EXIT WHEN cur_sno%NOTFOUND;//游标到头跳出循环
//下面是执行相应的操作
INSERT INTO tsecpergrade
(sno, lmenu_id, mmenu_id, mmenu_grade, re,
insert_date, insert_id, modify_date, modify_id
)
VALUES (s_sno, ag_lmenu_id, ag_mmenu_id, '99', '',
ag_insert_date, ag_insert_id, ag_insert_date, ag_insert_id
);
END LOOP; CLOSE cur_sno; 关闭游标
end; 存储过程结束