文章目錄

• 第一步：先連接遠程 Windows 主機：
• 第二步：連接遠程 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.LINBUSSTATUSFROM HYT2LINEHN.BUSLINECARD T1LEFT OUTER JOIN HYT2LINEHN.CMCLT T2 ON T1.CMCLTID=T2.CMCLTIDLEFT OUTER JOIN HYT2LINEHN.BUSLINE T3 ON T1.BUSLINEID=T3.BUSLINEIDWHERE 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.DOWNSTATIONCNFROM HYT2LINEHN.BUSLINE T1WHERE 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 DANGNUMFROM 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.CLITNAMEELSE (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')) <= 6 THEN (SELECT CANNAME FROM COUCANCODE WHERE CANCODE=SUBSTR(T1.STADEPOT,0,6))WHEN LENGTH(RTRIM(T1.STADEPOT, '0')) <= 9 THEN (SELECT CANNAME FROM VILTOWCANCODE WHERE CANCODE=SUBSTR(T1.STADEPOT,0,9))ELSE (SELECT CANNAME FROM VILTOWCANCODE WHERE CANCODE=RTRIM(T1.STADEPOT, '0')) END) AS STADEPOT,(CASE WHEN LENGTH(RTRIM(T1.ENDDEPOT, '0')) <= 6 THEN (SELECT CANNAME FROM COUCANCODE WHERE CANCODE=SUBSTR(T1.ENDDEPOT,0,6))WHEN LENGTH(RTRIM(T1.STADEPOT, '0')) <= 9 THEN (SELECT CANNAME FROM VILTOWCANCODE WHERE CANCODE=SUBSTR(T1.ENDDEPOT,0,9))ELSE (SELECT CANNAME FROM VILTOWCANCODE WHERE CANCODE=RTRIM(T1.ENDDEPOT, '0')) END) AS ENDDEPOT,(SELECT CANNAME FROM COUCANCODE WHERE CANCODE=T1.STALINDEPCODE)AS STALINDEPCODE,(SELECT CANNAME FROM COUCANCODE WHERE CANCODE=T1.ENDLINDEPCODE)AS ENDLINDEPCODE,T4.REGCTCTYPE,T4.DAYS||'天'||T4.TIMES||'班次' AS DAYSTIMES,T4.PERVECNUM,T4.PASSEANUM,T4.STADATE AS APPSTADATE,T4.ENDDATE AS APPENDDATE,T2.PERWORD,T2.PERNUM,T3.BRANUM,T3.BRACOLOR,T3.TRAWORD,T3.TRANO,T3.STADATE AS VECSTADATE,T3.ENDDATE AS VECENDDATE,T3.VECTYPE, T3.PASGRADE,T.BERTHFROM HYT2LINEHN.LINCARD TLEFT JOIN HYT2LINEHN.LINE T1 ON T1.LINID=T.LINIDLEFT JOIN HYT2LINEHN.CMCLT T2 ON T2.CMCLTID=T.CMCLTIDLEFT JOIN HYT2LINEHN.CMVEC T3 ON T3.CMVECID=T.CMVECIDLEFT OUTER JOIN HYT2LINEHN.LINPERREG T4 ON T4.LINPERREGID=T.LINPERREGIDWHERE T.ISEFFECT=0;CREATE OR REPLACE VIEW HYT2LINEHN.V_LINEPARAM AS SELECT CASE WHEN A2.PARAMVALUE IS NOT NULL THEN A2.PARAMVALUEELSE A1.DFTVALUE END DFTVALUE,A1.PARAMCODE FROM T_PARAMCODE A1 LEFT JOIN HYT2LINEHN.T_CUSTPARAM A2 ON A1.PARAMCODE=A2.PARAMCODE; CREATE OR REPLACE VIEW HYT2LINEHN.V_LINPERREG AS SELECT T.LINPERREGID, T1.CLITNAME, T1.PERNUM, T2.LINNAME,T2.LINCODE,T3.STADEPOTNAME AS STADEPOT,T4.ENDDEPOTNAME AS ENDDEPOT,T.STADATE,T.ENDDATE,T2.BUSAREA,T2.SUMMIL,T.PERDECNO,T.LINTYPE,T.REGCTCTYPE, T.PERVECNUM,T2.BYWAYOF,T5.BERTHFROM HYT2LINEHN.LINPERREG TLEFT JOIN HYT2LINEHN.CMCLT T1 ON T.CMCLTID=T1.CMCLTIDLEFT JOIN HYT2LINEHN.LINE T2 ON T.LINID=T2.LINIDLEFT OUTER JOIN (SELECT CANNAME AS STADEPOTNAME, RPAD(CANCODE, 11, '0') CANCODE FROM HYT2LINEHN.COUCANCODE UNIONSELECT CANNAME , RPAD(CANCODE, 11, '0') CANCODE FROM HYT2LINEHN.VILTOWCANCODE) T3 ON T2.STADEPOT=T3.CANCODELEFT OUTER JOIN (SELECT CANNAME AS ENDDEPOTNAME, RPAD(CANCODE, 11, '0') CANCODE FROM HYT2LINEHN.COUCANCODE UNIONSELECT CANNAME , RPAD(CANCODE, 11, '0') CANCODE FROM HYT2LINEHN.VILTOWCANCODE) T4 ON T2.ENDDEPOT=T4.CANCODELEFT JOIN HYT2LINEHN.LINCARD T5 ON T5.LINPERREGID=T.LINPERREGIDWHERE T.ISEFFECT=0;CREATE OR REPLACE VIEW HYT2LINEHN.V_MATACMCLT 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_MATACMCLT, --維修業戶綜合信息 MATAOBJ,MINSORT,SIGN,ISONO,VINBNAREA,VINSPEPRKAREA,VINEQUIDSUM,MINTECPSN,TECHNIC,IDENTIFIER,CHIEFIDENTIFIER,BALANCE, '' HZT_REGCMCLT,--業戶注冊信息 BNBANK,CAPITALSUM,FORCAPITAL,FXNOWCAPTL,FXBEGCAPTL,FLOWCAPTL, '' HZT_APP, --業戶許可信息 APPDATE,PERDECNO FROM HYT2LINEHN.V_CMCLT A WHERE bitand(A.CLTENTITYTYPE,'8')=8; CREATE OR REPLACE VIEW HYT2LINEHN.V_PASSCAR 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_PASSCAR, --客車信息 PASGRADE, '' 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 VECENTYPE=1; CREATE OR REPLACE VIEW HYT2LINEHN.V_PASSCMCLT 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_PASSCMCLT, --客運業戶綜合信息 HIGHPASVECSUM,HIGHPASSEATSUM,MIDPASVECSUM,MIDPASSEATSUM,NORPASVECSUM,NORPASSEATSUM,PASVECSUM,PASSEATSUM,DRIPASVECSUM,SERPSNSUM, '' HZT_REGCMCLT,--業戶注冊信息 BNBANK,CAPITALSUM,FORCAPITAL,FXNOWCAPTL,FXBEGCAPTL,FLOWCAPTL, '' HZT_APP, --業戶許可信息 APPDATE,PERDECNO,PASSCHENUM,PASSNUM FROM HYT2LINEHN.V_CMCLT A WHERE BITAND(A.CLTENTITYTYPE,'1')=1 OR BITAND(A.CLTENTITYTYPE,'32')=32; CREATE OR REPLACE VIEW HYT2LINEHN.V_SINGLECMPSN AS SELECT '' HZF_SINGLECMPSN, --設置默認查詢條件 '' HZS_CMPSN, --設置默認查詢列 '' HZT_CMCLTINFO,--業戶信息 CLITNAME, '' HZT_CMPSNINFO,--從業人員信息 NAME,SEX,BIRTHDAY,CARDNO,WORSTATUS, '' HZT_CERTIFICATE, --從業資格證信息 CMPSNID,BEWORSCOPE,DOCNO,TITNO,FIRSTDATE,GRANTDATE,CREDATE,ENDDATE,EXAMINEEAREA,BEWORLEVEL,TRATIMES,LICSTATUS FROM HYT2LINEHN.V_CMPSN; CREATE OR REPLACE VIEW HYT2LINEHN.V_STUDENT AS SELECT '' HZF_STUDENT,--設置默認查詢條件 '' HZS_STUDENT,--設置默認查詢列 '' HZT_STUDENTINFO,--學員基本信息 T1.STUID,T1.NAME,T1.SEX,T1.BIRTHDAY,T1.CARDNO,T1.NATION,T1.NATPLACE,T1.PHONE,T1.ADDRESS,T1.MAICODING,T1.EMAIL,T1.EDUCATION,T1.TECCLASS,T1.HEALTH FROM HYT2LINEHN.STUDENT T1 WHERE T1.ISEFFECT=0; CREATE OR REPLACE VIEW HYT2LINEHN.V_STUTIMESTATUS AS SELECT M.STUAPPLICATIONID,M.STUHOURSRECORDID,M.CHEAKAPPROVALID,M.SUBJECT,CASE WHEN INSTR(M.OUTRIDETIMECOLOR||'###'||M.ONTIMECOLOR||'###'||M.ISEFFECTCOUNT||'###'||M.EFFECTTIMECOLOR,'1')>0 THEN '1'WHEN INSTR(M.OUTRIDETIMECOLOR||'###'||M.ONTIMECOLOR||'###'||M.ISEFFECTCOUNT||'###'||M.EFFECTTIMECOLOR,'2')>0 THEN '2'WHEN INSTR(M.OUTRIDETIMECOLOR||'###'||M.ONTIMECOLOR||'###'||M.ISEFFECTCOUNT||'###'||M.EFFECTTIMECOLOR,'4')>0 THEN '4'WHEN INSTR(M.OUTRIDETIMECOLOR||'###'||M.ONTIMECOLOR||'###'||M.ISEFFECTCOUNT||'###'||M.EFFECTTIMECOLOR,'3')>0 THEN '3'ELSE '0' END SHOWTIMECOLORFROM (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 ISEFFECTCOUNTFROM (SELECT T2.STUAPPLICATIONID,T2.STUHOURSRECORDID,T2.CHEAKAPPROVALID,T2.SUBJECT,CASE WHEN T2.RECORDTIMESTART<T3.DFTVALUE OR T2.RECORDTIMEEND>T4.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.STUAPPLICATIONIDAND A.CHEAKAPPROVALID=T2.CHEAKAPPROVALID AND A.RECORDTIMESTART=T2.RECORDTIMESTART GROUP BY A.RECORDTIMESTART )> = T8.DFTVALUEor ( SELECT COUNT(A.RECORDTIMEEND) FROM TSSTUHOURSRECORD A WHERE A.ISEFFECT='0' AND A.STUAPPLICATIONID=t2.STUAPPLICATIONIDAND A.RECORDTIMEEND=T2.RECORDTIMEEND AND A.CHEAKAPPROVALID=T2.CHEAKAPPROVALID GROUP BY A.RECORDTIMEEND )> = T8.DFTVALUETHEN '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 ISEFFECTCOUNTFROM TSSTUHOURSRECORD T2LEFT JOIN V_LINEPARAM T3ON T3.PARAMCODE='OUTRIDSTARTTIME'LEFT JOIN V_LINEPARAM T4ON T4.PARAMCODE='OUTRIDENDTIME'LEFT JOIN V_LINEPARAM T5ON T5.PARAMCODE='EFFECTTIME'LEFT JOIN V_LINEPARAM T6ON T6.PARAMCODE='OUTRIDETIMESTATUS'LEFT JOIN V_LINEPARAM T7ON T7.PARAMCODE='ONTIMESTATUS'LEFT JOIN V_LINEPARAM T8ON T8.PARAMCODE='EFFECTTIMESTATUS'LEFT JOIN V_LINEPARAM T9ON T9.PARAMCODE='COUNTSTATUS'LEFT JOIN V_LINEPARAM T10ON 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.RECORDTIMESTART<T3.DFTVALUE OR T2.RECORDTIMEEND>T4.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.STUAPPLICATIONIDAND A.CHEAKAPPROVALID=T2.CHEAKAPPROVALID AND A.RECORDTIMESTART=T2.RECORDTIMESTART GROUP BY A.RECORDTIMESTART )> = T6.DFTVALUEOR ( SELECT COUNT(A.RECORDTIMEEND) FROM TSSTUHOURSRECORD A WHERE A.ISEFFECT='0' AND A.STUAPPLICATIONID=T2.STUAPPLICATIONIDAND A.RECORDTIMEEND=T2.RECORDTIMEEND AND A.CHEAKAPPROVALID=T2.CHEAKAPPROVALID GROUP BY A.RECORDTIMEEND )> = T6.DFTVALUETHEN '1' ELSE '0' END SHOWTIMECOLORFROM TSSTUHOURSRECORD T2INNER JOIN TSCHEAKAPPROVALDETAIL TT1ON T2.STUAPPLICATIONID = TT1.STUAPPLICATIONID AND T2.CHEAKAPPROVALID=TT1.CHEAKAPPROVALIDAND (TT1.GOVCHEAKAPPROVALSTATUS ='01' OR TT1.GOVCHEAKAPPROVALSTATUS IS NULL)AND TT1.CREDATE >'2011-10-21'AND TT1.ISEFFECT='0'LEFT JOIN V_LINEPARAM T3ON T3.PARAMCODE='OUTRIDSTARTTIME'LEFT JOIN V_LINEPARAM T4ON T4.PARAMCODE='OUTRIDENDTIME'LEFT JOIN V_LINEPARAM T5ON T5.PARAMCODE='EFFECTTIME'LEFT JOIN V_LINEPARAM T6ON 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.FRANUMFROM HYT2LINEHN.CMVEC T2LEFT OUTER JOIN HYT2LINEHN.CMCLT T1 ON T2.CMCLTID=T1.CMCLTIDLEFT OUTER JOIN HYT2LINEHN.COUCANCODE T4 ON T2.VECADDR=T4.CANCODEWHERE 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 IFBRANCHFROM HYT2LINEHN.CMCLT TWHERE ISEFFECT = '0' AND bitand(T.CLTENTITYTYPE,1024)=1024ORDER 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 TLEFT 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.CMCLTIDWHERE ISEFFECT = '0'AND BITAND(T.CLTENTITYTYPE,1)=1ORDER 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.NEXTECGRDTODATEFROM HYT2LINEHN.CMVEC T2LEFT OUTER JOIN HYT2LINEHN.CMCLT T1 ONT2.CMCLTID=T1.CMCLTIDLEFT OUTER JOIN HYT2LINEHN.COUCANCODE T4 ONT2.VECADDR=T4.CANCODEWHERE 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.FRANUMFROM HYT2LINEHN.CMVEC T2LEFT OUTER JOIN HYT2LINEHN.CMCLT T1 ON T2.CMCLTID=T1.CMCLTIDLEFT OUTER JOIN HYT2LINEHN.COUCANCODE T4 ON T2.VECADDR=T4.CANCODEWHERE T2.ISEFFECT=0 ANDINSTR(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.FRANUMFROM HYT2LINEHN.CMVEC T2LEFT OUTER JOIN HYT2LINEHN.CMCLT T1 ON T2.CMCLTID=T1.CMCLTIDLEFT OUTER JOIN HYT2LINEHN.COUCANCODE T4 ON T2.VECADDR=T4.CANCODEWHERE 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.FRANUMFROM HYT2LINEHN.CMVEC T2LEFT OUTER JOIN HYT2LINEHN.CMCLT T1 ON T2.CMCLTID=T1.CMCLTIDLEFT OUTER JOIN HYT2LINEHN.COUCANCODE T4 ON T2.VECADDR=T4.CANCODEWHERE T2.ISEFFECT=0 AND(INSTR(T2.VECENTYPE,'5')>=1);

第七步：創建過程

CREATE OR REPLACE FUNCTION HYT2LINEHN.GETBNSCOPE(BNSCOPES IN VARCHAR2) RETURN VARCHAR2 ISSTRRESULT VARCHAR2(4000);IDX NUMBER(5):=1;NEXT_IDX NUMBER(5):=1;IN_STR VARCHAR2(4000);BNAME VARCHAR2(500);BCODE VARCHAR2(4000); BEGININ_STR:=BNSCOPES;IF INSTR(IN_STR,',',IDX)<>0 THENloopNEXT_IDX:=INSTR(IN_STR,',',IDX);IF NEXT_IDX<>0 THENBCODE := SUBSTR(IN_STR,IDX,NEXT_IDX-IDX);SELECT NAME INTO BNAME FROM HYT2PFHN.T_PF_DATADICT WHERE DDFLD='BNSCOPE'AND CODE=BCODE;ELSESELECT 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;ELSESELECT NAME INTO BNAME FROM HYT2PFHN.T_PF_DATADICT WHERE DDFLD='BNSCOPE'AND CODE=BNSCOPES;STRRESULT :=BNAME;END IF;IF LENGTH(STRRESULT)>0 THENSTRRESULT := SUBSTR(STRRESULT,1,LENGTH(STRRESULT)-1);END IF;RETURN STRRESULT;END GETBNSCOPE; / CREATE OR REPLACE FUNCTION HYT2LINEHN.REPORTMAJORBRAND(CMCLTID IN VARCHAR2) RETURN VARCHAR2 ISCURSOR CS_FORMINPUT isSELECT F1 FROM HYT2LINEHN.FORMINPUT where DATUMID='MAINREPAIR' AND ENTITYID = CMCLTID ;STRRESULT VARCHAR2(4000); BEGIN for VAR_TYPE in CS_FORMINPUT loopSTRRESULT := STRRESULT || VAR_TYPE.F1 ||',' ;end loop;IF LENGTH(STRRESULT)>0 THENSTRRESULT := 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 isresult varchar2(2000);cursor c_binobj is select objsrc||'|'||filename||'.'||extname as info from binobjwhere iseffect=0 and refobjid=v_refobjid and datumid=v_datumid; beginresult:=null;for v_binobj in c_binobj loopif result is null thenresult:=v_binobj.info; elseresult:=result||':'||v_binobj.info; end if;end loop; return(result); end getBinObj; / CREATE OR REPLACE FUNCTION HYT2LINEHN.GETBNSCOPE(BNSCOPES IN VARCHAR2) RETURN VARCHAR2 ISSTRRESULT VARCHAR2(4000);IDX NUMBER(5):=1;NEXT_IDX NUMBER(5):=1;IN_STR VARCHAR2(4000);BNAME VARCHAR2(500);BCODE VARCHAR2(4000); BEGININ_STR:=BNSCOPES;IF INSTR(IN_STR,',',IDX)<>0 THENloopNEXT_IDX:=INSTR(IN_STR,',',IDX);IF NEXT_IDX<>0 THENBCODE := SUBSTR(IN_STR,IDX,NEXT_IDX-IDX);SELECT NAME INTO BNAME FROM HYT2PFHN.T_PF_DATADICT WHERE DDFLD='BNSCOPE'AND CODE=BCODE;ELSESELECT 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;ELSESELECT NAME INTO BNAME FROM HYT2PFHN.T_PF_DATADICT WHERE DDFLD='BNSCOPE'AND CODE=BNSCOPES;STRRESULT :=BNAME;END IF;IF LENGTH(STRRESULT)>0 THENSTRRESULT := SUBSTR(STRRESULT,1,LENGTH(STRRESULT)-1);END IF;RETURN STRRESULT;END GETBNSCOPE; / CREATE OR REPLACE FUNCTION HYT2PFHN.GETBNSCOPE(BNSCOPES IN VARCHAR2) RETURN VARCHAR2 ISSTRRESULT VARCHAR2(4000);IDX NUMBER(5):=1;NEXT_IDX NUMBER(5):=1;IN_STR VARCHAR2(4000);BNAME VARCHAR2(500);BCODE VARCHAR2(4000); BEGININ_STR:=BNSCOPES;IF INSTR(IN_STR,',',IDX)<>0 THENloopNEXT_IDX:=INSTR(IN_STR,',',IDX);IF NEXT_IDX<>0 THENBCODE := SUBSTR(IN_STR,IDX,NEXT_IDX-IDX);SELECT NAME INTO BNAME FROM HYT2PFHN.T_PF_DATADICT WHERE DDFLD='BNSCOPE'AND CODE=BCODE;ELSESELECT 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;ELSESELECT NAME INTO BNAME FROM HYT2PFHN.T_PF_DATADICT WHERE DDFLD='BNSCOPE'AND CODE=BNSCOPES;STRRESULT :=BNAME;END IF;IF LENGTH(STRRESULT)>0 THENSTRRESULT := SUBSTR(STRRESULT,1,LENGTH(STRRESULT)-1);END IF;RETURN STRRESULT;END GETBNSCOPE; /

第八步：部署工程

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

再啟動 Tomcat 就 OK 了

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的本地 Windows 如何将 Web 工程部署到远程 Windows 主机上的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。