关于预算的一些数据操作

预算常用数据库操作
1导入导出
–导入
门户库
imp EFM_PORTAL_XHQ/EFM_PORTAL_XHQ@ORCL file=D:\BAK1\EFM_PORTAL_XHQ.dmp full=y log=D:\BAK1\EFM_PORTAL_XHQ.log
业务库
imp EFMIS_XHQ_2018/EFMIS_XHQ_2018@ORCL file=D:\BAK1\EFMIS_XHQ_2018.dmp full=y log=D:\BAK1\EFMIS_XHQ_2018.log
–导出
门户库
exp EFM_PORTAL_TXX/EFM_PORTAL_TXX@ORCL OWNER=EFM_PORTAL_TXX file=D:\BAK1\EFM_PORTAL_TXX.dmp log=D:\BAK1\EFM_PORTAL_TXX.log
业务库
exp EFMIS_TXX_2018/EFMIS_TXX_2018@ORCL OWNER=EFMIS_TXX_2018 file=D:\BAK1\EFMIS_TXX_2018.dmp log=D:\BAK1\EFMIS_TXX_2018.log

2监听文件
–平顶山地区正式环境监听
pds =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.64.140)(PORT = 1521))
)
(CONNECT_DATA =
(SID = pdsys1)
)
)
–测试环境
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
–单机版监听配置
EFMIS127.0.0.1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
3修改项目性质
select * from exp_t_project_out where refid = ‘B206E40218EB4D3CAC0A3D6CDD5CEEBB’
select * from ref_t_xxmxz where refid in (select * from exp_t_project);
select * from exp_t_projecttype
select * from exp_t_project_out
where x_sfdnycxxm=”
and x_sfdnycxxm=’B206E40218EB4D3CAC0A3D6CDD5CEEBB’
4业务库角色
create user EFMIS_XHQ_2018
identified by “EFMIS_XHQ_2018”
default tablespace EFMIS
temporary tablespace TEMP
profile DEFAULT;
– Grant/Revoke role privileges
grant connect to EFMIS_XHQ_2018;
grant javasyspriv to EFMIS_XHQ_2018;
grant java_admin to EFMIS_XHQ_2018;
grant resource to EFMIS_XHQ_2018 with admin option;
– Grant/Revoke system privileges
grant create job to EFMIS_XHQ_2018;
grant create materialized view to EFMIS_XHQ_2018;
grant create procedure to EFMIS_XHQ_2018;
grant create sequence to EFMIS_XHQ_2018;
grant create synonym to EFMIS_XHQ_2018;
grant create table to EFMIS_XHQ_2018;
grant create trigger to EFMIS_XHQ_2018;
grant create type to EFMIS_XHQ_2018;
grant create view to EFMIS_XHQ_2018;
grant debug any procedure to EFMIS_XHQ_2018;
grant debug connect session to EFMIS_XHQ_2018;
grant unlimited tablespace to EFMIS_XHQ_2018 with admin option;

GRANT DBA TO EFMIS_XHQ_2018
–imp EFMIS_XHQ_2018/EFMIS_XHQ_2018@jz file=E:\ay\EFMIS_XHQ_2018.dmp FULL=Y
5门户库角色
create user EFM_PORTAL_XHQ
identified by “EFM_PORTAL_XHQ”
default tablespace EFM_PORTAL
temporary tablespace TEMP
profile DEFAULT;
– Grant/Revoke role privileges
grant connect to EFM_PORTAL_XHQ;
grant javasyspriv to EFM_PORTAL_XHQ;
grant java_admin to EFM_PORTAL_XHQ;
grant resource to EFM_PORTAL_XHQ with admin option;
– Grant/Revoke system privileges
grant create job to EFM_PORTAL_XHQ;
grant create materialized view to EFM_PORTAL_XHQ;
grant create procedure to EFM_PORTAL_XHQ;
grant create sequence to EFM_PORTAL_XHQ;
grant create synonym to EFM_PORTAL_XHQ;
grant create table to EFM_PORTAL_XHQ;
grant create trigger to EFM_PORTAL_XHQ;
grant create type to EFM_PORTAL_XHQ;
grant create view to EFM_PORTAL_XHQ;
grant debug any procedure to EFM_PORTAL_XHQ;
grant debug connect session to EFM_PORTAL_XHQ;
grant unlimited tablespace to EFM_PORTAL_XHQ with admin option;

–imp EFM_PORTAL_XHQ/EFM_PORTAL_XHQ@jz file=D:\BAK\EFM_PORTAL_XHQ.dmp FULL=Y

GRANT DBA TO EFM_PORTAL_XHQ
6创建备份表
create table efm_t_acctitem_1017 as select * from efm_t_acctitem
7删除单位后面的AA
–删除单位名称后面的AA
select * from EFM_T_DIVISION

UPDATE EFM_T_DIVISION T SET T.DIVNAME=REPLACE(T.DIVNAME,’AA’,”)WHERE DIVNAME LIKE ‘%AA’
–查询单位代码表
select * from efm_t_divuserinfo

–删除单位代码后面AA
update efm_t_divuserinfo t set t.logname=substr(t.logname,0,len(t.logname)-2)
8设置从表
select * from exp_t_model where modelname =’政府购买服务支出表’8FC70E81BE764F2BA473C5F1837E177B
select * from exp_t_modelrela for update
9同步失败
同步失败–编译无效对象–重启电脑？门户库的divid 是32位码，需要把EFM_业务库T_DIVISION的内容复制到业务库再同步
10总计设置
总计：选中收支明细表
alt+f5 isusedefault = 1
showformat=1
budgetlevel=1
11系统多开
1 regedit
2 ismultipotal=1
12清理流程
select * from exp_t_projtypeacct 单位对应科目

清理流程，清理待办，清理任务（下任务，清理后单位代办中无任务）
truncate table efm_t_check;
truncate table EFM_t_StartTask;
truncate table Adt_t_AuditFlow;
truncate table adt_t_auditflowuser;
13身份证正则表达式
([8][0-9])|([9][0-9]))\d{4}(?:([0-9]0-9))|([0-9]0-9(?:0[1-9]|1\d|2[0-8]))|(((?:((?:0[48]|[2468][048]|[13579][26]))|(?:0[48]00|[2468][048]00|[13579][26]00))0229)))\d{3}$|^(([1][1-5])|([2][1-3])|([3][1-7])|([4][1-6])|([5][0-4])|([6][1-5])|([7][0-9])|([8][0-9])|([9][0-9]))\d{4}((?:([1][9]\d{2}(?:(?:0[1,3-9]|1[0-2])(?:29|30)|((?:0[13578]|1[02])31)))|([1][9]\d{2}(?:0[1-9]|1[0-2])(?:0[1-9]|1\d|2[0-8]))|(((?:([1][9](?:0[48]|[2468][048]|[13579][26]))|(?:0[48]00|[2468][048]00|[13579][26]00))0229))))|((?:([2][0]\d{2}(?:(?:0[1,3-9]|1[0-2])(?:29|30)|((?:0[13578]|1[02])31)))|([2][0]\d{2}(?:0[1-9]|1[0-2])(?:0[1-9]|1\d|2[0-8]))|(((?:([2][0](?:0[48]|[2468][048]|[13579][26]))|(?:0[48]00|[2468][048]00|[13579][26]00))0229))))\d{3}[\dx|X]$

身份号码不正确，请重新输入！
1.身份证号码长度必须是15位或者18位
2.身份证号码中的出生年月日范围 （1900-2009）之间
3.注意检查月份和当月的最大天数
14 DBLINK创建
–创建DBLINK
CREATE DATABASE LINK EFMIS_XXX_2017 CONNECT TO EFMIS_XXX_2017 IDENTIFIED BY EFMIS_XXX_2017 USING’ (DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.8.4.201)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = budora)
)
)’
15查询长度只有三位的值
select * from jtqkb_bak3
where length(TRIM(id))=3
16改区划
–修改区划
select * from efm_t_district for update
select * from efm_t_district_all for update
select * from efm_t_division for update
17开关
select * from exp_t_public where keyid=’00010222’
18淘汰项目是否可以删除
select * from exp_t_public where keyname like ‘%淘汰%’ for update
–在业务库执行
19修改非税收入项目
select * from exp_t_refcol for update

SELECT Efm_t_acctitem.AcctID ID,
Efm_t_acctitem.AcctCode AS ShortID,
Efm_t_acctitem.AcctName Name,
Space(len(Efm_t_acctitem.LevelID)) || Efm_t_acctitem.AcctName ShowName,
Efm_t_acctitem.EndFlag,
Efm_t_acctitem.Breviary,
Efm_t_acctitem.LevelID LvlID,
Efm_t_acctitem.SuperID,
0 handle,
0 Closed,
0 PriorState,
0 CloseFlag
FROM Efm_t_acctitem
where AcctTypeID = ‘24’ and AcctCode like ‘103%’

–后期查出来的
select * from exp_t_refcol
SELECT Efm_t_acctitem.AcctID ID,
Efm_t_acctitem.AcctCode AS ShortID,
Efm_t_acctitem.AcctName Name,
Space(len(Efm_t_acctitem.LevelID)) || Efm_t_acctitem.AcctName ShowName,
Efm_t_acctitem.EndFlag,
Efm_t_acctitem.Breviary,
Efm_t_acctitem.LevelID LvlID,
Efm_t_acctitem.SuperID,
0 handle,
0 Closed,
0 PriorState,
0 CloseFlag
FROM Efm_t_acctitem
where AcctTypeID = ‘24’SELECT Efm_t_acctitem.AcctID ID,
Efm_t_acctitem.AcctCode AS ShortID,
Efm_t_acctitem.AcctName Name,
Space(len(Efm_t_acctitem.LevelID)) || Efm_t_acctitem.AcctName ShowName,
Efm_t_acctitem.EndFlag,
Efm_t_acctitem.Breviary,
Efm_t_acctitem.LevelID LvlID,
Efm_t_acctitem.SuperID,
0 handle,
0 Closed,
0 PriorState,
0 CloseFlag
FROM Efm_t_acctitem
where AcctTypeID = ‘24’ and AcctCode like ‘103%’select * from exp_t_refcol for update
20去掉dba权限
revoke dba from user_Name
21修改项目性质最新语句
update exp_t_project_out
set x_sfdnycxxm=’B206E40218EB4D3CAC0A3D6CDD5CEEBB’
where projtypeid like’00010001%’and x_sfdnycxxm is null
22修改字段类型
select * from bas_t_xdwjcxxlrb
create table bas_t_xdwjcxxlrb_20171206 as select * from bas_t_xdwjcxxlrb
truncate table bas_t_xdwjcxxlrb
insert into bas_t_xdwjcxxlrb select * from bas_t_xdwjcxxlrb_20171206