oracle 存储过程传入时间

「爱情、让人受尽委屈。」 2021-12-23 08:27 306阅读 0赞

自己用到的

主要就是通过这个存储过程创建一个带有参数视图

/* Formatted on 2013-7-18 14:33:03 (QP5 v5.185.11230.41888) */
    CREATE OR REPLACE PROCEDURE omp_produce_allinfo (starttime   IN DATE,
                                                     endtime     IN DATE)
    IS
       starttimetemp   DATE;
       tempdate        DATE;
       stmt            VARCHAR2 (32767);
       startstr        VARCHAR2 (100);
       endstr          VARCHAR2 (100);
    BEGIN
       starttimetemp := starttime;
       tempdate := endtime;
       startstr := TO_CHAR (starttimetemp, 'yyyy-mm-dd');
       endstr := TO_CHAR (tempdate, 'yyyy-mm-dd');
       stmt :=
             '
     
    
    CREATE OR REPLACE FORCE VIEW omp_view_allinfo
    (
       ID,
       AREA,
       SORT,
       WIN_NAME,
       COUNT_NUM,
       NAME,
       NUM
    )
    AS
         SELECT b.id,
                b.area,
                b.sort,
                b.win_name,
                a.count_num,
                a.name,
                a.num
           FROM    (SELECT *
                      FROM COMMON_WINDOW
                     WHERE if_del = ''1'') b
                LEFT JOIN
                   (  SELECT own_win AS win_id,
                             COUNT (1) AS count_num,
                             ''服务机构数'' AS name,
                             1 AS num
                        FROM COMMON_ORGANIZATION
                       WHERE     IF_AUDIT = ''3''
                             AND IF_DEL = ''1''
                             AND if_org <> ''1''
                             AND audit_time BETWEEN TO_DATE ('''--转义符
          || startstr--时间参数
          || ''',
                                                             ''yyyy-mm-dd'')
                                                AND TO_DATE ('''
          || endstr
          || ''',
                                                             ''yyyy-mm-dd'')
                    GROUP BY own_win                                    -----服务机构数
                    UNION ALL
                      SELECT own_win AS win_id,
                             COUNT (1) AS count_num,
                             ''中小企业数'' AS name,
                             2 AS num
                        FROM COMMON_ORGANIZATION
                       WHERE     IF_AUDIT = ''3''
                             AND IF_DEL = ''1''
                             AND if_org = ''1''
                             AND audit_time BETWEEN TO_DATE ('''
          || startstr
          || ''',
                                                             ''yyyy-mm-dd'')
                                                AND TO_DATE ('''
          || endstr
          || ''',
                                                             ''yyyy-mm-dd'')
                    GROUP BY own_win                                       --中小企业数
                                                     ) a
                ON a.win_id = b.id
       ORDER BY sort, num ';
    
       DBMS_OUTPUT.put_line (startstr);
       DBMS_OUTPUT.put_line (endstr);
       DBMS_OUTPUT.enable (40000);
       DBMS_OUTPUT.put_line (stmt);
    
       EXECUTE IMMEDIATE stmt;
    END omp_produce_allinfo;
    /