主页 / coding / 问题 / 79036495
Accepted
Bob
Asked: 2024-09-29 21:31:51 +0800 CST

DBMS_JOB.SUBMIT 带有 INSERT 语句

  • 772

我正在尝试使用DBMS_JOB.SUBMITINSERT 语句来实现该过程，但无法成功地将数据从一个表传输到另一个表。

DECLARE
    l_job NUMBER;
 BEGIN
    DBMS_JOB.SUBMIT(
       job       => l_job,
       what      => 'BEGIN insert into customers_tbl1 select * from customers_tbl; 
 END;'--,
     --  next_date => SYSDATE--, -- Schedule for 1 hour from now
       --interval  => 'SYSDATE'   -- Reschedule every day
    );
   COMMIT; -- Important to commit the transaction to schedule the job
 END;

表格数据：

    CREATE TABLE customers_tbl 
    ( customer_id number(10),
      customer_name varchar2(50),
      city varchar2(50)
    );
    insert into customers_tbl(customer_id,customer_name,city) values(1,'Albert','Munich');
    insert into customers_tbl(customer_id,customer_name,city) values(2,'Alex','Stuttgart');
    insert into customers_tbl(customer_id,customer_name,city) values(3,'Sasuke','Tokyo');
    
    CREATE TABLE customers_tbl1 
    ( customer_id number(10),
      customer_name varchar2(50),
      city varchar2(50)
    );
oracle19c

1 个回答

  1. Best Answer

    what参数需要适当的 PL/SQL 块并以分号(BEGIN ... END;)终止语句。INSERT

    修正代码：

    DECLARE
    l_job NUMBER;
BEGIN
    DBMS_JOB.SUBMIT(
        job       => l_job,
        what      => 'BEGIN insert into customers_tbl1 select * from customers_tbl; END;',
        next_date => SYSDATE,       -- Schedule for immediate execution
        interval  => SYSDATE + 1    -- Repeats daily
    );
    COMMIT; -- Commit the transaction to schedule the job
END;
/

    如果您想要安排从现在起一小时的计划，则可以使用SYSDATE + 1/24next_date 。

    验证数据是否已传输：

    SELECT * FROM customers_tbl1;

    监控你的工作状态：

    SELECT job, what, next_date, broken
FROM user_jobs;
    • 0

相关问题