自己用到的
主要就是通过这个存储过程创建一个带有参数视图
/* 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;
/