本地 Windows 如何将 Web 工程部署到远程 Windows 主机上

第一步：先连接远程 Windows 主机：

在设置本地文件远程共享，连接远程主机后，在远程主机上才能看到本地电脑的目录，这样才能将本地的发布包COPY到远程主机上：

这样连接远程主机成功之后，就可以看到本地计算机的磁盘D的所有文件了！

再回到远程桌面连接对话框，点击“连接”按钮： 出现如下的对话框，输入远程主机系统的登录用户名和密码：

第二步：连接远程 Windows 主机后，把发布包复制到远程主机上并确定有关的目录

找到远程主机 Tomcat 安装目录、Oracle 的安装目录、Web 工程的部署目录：

发布包的内容：

脚本文件：

备份数据文件：

工程文件：

第三步：删除远程主机的数据库中的旧数据

以 DBA（Database Administrator，简称DBA） 帐号进入数据库，河北 DBA 帐号 sys，密码 oracle

删除掉数据库中原有的和我们即将要创建的用户和表空间同名的用户和表空间，你可以 COPY 以下的脚本在 PL/SQL 中打开命令窗口来执行：

drop user hyt2prdhn cascade; 
drop tablespace hyt2prdhn including contents and datafiles cascade constraints;
drop user HYT2CREDITHN cascade; 
drop tablespace HYT2CREDITHN including contents and datafiles cascade constraints;
drop user JXPZMISPF cascade; 
drop tablespace JXPZMISPF including contents and datafiles cascade constraints;
drop user hyt2linehn cascade; 
drop tablespace hyt2linehn including contents and datafiles cascade constraints;
drop user hyt2pfhn cascade; 
drop tablespace hyt2pfhn including contents and datafiles cascade constraints;

文件路径：道路运输管理系统发布包v2.2_0613\脚本\1.drop_db.sql

第四步：创建新的用户和表空间

CREATE TABLESPACE "HYT2PFHN"
datafile 'D:/jlyz/HYT2PFHN.DBF' SIZE 200M 
REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 4000M 
DEFAULT STORAGE (INITIAL 2K NEXT 16K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 );
commit;

CREATE TABLESPACE "HYT2LINEHN"
datafile 'D:/jlyz/HYT2LINEHN.DBF' SIZE 200M 
REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 4000M 
DEFAULT STORAGE (INITIAL 2K NEXT 16K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 );
commit;

CREATE TABLESPACE "HYT2PRDHN"
datafile 'D:/jlyz/HYT2PRDHN.DBF' SIZE 200M 
REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 4000M 
DEFAULT STORAGE (INITIAL 2K NEXT 16K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 );
commit;

CREATE TABLESPACE "HYT2CREDITHN"
datafile 'D:/jlyz/HYT2CREDITHN.DBF' SIZE 200M 
REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 4000M 
DEFAULT STORAGE (INITIAL 2K NEXT 16K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 );
commit;

CREATE TABLESPACE "JXPZMISPF"
datafile 'D:/jlyz/JXPZMISPF.DBF' SIZE 200M 
REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 4000M 
DEFAULT STORAGE (INITIAL 2K NEXT 16K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 );
commit;

create user HYT2PFHN identified by "HYT2PFHN" default tablespace HYT2PFHN TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT CONNECT TO HYT2PFHN WITH ADMIN OPTION;
GRANT RESOURCE TO HYT2PFHN;
GRANT CREATE ANY TABLE TO HYT2PFHN;
GRANT INSERT ANY TABLE TO HYT2PFHN;
GRANT SELECT ANY TABLE TO HYT2PFHN;
GRANT UPDATE ANY TABLE TO HYT2PFHN;
GRANT DELETE ANY TABLE TO HYT2PFHN;
GRANT ALTER ANY TABLE TO HYT2PFHN;
GRANT DROP ANY TABLE TO HYT2PFHN;
GRANT CREATE ANY INDEX TO HYT2PFHN;
GRANT DROP ANY INDEX TO HYT2PFHN;
GRANT SELECT ANY SEQUENCE TO HYT2PFHN;
GRANT EXECUTE ANY PROCEDURE TO HYT2PFHN;
GRANT CREATE ANY VIEW TO HYT2PFHN;
GRANT DROP ANY VIEW TO HYT2PFHN;
commit;

create user HYT2LINEHN identified by "HYT2LINEHN" default tablespace HYT2LINEHN TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT CONNECT TO HYT2LINEHN WITH ADMIN OPTION;
GRANT RESOURCE TO HYT2LINEHN;
GRANT CREATE ANY TABLE TO HYT2LINEHN;
GRANT INSERT ANY TABLE TO HYT2LINEHN;
GRANT SELECT ANY TABLE TO HYT2LINEHN;
GRANT UPDATE ANY TABLE TO HYT2LINEHN;
GRANT DELETE ANY TABLE TO HYT2LINEHN;
GRANT ALTER ANY TABLE TO HYT2LINEHN;
GRANT DROP ANY TABLE TO HYT2LINEHN;
GRANT CREATE ANY INDEX TO HYT2LINEHN;
GRANT DROP ANY INDEX TO HYT2LINEHN;
GRANT SELECT ANY SEQUENCE TO HYT2LINEHN;
GRANT EXECUTE ANY PROCEDURE TO HYT2LINEHN;
GRANT CREATE ANY VIEW TO HYT2LINEHN;
GRANT DROP ANY VIEW TO HYT2LINEHN;
commit;

create user HYT2PRDHN identified by "HYT2PRDHN" default tablespace HYT2PRDHN TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT CONNECT TO HYT2PRDHN WITH ADMIN OPTION;
GRANT RESOURCE TO HYT2PRDHN;
GRANT CREATE ANY TABLE TO HYT2PRDHN;
GRANT INSERT ANY TABLE TO HYT2PRDHN;
GRANT SELECT ANY TABLE TO HYT2PRDHN;
GRANT UPDATE ANY TABLE TO HYT2PRDHN;
GRANT DELETE ANY TABLE TO HYT2PRDHN;
GRANT ALTER ANY TABLE TO HYT2PRDHN;
GRANT DROP ANY TABLE TO HYT2PRDHN;
GRANT CREATE ANY INDEX TO HYT2PRDHN;
GRANT DROP ANY INDEX TO HYT2PRDHN;
GRANT SELECT ANY SEQUENCE TO HYT2PRDHN;
GRANT EXECUTE ANY PROCEDURE TO HYT2PRDHN;
GRANT CREATE ANY VIEW TO HYT2PRDHN;
GRANT DROP ANY VIEW TO HYT2PRDHN;
commit;

create user HYT2CREDITHN identified by "HYT2CREDITHN" default tablespace HYT2CREDITHN TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT CONNECT TO HYT2CREDITHN WITH ADMIN OPTION;
GRANT RESOURCE TO HYT2CREDITHN;
GRANT CREATE ANY TABLE TO HYT2CREDITHN;
GRANT INSERT ANY TABLE TO HYT2CREDITHN;
GRANT SELECT ANY TABLE TO HYT2CREDITHN;
GRANT UPDATE ANY TABLE TO HYT2CREDITHN;
GRANT DELETE ANY TABLE TO HYT2CREDITHN;
GRANT ALTER ANY TABLE TO HYT2CREDITHN;
GRANT DROP ANY TABLE TO HYT2CREDITHN;
GRANT CREATE ANY INDEX TO HYT2CREDITHN;
GRANT DROP ANY INDEX TO HYT2CREDITHN;
GRANT SELECT ANY SEQUENCE TO HYT2CREDITHN;
GRANT EXECUTE ANY PROCEDURE TO HYT2CREDITHN;
GRANT CREATE ANY VIEW TO HYT2CREDITHN;
GRANT DROP ANY VIEW TO HYT2CREDITHN;
commit;

create user JXPZMISPF identified by "JXPZMISPF" default tablespace JXPZMISPF TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT CONNECT TO JXPZMISPF WITH ADMIN OPTION;
GRANT RESOURCE TO JXPZMISPF;
GRANT CREATE ANY TABLE TO JXPZMISPF;
GRANT INSERT ANY TABLE TO JXPZMISPF;
GRANT SELECT ANY TABLE TO JXPZMISPF;
GRANT UPDATE ANY TABLE TO JXPZMISPF;
GRANT DELETE ANY TABLE TO JXPZMISPF;
GRANT ALTER ANY TABLE TO JXPZMISPF;
GRANT DROP ANY TABLE TO JXPZMISPF;
GRANT CREATE ANY INDEX TO JXPZMISPF;
GRANT DROP ANY INDEX TO JXPZMISPF;
GRANT SELECT ANY SEQUENCE TO JXPZMISPF;
GRANT EXECUTE ANY PROCEDURE TO JXPZMISPF;
GRANT CREATE ANY VIEW TO JXPZMISPF;
GRANT DROP ANY VIEW TO JXPZMISPF;
commit;

注意：datafile 'D:/jlyz/HYT2PFHN.DBF’这是数据文件的保存路径，要改成远程主机的，上述讲到远程主机的数据库目录是 所以数据文件的路径修改为：E:/oracle/HYT2PFHN.DBF 再执行脚本。

第五步：导入备份的数据

接下来开始导入数据，打开 PL/SQL 软件，以对应的账号登录，再导入有关的备份数据，例如：以 hyt2prdhn 帐号登录，则导入hyt2prdhn.dmp 文件，以此类推导入其它备份数据。当所有 dmp 文件导入成功后，执行创建视图和过程的脚本

第六步：创建视图

CREATE OR REPLACE VIEW HYT2LINEHN.V_BUSLINCARD AS
SELECT T1.BUSLINECARDID,T2.CLITNAME,T1.LIDCARDNO,T1.STADATE,T1.ENDDATE,T3.BUSLINENAME,T3.UPSTATIONCN,T3.DOWNSTATIONCN,T1.BRANUM,T1.LINBUSSTATUS
  FROM HYT2LINEHN.BUSLINECARD T1
  LEFT OUTER JOIN HYT2LINEHN.CMCLT T2 ON T1.CMCLTID=T2.CMCLTID
  LEFT OUTER JOIN HYT2LINEHN.BUSLINE T3 ON T1.BUSLINEID=T3.BUSLINEID
	WHERE T1.ISEFFECT=0;
	CREATE OR REPLACE VIEW HYT2LINEHN.V_BUSLINE AS
SELECT T1.BUSLINEID,T1.BUSLINENAME,T1.BUSLINETYPE,T1.STADEPOTCN,T1.ENDDEPOTCN,T1.FIRENDTIME ,
       T1.DAYTIMES AS DAYSTIMES,T1.UPSTATIONCN,T1.DOWNSTATIONCN
  FROM HYT2LINEHN.BUSLINE T1
  WHERE T1.ISEFFECT=0;
CREATE OR REPLACE VIEW HYT2LINEHN.V_CHECKCARDNO AS
SELECT CARDINITID,CARDGUID,CARDNO,CARDBATCH,CREDATE,CRETIME,ISEFFECT,ISUSE,ICCARDUSERTYPE,SYSOPERTIME FROM  HYT2LINEHN.CARDINIT T WHERE T.ISEFFECT='0';
CREATE OR REPLACE VIEW HYT2LINEHN.V_CLTVEC AS
SELECT
'' HZF_CLTVEC,--设置默认查询条件
'' HZS_CLTVEC,--设置默认查询列
'' HZT_CMCLTINFO,--业户基本信息
T2.CMVECID,T1.CLITNAME,T1.CLTENTITYTYPE,T1.PERWORD,T1.PERNUM,T1.DEPCODE CLT_DPNM,T1.ADDRESS,T1.ECOTYPE,T1.MAICODING,T1.JURPERSON,T1.CTCNO,T1.PRINCIPAL,T1.PHONE,T1.CLTBUSSTATUS,T1.BUSLICNO,T1.TAXREGNO,T1.GRADEPCODE AS GRACODE_DPNM,T1.RPTCHKRSU,
'' HZT_VECINFO,  --车辆基本信息
T3.CANNAME AS VECADDR,T2.TRAWORD,T2.AREAABBR,T2.TRANO,T2.DEPCODE AS VEC_DPNM,T2.VECENTYPE,T4.DEPNAME AS MANAGER_DPNM,T2.GRADATE,T2.CREDATE,T2.STADATE,T2.ENDDATE,T2.BRACOLOR,T2.BRANUM,T2.FACTYPE,T2.MODEL,T2.VECTYPE,T2.VECBUSSTATUS,
T2.BNSCOPE,T2.ARCNO,T2.CSGUNIT,T2.BUSTYPE,T2.REGYPE,T2.CHENUM,T2.COLNUM,T2.VECLENGTH,T2.VECWIDE,T2.VECHIGH,T2.INSVECCARLONG,T2.INSVECCARWIDE,T2.INSVECCARHIGH,
T2.ENGTYPE,T2.VECCOLOR,T2.SETDATE,T2.BUYPRICE,T2.TRANCHA,T2.ISGOTOSYS,T2.ISINOUT,T2.ISCOMMTOOL,T2.COMMTOOL,T2.LOGAPPSTAT,T2.SELVECSHOP,T2.BUYDATE,
'' HZT_TECHNICINFO, --车辆技术信息
T2.PRDFACTORY,T2.ELDTYPE,T2.ENGNUM,T2.FRANUM,T2.VINNUM,T2.ENGDISMOMENT,T2.ENGPOWER,T2.WHETREAD,T2.BEFWHETREAD,T2.TYRENUM,T2.FULTON,T2.FULEQUIP,T2.SPRNUM,T2.WHEBASE,T2.AXLNUM,
T2.PRODATE,T2.TYRSTANDARD,T2.ISFOREIGN,T2.RATROTSPEED,T2.ENGTORMOMENT,T2.ENGTORMOMROTSPEED,T2.TURFORMAT,T2.CHELODMASS,T2.CHEPSNNUM,T2.TRAMASS,T2.MSTHIGSPEED,T2.HUNMILUSEOIL,
T2.WITTRAANGLE,T2.WOTTRAANGLE,T2.TSMFORMAT,T2.BEFPDTFORMAT,T2.AFTPDTFORMAT,T2.FUESPOUTFORMAT,T2.BEFLAPFORMAT,T2.HYDFORMAT,T2.HAVABS,T2.CHEPOWER,T2.DRITYPE,T2.ARCSPLACE,T2.MILAGE,
'' HZT_OPERATION, --车辆业务信息
T2.YEARS,T2.TECGRD,T2.NEXTECGRDTODATE,T2.COSSTATE,T2.STACOLDATE,T2.COLTODATE,T2.CHETODATE,T2.VINDATNUM,
'' HZT_INSURANCE,  --保险信息
T2.TRAINSNO,T2.TRAINSENDDATE,T2.TRAINSMONEY,T2.THRINSNO,T2.THRINSENDDATE,T2.THRINSMONEY,
'' HZT_PASSCAR,  --客车信息
T2.PASGRADE,
'' HZT_DAGCAR,  --危货车信息
T2.DAGVECCARNO,T2.FRETRATYPE,T2.DANFREGRADE,T2.CONTYPE,
'' HZT_TEACHCAR, --教练车信息
T2.COAVECCARNO,T2.COADATE
FROM HYT2LINEHN.CMVEC T2
LEFT OUTER JOIN HYT2LINEHN.CMCLT T1 ON T1.CMCLTID=T2.CMCLTID
LEFT OUTER JOIN HYT2LINEHN.COUCANCODE T3 ON T2.VECADDR=T3.CANCODE
LEFT OUTER JOIN HYT2PRDHN.T_PF_DEP T4 ON T2.MANAGERDEPCODE=T4.DEPCODE
WHERE T2.ISEFFECT=0;
CREATE OR REPLACE VIEW HYT2LINEHN.V_CMCLT AS
SELECT
'' HZF_CMCLT,  --设置默认查询条件
'' HZS_CMCLT,  --设置默认查询列
'' HZT_CMCLTINFO,--业户基本信息
T1.CMCLTID,T1.CLITNAME,T1.CLTENTITYTYPE,T1.PERWORD,T1.PERNUM,T1.BNSCOPE,T1.DEPCODE CLT_DPNM,T1.GRADATE,T1.STADATE,T1.ENDDATE,T1.ADDRESS,T1.ECOTYPE,T1.MAICODING,T1.JURPERSON,T1.CTCNO,T1.PRINCIPAL,T1.PHONE,T1.CLTBUSSTATUS,T1.BUSLICNO,T1.TAXREGNO,T1.GRADEPCODE AS GRACODE_DPNM,T1.RPTCHKRSU,
'' HZT_PASSCMCLT, --客运业户综合信息
T1.HIGHPASVECSUM,T1.HIGHPASSEATSUM,T1.MIDPASVECSUM,T1.MIDPASSEATSUM,T1.NORPASVECSUM,T1.NORPASSEATSUM,T1.PASVECSUM,T1.PASSEATSUM,T1.DRIPASVECSUM,T1.SERPSNSUM,
'' HZT_FRECMCLT,--货运业户综合信息
T1.BIGVECSUM,T1.EXPVECSUM,T1.NORVECSUM,T1.OTHVECSUM,T1.TRACTORSUM,T1.FREVECSUM,T1.BIGVECTNSUM,T1.EXPVECTNSUM,T1.NORVECTNSUM,T1.OTHVECTNSUM,T1.TRACTORTNSUM,T1.DRIVERSUM,T1.STWDSUM,
'' HZT_DAGCMCLT, --危货业户综合信息
T1.POTVECSUM,T1.BOXVECSUM,T1.DAGVECSUM,T1.DAGVECTNSUM,T1.SPECLEEQ,T1.SLGCBKEQ,T1.SAFEEQUIP,T1.FIREEQUIP,T1.DANFREDRISUM,T1.SUPERCARGOSUM,T1.DANSTWDMANSUM,T1.SPEPRKAREA,T1.PRKLOTAREA,
'' HZT_DRVCMCLT, --驾培业户综合信息
T1.DRVSCHSORT,T1.DRVOPCOAS,T1.THYCOASUM,T1.CLRMAREA,T1.COCHAREA,T1.MESSAREA,T1.DORMAREA,T1.TRACTORS,T1.TRAILERS,T1.SMLFRETRUKS,T1.MIDFRETRUKS,T1.BIGFRETRUKS,T1.SMLPASCARS,T1.MIDPASCARS,T1.BIGPASCARS,T1.SMLMANUALCARS,T1.SMLAUTOCARS,T1.MOTORSUM,T1.OTRVECSUM,
'' HZT_MATACMCLT, --维修业户综合信息
T1.MATAOBJ,T1.MINSORT,T1.SIGN,T1.ISONO,T1.VINBNAREA,T1.VINSPEPRKAREA,T1.VINEQUIDSUM,T1.EQUIDCIRCS,T1.DETEQUCIRCS,T1.MINTECPSN,T1.TECHNIC,T1.IDENTIFIER,T1.CHIEFIDENTIFIER,T1.BALANCE,
'' HZT_PASSTACMCLT, --客运站业户综合信息
T1.PASSTAGRADE,T1.PASSTAAUHFILENO,T1.PASSTAADDRESS,T1.INVSTSCALE,T1.STASCALE,T1.STACHARACT,T1.SERTYPE,T1.DAYCAPACITY,T1.DESIGNNUM,T1.YEARCAPACITY,T1.STAJOBER,T1.PASSTAENDDATE,T1.PASSTACREDATE,T1.PASSTACHKACPDATE,T1.PASSTASERVICDATE,T1.PASSTAAREA,
'' HZT_FRESTACMCLT,--货运站业户综合信息
T1.FRESTAGRADE,T1.HEIGHT,T1.PROFESSIONAL,T1.FRESTAENDDATE,T1.FRESTACREDATE,T1.FRESTACHKACPDATE,T1.FRESTASERVICDATE,
'' HZT_DETCMCLT,--检测站业户综合信息
T1.DETSTAGRADE,T1.AUHFILENO,T1.JPANO,T1.CURRENTDATEJPAFR,T1.CURRENTDATEJPATO,T1.DISONO,T1.BELNGDEP,T1.EQUPANO,T1.CURRENTDATEEQUPADT,T1.BDEPADD,T1.BDEPTEL,T1.APAREA,T1.OCUAREA,T1.BUILDAREA,T1.CKAREA,T1.DPERSUM,T1.TECHSUM,T1.ENGSUM,T1.STAFFSUM,
'' HZT_REGCMCLT,--业户注册信息
T1.BNBANK,T1.CAPITALSUM,T1.FORCAPITAL,T1.FXNOWCAPTL,T1.FXBEGCAPTL,T1.FLOWCAPTL,
'' HZT_APP,   --业户许可信息
T2.APPDATE,T2.PERDECNO,
T3.PASSCHENUM,T3.PASSNUM,T3.FRECHENUM,T3.FRENUM,T3.DANGCHENUM,T3.DANGNUM
FROM HYT2LINEHN.CMCLT T1
LEFT OUTER JOIN HYT2LINEHN.APPCLTPER T2 ON T2.APPLYID=T1.APPLYID
LEFT OUTER JOIN (SELECT CMCLTID,SUM(CASE WHEN VECENTYPE=1 THEN NVL(CHENUM,0) ELSE 0 END) AS PASSCHENUM,SUM(CASE WHEN VECENTYPE=1 THEN 1 ELSE 0 END) AS PASSNUM,
       SUM(CASE WHEN VECENTYPE=2 THEN NVL(CHENUM,0) ELSE 0 END) AS FRECHENUM,SUM(CASE WHEN VECENTYPE=2 THEN 1 ELSE 0 END) AS FRENUM,
       SUM(CASE WHEN VECENTYPE IN ('3','23') THEN NVL(CHENUM,0) ELSE 0 END) AS DANGCHENUM,SUM(CASE WHEN VECENTYPE IN ('3','23') THEN 1 ELSE 0 END) AS DANGNUM
 FROM HYT2LINEHN.CMVEC
WHERE ISEFFECT=0  AND VECBUSSTATUS='10' GROUP BY CMCLTID) T3 ON T1.CMCLTID=T3.CMCLTID
WHERE T1.ISEFFECT=0;

CREATE OR REPLACE VIEW HYT2LINEHN.V_CMPSN AS
SELECT '' HZF_CMPSN,  --设置默认查询条件
'' HZS_CMPSN,  --设置默认查询列
'' HZT_CMCLTINFO,--业户信息
T2.CLITNAME,T2.CLTENTITYTYPE,T2.PERWORD,T2.PERNUM,T2.DEPCODE DEP,T2.ECOTYPE,
'' HZT_CMPSNINFO,--从业人员信息
T1.CMPSNID,T1.NAME,T1.SEX,T1.BIRTHDAY,T1.CARDNO,T1.NATION,T1.NATPLACE,T1.PHONE,T1.MAICODING,T1.ADDRESS,T1.EMAIL,T1.POLITICSFACE,T1.EDUCATION,T1.TECCLASS,T1.HEALTH,T1.WORSTATUS,T1.STUTIMES,
'' HZT_CERTIFICATE, --从业资格证信息
BEWORSCOPE,APPIXSCOPE,
T3.DOCNO,T3.TITNO,T3.STARTDATE FIRSTDATE,T3.GRADATE GRANTDATE,T3.CREDATE,T3.ENDDATE,T3.EXAMINEEAREA,T3.BEWORLEVEL,T3.TRATIMES,T4.depname ORG,T3.LICSTATUS
,T3.WORTYPE
FROM HYT2LINEHN.CMPSN T1
LEFT OUTER JOIN HYT2LINEHN.CMCLT T2 ON T1.CMCLTID=T2.CMCLTID
LEFT OUTER JOIN HYT2LINEHN.ENBEWORRECORD T3 ON T3.CMPSNID=T1.CMPSNID
left join hyt2prdhn.t_pf_dep t4 on t3.org=t4.depcode
WHERE T1.ISEFFECT=0 AND T3.ISEFFECT=0;


CREATE OR REPLACE VIEW HYT2LINEHN.V_DAGCAR AS
SELECT
'' HZF_CLTVEC,--设置默认查询条件
'' HZS_CLTVEC,--设置默认查询列
'' HZT_CMCLTINFO,--业户基本信息
CMVECID,CLITNAME,CLTENTITYTYPE,PERWORD,PERNUM,VEC_DPNM,ADDRESS,ECOTYPE,MAICODING,JURPERSON,CTCNO,PRINCIPAL,PHONE,CLTBUSSTATUS,BUSLICNO,TAXREGNO,GRACODE_DPNM,RPTCHKRSU,
'' HZT_VECINFO,  --车辆基本信息
VECADDR,TRAWORD,AREAABBR,TRANO,MANAGER_DPNM,GRADATE,CREDATE,STADATE,ENDDATE,BRACOLOR,BRANUM,FACTYPE,MODEL,VECTYPE,VECBUSSTATUS,
BNSCOPE,ARCNO,CSGUNIT,BUSTYPE,REGYPE,CHENUM,VECLENGTH,VECWIDE,VECHIGH,INSVECCARLONG,INSVECCARWIDE,INSVECCARHIGH,
ENGTYPE,VECCOLOR,SETDATE,BUYPRICE,TRANCHA,ISGOTOSYS,ISINOUT,ISCOMMTOOL,COMMTOOL,LOGAPPSTAT,SELVECSHOP,BUYDATE,
'' HZT_DAGCAR,  --危货车信息
DAGVECCARNO,FRETRATYPE,DANFREGRADE,CONTYPE,
'' HZT_TECHNICINFO, --车辆技术信息
PRDFACTORY,ELDTYPE,ENGNUM,FRANUM,VINNUM,ENGDISMOMENT,ENGPOWER,WHETREAD,BEFWHETREAD,TYRENUM,FULTON,FULEQUIP,SPRNUM,WHEBASE,AXLNUM,
PRODATE,TYRSTANDARD,ISFOREIGN,RATROTSPEED,ENGTORMOMENT,ENGTORMOMROTSPEED,TURFORMAT,CHELODMASS,CHEPSNNUM,TRAMASS,MSTHIGSPEED,HUNMILUSEOIL,
WITTRAANGLE,WOTTRAANGLE,TSMFORMAT,BEFPDTFORMAT,AFTPDTFORMAT,FUESPOUTFORMAT,BEFLAPFORMAT,HYDFORMAT,HAVABS,CHEPOWER,DRITYPE,ARCSPLACE,MILAGE,
'' HZT_OPERATION, --车辆业务信息
YEARS,TECGRD,NEXTECGRDTODATE,COSSTATE,CHETODATE,VINDATNUM,
'' HZT_INSURANCE,  --保险信息
TRAINSNO,TRAINSENDDATE,TRAINSMONEY,THRINSNO,THRINSENDDATE,THRINSMONEY
FROM HYT2LINEHN.V_CLTVEC
WHERE INSTR(VECENTYPE,'3')>0;
CREATE OR REPLACE VIEW HYT2LINEHN.V_DAGCMCLT AS
SELECT
'' HZF_CMCLT,  --设置默认查询条件
'' HZS_CMCLT,  --设置默认查询列
'' HZT_CMCLTINFO,--业户基本信息
CMCLTID,CLITNAME,PERWORD,PERNUM,BNSCOPE,CLT_DPNM,ADDRESS,ECOTYPE,GRADATE,STADATE,ENDDATE,MAICODING,JURPERSON,CTCNO,PRINCIPAL,PHONE,CLTBUSSTATUS,BUSLICNO,TAXREGNO,GRACODE_DPNM,RPTCHKRSU,
'' HZT_DAGCMCLT, --危货业户综合信息
POTVECSUM,BOXVECSUM,DAGVECSUM,DAGVECTNSUM,SPECLEEQ,SLGCBKEQ,SAFEEQUIP,FIREEQUIP,DANFREDRISUM,SUPERCARGOSUM,DANSTWDMANSUM,SPEPRKAREA,PRKLOTAREA,
'' HZT_REGCMCLT,--业户注册信息
BNBANK,CAPITALSUM,FORCAPITAL,FXNOWCAPTL,FXBEGCAPTL,FLOWCAPTL,
'' HZT_APP,   --业户许可信息
APPDATE,PERDECNO,DANGCHENUM,DANGNUM
FROM HYT2LINEHN.V_CMCLT A
WHERE bitand(A.CLTENTITYTYPE,'4')=4;
CREATE OR REPLACE VIEW HYT2LINEHN.V_DETCMCLT AS
SELECT
'' HZT_CMCLTINFO,--业户基本信息
CMCLTID,CLITNAME,PERWORD,PERNUM,BNSCOPE,CLT_DPNM,ADDRESS,ECOTYPE,GRADATE,STADATE,ENDDATE,MAICODING,JURPERSON,CTCNO,PRINCIPAL,PHONE,CLTBUSSTATUS,BUSLICNO,TAXREGNO,GRACODE_DPNM,RPTCHKRSU,
'' HZT_DETCMCLT,--检测站业户综合信息
DETSTAGRADE,AUHFILENO,JPANO,CURRENTDATEJPAFR,CURRENTDATEJPATO,DISONO,BELNGDEP,EQUPANO,CURRENTDATEEQUPADT,BDEPADD,BDEPTEL,APAREA,OCUAREA,BUILDAREA,CKAREA,DPERSUM,TECHSUM,ENGSUM,STAFFSUM,
'' HZT_REGCMCLT,--业户注册信息
BNBANK,CAPITALSUM,FORCAPITAL,FXNOWCAPTL,FXBEGCAPTL,FLOWCAPTL
FROM HYT2LINEHN.V_CMCLT A
WHERE bitand(A.CLTENTITYTYPE,'128')=128;
CREATE OR REPLACE VIEW HYT2LINEHN.V_DRILLMASTER AS
SELECT T1.COACHID,
CASE WHEN T1.CLITNAME IS NOT NULL THEN  T1.CLITNAME
     ELSE  (SELECT  CLITNAME FROM  HYT2LINEHN.CMCLT  WHERE CMCLTID=T1.CMCLTID)
     END  AS CLITNAME,
T1.DOCNO,T1.NAME,T1.CARDNO,T1.YEARS,T1.CARDTYPE,T1.PERIOD,
T1.SEX,T1.BIRTHDAY,T1.TITNO,T1.BEWORSCOPE,T1.LICSTATUS,T1.GRADATE,T1.STARTDATE,T1.ENDDATE,T1.ORG AS DEPCODE,
 T1.TECCLASS,T1.EDUCATION,T3.CANNAME AS EXAM,T1.EXAMINEEAREA
FROM HYT2LINEHN.COACH T1
LEFT OUTER JOIN HYT2LINEHN.COUCANCODE T3 ON T1.EXAMINEEAREA=T3.CANCODE WHERE T1.ISEFFECT=0;
CREATE OR REPLACE VIEW HYT2LINEHN.V_DRVCMCLT AS
SELECT
'' HZF_CMCLT,  --设置默认查询条件
'' HZS_CMCLT,  --设置默认查询列
'' HZT_CMCLTINFO,--业户基本信息
CMCLTID,CLITNAME,PERWORD,PERNUM,BNSCOPE,CLT_DPNM,ADDRESS,ECOTYPE,GRADATE,STADATE,ENDDATE,MAICODING,JURPERSON,CTCNO,PRINCIPAL,PHONE,CLTBUSSTATUS,BUSLICNO,TAXREGNO,GRACODE_DPNM,RPTCHKRSU,
'' HZT_DRVCMCLT, --驾培业户综合信息
DRVSCHSORT,DRVOPCOAS,THYCOASUM,CLRMAREA,COCHAREA,MESSAREA,DORMAREA,TRACTORS,TRAILERS,SMLFRETRUKS,MIDFRETRUKS,BIGFRETRUKS,SMLPASCARS,MIDPASCARS,BIGPASCARS,SMLMANUALCARS,SMLAUTOCARS,MOTORSUM,OTRVECSUM,
'' HZT_REGCMCLT,--业户注册信息
BNBANK,CAPITALSUM,FORCAPITAL,FXNOWCAPTL,FXBEGCAPTL,FLOWCAPTL,
'' HZT_APP,   --业户许可信息
APPDATE,PERDECNO
FROM HYT2LINEHN.V_CMCLT A
WHERE bitand(A.CLTENTITYTYPE,'16')=16;
CREATE OR REPLACE VIEW HYT2LINEHN.V_FRECAR AS
SELECT
'' HZF_CLTVEC,--设置默认查询条件
'' HZS_CLTVEC,--设置默认查询列
'' HZT_CMCLTINFO,--业户基本信息
CMVECID,CLITNAME,CLTENTITYTYPE,PERWORD,PERNUM,CLT_DPNM,ADDRESS,ECOTYPE,MAICODING,JURPERSON,CTCNO,PRINCIPAL,PHONE,CLTBUSSTATUS,BUSLICNO,TAXREGNO,GRACODE_DPNM,RPTCHKRSU,
'' HZT_VECINFO,  --车辆基本信息
VECADDR,TRAWORD,AREAABBR,TRANO,VEC_DPNM,MANAGER_DPNM,GRADATE,CREDATE,STADATE,ENDDATE,BRACOLOR,BRANUM,FACTYPE,MODEL,VECTYPE,VECBUSSTATUS,
BNSCOPE,ARCNO,CSGUNIT,BUSTYPE,REGYPE,CHENUM,VECLENGTH,VECWIDE,VECHIGH,INSVECCARLONG,INSVECCARWIDE,INSVECCARHIGH,
ENGTYPE,VECCOLOR,SETDATE,BUYPRICE,TRANCHA,ISGOTOSYS,ISINOUT,ISCOMMTOOL,COMMTOOL,LOGAPPSTAT,SELVECSHOP,BUYDATE,
'' HZT_TECHNICINFO, --车辆技术信息
PRDFACTORY,ELDTYPE,ENGNUM,FRANUM,VINNUM,ENGDISMOMENT,ENGPOWER,WHETREAD,BEFWHETREAD,TYRENUM,FULTON,FULEQUIP,SPRNUM,WHEBASE,AXLNUM,
PRODATE,TYRSTANDARD,ISFOREIGN,RATROTSPEED,ENGTORMOMENT,ENGTORMOMROTSPEED,TURFORMAT,CHELODMASS,CHEPSNNUM,TRAMASS,MSTHIGSPEED,HUNMILUSEOIL,
WITTRAANGLE,WOTTRAANGLE,TSMFORMAT,BEFPDTFORMAT,AFTPDTFORMAT,FUESPOUTFORMAT,BEFLAPFORMAT,HYDFORMAT,HAVABS,CHEPOWER,DRITYPE,ARCSPLACE,MILAGE,
'' HZT_OPERATION, --车辆业务信息
YEARS,TECGRD,NEXTECGRDTODATE,COSSTATE,CHETODATE,VINDATNUM,
'' HZT_INSURANCE,  --保险信息
TRAINSNO,TRAINSENDDATE,TRAINSMONEY,THRINSNO,THRINSENDDATE,THRINSMONEY
FROM HYT2LINEHN.V_CLTVEC
WHERE INSTR(VECENTYPE,'2')>0;
CREATE OR REPLACE VIEW HYT2LINEHN.V_FRECMCLT AS
SELECT
'' HZF_CMCLT,  --设置默认查询条件
'' HZS_CMCLT,  --设置默认查询列
'' HZT_CMCLTINFO,--业户基本信息
CMCLTID,CLITNAME,PERWORD,PERNUM,BNSCOPE,CLT_DPNM,ADDRESS,ECOTYPE,GRADATE,STADATE,ENDDATE,MAICODING,JURPERSON,CTCNO,PRINCIPAL,PHONE,CLTBUSSTATUS,BUSLICNO,TAXREGNO,GRACODE_DPNM,RPTCHKRSU,
'' HZT_FRECMCLT,--货运业户综合信息
BIGVECSUM,EXPVECSUM,NORVECSUM,OTHVECSUM,TRACTORSUM,FREVECSUM,BIGVECTNSUM,EXPVECTNSUM,NORVECTNSUM,OTHVECTNSUM,TRACTORTNSUM,DRIVERSUM,STWDSUM,
'' HZT_REGCMCLT,--业户注册信息
BNBANK,CAPITALSUM,FORCAPITAL,FXNOWCAPTL,FXBEGCAPTL,FLOWCAPTL,
'' HZT_APP,   --业户许可信息
APPDATE,PERDECNO,FRECHENUM,FRENUM
FROM HYT2LINEHN.V_CMCLT A
WHERE bitand(A.CLTENTITYTYPE,'2')=2;
CREATE OR REPLACE VIEW HYT2LINEHN.V_FRESTACMCLT AS
SELECT
'' HZF_CMCLT,  --设置默认查询条件
'' HZS_CMCLT,  --设置默认查询列
'' HZT_CMCLTINFO,--业户基本信息
CMCLTID,CLITNAME,PERWORD,PERNUM,BNSCOPE,CLT_DPNM,ADDRESS,ECOTYPE,GRADATE,STADATE,ENDDATE,MAICODING,JURPERSON,CTCNO,PRINCIPAL,PHONE,CLTBUSSTATUS,BUSLICNO,TAXREGNO,GRACODE_DPNM,RPTCHKRSU,
'' HZT_FRESTACMCLT,--货运站业户综合信息
FRESTAGRADE,HEIGHT,PROFESSIONAL,FRESTAENDDATE,FRESTACREDATE,FRESTACHKACPDATE,FRESTASERVICDATE,
'' HZT_REGCMCLT,--业户注册信息
BNBANK,CAPITALSUM,FORCAPITAL,FXNOWCAPTL,FXBEGCAPTL,FLOWCAPTL,
'' HZT_APP,   --业户许可信息
APPDATE,PERDECNO
FROM HYT2LINEHN.V_CMCLT A
WHERE bitand(A.CLTENTITYTYPE,'64')=64;
CREATE OR REPLACE VIEW HYT2LINEHN.V_LINCARD AS
SELECT T.LINCARID,T2.CLITNAME,T.GRADEPCODE,T.LIDCARDNO,T1.LINNAME,T1.LINCODE,T.LINCARTYPE,T.LINBUSSTATUS,T1.BUSAREA,T1.LINTYPE,T1.BYWAYOF,T1.SUMMIL,
         (CASE WHEN LENGTH(RTRIM(T1.STADEPOT, '0')) 0 THEN '3'
             ELSE '0' END SHOWTIMECOLOR
       FROM (SELECT H.STUAPPLICATIONID,H.STUHOURSRECORDID,H.CHEAKAPPROVALID,H.SUBJECT
            ,CASE WHEN H.OUTRIDETIMESTATUS='1' THEN H.OUTRIDETIMECOLOR ELSE '0' END OUTRIDETIMECOLOR
            ,CASE WHEN H.ONTIMESTATUS='1' THEN H.ONTIMECOLOR ELSE '0' END ONTIMECOLOR
            ,CASE WHEN H.EFFECTTIMESTATUS='1' THEN H.EFFECTTIMECOLOR ELSE '0' END EFFECTTIMECOLOR
            ,CASE WHEN H.COUNTSTATUS='1' THEN H.ISEFFECTCOUNT ELSE '0' END ISEFFECTCOUNT
            FROM (SELECT T2.STUAPPLICATIONID,T2.STUHOURSRECORDID,T2.CHEAKAPPROVALID,T2.SUBJECT,
                 CASE WHEN T2.RECORDTIMESTARTT4.DFTVALUE THEN '1' ELSE '0' END OUTRIDETIMESTATUS,
                 CASE WHEN DECODE(T2.EFFECTTIME/60,0,0,ROUND(T2.EFFECTTIME/60,1))>T5.DFTVALUE THEN '1' ELSE '0' END EFFECTTIMESTATUS,
                 CASE WHEN ( SELECT COUNT(A.RECORDTIMESTART) FROM TSSTUHOURSRECORD A  WHERE A.ISEFFECT='0' AND A.STUAPPLICATIONID=t2.STUAPPLICATIONID
                     AND A.CHEAKAPPROVALID=T2.CHEAKAPPROVALID AND A.RECORDTIMESTART=T2.RECORDTIMESTART GROUP BY A.RECORDTIMESTART )> = T8.DFTVALUE
                        or ( SELECT COUNT(A.RECORDTIMEEND) FROM TSSTUHOURSRECORD A  WHERE A.ISEFFECT='0' AND A.STUAPPLICATIONID=t2.STUAPPLICATIONID
                        AND A.RECORDTIMEEND=T2.RECORDTIMEEND  AND A.CHEAKAPPROVALID=T2.CHEAKAPPROVALID GROUP BY A.RECORDTIMEEND )> = T8.DFTVALUE
                         THEN '1' ELSE '0' END COUNTSTATUS,
                 CASE WHEN SUBSTR(T2.RECORDTIMESTART,4,5)='00:00' OR SUBSTR(T2.RECORDTIMEEND,4,5)='00:00' THEN '1' ELSE '0' END ONTIMESTATUS,
                T6.DFTVALUE OUTRIDETIMECOLOR,T7.DFTVALUE ONTIMECOLOR,T8.DFTVALUE AS EFFECTTIMECOLOR,T10.DFTVALUE AS ISEFFECTCOUNT
                 FROM TSSTUHOURSRECORD T2
                LEFT JOIN V_LINEPARAM T3
                ON T3.PARAMCODE='OUTRIDSTARTTIME'
                LEFT JOIN V_LINEPARAM T4
                ON T4.PARAMCODE='OUTRIDENDTIME'
                LEFT JOIN V_LINEPARAM T5
                ON T5.PARAMCODE='EFFECTTIME'
                LEFT JOIN V_LINEPARAM T6
                ON T6.PARAMCODE='OUTRIDETIMESTATUS'
                LEFT JOIN V_LINEPARAM T7
                ON T7.PARAMCODE='ONTIMESTATUS'
                LEFT JOIN V_LINEPARAM T8
                ON T8.PARAMCODE='EFFECTTIMESTATUS'
                 LEFT JOIN V_LINEPARAM T9
                ON T9.PARAMCODE='COUNTSTATUS'
                LEFT JOIN V_LINEPARAM T10
                ON T10.PARAMCODE='ISEFFECTCOUNT'
                WHERE T2.ISEFFECT='0'
                )H
            )M;
            CREATE OR REPLACE VIEW HYT2LINEHN.V_STUTIMESTATUS2 AS
SELECT T2.STUAPPLICATIONID,T2.STUHOURSRECORDID,T2.CHEAKAPPROVALID,T2.SUBJECT,TT1.CREDATE,
                 CASE WHEN T2.RECORDTIMESTARTT4.DFTVALUE THEN '1'
                   WHEN DECODE(T2.EFFECTTIME/60,0,0,ROUND(T2.EFFECTTIME/60,1))>T5.DFTVALUE THEN '1'
                  WHEN ( SELECT COUNT(A.RECORDTIMESTART) FROM TSSTUHOURSRECORD A  WHERE A.ISEFFECT='0' AND A.STUAPPLICATIONID=T2.STUAPPLICATIONID
                     AND A.CHEAKAPPROVALID=T2.CHEAKAPPROVALID AND A.RECORDTIMESTART=T2.RECORDTIMESTART GROUP BY A.RECORDTIMESTART )> = T6.DFTVALUE
                        OR ( SELECT COUNT(A.RECORDTIMEEND) FROM TSSTUHOURSRECORD A  WHERE A.ISEFFECT='0' AND A.STUAPPLICATIONID=T2.STUAPPLICATIONID
                        AND A.RECORDTIMEEND=T2.RECORDTIMEEND  AND A.CHEAKAPPROVALID=T2.CHEAKAPPROVALID GROUP BY A.RECORDTIMEEND )> = T6.DFTVALUE
                         THEN '1' ELSE '0' END SHOWTIMECOLOR
                            FROM TSSTUHOURSRECORD T2
                INNER JOIN TSCHEAKAPPROVALDETAIL TT1
                    ON T2.STUAPPLICATIONID = TT1.STUAPPLICATIONID AND T2.CHEAKAPPROVALID=TT1.CHEAKAPPROVALID
                   AND (TT1.GOVCHEAKAPPROVALSTATUS ='01' OR TT1.GOVCHEAKAPPROVALSTATUS IS NULL)
                   AND TT1.CREDATE >'2011-10-21'
                AND TT1.ISEFFECT='0'
                LEFT JOIN V_LINEPARAM T3
                ON T3.PARAMCODE='OUTRIDSTARTTIME'
                LEFT JOIN V_LINEPARAM T4
                ON T4.PARAMCODE='OUTRIDENDTIME'
                LEFT JOIN V_LINEPARAM T5
                ON T5.PARAMCODE='EFFECTTIME'
                LEFT JOIN V_LINEPARAM T6
                ON T6.PARAMCODE='EFFECTTIMESTATUS'
                WHERE T2.ISEFFECT='0';
                CREATE OR REPLACE VIEW HYT2LINEHN.V_TEACHCAR AS
SELECT
'' HZF_CLTVEC,--设置默认查询条件
'' HZS_CLTVEC,--设置默认查询列
'' HZT_CMCLTINFO,--业户基本信息
CLITNAME,CLTENTITYTYPE,PERWORD,PERNUM,CLT_DPNM,ADDRESS,ECOTYPE,MAICODING,JURPERSON,CTCNO,PRINCIPAL,PHONE,CLTBUSSTATUS,BUSLICNO,TAXREGNO,GRACODE_DPNM,RPTCHKRSU,
'' HZT_VECINFO,  --车辆基本信息
CMVECID,VECADDR,TRAWORD,AREAABBR,TRANO,VEC_DPNM,MANAGER_DPNM,GRADATE,STADATE,ENDDATE,BRACOLOR,BRANUM,FACTYPE,MODEL,VECTYPE,VECBUSSTATUS,
BNSCOPE,ARCNO,CSGUNIT,BUSTYPE,REGYPE,CHENUM,COLNUM,VECLENGTH,VECWIDE,VECHIGH,INSVECCARLONG,INSVECCARWIDE,INSVECCARHIGH,
ENGTYPE,VECCOLOR,SETDATE,BUYPRICE,TRANCHA,ISGOTOSYS,ISINOUT,ISCOMMTOOL,COMMTOOL,LOGAPPSTAT,SELVECSHOP,BUYDATE,
'' HZT_TEACHCAR, --教练车信息
COAVECCARNO,COADATE,
'' HZT_TECHNICINFO, --车辆技术信息
PRDFACTORY,ELDTYPE,ENGNUM,FRANUM,VINNUM,ENGDISMOMENT,ENGPOWER,WHETREAD,BEFWHETREAD,TYRENUM,FULTON,FULEQUIP,SPRNUM,WHEBASE,AXLNUM,
PRODATE,TYRSTANDARD,ISFOREIGN,RATROTSPEED,ENGTORMOMENT,ENGTORMOMROTSPEED,TURFORMAT,CHELODMASS,CHEPSNNUM,TRAMASS,MSTHIGSPEED,HUNMILUSEOIL,
WITTRAANGLE,WOTTRAANGLE,TSMFORMAT,BEFPDTFORMAT,AFTPDTFORMAT,FUESPOUTFORMAT,BEFLAPFORMAT,HYDFORMAT,HAVABS,CHEPOWER,DRITYPE,ARCSPLACE,MILAGE,
'' HZT_OPERATION, --车辆业务信息
YEARS,TECGRD,NEXTECGRDTODATE,COSSTATE,STACOLDATE,COLTODATE,CHETODATE,VINDATNUM,
'' HZT_INSURANCE,  --保险信息
TRAINSNO,TRAINSENDDATE,TRAINSMONEY,THRINSNO,THRINSENDDATE,THRINSMONEY
FROM HYT2LINEHN.V_CLTVEC
WHERE VECENTYPE=4;
CREATE OR REPLACE VIEW HYT2LINEHN.V_TOTALCAR AS
SELECT T2.CMVECID,T4.CANNAME,T1.CLITNAME,T1.PERNUM,T2.BRACOLOR,T2.BRANUM,T2.CHENUM,
       T2.VECTYPE,T2.VECBUSSTATUS,T2.TRANO,T2.CHETODATE,T2.ENGNUM,T2.FRANUM
  FROM HYT2LINEHN.CMVEC T2
  LEFT OUTER JOIN HYT2LINEHN.CMCLT T1 ON T2.CMCLTID=T1.CMCLTID
  LEFT OUTER JOIN HYT2LINEHN.COUCANCODE T4 ON T2.VECADDR=T4.CANCODE
  WHERE T2.ISEFFECT=0 AND
		(INSTR(T2.VECENTYPE,'5')>=1 OR INSTR(T2.VECENTYPE,'6')>=1 OR INSTR(T2.VECENTYPE,'7')>=1);
		CREATE OR REPLACE VIEW HYT2LINEHN.V_TOTALCMCLT AS
SELECT   T.CMCLTID, T.CLITNAME, T.DEPCODE,PERWORD || PERNUM AS PERNUM,T.ECOTYPE,T.BNSCOPE,T.CLTBUSSTATUS,TO_CHAR(T.CLTENTITYTYPE) AS CLTENTITYTYPE,
T.BNBGNDATE,T.GRADATE,T.STADATE,T.ENDDATE,T.PRINCIPAL,T.ZYMJ,T.CTCNO,T.STAFFSUM,T.SPERSUM,T.JOBERSUM
FROM HYT2LINEHN.CMCLT T WHERE T.ISEFFECT=0
AND (bitand(T.CLTENTITYTYPE,512)=512 OR bitand(T.CLTENTITYTYPE,1024)=1024 OR bitand(T.CLTENTITYTYPE,2048)=2048 );
CREATE OR REPLACE VIEW HYT2LINEHN.V_VECTECH AS
SELECT T1.CMVECID,T1.BRACOLOR,T1.BRANUM,T1.TRAWORD||T1.TRANO AS TRANO,T1.ENDDATE,T2.OPERATEID,T1.DEPCODE,
       T2.VINDDATE,T2.VINDNEXTDATE,T2.CLITNAME,T2.VINDCONNO,T2.VINDGRDNO,T2.GRDTODATE,T2.VINDREPNO,
       T2.TECID  AS TECGRD,T2.GRADE AS PASGRADE,T2.QUALITYNAME,T2.DETRESULT,T3.USERNAME,T2.REMARKC
FROM HYT2LINEHN.CMVEC T1
INNER JOIN HYT2LINEHN.VECTECH T2 ON T1.CMVECID=T2.CMVECID
LEFT OUTER JOIN HYT2PRDHN.T_PF_USER T3 ON T3.USERCODE=T2.CREUSRCODE
WHERE T1.ISEFFECT=0;

CREATE OR REPLACE VIEW HYT2LINEHN.V_BUSSTACMCLT AS
SELECT
'' HZF_CMCLT,  --设置默认查询条件
'' HZS_CMCLT,  --设置默认查询列
'' HZT_CMCLTINFO,--业户基本信息
CMCLTID,CLITNAME,PERWORD,PERNUM,BNSCOPE,CLT_DPNM,ADDRESS,ECOTYPE,GRADATE,STADATE,ENDDATE,MAICODING,JURPERSON,CTCNO,PRINCIPAL,PHONE,CLTBUSSTATUS,BUSLICNO,TAXREGNO,GRACODE_DPNM,RPTCHKRSU,
'' HZT_BUSSTACMCLT,--客运站业户综合信息
PASSTAGRADE,PASSTAADDRESS,INVSTSCALE,STASCALE,STACHARACT,SERTYPE,DAYCAPACITY,DESIGNNUM,YEARCAPACITY,STAJOBER,PASSTAENDDATE,PASSTACREDATE,PASSTACHKACPDATE,PASSTASERVICDATE,PASSTAAREA,
'' HZT_REGCMCLT,--业户注册信息
BNBANK,CAPITALSUM,FORCAPITAL,FXNOWCAPTL,FXBEGCAPTL,FLOWCAPTL,
'' HZT_APP,   --业户许可信息
APPDATE,PERDECNO
FROM HYT2LINEHN.V_CMCLT A
WHERE  BITAND(A.CLTENTITYTYPE,'32')=32;

CREATE OR REPLACE VIEW HYT2LINEHN.V_TAXICMCLT AS
SELECT CMCLTID, CLITNAME, DEPCODE, BNSCOPE, BNSCOPE AS S_1BNSCOPE,CLTBUSSTATUS, FILNO,
           PERWORD || PERNUM AS PERNUM,ECOTYPE,JURPERSON, PHONE,
           (CASE WHEN (FILNO = '' OR FILNO IS NULL) THEN  '否'  ELSE '是' END) AS IFBRANCH
    FROM HYT2LINEHN.CMCLT T
    WHERE ISEFFECT = '0' AND bitand(T.CLTENTITYTYPE,1024)=1024
    ORDER BY  T.CREDATE DESC;
    
CREATE OR REPLACE VIEW HYT2LINEHN.V_KYCMCLT AS
SELECT T.CMCLTID, CLITNAME, DEPCODE, BNSCOPE,CLTBUSSTATUS,
           PERWORD || PERNUM AS PERNUM,ECOTYPE,JURPERSON, PHONE,T.ADDRESS,T1.SUMVEC AS 车辆数
    FROM HYT2LINEHN.CMCLT T
    LEFT OUTER JOIN (SELECT CMCLTID,SUM(NVL(CHENUM,0)) AS CHENUM,COUNT(1) AS SUMVEC FROM HYT2LINEHN.CMVEC WHERE ISEFFECT=0 AND VECENTYPE='1' AND VECBUSSTATUS='10' GROUP BY CMCLTID) T1 ON T.CMCLTID=T1.CMCLTID
    WHERE ISEFFECT = '0'
     AND BITAND(T.CLTENTITYTYPE,1)=1
    ORDER BY  T.CREDATE,T.CRETIME;
    
    CREATE OR REPLACE VIEW HYT2LINEHN.V_KYVEC AS
SELECT T2.CMVECID,T4.CANNAME AS
    车籍地,T1.CLITNAME,T1.PERNUM,T2.BRACOLOR,T2.BRANUM,T2.CHENUM,T2.VINNEXTDATE,
    T2.VECTYPE,T2.VECBUSSTATUS,T2.TRANO,T2.CHETODATE,T2.FACTYPE,T2.SETDATE,T2.TECGRD,T2.NEXTECGRDTODATE
    FROM HYT2LINEHN.CMVEC T2
    LEFT OUTER JOIN HYT2LINEHN.CMCLT T1 ON
    T2.CMCLTID=T1.CMCLTID
    LEFT OUTER JOIN HYT2LINEHN.COUCANCODE T4 ON
    T2.VECADDR=T4.CANCODE
    WHERE T2.ISEFFECT=0 AND T2.VECENTYPE=1 ORDER BY T2.CREDATE DESC,T2.CRETIME DESC;
    
    CREATE OR REPLACE VIEW HYT2LINEHN.V_QCZLCAR AS
SELECT T2.CMVECID,T4.CANNAME,T1.CLITNAME,T1.PERNUM,T2.BRACOLOR,T2.BRANUM,T2.CHENUM,
       T2.VECTYPE,T2.VECBUSSTATUS,T2.TRANO,T2.CHETODATE,T2.ENGNUM,T2.FRANUM
  FROM HYT2LINEHN.CMVEC T2
  LEFT OUTER JOIN HYT2LINEHN.CMCLT T1 ON T2.CMCLTID=T1.CMCLTID
  LEFT OUTER JOIN HYT2LINEHN.COUCANCODE T4 ON T2.VECADDR=T4.CANCODE
  WHERE T2.ISEFFECT=0 AND
	INSTR(T2.VECENTYPE,'7')>=1;

CREATE OR REPLACE VIEW HYT2LINEHN.V_QCZLCMCLT AS
SELECT   T.CMCLTID, T.CLITNAME, T.DEPCODE,PERWORD || PERNUM AS PERNUM,T.ECOTYPE,T.BNSCOPE,T.CLTBUSSTATUS,TO_CHAR(T.CLTENTITYTYPE) AS CLTENTITYTYPE,
T.BNBGNDATE,T.GRADATE,T.STADATE,T.ENDDATE,T.PRINCIPAL,T.ZYMJ,T.CTCNO,T.STAFFSUM,T.SPERSUM,T.JOBERSUM
FROM HYT2LINEHN.CMCLT T WHERE T.ISEFFECT=0
AND bitand(T.CLTENTITYTYPE,2048)=2048 ;

CREATE OR REPLACE VIEW HYT2LINEHN.V_CSGJCAR AS
SELECT T2.CMVECID,T4.CANNAME,T1.CLITNAME,T1.PERNUM,T2.BRACOLOR,T2.BRANUM,T2.CHENUM,
       T2.VECTYPE,T2.VECBUSSTATUS,T2.TRANO,T2.CHETODATE,T2.ENGNUM,T2.FRANUM
  FROM HYT2LINEHN.CMVEC T2
  LEFT OUTER JOIN HYT2LINEHN.CMCLT T1 ON T2.CMCLTID=T1.CMCLTID
  LEFT OUTER JOIN HYT2LINEHN.COUCANCODE T4 ON T2.VECADDR=T4.CANCODE
  WHERE T2.ISEFFECT=0 AND
		( INSTR(T2.VECENTYPE,'6')>=1 );

CREATE OR REPLACE VIEW HYT2LINEHN.V_CSGJCMCLT AS
SELECT   T.CMCLTID, T.CLITNAME, T.DEPCODE,PERWORD || PERNUM AS PERNUM,T.ECOTYPE,T.BNSCOPE,T.CLTBUSSTATUS,TO_CHAR(T.CLTENTITYTYPE) AS CLTENTITYTYPE,
T.BNBGNDATE,T.GRADATE,T.STADATE,T.ENDDATE,T.PRINCIPAL,T.ZYMJ,T.CTCNO,T.STAFFSUM,T.SPERSUM,T.JOBERSUM
FROM HYT2LINEHN.CMCLT T WHERE T.ISEFFECT=0
AND (bitand(T.CLTENTITYTYPE,512)=512 );


CREATE OR REPLACE VIEW HYT2LINEHN.V_TOTALTAX AS
SELECT T2.CMVECID,T4.CANNAME,T1.CLITNAME,T1.PERNUM,T2.BRACOLOR,T2.BRANUM,T2.CHENUM,
       T2.VECTYPE,T2.VECBUSSTATUS,T2.TRANO,T2.CHETODATE,T2.ENGNUM,T2.FRANUM
  FROM HYT2LINEHN.CMVEC T2
  LEFT OUTER JOIN HYT2LINEHN.CMCLT T1 ON T2.CMCLTID=T1.CMCLTID
  LEFT OUTER JOIN HYT2LINEHN.COUCANCODE T4 ON T2.VECADDR=T4.CANCODE
  WHERE T2.ISEFFECT=0 AND
    (INSTR(T2.VECENTYPE,'5')>=1);

第七步：创建过程

CREATE OR REPLACE FUNCTION HYT2LINEHN.GETBNSCOPE(BNSCOPES IN VARCHAR2) RETURN VARCHAR2
IS
  STRRESULT VARCHAR2(4000);
  IDX NUMBER(5):=1;
  NEXT_IDX NUMBER(5):=1;
  IN_STR VARCHAR2(4000);
  BNAME VARCHAR2(500);
  BCODE VARCHAR2(4000);
BEGIN
  IN_STR:=BNSCOPES;
  IF INSTR(IN_STR,',',IDX)0 THEN
    loop
      NEXT_IDX:=INSTR(IN_STR,',',IDX);
      IF NEXT_IDX0 THEN
        BCODE := SUBSTR(IN_STR,IDX,NEXT_IDX-IDX);
        SELECT NAME  INTO BNAME FROM  HYT2PFHN.T_PF_DATADICT  WHERE   DDFLD='BNSCOPE'
        AND CODE=BCODE;
      ELSE
        SELECT NAME  INTO BNAME FROM  HYT2PFHN.T_PF_DATADICT  WHERE   DDFLD='BNSCOPE'
        AND CODE=IN_STR;
      END IF;
      STRRESULT := STRRESULT || BNAME || ',';
      IN_STR:=SUBSTR(IN_STR,NEXT_IDX+1);
      IF  NEXT_IDX=0 THEN  IDX:=NEXT_IDX; END IF;
      exit when (IDX=0 OR LENGTH(IN_STR)=0);
    end loop;
  ELSE
    SELECT NAME  INTO BNAME FROM HYT2PFHN.T_PF_DATADICT  WHERE   DDFLD='BNSCOPE'
    AND CODE=BNSCOPES;
    STRRESULT :=BNAME;
  END IF;

  IF LENGTH(STRRESULT)>0 THEN
   STRRESULT := SUBSTR(STRRESULT,1,LENGTH(STRRESULT)-1);
  END IF;

  RETURN STRRESULT;

END GETBNSCOPE;
/
CREATE OR REPLACE FUNCTION HYT2LINEHN.REPORTMAJORBRAND(CMCLTID IN VARCHAR2) RETURN VARCHAR2
IS
  CURSOR CS_FORMINPUT is
      SELECT  F1 FROM  HYT2LINEHN.FORMINPUT where DATUMID='MAINREPAIR' AND ENTITYID = CMCLTID ;
   
  STRRESULT VARCHAR2(4000);  
BEGIN  
  for VAR_TYPE in CS_FORMINPUT loop
        STRRESULT := STRRESULT || VAR_TYPE.F1 ||',' ;
    end loop;
  IF LENGTH(STRRESULT)>0 THEN
   STRRESULT := SUBSTR(STRRESULT,1,LENGTH(STRRESULT)-1);
  END IF;
  RETURN STRRESULT;

END REPORTMAJORBRAND;
/
create or replace function HYT2LINEHN.getBinObj(v_refobjid in varchar2, v_datumid in varchar2) return varchar2 is
  result varchar2(2000);
  cursor c_binobj is select objsrc||'|'||filename||'.'||extname as info from binobj
         where iseffect=0 and refobjid=v_refobjid and datumid=v_datumid;
begin
  result:=null;
  for v_binobj in c_binobj loop
    if result is null then
      result:=v_binobj.info;   
    else
      result:=result||':'||v_binobj.info;   
    end if;
     
  end loop;  
  
  return(result);
end getBinObj;
/
CREATE OR REPLACE FUNCTION HYT2LINEHN.GETBNSCOPE(BNSCOPES IN VARCHAR2) RETURN VARCHAR2
IS
  STRRESULT VARCHAR2(4000);
  IDX NUMBER(5):=1;
  NEXT_IDX NUMBER(5):=1;
  IN_STR VARCHAR2(4000);
  BNAME VARCHAR2(500);
  BCODE VARCHAR2(4000);
BEGIN
  IN_STR:=BNSCOPES;
  IF INSTR(IN_STR,',',IDX)0 THEN
    loop
      NEXT_IDX:=INSTR(IN_STR,',',IDX);
      IF NEXT_IDX0 THEN
        BCODE := SUBSTR(IN_STR,IDX,NEXT_IDX-IDX);
        SELECT NAME  INTO BNAME FROM  HYT2PFHN.T_PF_DATADICT  WHERE   DDFLD='BNSCOPE'
        AND CODE=BCODE;
      ELSE
        SELECT NAME  INTO BNAME FROM  HYT2PFHN.T_PF_DATADICT  WHERE   DDFLD='BNSCOPE'
        AND CODE=IN_STR;
      END IF;
      STRRESULT := STRRESULT || BNAME || ',';
      IN_STR:=SUBSTR(IN_STR,NEXT_IDX+1);
      IF  NEXT_IDX=0 THEN  IDX:=NEXT_IDX; END IF;
      exit when (IDX=0 OR LENGTH(IN_STR)=0);
    end loop;
  ELSE
    SELECT NAME  INTO BNAME FROM HYT2PFHN.T_PF_DATADICT  WHERE   DDFLD='BNSCOPE'
    AND CODE=BNSCOPES;
    STRRESULT :=BNAME;
  END IF;

  IF LENGTH(STRRESULT)>0 THEN
   STRRESULT := SUBSTR(STRRESULT,1,LENGTH(STRRESULT)-1);
  END IF;

  RETURN STRRESULT;

END GETBNSCOPE;
/
CREATE OR REPLACE FUNCTION HYT2PFHN.GETBNSCOPE(BNSCOPES IN VARCHAR2) RETURN VARCHAR2
IS
  STRRESULT VARCHAR2(4000);
  IDX NUMBER(5):=1;
  NEXT_IDX NUMBER(5):=1;
  IN_STR VARCHAR2(4000);
  BNAME VARCHAR2(500);
  BCODE VARCHAR2(4000);
BEGIN
  IN_STR:=BNSCOPES;
  IF INSTR(IN_STR,',',IDX)0 THEN
    loop
      NEXT_IDX:=INSTR(IN_STR,',',IDX);
      IF NEXT_IDX0 THEN
        BCODE := SUBSTR(IN_STR,IDX,NEXT_IDX-IDX);
        SELECT NAME  INTO BNAME FROM  HYT2PFHN.T_PF_DATADICT  WHERE   DDFLD='BNSCOPE'
        AND CODE=BCODE;
      ELSE
        SELECT NAME  INTO BNAME FROM  HYT2PFHN.T_PF_DATADICT  WHERE   DDFLD='BNSCOPE'
        AND CODE=IN_STR;
      END IF;
      STRRESULT := STRRESULT || BNAME || ',';
      IN_STR:=SUBSTR(IN_STR,NEXT_IDX+1);
      IF  NEXT_IDX=0 THEN  IDX:=NEXT_IDX; END IF;
      exit when (IDX=0 OR LENGTH(IN_STR)=0);
    end loop;
  ELSE
    SELECT NAME  INTO BNAME FROM HYT2PFHN.T_PF_DATADICT  WHERE   DDFLD='BNSCOPE'
    AND CODE=BNSCOPES;
    STRRESULT :=BNAME;
  END IF;

  IF LENGTH(STRRESULT)>0 THEN
   STRRESULT := SUBSTR(STRRESULT,1,LENGTH(STRRESULT)-1);
  END IF;

  RETURN STRRESULT;

END GETBNSCOPE;
/

第八步：部署工程

把发布包里的工程 copy 到 E:\hbyz\v22 下，然后找到 Tomcat 的 server.xml 进行配置：

再启动 Tomcat 就 OK 了