问题:以下是三段sql,首先用第三段替换第二段中的@values,然后用替换后的语句再替换第一段中的@values,将这个替换后的语句保存在一个变量中,这个变量是long型的,执行这个变量,可是oracle返回缺少value关键字,但是如果我把第三段sql中的最后一个union all 都去掉的话,再执行又正确了,不知道是什么原因,本人怀疑是第三段sql太长的缘故,可存储过程中变量都是long型的,应该不存在保存不下的问题。注:替换语句都是用replace方法实现,是不是这个方法出现了问题,不支持太长的sql替换呀,请各位高手指教
第一段
INSERT INTO CAS_INPUT_DEPT_INCOME_LIST (
INCOME_NO ,
INCOME_PRO_CODE ,
TYPE_CODE ,
PATIENT_WARD_CODE ,
PATIENT_DEPT_CODE ,
SET_DEPT_CODE ,
EXECUTE_DEPT_CODE ,
SPEC_DEPT_CODE ,
EXEC_MONEY ,
INCOME_DATE ,
INCOME_STATUS ,
REMARK ,
INCOME_SOURCE ,
OPER_ID ,
OPER_TIME,
WASTE_ID,
WASTE_TIME
)
@values第二段
select to_char(sysdate,'yyyymmdd')||SEQ_CAS_DEPT_INCOME_NO.Nextval,
a.PRO_CODE,
a.TYPE_CODE,
a.PAT_WARD_CODE,
a.PAT_DEPT_CODE,
a.SET_DEPT_CODE,
a.EXE_DEPT_CODE,
a.SPEC_DEPT_CODE,
a.EXE_MONEY,
a.PRO_DATE,
a.INCOME_STATUS,
a.REMARK,
a.INCOME_SOURCE,
a.OPER_ID,
a.OPER_TIME,
a.WASTE_ID,
a.WASTE_TIME
from (@values) a第三段
select 'INFEEINCOME' PRO_CODE,
t.fee_code TYPE_CODE,
t.nurse_cell_code PAT_WARD_CODE,
'' PAT_DEPT_CODE,
t.inhos_deptcode SET_DEPT_CODE,
t.execute_deptcode EXE_DEPT_CODE,
'' SPEC_DEPT_CODE,
sum(t.tot_cost) EXE_MONEY,
to_date(t.fee_date) PRO_DATE,
'1' INCOME_STATUS,
'' REMARK,
'IMPORT' INCOME_SOURCE,
'system' OPER_ID,
sysdate OPER_TIME,
'' WASTE_ID,
'' WASTE_TIME
from fin_ipb_feeinfo t
where t.fee_date >= to_date('@startTime', 'yyyy-mm-dd HH24:mi:ss')
and t.fee_date < to_date('@endTime', 'yyyy-mm-dd HH24:mi:ss')
group by t.fee_code,
t.inhos_deptcode,
t.nurse_cell_code,
t.execute_deptcode,
to_date(t.fee_date)
union all
select 'INFEEINCOME' PRO_CODE,
t.type_code TYPE_CODE,
t.nurse_cell_code PAT_WARD_CODE,
'' PAT_DEPT_CODE,
t.inhos_deptcode SET_DEPT_CODE,
t.execute_deptcode EXE_DEPT_CODE,
'' SPEC_DEPT_CODE,
sum(t.tot_cost) EXE_MONEY,
to_date(t.fee_date) PRO_DATE,
'1' INCOME_STATUS,
'' REMARK,
'IMPORT' INCOME_SOURCE,
'system' OPER_ID,
sysdate OPER_TIME,
'' WASTE_ID,
'' WASTE_TIME
from
(select decode(a.item_code,'F00000036193','134','F00000003709','134','F00000003689','135','F00000000451','136','F00000000237','137','138') type_code,
a.nurse_cell_code nurse_cell_code,
a.inhos_deptcode inhos_deptcode,
a.execute_deptcode execute_deptcode,
a.tot_cost tot_cost,
a.fee_date fee_date
from fin_ipb_itemlist a
WHERE (a.item_code = 'F00000036193' or a.item_code = 'F00000003709' or a.item_code = 'F00000003689' or a.item_code = 'F00000000451' or a.item_code = 'F00000000237' or
(a.fee_code = '010' and a.inhos_deptcode = '0037' and a.execute_deptcode = '0136' ))
and a.fee_date >= to_date('@startTime', 'yyyy-mm-dd HH24:mi:ss')
and a.fee_date < to_date('@endTime', 'yyyy-mm-dd HH24:mi:ss')) t
group by t.type_code,
t.inhos_deptcode,
t.nurse_cell_code,
t.execute_deptcode,
to_date(t.fee_date)
union all
select 'INFEEINCOME' PRO_CODE,
decode(t.fee_code,'094','139','095','140','104','141','074','142','093','143') TYPE_CODE,
t.nurse_cell_code PAT_WARD_CODE,
'' PAT_DEPT_CODE,
t.inhos_deptcode SET_DEPT_CODE,
t.execute_deptcode EXE_DEPT_CODE,
'' SPEC_DEPT_CODE,
sum(t.tot_cost) EXE_MONEY,
to_date(t.fee_date) PRO_DATE,
'1' INCOME_STATUS,
'' REMARK,
'IMPORT' INCOME_SOURCE,
'system' OPER_ID,
sysdate OPER_TIME,
'' WASTE_ID,
'' WASTE_TIME
from fin_ipb_feeinfo t
where t.execute_deptcode = '0271'
and t.fee_date >= to_date('@startTime', 'yyyy-mm-dd HH24:mi:ss')
and t.fee_date < to_date('@endTime', 'yyyy-mm-dd HH24:mi:ss')
group by t.fee_code,
t.inhos_deptcode,
t.nurse_cell_code,
t.execute_deptcode,
to_date(t.fee_date)
第一段
INSERT INTO CAS_INPUT_DEPT_INCOME_LIST (
INCOME_NO ,
INCOME_PRO_CODE ,
TYPE_CODE ,
PATIENT_WARD_CODE ,
PATIENT_DEPT_CODE ,
SET_DEPT_CODE ,
EXECUTE_DEPT_CODE ,
SPEC_DEPT_CODE ,
EXEC_MONEY ,
INCOME_DATE ,
INCOME_STATUS ,
REMARK ,
INCOME_SOURCE ,
OPER_ID ,
OPER_TIME,
WASTE_ID,
WASTE_TIME
)
@values第二段
select to_char(sysdate,'yyyymmdd')||SEQ_CAS_DEPT_INCOME_NO.Nextval,
a.PRO_CODE,
a.TYPE_CODE,
a.PAT_WARD_CODE,
a.PAT_DEPT_CODE,
a.SET_DEPT_CODE,
a.EXE_DEPT_CODE,
a.SPEC_DEPT_CODE,
a.EXE_MONEY,
a.PRO_DATE,
a.INCOME_STATUS,
a.REMARK,
a.INCOME_SOURCE,
a.OPER_ID,
a.OPER_TIME,
a.WASTE_ID,
a.WASTE_TIME
from (@values) a第三段
select 'INFEEINCOME' PRO_CODE,
t.fee_code TYPE_CODE,
t.nurse_cell_code PAT_WARD_CODE,
'' PAT_DEPT_CODE,
t.inhos_deptcode SET_DEPT_CODE,
t.execute_deptcode EXE_DEPT_CODE,
'' SPEC_DEPT_CODE,
sum(t.tot_cost) EXE_MONEY,
to_date(t.fee_date) PRO_DATE,
'1' INCOME_STATUS,
'' REMARK,
'IMPORT' INCOME_SOURCE,
'system' OPER_ID,
sysdate OPER_TIME,
'' WASTE_ID,
'' WASTE_TIME
from fin_ipb_feeinfo t
where t.fee_date >= to_date('@startTime', 'yyyy-mm-dd HH24:mi:ss')
and t.fee_date < to_date('@endTime', 'yyyy-mm-dd HH24:mi:ss')
group by t.fee_code,
t.inhos_deptcode,
t.nurse_cell_code,
t.execute_deptcode,
to_date(t.fee_date)
union all
select 'INFEEINCOME' PRO_CODE,
t.type_code TYPE_CODE,
t.nurse_cell_code PAT_WARD_CODE,
'' PAT_DEPT_CODE,
t.inhos_deptcode SET_DEPT_CODE,
t.execute_deptcode EXE_DEPT_CODE,
'' SPEC_DEPT_CODE,
sum(t.tot_cost) EXE_MONEY,
to_date(t.fee_date) PRO_DATE,
'1' INCOME_STATUS,
'' REMARK,
'IMPORT' INCOME_SOURCE,
'system' OPER_ID,
sysdate OPER_TIME,
'' WASTE_ID,
'' WASTE_TIME
from
(select decode(a.item_code,'F00000036193','134','F00000003709','134','F00000003689','135','F00000000451','136','F00000000237','137','138') type_code,
a.nurse_cell_code nurse_cell_code,
a.inhos_deptcode inhos_deptcode,
a.execute_deptcode execute_deptcode,
a.tot_cost tot_cost,
a.fee_date fee_date
from fin_ipb_itemlist a
WHERE (a.item_code = 'F00000036193' or a.item_code = 'F00000003709' or a.item_code = 'F00000003689' or a.item_code = 'F00000000451' or a.item_code = 'F00000000237' or
(a.fee_code = '010' and a.inhos_deptcode = '0037' and a.execute_deptcode = '0136' ))
and a.fee_date >= to_date('@startTime', 'yyyy-mm-dd HH24:mi:ss')
and a.fee_date < to_date('@endTime', 'yyyy-mm-dd HH24:mi:ss')) t
group by t.type_code,
t.inhos_deptcode,
t.nurse_cell_code,
t.execute_deptcode,
to_date(t.fee_date)
union all
select 'INFEEINCOME' PRO_CODE,
decode(t.fee_code,'094','139','095','140','104','141','074','142','093','143') TYPE_CODE,
t.nurse_cell_code PAT_WARD_CODE,
'' PAT_DEPT_CODE,
t.inhos_deptcode SET_DEPT_CODE,
t.execute_deptcode EXE_DEPT_CODE,
'' SPEC_DEPT_CODE,
sum(t.tot_cost) EXE_MONEY,
to_date(t.fee_date) PRO_DATE,
'1' INCOME_STATUS,
'' REMARK,
'IMPORT' INCOME_SOURCE,
'system' OPER_ID,
sysdate OPER_TIME,
'' WASTE_ID,
'' WASTE_TIME
from fin_ipb_feeinfo t
where t.execute_deptcode = '0271'
and t.fee_date >= to_date('@startTime', 'yyyy-mm-dd HH24:mi:ss')
and t.fee_date < to_date('@endTime', 'yyyy-mm-dd HH24:mi:ss')
group by t.fee_code,
t.inhos_deptcode,
t.nurse_cell_code,
t.execute_deptcode,
to_date(t.fee_date)
解决方案 »
- oralce 下,列名有特殊符号的sql 如何编写?
- 关于数据库开发,怎么确定指标?
- oracle触发器内如何获取刚insert的记录的 字段值?
- 关于 公共网->DB1<->防火墙<->DB2<-内网 的交互问题
- 通用数据库及文件同步软件《同步专家网络版》
- oracle10g 安装过程中终止的问题,求高人解答
- 关于oracle的DMP数据文件的问题?急急急急急急急急急急急急急急急
- 剧情是这样的..............
- 安装数据库时报“ORA-24324:未初始化服务句柄”的错误,为什么?
- 如何设置ORACLE远程连接,才能让它远程连接时不会自动断掉
- oracle 中 有一个名字叫a 的东西 不是表的名字,也不知是什么名字....怎知道他是什么东西(视图.或者 权限 或者 触发器)
- Oracle library OCI.DLL or ORA803.DLL could not be loaded
这种方法不可取,这些语句都是在后台自动执行的,请问有更好的方法替换吗?谢谢
INSERT INTO CAS_INPUT_DEPT_INCOME_LIST (
INCOME_NO ,
INCOME_PRO_CODE ,
TYPE_CODE ,
PATIENT_WARD_CODE ,
PATIENT_DEPT_CODE ,
SET_DEPT_CODE ,
EXECUTE_DEPT_CODE ,
SPEC_DEPT_CODE ,
EXEC_MONEY ,
INCOME_DATE ,
INCOME_STATUS ,
REMARK ,
INCOME_SOURCE ,
OPER_ID ,
OPER_TIME,
WASTE_ID,
WASTE_TIME
) 没有value,你说的对,replace函数不支持超过4000的长度,我把这几段语句中间的空格都去掉后就可以执行了,但我有个问题,如果sql语句精简后长度也超过4000的话,那如何进行替换操作呢
如果你的第二段和第三段字符串是以varchar类型接收,那么只要任一个字符串大于4000,程序就会抛错,可如果你用long或clob来代替varchar类型来接收的话,那么一般程序在replace的时候是不会抛错的.
可想而知,replace既然能处理long类型的数据,那么replace函数处理的最大字符串长度肯定是可以大于4000的,当然大于4000只是相对于非varchar类型的数据!
replace处理的最大长度是4000,变量定义varchar2的最大长度是32767,分段替换,最后做拼接