oracle 存储过程 传入时间

自己用到的

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

/* 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;
/