繫結變數的一個例子

繫結變數的一個例子[@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;