sql中参数过多利用变量替换参数的方法
2023-06-13 09:15:16 时间
工作中遇到一个需求，需要非常多的参数，例如如下sql，
复制代码代码如下:
select
ff.fundsc||"-"||ff.fundtzfs||"-"||ff.fundcjfl||"-"||ff.fundonefl||"-"||ff.fundtowflcatagory,
sf.scode,replace(sf.fund5,"型证券投资基金","")fund5,sf.fund4,sf.fund10,
(selectto_date(tradedate,"yyyy-MM-dd")fromsdc_fundnetassetvaluewherescode=sf.scodeandtradedatein
(selectmax(tradedate)fromsdc_fundnetassetvaluewherescode=sf.scodeandto_date(tradedate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andtradedate<=20120801)andrownum<2)trdatedate,
(selectunitvaluefromsdc_fundnetassetvaluewherescode=sf.scodeandtradedatein
(selectmax(tradedate)fromsdc_fundnetassetvaluewherescode=sf.scodeandto_date(tradedate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andtradedate<=20120801)andrownum<2)fejz,
(selectaccumulatedunitvaluefromsdc_fundnetassetvaluewherescode=sf.scodeandtradedatein
(selectmax(tradedate)fromsdc_fundnetassetvaluewherescode=sf.scodeandto_date(tradedate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andtradedate<=20120801)andrownum<2)feljjz,
(selectf30004_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2)gqyzjzzzl,
(selectf30006_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2)gqyyjzzzl,
decode((selectf30007_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),
null,"-",to_char((selectf30007_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),"fm999999990.9999"))gqsgyjzzzl,
row_number()over(partitionbyff.fundsc||"-"||ff.fundtzfs||"-"||ff.fundcjfl||"-"||ff.fundonefl||"-"||ff.fundtowfl
orderbydecode((selectf30007_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),
null,"-",to_char((selectf30007_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),"fm999999990.9999"))desc)sgy,
decode((selectf30008_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),
null,"-",to_char((selectf30008_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),"fm999999990.9999"))gqbnjzzzl,
row_number()over(partitionbyff.fundsc||"-"||ff.fundtzfs||"-"||ff.fundcjfl||"-"||ff.fundonefl||"-"||ff.fundtowfl
orderbydecode((selectf30008_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),
null,"-",to_char((selectf30008_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),"fm999999990.9999"))desc)bn,
decode((selectF30017_30003fromsi_fund_30003wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30003wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),
null,"-",to_char((selectF30017_30003fromsi_fund_30003wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30003wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),"fm999999990.9999"))jinnian,
row_number()over(partitionbyff.fundsc||"-"||ff.fundtzfs||"-"||ff.fundcjfl||"-"||ff.fundonefl||"-"||ff.fundtowfl
orderbydecode((selectF30017_30003fromsi_fund_30003wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30003wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),
null,"-",to_char((selectF30017_30003fromsi_fund_30003wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30003wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),"fm999999990.9999"))desc)jn,
decode((selectf30009_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),
null,"-",to_char((selectf30009_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),"fm999999990.9999"))gqynjzzzl,
row_number()over(partitionbyff.fundsc||"-"||ff.fundtzfs||"-"||ff.fundcjfl||"-"||ff.fundonefl||"-"||ff.fundtowfl
orderbydecode((selectf30009_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),
null,"-",to_char((selectf30009_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),"fm999999990.9999"))desc)gqyn,
decode((selectf30010_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),
null,"-",to_char((selectf30010_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),"fm999999990.9999"))gqlnjzzzl,
row_number()over(partitionbyff.fundsc||"-"||ff.fundtzfs||"-"||ff.fundcjfl||"-"||ff.fundonefl||"-"||ff.fundtowfl
orderbydecode((selectf30010_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),
null,"-",to_char((selectf30010_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),"fm999999990.9999"))desc)gqln,
decode((selectf30011_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),
null,"-",to_char((selectf30011_30002fromsi_fund_30002wheref_fundcode=sf.scodeandf_type=0andf_tradingdatein
(selectmax(f_tradingdate)fromsi_fund_30002wheref_fundcode=sf.scodeandto_date(f_tradingdate,"yyyyMMdd")>=(to_date(20120801,"yyyyMMdd")-10)andf_tradingdate<=20120801)andrownum<2),"fm999999990.9999"))gqsnjzzzl
from
(select
b.scode,b.fund5,b.fund4,b.fund10
from
sdc_fundbasebleftjoinsdc_securityaonb.scode=a.scodeandb.mktcode=a.mktcodeandb.fund49=a.stype
where
b.scodein(selectt.fundcodefromfund_fundcategoryonetwhere(t.fundtzfs="开放式"ort.fundtzfs="封闭式")andt.fundonefl!="货币市场基金"andt.fundonefl!="交易类")
and(a.enddate>=to_date(20120801,"yyyy-MM-dd")ora.enddateisnullora.enddate=to_date(19000101,"yyyy-MM-dd"))
anda.stypein(6,7)orderbyscode
)sf
leftjoinfund_fundcategoryoneffonsf.scode=ff.fundcodeandff.fundonefl!="交易类"
非常复杂，不仔细介绍，此时需要将20120801处全部变成参数传入，，只写参数就得累死我，下面方法可以节省我的好多工作量，在最内部函数中修改select
b.scode,b.fund5,b.fund4,b.fund10,20120801tdate
from
将20120801用tdate变量来代替，这样传入的参数就用tdate来表示就可以了。
相关文章
- java.sql.SQLException: ORA-01008: 并非所有变量都已绑定的解决方法「建议收藏」
- 优化Oracle SQL优化：提升性能的先进方法（oracle当前sql）
- 轻松使用Oracle实现SQL数据导入（oracle导入sql）
- Linux限制文件大小：有效方法（linux限制文件大小）
- MSSQL导出SQL文件的简易方法（mssql导出sql文件）
- 语句利用MSSQL执行SQL语句的简单指南（mssql执行sql）
- 查询查询SQL Server服务器名称的简易方法（sqlserver服务器名称）
- 连接Oracle数据库，使用SQL进行查询（sql连oracle）
- MySQL教程：拼接SQL语句的方法（mysql拼接sql语句）
- 库恢复SQL Server数据库的一种方法（sqlserver还原）
- 2005升级到2008从SQL Server 2005升级到2008：注意事项与方法（怎么把sqlserver）
- Linux环境下执行SQL语句的步骤与方法（linux下执行sql）
- 方法利用SQL Server中的IN方法提升数据查询效率（sqlserver的in）
- MySQL SQL优化，提高查询效率。（mysql中的sql优化）
- 龄SQL Server计算人员年龄的实现方法（sqlserver得到年）
- 份SQL Server获取月份数据的简单方法（sqlserver取得月）
- SQL Server中简单的删除文件方法（sqlserver删文件）
- 简单易懂，直击主题：MySQL回滚SQL（mysql回滚sql）
- 探究ORACLE错误代码29861的含义及处理方法（oracle29861）
- MySQL 中 SQL 比较详解常用语句及其实现方法（mysql中sql比较）
- sql使用cxoracle写出优化的SQL（cx_oracle写死）
- MySQL SQL语句编写指南（mysql中写sql语句）
- Oracle中求和的实现方法（oracle中如何列求和）
- Oracle SQL编程从入门到精通（oracle sql编译）
- JSP多条SQL语句同时执行的方法
- SQL语句删除和添加外键、主键的方法
- jquery遍历之parent()和parents()的区别及parentsUntil()方法详解
- 查找php配置文件php.ini所在路径的二种方法