oracle MySQL创建视图

oracle视图创建：

CREATE OR REPLACE FORCE EDITIONABLE VIEW "VIEW_DEVICE_OPTION" ("F_ENABLE_TIME", "F_CHANGE_TIME", "F_SERVER_STATUS", "F_LAST_RECORD", "F_DEVICE_VERSION", "F_AUTO_REBOOT", "F_REBOOT_TIME", "F_DEVICE_REBOOT_TIME", "F_DEVICE_TIMESYNC_TIME", "F_DEVICE_NEWSERVER_TIME", "F_MAC", "F_DEVICE_CODE", "F_DEVICE_NAME", "F_DEVICE_STATUS", "F_IP", "F_TCP_PORT", "F_UDP_PORT", "F_LOCATION_ID", "F_DEVICE_ID", "F_LOCATION_SERVER_ID", "F_SERVER_ID", "F_USER_ID", "F_LONGITUDE", "F_LATITUDE", "F_DESCRIPTION", "F_CAP_INTERVAL", "F_CAP_DATA_TYPE", "F_SYNC_DATA_INTERVAL", "F_START_VSSID", "F_HARD_WARE_VERSION", "F_SOFT_WARE_VERSION", "F_LIMIT_RATE", "F_VSSID_FLOW", "F_SSID_1", "F_SSID_2", "F_SSID_3", "F_SSID_4", "F_INDUCE_INTERVAL", "F_INDUCE_FREE_SSID", "F_INDUCE_ENCRYPT_SSID", "F_DECRYPT_AP_SSID", "F_DECRYPT_AP_PWD", "F_DECRYPT_MODE", "F_LOG_UPLOAD", "F_GET_ONLINE_DATA", "F_NET_MODE", "F_THREE_IN_ONE", "F_WIFIDOG_CONFIG") AS 
  SELECT TO_CHAR(T_LOCATION_SERVER.F_ENABLE_TIME,'YYYY-MM-DD hh24:mi:ss') AS F_ENABLE_TIME,
    T_LOCATION_SERVER.F_CHANGE_TIME                                       AS F_CHANGE_TIME,
    T_LOCATION_SERVER.F_STATUS                                            AS F_SERVER_STATUS,
    T_LOCATION_SERVER.F_LAST_DATA_TIME                                    AS F_LAST_RECORD,
    T_DEVICE.F_VERSION                                                    AS F_DEVICE_VERSION,
    T_LOCATION.F_AUTO_REBOOT                                              AS F_AUTO_REBOOT,
    T_LOCATION.F_REBOOT_TIME                                              AS F_REBOOT_TIME,
    T_DEVICE.F_LASTEST_REBOOT_TIME                                        AS F_DEVICE_REBOOT_TIME,
    T_DEVICE.F_TIMESYNC_TIME                                              AS F_DEVICE_TIMESYNC_TIME,
    T_DEVICE.F_NEWSERVER_TIME                                             AS F_DEVICE_NEWSERVER_TIME,
    T_DEVICE.F_MAC                                                        AS F_MAC,
    T_DEVICE.F_DEVICE_CODE                                                AS F_DEVICE_CODE,
    T_DEVICE.F_DEVICE_NAME                                                AS F_DEVICE_NAME,
    T_DEVICE.F_STATUS                                                     AS F_DEVICE_STATUS,
    T_SERVER.F_IP                                                         AS F_IP,
    T_DEVICE_MANUFACTURER.F_TCP_PORT                                      AS F_TCP_PORT,
    T_DEVICE_MANUFACTURER.F_UDP_PORT                                      AS F_UDP_PORT,
    T_LOCATION.F_LOCATION_ID                                              AS F_LOCATION_ID,
    T_LOCATION.F_DEVICE_ID                                                AS F_DEVICE_ID,
    T_LOCATION_SERVER.F_LOCATION_SERVER_ID                                AS F_LOCATION_SERVER_ID,
    T_SERVER.F_SERVER_ID                                                  AS F_SERVER_ID,
    T_SERVER.F_USER_ID                                                    AS F_USER_ID,
    T_LOCATION.F_LONGITUDE                                                AS F_LONGITUDE,
    T_LOCATION.F_LATITUDE                                                 AS F_LATITUDE,
    T_SERVER.F_DESCRIPTION                                                AS F_DESCRIPTION,
    T_Device.F_Cap_Interval                                               AS F_Cap_Interval,
    T_Device.F_CAP_DATA_TYPE                                              AS F_CAP_DATA_TYPE,
    T_Device.F_SYNC_DATA_INTERVAL                                         AS F_SYNC_DATA_INTERVAL,
    T_Device.F_START_VSSID                                                AS F_START_VSSID,
    T_Device.F_HARD_WARE_VERSION                                          AS F_HARD_WARE_VERSION,
    T_Device.F_SOFT_WARE_VERSION                                          AS F_SOFT_WARE_VERSION,
    T_Device.F_LIMIT_RATE                                                 AS F_LIMIT_RATE,
    T_Device.F_VSSID_FLOW                                                 AS F_VSSID_FLOW,
    T_Device.F_SSID_1                                                     AS F_SSID_1,
    T_Device.F_SSID_2                                                     AS F_SSID_2,
    T_Device.F_SSID_3                                                     AS F_SSID_3,
    T_Device.F_SSID_4                                                     AS F_SSID_4,
    T_Device.F_SSID_5                                                     AS F_SSID_5,
   T_DEVICE.F_INDUCE_INTERVAL											  AS F_INDUCE_INTERVAL,
   T_DEVICE.F_INDUCE_FREE_SSID											AS F_INDUCE_FREE_SSID,
   T_DEVICE.F_INDUCE_ENCRYPT_SSID									AS F_INDUCE_ENCRYPT_SSID,
   T_DEVICE.F_DECRYPT_AP_SSID										AS F_DECRYPT_AP_SSID,
   T_DEVICE.F_DECRYPT_AP_PWD										AS F_DECRYPT_AP_PWD,
   T_DEVICE.F_DECRYPT_MODE											AS F_DECRYPT_MODE,
   T_DEVICE.F_LOG_UPLOAD											AS F_LOG_UPLOAD,
	
    CASE
      WHEN T_Device.F_Type IN (4,5)
      THEN 1
      ELSE 0
    END AS F_GET_ONLINE_DATA,
    T_Device.F_NET_MODE F_NET_MODE,
    T_Device.F_THREE_IN_ONE F_THREE_IN_ONE,
    T_Device.F_WIFIDOG_CONFIG F_WIFIDOG_CONFIG
  FROM T_LOCATION
  LEFT JOIN T_LOCATION_SERVER
  ON T_LOCATION.F_LOCATION_ID = T_LOCATION_SERVER.F_LOCATION_ID
  LEFT JOIN T_DEVICE
  ON T_DEVICE.F_DEVICE_ID = T_LOCATION.F_DEVICE_ID
  LEFT JOIN T_SERVER
  ON T_SERVER.F_SERVER_ID = T_LOCATION_SERVER.F_SERVER_ID
  LEFT JOIN T_SITE
  ON T_LOCATION.F_SITE_ID           = T_SITE.F_ID
  LEFT JOIN T_DEVICE_MANUFACTURER 
  ON T_DEVICE.F_MANUFACTURER_ID = T_DEVICE_MANUFACTURER.F_ID
  WHERE T_LOCATION_SERVER.F_STATUS != 4
  ORDER BY F_CHANGE_TIME DESC;

 

MySQL视图创建：

CREATE OR REPLACE VIEW VIEW_DEVICE_OPTION_TEST AS
SELECT
	`D`.`ID` AS `ID`,
	`SITE`.`LONGITUDE` AS `LONGITUDE`,
	`SITE`.`LATITUDE` AS `LATITUDE`,
	`D`.`AUTO_REBOOT` AS `AUTO_REBOOT`,
	`D`.`REBOOT_TIME` AS `REBOOT_TIME`,
	`D`.`FLOOR` AS `FLOOR`,
	`D`.`DEVICE_CODE` AS `DEVICE_CODE`,
	`D`.`DEVICE_NAME` AS `DEVICE_NAME`,
	`D`.`DESCRIPTION` AS `DESCRIPTION`,
	`D`.`AREA_CODE` AS `AREA_CODE`,
	`D`.`SALE_PERSON` AS `SALE_PERSON`,
	`D`.`BUY_DATE` AS `BUY_DATE`,
	`D`.`DEVICE_IP` AS `DEVICE_IP`,
	`D`.`SERVER_STATUS` AS `SERVER_STATUS`,
	`D`.`STATUS` AS `STATUS`,
	`D`.`DATA_STATUS` AS `DATA_STATUS`,
	`D`.`TYPE` AS `TYPE`,
	`D`.`MAC` AS `MAC`,
	`D`.`LASTEST_REBOOT_TIME` AS `LASTEST_REBOOT_TIME`,
	`D`.`VERSION` AS `VERSION`,
	`D`.`SUBWAY_STATION` AS `SUBWAY_STATION`,
	`D`.`SUBWAY_LINE_INFO` AS `SUBWAY_LINE_INFO`,
	`D`.`SUBWAY_VEHICLE_INFO` AS `SUBWAY_VEHICLE_INFO`,
	`D`.`SUBWAY_COMPARTMENT_NUMBER` AS `SUBWAY_COMPARTMENT_NUMBER`,
	`D`.`CAR_CODE` AS `CAR_CODE`,
	`D`.`CAP_RADIUS` AS `CAP_RADIUS`,
	`D`.`CAP_INTERVAL` AS `CAP_INTERVAL`,
	`D`.`NEWSERVER_TIME` AS `NEWSERVER_TIME`,
	`D`.`TIMESYNC_TIME` AS `TIMESYNC_TIME`,
	`D`.`START_VSSID` AS `START_VSSID`,
	`D`.`DEVICE_WORK_MODEL` AS `DEVICE_WORK_MODEL`,
	`D`.`HARD_WARE_VERSION` AS `HARD_WARE_VERSION`,
	`D`.`SOFT_WARE_VERSION` AS `SOFT_WARE_VERSION`,
	`D`.`CAP_DATA_TYPE` AS `CAP_DATA_TYPE`,
	`D`.`SYNC_DATA_INTERVAL` AS `SYNC_DATA_INTERVAL`,
	`D`.`LIMIT_RATE` AS `LIMIT_RATE`,
	`D`.`NET_MODE` AS `NET_MODE`,
	`D`.`VSSID_FLOW` AS `VSSID_FLOW`,
	`D`.`WIFIDOG_CONFIG` AS `WIFIDOG_CONFIG`,
	`D`.`THREE_IN_ONE` AS `THREE_IN_ONE`,
	`D`.`ISDEL` AS `ISDEL`,
	`D`.`SSID_1` AS `SSID_1`,
	`D`.`SSID_2` AS `SSID_2`,
	`D`.`SSID_3` AS `SSID_3`,
	`D`.`SSID_4` AS `SSID_4`,
	`D`.`SSID_5` AS `SSID_5`,
	`D`.`INDUCE_INTERVAL` AS `INDUCE_INTERVAL`,
	`D`.`CREATE_TIME` AS `CREATE_TIME`,
	`D`.`START_TIME` AS `START_TIME`,
	`D`.`INDUCE_FREE_SSID` AS `INDUCE_FREE_SSID`,
	`D`.`INDUCE_ENCRYPT_SSID` AS `INDUCE_ENCRYPT_SSID`,
	`D`.`DECRYPT_AP_SSID` AS `DECRYPT_AP_SSID`,
	`D`.`DECRYPT_AP_PWD` AS `DECRYPT_AP_PWD`,
	`D`.`DECRYPT_MODE` AS `DECRYPT_MODE`,
	`D`.`LOG_UPLOAD` AS `LOG_UPLOAD`,
	`D`.`SSID` AS `SSID`,
	`D`.`SECRET_KEY` AS `SECRET_KEY`,
	`D`.`S_ID` AS `S_ID`,
	`D`.`RUNNING_TIME` AS `RUNNING_TIME`,
	`DS`.`CHANGE_TIME` AS `CHANGE_TIME`,
	`DS`.`ENABLE_TIME` AS `ENABLE_TIME`,
	`DS`.`LAST_BEAT_TIME` AS `LAST_BEAT_TIME`,
	`DS`.`LAST_DATA_TIME` AS `LAST_DATA_TIME`,
	`DS`.`DEVICE_SERVER_STATUS` AS `DEVICE_SERVER_STATUS`,
	`US`.`CHINESE_NAME` AS `CREATOR`,
	`DM`.`ID` AS `MANUFACTURER_ID`,
	`DM`.`NAME` AS `MANUFACTURER_NAME`,
	`S`.`IP` AS `SERVER_IP`,
	`S`.`ID` AS `SERVER_ID`,
	`SITE`.`ID` AS `SITE_ID`,
	`SITE`.`NAME` AS `SITE_NAME`,
	`SITE`.`CODE` AS `SITE_CODE`,
	`DEPT`.`ID` AS `DEPT_ID`,
	`DEPT`.`NAME` AS `DEPT_NAME`,
	`ADDR`.`ADDRESSSTR` AS `ADDRESS`
FROM
	(
		(
			(
				(
					(
						(
							(
								(
									(
										`T_DEVICE` `D`
										LEFT JOIN `T_DEVICE_SERVER` `DS` ON (
											(`D`.`ID` = `DS`.`DEVICE_ID`)
										)
									)
									LEFT JOIN `T_USER` `US` ON (
										(`US`.`ID` = `D`.`CREATE_ID`)
									)
								)
								LEFT JOIN `T_DEVICE_MANUFACTURER` `DM` ON (
									(
										`D`.`MANUFACTURER_ID` = `DM`.`ID`
									)
								)
							)
							LEFT JOIN `T_SERVER` `S` ON (
								(`S`.`ID` = `DS`.`SERVER_ID`)
							)
						)
						LEFT JOIN `T_SITE` `SITE` ON (
							(`D`.`SITE_ID` = `SITE`.`ID`)
						)
					)
					LEFT JOIN `T_ADDRESS` `ADDR` ON (
						(
							`SITE`.`ADDRESS_ID` = `ADDR`.`ID`
						)
					)
				)
				LEFT JOIN `T_DEPARTMENT` `DEPT` ON (
					(
						`SITE`.`DEPT_ID` = `DEPT`.`ID`
					)
				)
			)
			LEFT JOIN `T_UNIT` `U` ON (
				(`U`.`ID` = `DEPT`.`UNIT_ID`)
			)
		)
		LEFT JOIN `T_SHOP` `SH` ON ((`SH`.`ID` = `D`.`S_ID`))
	)
 ORDER BY CREATE_TIME DESC;