将脚本转换为dbms_scheduler的PL / SQL过程

朱拜尔·艾哈迈德（Jubair Ahmed）

我对SQL相当陌生。我正在尝试将已有的脚本转换为存储过程，以便可以运行：

dbms_scheduler.create_job (job_type => 'STORED_PROCEDURE')

调度程序。

这是我的SQL：

insert into EBA_PROJ_STATUS_HEALTH (DATESTAMP, WEEK, PROJECT_HEALTH, PROJECT_NAME, PROJECT_ID)
select
TO_DATE (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "DateStamp",
TO_CHAR (SYSDATE, 'WW') "WeekStamp",
decode (PROJECT_STATUS, '26080667600194118828749753718250690144', 'Red', '26080667600198954532028212234949514848', 'Green', '2608066760019489567412685398756201486', 'Yellow', '32151851918961403472745066957057582129', 'Terminated') Health,
PROJECT,
ID
from
EBA_PROJ_STATUS

拉利特·库马尔（Lalit Kumar B）

创建过程如下：

CREATE OR REPLACE
PROCEDURE p_ins_EBA_PROJ_STATUS_HEALTH
AS
BEGIN
  INSERT
  INTO EBA_PROJ_STATUS_HEALTH
    (
      DATESTAMP,
      WEEK,
      PROJECT_HEALTH,
      PROJECT_NAME,
      PROJECT_ID
    )
  SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "DateStamp",
    TO_CHAR (SYSDATE, 'WW') "WeekStamp",
    DECODE (PROJECT_STATUS, '26080667600194118828749753718250690144', 'Red', '26080667600198954532028212234949514848', 'Green', '2608066760019489567412685398756201486', 'Yellow', '32151851918961403472745066957057582129', 'Terminated') Health,
    PROJECT,
    ID
  FROM EBA_PROJ_STATUS;
END;
/

您可以在过程中添加参数，以避免使用硬编码的值。

创建作业并按以下方式内联计划：

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'insert into EBA_PROJ_STATUS_HEALTH',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN p_ins_EBA_PROJ_STATUS_HEALTH; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
    enabled         => TRUE);
END;
/

顺便说一句，

永远不要在DATE上使用TO_DATE，它将隐式地将其转换为字符串，然后使用特定于语言环境的NLS格式恢复为最新。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。