oracle基础知识积累-创建Packages和Packages bodies

场景:
    基础知识查询
动机:
    纯粹基础知识积累,便于后续查询.在很多场合,负责人们都在谈论去IOE话题,
所以,难免使用场合会或多或少会减少,于是按照习惯的思路记录一下.
环境: 
    Oracle Database 11g; PL/SQL Developer
1.功能
    创建Packages和Packages bodies是为了更好管理存储过程
2.创建Packages和Packages bodies

--1.创建包(Packages)
CREATE OR REPLACE PACKAGE PKG_ZBZ_PORTAL IS
END PKG_ZBZ_PORTAL;
--2.创建包体(Packages bodies)
CREATE OR REPLACE PACKAGE BODY PKG_ZBZ_PORTAL IS		
END PKG_ZBZ_PORTAL;

3.在Packages添加存储过程声明和在Packages bodies添加实现
3.1在Packages中的代码

--包存储过程定义
PROCEDURE PRO_INSERT_INFO_ARGS(IN_DATE IN VARCHAR2 := NULL,
                           FLAG OUT NUMBER,
                           FAIL_INFO OUT VARCHAR2);

3.2在Packages bodies中代码

--包体存储过程实现
PROCEDURE PRO_INSERT_INFO_ARGS(IN_DATE IN VARCHAR2 := NULL,
                           FLAG OUT NUMBER,
                           FAIL_INFO OUT VARCHAR2) IS
BEGIN
  DECLARE
    V_DATE DATE := TRUNC(SYSDATE - 1);
  BEGIN
    FLAG := 1;
	FAIL_INFO :='';
    IF (IN_DATE IS NOT NULL) THEN
      V_DATE := TO_DATE(IN_DATE, 'YYYYMMDD');
    END IF;  
    INSERT INTO B_LOG_INFO
      SELECT SEQ_DEMO_ID.NEXTVAL,
             V_DATE,
             SYSDATE,
             '1',
             '执行成功',
             50,
             'DUAL'
        FROM DUAL;
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
	  FLAG := 0;
	  FAIL_INFO := '此次执行: ' || IN_DATE || '输出结果: FLAG = ' || FLAG ||
	          '异常信息:  ' || SQLERRM;
      ROLLBACK;
  END;
END PRO_INSERT_INFO_ARGS;

3.3Packages完整代码

--完整包头:
CREATE OR REPLACE PACKAGE PKG_ZBZ_PORTAL IS
  PROCEDURE PRO_INSERT_INFO_ARGS(IN_DATE   IN VARCHAR2 := NULL,
                                 FLAG      OUT NUMBER,
                                 FAIL_INFO OUT VARCHAR2);
END PKG_ZBZ_PORTAL;

3.4Packages bodies完整代码

--完整包体
CREATE OR REPLACE PACKAGE BODY PKG_ZBZ_PORTAL IS
  PROCEDURE PRO_INSERT_INFO_ARGS(IN_DATE   IN VARCHAR2 := NULL,
                                 FLAG      OUT NUMBER,
                                 FAIL_INFO OUT VARCHAR2) IS
  BEGIN
    DECLARE
      V_DATE DATE := TRUNC(SYSDATE - 1);
    BEGIN
      FLAG      := 1;
      FAIL_INFO := '';
      IF (IN_DATE IS NOT NULL) THEN
        V_DATE := TO_DATE(IN_DATE, 'YYYYMMDD');
      END IF;
      INSERT INTO B_LOG_INFO
        SELECT SEQ_DEMO_ID.NEXTVAL,
               V_DATE,
               SYSDATE,
               '1',
               '执行成功',
               50,
               'DUAL'
          FROM DUAL;
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        FLAG      := 0;
        FAIL_INFO := '此次执行: ' || IN_DATE || ' 输出结果: FLAG = ' || FLAG ||
                     '异常信息:  ' || SQLERRM;
        ROLLBACK;
    END;
  END PRO_INSERT_INFO_ARGS;
END PKG_ZBZ_PORTAL;

4.创建前Packages和Packages bodies目录
 
5.创建后Packages和Packages bodies目录
 
6.选中测试
 
7.输入测试参数
 
8.运行成功结果
 
9.抛出异常结果
 
10.配置存储过程选择jobs目录配置

11.任务状况查看

12.配置job的sql

begin
  sys.dbms_scheduler.create_job(job_name            => 'TEST_DEMO.DEMO_TEST',
                                job_type            => 'PLSQL_BLOCK',
                                job_action          => 'Declare  FLAG Number(20);FAIL_INFO   Varchar2(2000) ;Begin PKG_ZBZ_PORTAL.PRO_INSERT_INFO_ARGS(To_Char(Sysdate - 1,''YYYYMMDD''),FLAG,FAIL_INFO);End;',
                                start_date          => to_date('25-08-2019 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                                repeat_interval     => 'Freq=Minutely;Interval=1',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => false,
                                comments            => '');
end;

以上,感谢.