Oracle使用package来封装存储过程或函数(Function)

一.包的组成介绍

• 包头(package)：包头部分申明包内数据类型，常量，变量，游标，子程序和异常错误处理，这些元素为包的公有元素。
• 包主体(package body)：包主体则是包定义部分的具体实现，它负责为包头中所声明子程序提供具体的实现，在包主体中还可以声明包的私有元素。
• 包头和包主体分开编译，并作为两个分开的对象分别存放在数据库字典中。

二.Oracle包与Java接口的比较

      可以这样理解，把PACKAGE包头理解为JAVA中的接口，把PACKAGE BODY理解为JAVA中实现这个接口的类，而里面的存储过程和函数就相当于类中的方法了。调用执行直接在存储过程中以(包名.存储过程/函数)即可。也方便Java程序直接调用。

三.开发示例

3.1 封装单一存储过程的包

  3.1.1 包

CREATE OR REPLACE PACKAGE MES_APP_PKG IS
  TYPE MESA_CURSOR IS REF CURSOR;
  -- Author  : LINHAIY
  -- Created : 2019-05-24 9:42:08
  -- Purpose : Used for mes4 app kpis

  -- Public type declarations
  /*
  type <TypeName> is <Datatype>;
  
  -- Public constant declarations
  <ConstantName> constant <Datatype> := <Value>;
  
  -- Public variable declarations
  <VariableName> <Datatype>;
  
  -- Public function and procedure declarations
  function <FunctionName>(<Parameter> <Datatype>) return <Datatype>;
  */
  --user login 
  PROCEDURE MESA_UER_LOGIN(P_USER_NAME VARCHAR2, P_PASSWORD VARCHAR2, X_STATUS OUT VARCHAR2, X_CURSOR OUT MESA_CURSOR);
  
  PROCEDURE MESA_GET_OLOFL_AMOUNT_P(P_LINE_ID NUMBER, X_ON_AMOUNT OUT NUMBER, X_OFF_AMOUNT OUT NUMBER);
  --PROCEDURE MESA_GET_OLOFL_AMOUNT_P(P_LINE_ID NUMBER, X_CURSOR OUT MESA_CURSOR);
  
  PROCEDURE MESA_PLAN_P(P_DATE VARCHAR2);

  PROCEDURE MESA_KPI_1001_P(P_DATE VARCHAR2);
  
  
  --驱动函数
  PROCEDURE DRIVE_FUNC;
END MES_APP_PKG;

3.1.2 包的主体(实现)

CREATE OR REPLACE PACKAGE BODY MES_APP_PKG IS

  -- Private type declarations
  --type <TypeName> is <Datatype>;

  -- Private constant declarations
  /*
  <ConstantName> constant <Datatype> := <Value>;
  
  -- Private variable declarations
  <VariableName> <Datatype>;
  
  -- Function and procedure implementations
  function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is
    <LocalVariable> <Datatype>;
  begin
    <Statement>;
    return(<Result>);
  end;
  */
  --begin
  -- Initialization
  --<Statement>;
  PROCEDURE MESA_JOB_LOG_P(P_DATE VARCHAR2, P_MODULE VARCHAR2, P_MSG VARCHAR2) IS 
    BEGIN
      INSERT INTO MESA_JOB_LOG(EXEC_DATE, FUNC_NAME, ERR_MESSAGE)
      VALUES(P_DATE, P_MODULE, P_MSG);
      COMMIT;
    END MESA_JOB_LOG_P;
  --user login 
  /*MESA_UER_LOGIN:  IF LOGIN FAILED: X_STATUS=0 X_CURSOR=NULL;
                     IF LOGIN SUCCESS: X_STATUS=1 X_XURSOR = RESULT_SET
  */
  PROCEDURE MESA_UER_LOGIN(P_USER_NAME VARCHAR2, P_PASSWORD VARCHAR2, X_STATUS OUT VARCHAR2, X_CURSOR OUT MESA_CURSOR) IS
    BEGIN
      X_STATUS := 0 ;
      BEGIN
        SELECT COUNT(*) INTO X_STATUS FROM SYS_USER U 
         WHERE U.LOGIN_NAME = P_USER_NAME
           AND U.PASSWORD = P_PASSWORD;
      EXCEPTION
        WHEN OTHERS THEN
          X_STATUS :=0;
      END ;
      IF X_STATUS = 0 THEN
        --LOGIN FIALED 
        OPEN X_CURSOR FOR SELECT NULL FROM DUAL;
        RETURN;
      ELSE
        X_STATUS := 1;
        OPEN X_CURSOR FOR 
          SELECT DISTINCT U.LOGIN_NAME, RR.RESOURCE_ID, RR.RESOURCE_NAME, RR.KEY_TYPE
            FROM SYS_ROLE_RESOURCES RR, SYS_USER_ROLE R, SYS_USER U
           WHERE U.ID = R.USER_ID
             AND R.ROLE_ID = RR.ROLE_ID
             AND U.LOGIN_NAME = P_USER_NAME;
      END IF;
    END MESA_UER_LOGIN;
  
  PROCEDURE MESA_GET_OLOFL_AMOUNT_P(P_LINE_ID NUMBER, X_ON_AMOUNT OUT NUMBER, X_OFF_AMOUNT OUT NUMBER) IS 
  /*PROCEDURE MESA_GET_OLOFL_AMOUNT_P(P_LINE_ID NUMBER, X_CURSOR OUT MYCURSOR) IS 
      X_ON_AMOUNT NUMBER :=0;
      X_OF_AMOUNT NUMBER :=0;*/
    BEGIN
      --当前上线数量
      BEGIN
      SELECT NVL(COUNT(DISTINCT PPS.PRODUCT_CODE),0) INTO X_ON_AMOUNT
        FROM pln.PLN_PRODUCT_STATUS PPS
       WHERE PPS.LINE_ID = P_LINE_ID --200000359 --(按照所选生产线查询)
         AND PPS.STATUS_CODE = '1010'
         AND PPS.STATUS_DATE >= TRUNC(SYSDATE) 
         AND PPS.STATUS_DATE < TRUNC(SYSDATE) + 1;
      EXCEPTION 
        WHEN OTHERS THEN
          X_ON_AMOUNT := 0 ;
      END ;
      --当前完工数量
      BEGIN
      SELECT NVL(COUNT(DISTINCT PPS.PRODUCT_CODE),0) INTO X_OFF_AMOUNT
        FROM pln.PLN_PRODUCT_STATUS PPS
       WHERE PPS.LINE_ID = P_LINE_ID --200000359 --(按照所选生产线查询)
         AND PPS.STATUS_CODE = '1070'
         AND PPS.STATUS_DATE >= TRUNC(SYSDATE) 
         AND PPS.STATUS_DATE < TRUNC(SYSDATE) + 1;
      EXCEPTION
        WHEN OTHERS THEN
          X_OFF_AMOUNT := 0;
      END ;
      --OPEN X_CURSOR FOR
      --     SELECT X_ON_AMOUNT CURRENT_ONLINE_AMOUNT, X_OFF_AMOUNT CURRENT_OFFLINE_AMOUNT FROM DUAL;
    END MESA_GET_OLOFL_AMOUNT_P;
    
  PROCEDURE MESA_PLAN_OFL_AMT_YM_P(P_DATE VARCHAR2, P_TYPE VARCHAR2) IS
    V_DATE_START DATE;
    V_DATE_END   DATE;
  BEGIN
    V_DATE_END := TO_DATE(P_DATE, 'YYYY-MM-DD') - 1;
    IF P_TYPE = 'M' THEN
      V_DATE_START := TRUNC(TO_DATE(P_DATE, 'YYYY-MM-DD'), 'MM');
      MERGE INTO MESA_KPI_PLANS MKP
      USING (SELECT PPS.LINE_ID,
                    P_DATE S_DATE,
                    --TRUNC(PPS.STATUS_DATE) S_DATE,
                    COUNT(DISTINCT PPS.PRODUCT_CODE) S_AMT
               FROM PLN.PLN_PRODUCT_STATUS PPS
              WHERE PPS.STATUS_CODE = '1070'
                AND PPS.STATUS_DATE >= V_DATE_START
                AND PPS.STATUS_DATE < V_DATE_END --TRUNC(SYSDATE) + 1
              GROUP BY PPS.LINE_ID/*, TRUNC(PPS.STATUS_DATE)*/) KV
      ON (MKP.KPI_DATE = KV.S_DATE AND MKP.LINE_ID = KV.LINE_ID)
      WHEN MATCHED THEN
        UPDATE SE