绑定变量的一个例子

最新推荐文章于 2022-01-19 07:50:00 发布

congzhi8603

最新推荐文章于 2022-01-19 07:50:00 发布

阅读量112

点赞数

绑定变量的一个例子[@more@]

在ORACLE中,使用绑定变量,可以降低硬解析,通常可以提高系统的性能.
如下是一个绑定变量的例子(在存储过程中使用):
create or replace procedure pro_update_his_data_mon(v_table in varchar)
as
v_sql varchar2(500);
v_sum_filed varchar2(500);
v_sum_mid varchar2(100);
v_like varchar2(10):='A2%';
v_99999 varchar2(10):='99999';
v_zero varchar2(10):='0';
v_table_exist number;
v_table_exist_bak number;
cursor fet_filed is select column_name from user_tab_columns a
where a.table_name = upper(v_table)
and a.data_type like '%NUMBER%'
and column_name <> 'STATIS_MONTH'
order by a.column_id asc;
begin
open fet_filed;
fetch fet_filed into v_sum_mid;
while fet_filed%found loop
v_sum_filed:=v_sum_filed||',sum('||v_sum_mid||')';
fetch fet_filed into v_sum_mid;
end loop;
close fet_filed;
-- 判断临时表是否存在
v_sql:= 'create table tmp as select * from '||v_table||' where 1=2';

select count(*) into v_table_exist
from user_tables a
where table_name = upper('tmp')
;
if v_table_exist = 0 then
execute immediate v_sql;
else
execute immediate 'truncate table tmp';
end if;

-- 提取各地市数据
execute immediate 'insert into tmp'
||' select * from '
||v_table
||' where organize_id like :1'
||' or organize_id = :2'
using v_like,v_99999;

-- 创建备份表
select count(*) into v_table_exist_bak
from user_tables a
where table_name = upper(v_table||'b')
;
if v_table_exist_bak = 0 then
execute immediate 'create table '||v_table||'b'||' as select * from '||v_table;
end if;

execute immediate 'delete from '||v_table;

execute immediate 'insert into '||v_table
||' select'
||' statis_month'
||' ,case grouping_id(a.prov_total_area,a.fee_total_area,a.new_fee_area,a.area_code)'
||' when 7 then :1' -- 广西合计 0111
||' when 13 then :2' -- 省级汇总 1101
||' when 9 then a.new_fee_area' -- 市级汇总 1001
||' when 12 then a.new_fee_area' -- 市级合计 1100
||' else a.area_code' -- 1110
||' end organize_id' -- 组织ID
||' ,case grouping_id(a.prov_total_area,a.fee_total_area,a.new_fee_area,a.area_code)'
||' when 7 then a.prov_total_area' -- 广西合计
||' when 13 then a.new_fee_area' -- 省级汇总
||' when 9 then a.fee_total_area' -- 市级汇总
||' when 12 then a.area_code' -- 市级合计
||' else a.area_code'
||' end area_code' -- 地域编码
||' ,hwdic.pkg_area.get_area_name('
||' case grouping_id(a.prov_total_area,a.fee_total_area,a.new_fee_area,a.area_code)'
||' when 7 then a.prov_total_area' -- 广西合计
||' when 13 then a.new_fee_area' -- 省级汇总
||' when 9 then a.fee_total_area' -- 市级汇总
||' when 12 then a.area_code' -- 市级合计
||' else a.area_code'
||' end'
||' ) area_name '
||v_sum_filed
||' from (select '
||' hwdic.pkg_area.get_fee_total_area(:3) as prov_total_area' -- 省合计编码
||' ,hwdic.pkg_area.get_fee_area(area_code) as new_fee_area' -- 费率区编码
||' ,hwdic.pkg_area.get_fee_total_area(area_code) as fee_total_area' -- 费率区合计编码
||' ,s.*'
||' from tmp s ' -- 测试表可用任意业务表代替不用关联维表
||' where area_code like :4 or area_code=:5'
||' ) a'
||' group by grouping sets ('
||' (a.area_code)' -- 1110 14 产生县级汇总值格式 A2101 A2101
||' ,(a.new_fee_area,a.area_code)' -- 1100 12 产生市级汇总值格式 771 A2101
||' ,(a.fee_total_area,a.new_fee_area)' -- 1001 9 产生市公司合计值格式 771 A2199
||' ,(a.new_fee_area)' -- 1101 13 产生省级合计值格式 0 771
||' ,(a.prov_total_area)' -- 0111 7 产生全区合计格式 0 9
||' )'
||' ,statis_month'
using v_zero,v_zero,v_zero,v_like,v_99999
;
execute immediate 'drop table tmp';
end;