ecif数据库修改

杨庆伟加表

1130：

-- 2016-11-30 刘琨添加
DROP TABLE ECIF_ACCESS_PATH_INFO CASCADE CONSTRAINTS PURGE;

DROP TABLE ECIF_Access_log CASCADE CONSTRAINTS PURGE;

CREATE TABLE ECIF_Access_log
(
    Id                   VARCHAR2(50) NOT NULL ,
    IP_ADDRESS           VARCHAR2(50) NULL ,
    USER_ACCOUNT         VARCHAR2(50) NULL ,
    ACCESS_PATHId        VARCHAR2(50) NULL ,
    ACCESS_TIME          VARCHAR2(50) NULL 
)
    TABLESPACE ECIFDATA;

COMMENT ON TABLE ECIF_Access_log IS '杨庆伟20161129添加访问日志信息
CREATE TABLE ECIF_ACCESS_LOG (
    ID VARCHAR2 (50) NOT NULL PRIMARY KEY,
    IP_ADDRESS VARCHAR2 (50) NOT NULL,
    USER_ACCOUNT VARCHAR2 (50) NOT NULL,
    ACCESS_PATH_ID VARCHAR2 (50),
    ACCESS_TIME DATE DEFAULT SYSDATE
);
COMMENT ON TABLE ECIF_Access_log IS ''访问日志信息'';

COMMENT ON COLUMN ECIF_Access_log. ID IS ''日志Id'';

COMMENT ON COLUMN ECIF_Access_log.IP_ADDRESS IS ''访问IP'';

COMMENT ON COLUMN ECIF_Access_log.USER_ACCOUNT IS ''访问账号'';

COMMENT ON COLUMN ECIF_Access_log.access_Path_Id IS ''访问路径Id'';

COMMENT ON COLUMN ECIF_Access_log.access_Time IS ''访问时间'';';

COMMENT ON COLUMN ECIF_Access_log.Id IS '日志Id';

COMMENT ON COLUMN ECIF_Access_log.IP_ADDRESS IS '访问IP';

COMMENT ON COLUMN ECIF_Access_log.USER_ACCOUNT IS '访问账号';

COMMENT ON COLUMN ECIF_Access_log.ACCESS_PATHId IS '访问路径Id';

COMMENT ON COLUMN ECIF_Access_log.ACCESS_TIME IS '访问时间';

CREATE UNIQUE INDEX XPKaccess_log ON ECIF_Access_log
(Id   ASC);

ALTER TABLE ECIF_Access_log
    ADD CONSTRAINT  XPKaccess_log PRIMARY KEY (Id);

CREATE TABLE ECIF_ACCESS_PATH_INFO
(
    ACCESS_PATHId        VARCHAR2(50) NOT NULL ,
    Menu_Id              VARCHAR2(50) NULL ,
    ACCESS_PATH          VARCHAR2(50) NULL ,
    ACCESS_PATH_DESC     VARCHAR2(100) NULL ,
    OPERATE_TYPE_CD      VARCHAR2(50) NULL 
)
    TABLESPACE ECIFDATA;

COMMENT ON TABLE ECIF_ACCESS_PATH_INFO IS '杨庆伟20161129添加访问路径信息
CREATE TABLE ECIF_ACCESS_PATH_INFO (
    ACCESS_PATH_ID VARCHAR2 (50) NOT NULL PRIMARY KEY,
    MENU_ID VARCHAR2 (50),
    ACCESS_PATH VARCHAR2 (50) NOT NULL,
    ACCESS_PATH_DESC VARCHAR2 (100) NOT NULL,
    OPERATE_TYPE_CD VARCHAR2 (50)
);
COMMENT ON TABLE ECIF_Access_Path_Info IS ''访问路径信息'';

COMMENT ON COLUMN ECIF_Access_Path_Info.access_Path_Id IS ''访问Id'';

COMMENT ON COLUMN ECIF_Access_Path_Info.MENU_ID IS ''菜单Id'';

COMMENT ON COLUMN ECIF_Access_Path_Info.access_Path IS ''访问路径'';

COMMENT ON COLUMN ECIF_Access_Path_Info.access_Path_Desc IS ''访问路径描述'';

COMMENT ON COLUMN ECIF_Access_Path_Info.operate_Type_Cd IS ''操作类型'';';

COMMENT ON COLUMN ECIF_ACCESS_PATH_INFO.ACCESS_PATHId IS '访问路径Id';

COMMENT ON COLUMN ECIF_ACCESS_PATH_INFO.Menu_Id IS '菜单Id';

COMMENT ON COLUMN ECIF_ACCESS_PATH_INFO.ACCESS_PATH IS '访问路径';

COMMENT ON COLUMN ECIF_ACCESS_PATH_INFO.ACCESS_PATH_DESC IS '访问路径描述';

COMMENT ON COLUMN ECIF_ACCESS_PATH_INFO.OPERATE_TYPE_CD IS '操作类型';

CREATE UNIQUE INDEX XPKaccess_path_info ON ECIF_ACCESS_PATH_INFO
(ACCESS_PATHId   ASC);

ALTER TABLE ECIF_ACCESS_PATH_INFO
    ADD CONSTRAINT  XPKaccess_path_info PRIMARY KEY (ACCESS_PATHId);
    
alter table ecif_access_path_info add (ACCESS_PAGE_URL varchar2(200));
comment on column ecif_access_path_info.ACCESS_PAGE_URL is '访问页面地址';

韦雷加字段

ALTER TABLE ecif_access_path_info ADD (
    ACCESS_PAGE_URL VARCHAR2 (200)
);

COMMENT ON COLUMN ecif_access_path_info.ACCESS_PAGE_URL IS '访问页面地址';

庆伟修改字段长度

ALTER TABLE "SCVUSER"."ECIF_SYSTEMUSER" MODIFY (
    "USER_ACCT_NUM" VARCHAR2 (100 BYTE)
) MODIFY (
    "USER_NAME" VARCHAR2 (100 BYTE)
);

ALTER TABLE "SCVUSER"."ECIF_ROLE" MODIFY (
    "ROLE_NAME" VARCHAR2 (100 BYTE)
) MODIFY (
    "ROLE_DESC" VARCHAR2 (100 BYTE)
);

长勇加函数

生产的2个函数+sequence

SEQ_ID_NO.sql

CREATE SEQUENCE SEQ_ID_NO MINVALUE 1 MAXVALUE 999999999999 START WITH 100000000000 INCREMENT BY 1 CACHE 20;

f_id_generator.fnc

CREATE
OR REPLACE FUNCTION "F_ID_GENERATOR" (SRC_SYSTEM_CD IN VARCHAR2) RETURN VARCHAR2 IS ID VARCHAR2 (50) ; /*输出18位ID*/
vR3 VARCHAR (3) ; /*3位随机数*/
vT12 VARCHAR (12) ; /*当前时间戳的毫秒数后12位*/
vR1 VARCHAR (1) ; /*1位随机数*/
BEGIN
    vR3 := TRUNC (dbms_random. VALUE(100, 999)) ; -- select to_char(systimestamp, 'hh24missff') into vT12 from dual ;
    Vt12 := LPAD (seq_id_no.nextval, 12, '0') ; vR1 := TRUNC (dbms_random. VALUE(0, 9)) ; ID := vR3 || vT12 || SRC_SYSTEM_CD || vR1 ; RETURN ID ;
END ;/

f_md5_generator.fnc

CREATE
OR REPLACE FUNCTION "F_MD5_GENERATOR" (inputstring IN VARCHAR2) RETURN VARCHAR2 IS retval VARCHAR2 (32) ; v_inputstring VARCHAR2 (4000) ;
BEGIN
    v_inputstring := UPPER (TRIM(inputstring)) ; retval := utl_raw.cast_to_raw (
        DBMS_OBFUSCATION_TOOLKIT.MD5 (
            INPUT_STRING => v_inputstring
        )
    ) ; RETURN retval ;
END ;/

 长勇清数据

（20161205 数据备份之前，回复后，重复运行）

delete from ecif_certificate s where s.iden_num is null;
commit;

 9151718条数据 （SELECT count(1) from ecif_certificate s where s.iden_num is null;）

文正

错误码code

INSERT INTO ERRORCODE
VALUES('11006', '11006','请求的报文体中字段值验证不通过，原因[{0}]');

 曹岩20161206修改字符长度

ALTER TABLE "SCVUSER"."ECIF_SERVICE_EXCEPTION_LOG"
MODIFY ( "STATUS_DESC" VARCHAR2(500 BYTE) ) ;
ALTER TABLE "SCVUSER"."ECIF_COMMUNICATION_LOG"
MODIFY ( "STATUS_DESC" VARCHAR2(500 BYTE) ) ;

-- ECIF_SERVICE_EXCEPTION_LOG表STATUS_DESC字段和ECIF_COMMUNICATION_LOG表STATUS_DESC字段长度变为500

 曹岩添加字段报文序号

ALTER TABLE "SCVUSER"."ECIF_COMMUNICATION_LOG"
ADD ( "SEQ_NUM" VARCHAR2(50) NULL  ) ;

 曹岩修改字符长度

-- @ECIF_ROOM表中的House_Num字段长度从varchar2 50 改为varchar2 100
ALTER TABLE "SCVUSER"."ECIF_ROOM"
MODIFY ( "ROOM_NUM" VARCHAR2(100 BYTE) ) ;