--建立序列
create sequence seq_map_param_id ;
--建立参数表
create table map_param(id number primary key , tname varchar2(30)) ;
--初始化
insert into map_param values (seq_map_param_id.nextval , 'EMP') ;
insert into map_param values (seq_map_param_id.nextval , 'DEPT') ;
CREATE OR REPLACE PACKAGE BODY utl_map_2 is
PROCEDURE output_map (p_in_tname VARCHAR2,p_in_idx_col_name VARCHAR2 DEFAULT '') IS
tb_count number ;
l_col_type user_tab_cols.DATA_TYPE%type;
main_sql varchar2(32767);
BEGIN
--各种判断
--表是否存在
select count(*) into tb_count from user_tables where table_name=p_in_tname ;
if tb_count = 0 then
raise_application_error(-20999,'Table not found') ;
end if ;
--列是否存在,map key column类型是否正确
begin
select data_type into l_col_type
from user_tab_cols col
where col.TABLE_NAME=p_in_tname
and col.COLUMN_NAME=p_in_idx_col_name ;
if l_col_type not in ('VARCHAR2') then
raise_application_error(-20999,'idx column only varchar2');
end if ;
exception
when no_data_found then
raise_application_error(-20999,'Column not found in table '||p_in_tname) ;
end ;
--example:declare cursor cur is select * from EMP ; begin for rec in cur loop utl_var_2.EMP_list(rec.ENAME) := rec ; end loop ; end;
main_sql := 'declare ' ||
'cursor cur is select * from '||p_in_tname||' ; ' ||
'begin ' ||
'for rec in cur loop ' ||
'utl_var_2.'||p_in_tname||'_list(rec.'||p_in_idx_col_name||') := rec ; '||
'end loop ; '||
'end;' ;
execute immediate main_sql ;
--dbms_output.put_line(main_sql) ;
END ;
PROCEDURE var_init is
pkg_dec_sql varchar2(32767) ;
CURSOR param_cur is select * from map_param ;
begin
pkg_dec_sql := 'create or replace package utl_var_2 is ' ;
for rec in param_cur loop
pkg_dec_sql := pkg_dec_sql || 'type t_'||rec.tname||' is table of '||rec.tname||'%rowtype index by varchar2(4000) ; '||
rec.tname||'_list t_'||rec.tname||' ; ' ;
end loop ;
/*
example :
create or replace package utl_var_2 is
type t_EMP is table of EMP%rowtype index by varchar2(4000);
EMP_list t_EMP;
type t_DEPT is table of DEPT%rowtype index by varchar2(4000);
DEPT_list t_DEPT;
end;
*/
pkg_dec_sql := pkg_dec_sql || ' end ; ';
execute immediate pkg_dec_sql ;
end var_init ;
END utl_map_2 ;
SQL> begin
2 utl_map_2.output_map(p_in_tname => 'DEPT', p_in_idx_col_name => 'LOC');
3 dbms_output.put_line('deptno : '||utl_var_2.dept_list('NEW YORK').deptno ||' loc : ' ||utl_var_2.dept_list('NEW YORK').loc ) ;
4 end ;
5 /
deptno : 10 loc : NEW YORK
PL/SQL procedure successfully completed
SQL> begin
2 utl_map_2.output_map(p_in_tname => 'EMP', p_in_idx_col_name => 'ENAME');
3 dbms_output.put_line('ename : '||utl_var_2.emp_list('SMITH').ename ||' job : ' ||utl_var_2.emp_list('SMITH').job ) ;
4 end ;
5 /
ename : SMITH job : CLERK
PL/SQL procedure successfully completed
不过每次配置后的时候需要手动重新执行以下初始化程序 utl_map_2.var_init