ORACLE存储过程sample

create or replace procedure ET(p1 in number,p2 in number , m out number) is
begin
--sql_str := 'insert into TABLE1(LKEY,TKBN) values(111,"758")' ;
-- EXECUTE IMMEDIATE sql_str;
DECLARE
v_title varchar(100);
l_count number ;
--定义游标
l_table1 WL.TABLE1%rowtype;
CURSOR cur_table1(vID number) is
SELECT * FROM WL.TABLE1 where table1.lkey = vID;

BEGIN
--打开游标方法一
/*
OPEN cur_table1(1) ;
--fetch cur_table1 into l_table1 ;

while cur_table1% found loop
l_count := l_count +1 ;
--比较
INSERT INTO TABLE2(ID,TXT) VALUES (l_table1.LKEY, l_table1.TKNB);
commit;

fetch cur_table1 into l_table1 ;
end loop;
*/
-- vs_ym_sn_beg :=
TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'), -12),'yyyymm');

FOR rec IN cur_table1(1) LOOP
INSERT INTO TABLE2(ID,TXT,DT)
VALUES (rec.LKEY, rec.TKNB,to_char(SYSDATE,'yymmdd'));
commit;

l_count := l_count +1 ;
dbms_output.put_line('插入记录数:'||l_count);
END LOOP;
CLOSE cur_table1;
--dbms_sql.close_cursor(cur_table1); --关闭游标;

END;

EXCEPTION
WHEN OTHERS THEN
BEGIN
dbms_output.put_line(SQLERRM);
rollback;
END;

end ET;
create or replace procedure ET(p1 in number,p2 in number , m out number) is
begin
--sql_str := 'insert into TABLE1(LKEY,TKBN) values(111,"758")' ;
-- EXECUTE IMMEDIATE sql_str;
DECLARE
v_title varchar(100);
l_count number ;
--定义游标
l_table1 WL.TABLE1%rowtype;
CURSOR cur_table1(vID number) is
SELECT * FROM WL.TABLE1 where table1.lkey = vID;

BEGIN
--打开游标方法一
/*
OPEN cur_table1(1) ;
--fetch cur_table1 into l_table1 ;

while cur_table1% found loop
l_count := l_count +1 ;
--比较
INSERT INTO TABLE2(ID,TXT) VALUES (l_table1.LKEY, l_table1.TKNB);
commit;

fetch cur_table1 into l_table1 ;
end loop;
*/
-- vs_ym_sn_beg :=
TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'), -12),'yyyymm');

FOR rec IN cur_table1(1) LOOP
INSERT INTO TABLE2(ID,TXT,DT)
VALUES (rec.LKEY, rec.TKNB,to_char(SYSDATE,'yymmdd'));
commit;

l_count := l_count +1 ;
dbms_output.put_line('插入记录数:'||l_count);
END LOOP;
CLOSE cur_table1;
--dbms_sql.close_cursor(cur_table1); --关闭游标;

END;

EXCEPTION
WHEN OTHERS THEN
BEGIN
dbms_output.put_line(SQLERRM);
rollback;
END;

end ET;