使用存储过程根据"最后更新"时间同步数据:
create or replace procedure SP_UPDATE_AGE(row_count out number)
as
begin
declare cursor cur is select * from test1 where UPDATE_TIME > (select UPDATE_TIME from update_log);
raw_row test1%rowtype;
begin
for raw_row in cur
loop
insert into test4(content) values(raw_row.content);
row_count := row_count + 1;
end loop;
UPDATE "UPDATE_LOG" SET "UPDATE_TIME" = SYSDATE WHERE "TABLE_NAME" =
'xxxxx';
dbms_output.put_line(row_count);
end;
end SP_UPDATE_AGE;
#存储过程名称后面括号中的参数,可以是 "in" "out" "in out"三种类型,但是,必须有其中一种传参!
oracle中sql变量的声明:
declare [变量名] int;
[变量名] varchar2(100);
#多个变量声明,只需要一个 declare 关键词
Oracle SQL Developer中存储过程的调用方式:
variable c number;
exec SP_UPDATE_AGE(:c);
PL/SQL中调用存储过程:
declare row_count number;
row_count2 number;
begin
test1(row_count);
test2(row_count2);
end;