Oracle数据库 PL/SQL存储过程

PL/SQL存储过程（Procedures)，可以动态进行sql语句的执行，比如说我们要定时去向数据库插入数据，我们可以写存储过程，然后用定时器去触发执行。

一、PL/SQL子程序的部分

每个PL/SQL子程序都有一个名称，也可能有一个参数列表。 像匿名PL/SQL块一样，命名块也将具有以下三个部分

编号	部分	描述
1	声明部分	这是一个可选的部分。 但是，子程序的声明部分不以DECLARE关键字开头。 它包含类型，游标， 常量，变量，异常和嵌套子程序的声明。这些项是本 子程序，当子程序完成执行时，它们将不复存在
2	可执行部分	这是一个强制性部分(必须有)，并包含执行指定操作的语句。
3	异常处理	这是一个可选的部分。它包含处理运行时错误的代码。

二. 创建存储过程

语法

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name;

其中

• procedure-name是要创建的存储过程的名称。
• [OR REPLACE]选项允许修改现有的过程。
• 可选参数列表包含参数的名称，模式和类型。IN表示将从外部传递的值，OUT表示将用于返回过程外的值的参数。
• procedure-body包含可执行部分。 使用AS关键字而不是IS关键字来创建存储过程。

示例 (从USER表导入前一天的数据到USER_ALL表)

create or replace procedure USER_ALL_PRO is

  CURSOR CUR IS
   select t.user_id,t.gr_jbxx_id,t.xm,t.zjlxdm,t.zjhm,t.csrq,t.sjhm
    from USER t 
    where to_char ( to_date ( t.xt_xgsj , 'yyyy-MM-dd HH24:mi:ss' ) , 'yyyy-MM-dd') = to_char( sysdate-1 , 'yyyy-MM-dd')
    order by t.xt_xgsj desc ;
begin
  FOR NROW IN CUR LOOP   
        INSERT INTO USER_ALL_PRO 
        (user_id,gr_jbxx_id,xm,zjlxdm,zjhm,csrq,sjhm)
      VALUES
        (NROW.user_id,NROW.gr_jbxx_id,NROW.xm,NROW.zjlxdm,NROW.zjhm,NROW.csrq);
  END LOOP;
  COMMIT;
end USER_ALL_PRO ;

三、执行存储过程

在PL/SQL顶部菜单中，新建–>命令窗口（command window）
执行 exec USER_ALL_PRO ();
按回车，如果显示以下信息，则存储过程执行成功

SQL> exec USER_ALL_PRO();

PL/SQL procedure successfully completed

查询存储过程定时任务

SELECT JOB, WHAT, NEXT_DATE, INTERVAL FROM DBA_JOBS WHERE WHAT LIKE '%USER_ALL_PRO %';

四、定时执行存储过程

在 Oracle 数据库中，定时执行存储过程可通过 ​​DBMS_SCHEDULER​​（推荐）或 ​​DBMS_JOB​​（旧版）实现。以下是两种方法的完整操作指南，包含创建、管理和验证步骤：

1.使用 DBMS_JOB（旧版兼容方法）

（1）创建存储过程
如上面创建的 存储过程 USER_ALL_PRO

（2）提交定时任务​

DECLARE
  v_job_num NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
    job       => v_job_num,
    what      => 'USER_ALL_PRO ;', --存储过程名称
    next_date => SYSDATE,
    interval  => 'SYSDATE + 1' -- 每天执行一次
  );
  COMMIT;
END;

（3） ​​管理任务​​

• ​​查看任务列表​​：

SELECT JOB, WHAT, NEXT_DATE, INTERVAL FROM DBA_JOBS WHERE WHAT LIKE '%USER_ALL_PRO %';

• 立即运行任务​​：

BEGIN
  DBMS_JOB.RUN(v_job_num);
END;

• ​​删除任务​​：

BEGIN
  DBMS_JOB.REMOVE(v_job_num);
END;
/

​2、使用 DBMS_SCHEDULER （Oracle 12c+ 推荐）​

（1） ​​创建存储过程​​

CREATE OR REPLACE PROCEDURE clean_old_data AS
BEGIN
  DELETE FROM log_table WHERE log_date < SYSDATE - 30;
  COMMIT;
END clean_old_data;

2. ​​创建定时任务​​

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'DAILY_CLEAN_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'clean_old_data', -- 存储过程名称
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0', -- 每天凌晨2点执行
    enabled         => TRUE,
    comments        => '每日清理30天前的日志'
  );
END;

（3） ​​管理任务​​

• 查看任务状态​​：

SELECT JOB_NAME, ENABLED, NEXT_RUN_DATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'DAILY_CLEAN_JOB';

• 禁用/启用任务​​：

BEGIN
  DBMS_SCHEDULER.DISABLE('DAILY_CLEAN_JOB'); -- 禁用
  DBMS_SCHEDULER.ENABLE('DAILY_CLEAN_JOB');  -- 启用
END;

• 删除任务​​：

BEGIN
  DBMS_SCHEDULER.DROP_JOB('DAILY_CLEAN_JOB', TRUE); -- TRUE 表示强制删除
END;