instr() v.s. like

本文介绍了两种SQL查询方法:使用INSTR函数和LIKE子句来搜索包含特定字符串的数据记录。通过对比这两种方式,文章指出INSTR函数在某些情况下可以更快地完成查询任务。

select * from t where instr(cnation,'中国')>0;

select * from t where cnation like '%中国%';

instr约快1/3

v_vc_ycbz varchar2(2):= 'Y'; begin return_code:=-99; return_str := '[pkg_dubbo.pkgsp_sp_hsdubbo_new_cxPz]获取凭证表数据失败,请联系系统管理员'; open return_data for /* with v_gzbdate as ( select i.l_ztbh ,i.d_ywrq from tfundinfo t ,ttmpgzb_index i where t.l_fundid = i.l_ztbh and i.l_sfqr = 1 and instr(',' || a_vc_cpdm || ',', ',' || a.vc_code || ',') > 0 and i.d_ywrq >= to_date(a_d_date_b,'yyyy-mm-dd') and i.d_ywrq <= to_date(a_d_date_e,'yyyy-mm-dd') ) , */ with v_pzbpage as ( select t.*,ROW_NUMBER() OVER (order by t.vc_fundname asc , t.d_pzrq desc ,t.vc_pzh asc ) rowno from ( select a.vc_code vc_fundcode, a.vc_name vc_fundname, a.vc_glr vc_glrmc, v.d_make d_pzrq, lpad(v.l_no, 6, 0) vc_pzh, vs.vc_code vc_kmdm, vs.vc_fullname vc_kmmc, vs.VC_DIGEST vc_zy, decode(vs.EN_DEBIT, 0, -1, 1) en_jd, decode(vs.EN_DEBIT, 0, vs.EN_CREDIT, vs.EN_DEBIT) en_je, vs.EN_QUANTITY en_sl, vs.VC_JSBZ vc_currency, vs.EN_FOREIGN EN_YBJE, vs.en_exch EN_HL, nvl((select nvl((select d.vc_item_name from tdictionary d where 10249 = l_dictionary_no and d.c_lemma_item = z.vc_type), z.vc_sm) vc_name from tzdyzzszz z where z.l_ztbh = (select s.l_type from tsysinfo s where s.l_id=vs.l_fundid ) and z.l_bh = v.l_zzgsbh ),'手工凭证' ) vc_pzlb, 1 l_sjly, vs.l_fundid vc_ztbh from tfundinfo a ,tvoucher v, tvouchers vs where instr(',' || a_vc_cpdm || ',', ',' || a.vc_code || ',') > 0 and vs.l_FundID = a.l_fundid and (vs.vc_code like a_vc_kmdm||'%' or a_vc_kmdm is null) and (vs.vc_fullname like '%'||a_vc_kmmc||'%' or a_vc_kmmc is null) and (vs.VC_DIGEST like '%'||a_vc_zy||'%' or a_vc_zy is null) and vs.d_make between a_d_date_b and a_d_date_e and v.d_make between a_d_date_b and a_d_date_e and v.l_fundid = vs.l_fundid and v.l_id = vs.l_mainid and v.L_STATE < 32 union all select a.vc_code vc_fundcode, a.vc_name vc_fundname, a.vc_glr vc_glrmc, v.d_make d_pzrq, lpad(v.l_no, 6, 0) vc_pzh, vs.vc_code vc_kmdm, vs.vc_fullname vc_kmmc, vs.VC_DIGEST vc_zy, decode(vs.EN_DEBIT, 0, -1, 1) en_jd, decode(vs.EN_DEBIT, 0, vs.EN_CREDIT, vs.EN_DEBIT) en_je, vs.EN_QUANTITY en_sl, vs.VC_JSBZ vc_currency, vs.EN_FOREIGN EN_YBJE, vs.en_exch EN_HL, nvl((select nvl((select d.vc_item_name from tdictionary d where 10249 = l_dictionary_no and d.c_lemma_item = z.vc_type), z.vc_sm) vc_name from tzdyzzszz z where z.l_ztbh = (select s.l_type from tsysinfo s where s.l_id=vs.l_fundid ) and z.l_bh = v.l_zzgsbh ),'手工凭证' ) vc_pzlb, 1 l_sjly, vs.l_fundid vc_ztbh from tfundinfo a , t_h_voucher v ,t_h_vouchers vs where instr(',' || a_vc_cpdm || ',', ',' || a.vc_code || ',') > 0 and vs.l_FundID = a.l_fundid and (vs.vc_code like a_vc_kmdm||'%' or a_vc_kmdm is null) and (vs.vc_fullname like '%'||a_vc_kmmc||'%' or a_vc_kmmc is null) and (vs.VC_DIGEST like '%'||a_vc_zy||'%' or a_vc_zy is null) and v.l_fundid = vs.l_fundid and vs.d_make between a_d_date_b and a_d_date_e and v.d_make between a_d_date_b and a_d_date_e and v.l_id = vs.l_mainid and v.L_STATE < 32 ) t --order by t.vc_fundname desc , t.d_pzrq desc ,t.vc_pzh ) select pp.vc_fundcode, pp.vc_fundname, pp.vc_glrmc, pp.d_pzrq, pp.vc_pzh, pp.vc_kmdm, pp.vc_kmmc , pp.vc_zy , pp.en_jd , pp.en_je , pp.en_sl , pp.vc_currency, pp.EN_YBJE , pp.EN_HL , pp.vc_pzlb , pp.l_sjly, pp.vc_ztbh from v_pzbpage pp where pp.rowno>=((a_l_pageno-1)* a_l_pagenum + 1) and pp.rowno<=a_l_pageno*a_l_pagenum ; -- select count(1) into return_recnum from v_pzbpage ; select count(*) into return_totalcount from ( select a.vc_code vc_fundcode, a.vc_name vc_fundname, a.vc_glr vc_glrmc, v.d_make d_pzrq, lpad(v.l_no, 6, 0) vc_pzh, vs.vc_code vc_kmdm, vs.vc_fullname vc_kmmc, vs.VC_DIGEST vc_zy, decode(vs.EN_DEBIT, 0, -1, 1) en_jd, decode(vs.EN_DEBIT, 0, vs.EN_CREDIT, vs.EN_DEBIT) en_je, vs.EN_QUANTITY en_sl, vs.VC_JSBZ vc_currency, vs.EN_FOREIGN EN_YBJE, vs.en_exch EN_HL, nvl((select nvl((select d.vc_item_name from tdictionary d where 10249 = l_dictionary_no and d.c_lemma_item = z.vc_type), z.vc_sm) vc_name from tzdyzzszz z where z.l_ztbh = (select s.l_type from tsysinfo s where s.l_id=vs.l_fundid ) and z.l_bh = v.l_zzgsbh ),'手工凭证' ) vc_pzlb, 1 l_sjly, vs.l_fundid vc_ztbh from tfundinfo a ,tvoucher v, tvouchers vs where instr(',' || a_vc_cpdm || ',', ',' || a.vc_code || ',') > 0 and vs.l_FundID = a.l_fundid and (vs.vc_code like a_vc_kmdm||'%' or a_vc_kmdm is null) and (vs.vc_fullname like '%'||a_vc_kmmc||'%' or a_vc_kmmc is null) and (vs.VC_DIGEST like '%'||a_vc_zy||'%' or a_vc_zy is null) and v.l_fundid = vs.l_fundid and vs.d_make between a_d_date_b and a_d_date_e and v.d_make between a_d_date_b and a_d_date_e and v.l_id = vs.l_mainid and v.L_STATE < 32 union all select a.vc_code vc_fundcode, a.vc_name vc_fundname, a.vc_glr vc_glrmc, v.d_make d_pzrq, lpad(v.l_no, 6, 0) vc_pzh, vs.vc_code vc_kmdm, vs.vc_fullname vc_kmmc, vs.VC_DIGEST vc_zy, decode(vs.EN_DEBIT, 0, -1, 1) en_jd, decode(vs.EN_DEBIT, 0, vs.EN_CREDIT, vs.EN_DEBIT) en_je, vs.EN_QUANTITY en_sl, vs.VC_JSBZ vc_currency, vs.EN_FOREIGN EN_YBJE, vs.en_exch EN_HL, nvl((select nvl((select d.vc_item_name from tdictionary d where 10249 = l_dictionary_no and d.c_lemma_item = z.vc_type), z.vc_sm) vc_name from tzdyzzszz z where z.l_ztbh = (select s.l_type from tsysinfo s where s.l_id=vs.l_fundid ) and z.l_bh = v.l_zzgsbh ),'手工凭证' ) vc_pzlb, 1 l_sjly, vs.l_fundid vc_ztbh from tfundinfo a , t_h_voucher v ,t_h_vouchers vs where instr(',' || a_vc_cpdm || ',', ',' || a.vc_code || ',') > 0 and vs.l_FundID = a.l_fundid and (vs.vc_code like a_vc_kmdm||'%' or a_vc_kmdm is null) and (vs.vc_fullname like '%'||a_vc_kmmc||'%' or a_vc_kmmc is null) and (vs.VC_DIGEST like '%'||a_vc_zy||'%' or a_vc_zy is null) and v.l_fundid = vs.l_fundid and vs.d_make between a_d_date_b and a_d_date_e and v.d_make between a_d_date_b and a_d_date_e and v.l_id = vs.l_mainid and v.L_STATE < 32 ) t ; return_pages :=floor((return_totalcount + a_l_pagenum -1)/ a_l_pagenum ) ; v_vc_ycbz := 'N'; return_code := 0; return_str := '成功执行'; exception when others then --系统自动异常捕捉 if v_vc_ycbz = 'N' then return_code:=-1; return_str := '[pkg_dubbo.pkgsp_sp_hsdubbo_new_cxPz]异常错误:'||chr(13)||sqlerrm; end if; --人为考虑系统异常 if v_vc_ycbz = 'Y' then return_str := return_str||chr(13)||sqlerrm; end if; --人为的制造了异常 if v_vc_ycbz = 'H' then return_str := return_str; end if; end pkgsp_sp_hsdubbo_new_cxPzbPage;
最新发布
11-14
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值