ks_led.sql

自己编写的基本Orcale的通用的数据库初始化工具。

/*==============================================================*/
/* DBMS name:      ORACLE Version 9i                            */
/* Created on:     2007-8-27 15:22:26                           */
/*==============================================================*/


drop view V_LED_ZYJQ;

drop view V_LED_LQJQ;

drop view V_LED_JRJQ;

drop view V_LED_JQFK;

drop view V_LED_GG;

drop view V_LED_DQJQ;

/*==============================================================*/
/* View: V_LED_DQJQ                                             */
/*==============================================================*/
create or replace view V_LED_DQJQ as
SELECT
	ROWNUM AS XH, XQDW, SLSJ, SJLX, CZZT
FROM
(
	SELECT 
		T_ZZ_DSRXX.DSRMC AS XQDW,
		T_SJ_SJXX.SLSJ AS SLSJ,
		T_SJ_DM_SJLX.MS AS SJLX,
        T_SJ_SJXX.SJCZZT AS CZZT
	FROM T_SJ_SJXX
		INNER JOIN T_ZZ_DSRXX ON T_ZZ_DSRXX.DSRBH = T_SJ_SJXX.ZGJG
		INNER JOIN T_SJ_DM_SJLX ON T_SJ_DM_SJLX.SJLX = T_SJ_SJXX.SJLX
	WHERE
		TO_CHAR(SYSDATE,'YYYY-MM-DD') = TO_CHAR(T_SJ_SJXX.SLSJ,'YYYY-MM-DD')
		AND T_SJ_SJXX.FKSJ IS NULL
        AND T_SJ_SJXX.SJXZ = 1
        AND T_SJ_SJXX.SJHBBH IS NULL
	ORDER BY T_SJ_SJXX.SLSJ DESC
);

comment on table V_LED_DQJQ is
'当前警情';

/*==============================================================*/
/* View: V_LED_GG                                               */
/*==============================================================*/
create or replace view V_LED_GG as
SELECT
	  ROWNUM AS XH,
	  T_XZ_DM_GGLB.MS AS GGLB,
	  T_XZ_GGXX.GGNR AS GGNR
FROM T_XZ_GGXX
	 LEFT JOIN T_XZ_DM_GGLB ON T_XZ_DM_GGLB.GGLB = T_XZ_GGXX.GGLB
WHERE
	 (SYSDATE-T_XZ_GGXX.FSSJ)*24 < NVL(
		(SELECT PZZ FROM T_XT_XTQJPZXX,T_XT_XTPZX WHERE T_XT_XTQJPZXX.XTPZX=T_XT_XTPZX.XTPZX AND T_XT_XTPZX.BSF='SJLBSJJG'),
		(SELECT QSZ FROM T_XT_XTPZX WHERE T_XT_XTPZX.BSF='SJLBSJJG'));

comment on table V_LED_GG is
'公告';

/*==============================================================*/
/* View: V_LED_JQFK                                             */
/*==============================================================*/
create or replace view V_LED_JQFK as
SELECT
	ROWNUM AS XH, CJDW, SLSJ, SJLX, CZZT, ZYQJ
FROM
(
	SELECT 
		T_ZZ_DSRXX.DSRMC AS CJDW,
		T_SJ_SJXX.SLSJ AS SLSJ,
		T_SJ_DM_SJLX.MS AS SJLX,
        T_SJ_SJXX.SJCZZT AS CZZT,
        T_SJ_SJXX.ZYQJ AS ZYQJ
	FROM T_SJ_SJXX
		INNER JOIN T_ZZ_DSRXX ON T_ZZ_DSRXX.DSRBH = T_SJ_SJXX.ZGJG
		INNER JOIN T_SJ_DM_SJLX ON T_SJ_DM_SJLX.SJLX = T_SJ_SJXX.SJLX
	WHERE
		TO_CHAR(SYSDATE,'YYYY-MM-DD') = TO_CHAR(T_SJ_SJXX.SLSJ,'YYYY-MM-DD')
		AND T_SJ_SJXX.FKSJ IS NULL
        AND T_SJ_SJXX.SJXZ = 1
        AND T_SJ_SJXX.SJHBBH IS NULL
        AND EXISTS ( SELECT 1 FROM T_ZZ_JGYJGNXX 
         	WHERE T_ZZ_JGYJGNXX.YJJGBH = T_ZZ_DSRXX.DSRBH AND T_ZZ_JGYJGNXX.YJZN = 1)
		AND (T_SJ_SJXX.FKSJ IS NULL OR T_SJ_SJXX.FKSJ = TO_DATE('0001-1-1 00:00:00','yyyy-mm-dd hh24:mi:ss'))
        AND (SYSDATE - T_SJ_SJXX.SLSJ)*24 > 2
	ORDER BY T_SJ_SJXX.SLSJ DESC
 );

comment on table V_LED_JQFK is
'警情反馈';

/*==============================================================*/
/* View: V_LED_JRJQ                                             */
/*==============================================================*/
create or replace view V_LED_JRJQ as
SELECT 
	NVL(S1.JQSL,0) AS JQSL,S2.BJTFHM AS BJFS
FROM
(
	SELECT 
		T_SJ_DHBJXX.BJHM,
		COUNT(*) AS JQSL
	FROM T_SJ_BJSLD
		INNER JOIN T_SJ_DHBJXX ON T_SJ_DHBJXX.BJBH = T_SJ_BJSLD.BJBH
	WHERE
	 	TO_CHAR(SYSDATE,'YYYY-MM-DD') = TO_CHAR(T_SJ_BJSLD.BJKSSJ,'YYYY-MM-DD')
	 	AND T_SJ_BJSLD.BJCLJG = 2
	GROUP BY T_SJ_DHBJXX.BJHM
)S1
RIGHT JOIN 
(
	SELECT BJTFHM FROM T_SJ_BJHMSZ
)S2
ON S1.BJHM = S2.BJTFHM 
ORDER BY S2.BJTFHM;

comment on table V_LED_JRJQ is
'今日警情';

/*==============================================================*/
/* View: V_LED_LQJQ                                             */
/*==============================================================*/
create or replace view V_LED_LQJQ as
SELECT 
	T_ZZ_DSRXX.DSRMC AS XQDW,
	T_SJ_SJXX.SLSJ AS SLSJ,
	T_SJ_SJXX.SFDZ AS SFDZ,
	T_SJ_DM_SJLX.MS AS SJLX,
    T_SJ_SJXX.SJCZZT AS CZZT
FROM T_SJ_SJXX
	INNER JOIN T_ZZ_DSRXX ON T_ZZ_DSRXX.DSRBH = T_SJ_SJXX.ZGJG
	INNER JOIN T_SJ_DM_SJLX ON T_SJ_DM_SJLX.SJLX = T_SJ_SJXX.SJLX
WHERE
	 TO_CHAR(SYSDATE,'YYYY-MM-DD') = TO_CHAR(T_SJ_SJXX.SLSJ,'YYYY-MM-DD')
	 AND TJ_SJLX_FL(TJ_SJLX_FL(T_SJ_SJXX.SJLX)) IN (63,64)
	 AND T_SJ_SJXX.FKSJ IS NULL
     AND T_SJ_SJXX.SJXZ = 1
     AND T_SJ_SJXX.SJHBBH IS NULL
ORDER BY T_SJ_SJXX.SLSJ DESC;

comment on table V_LED_LQJQ is
'两抢警情';

/*==============================================================*/
/* View: V_LED_ZYJQ                                             */
/*==============================================================*/
create or replace view V_LED_ZYJQ as
SELECT
	ROWNUM AS XH, XQDW, SLSJ, SJLX, CZZT
FROM
(
	SELECT 
		T_ZZ_DSRXX.DSRMC AS XQDW,
		T_SJ_SJXX.SLSJ AS SLSJ,
		T_SJ_DM_SJLX.MS AS SJLX,
        T_SJ_SJXX.SJCZZT AS CZZT
	FROM T_SJ_SJXX
		INNER JOIN T_ZZ_DSRXX ON T_ZZ_DSRXX.DSRBH = T_SJ_SJXX.ZGJG
		INNER JOIN T_SJ_DM_SJLX ON T_SJ_DM_SJLX.SJLX = T_SJ_SJXX.SJLX
	WHERE
		TO_CHAR(SYSDATE,'YYYY-MM-DD') = TO_CHAR(T_SJ_SJXX.SLSJ,'YYYY-MM-DD')
        AND T_SJ_SJXX.SJLX IN 
            (SELECT SJLX FROM T_SJ_DM_SJLX WHERE SCBZ = 0 AND SFZY = 1)
		AND T_SJ_SJXX.FKSJ IS NULL
        AND T_SJ_SJXX.SJXZ = 1
        AND T_SJ_SJXX.SJHBBH IS NULL
	ORDER BY T_SJ_SJXX.SLSJ DESC
);

comment on table V_LED_ZYJQ is
'重要警情';