将varchar参数传递给过程
问题描述:
我正试图编写一个过程来收集所有给定文件的数据。 我希望程序被用于多个文件。 您应该能够给程序提供任意数量的文件,比如25个文件,并且它会收集这些文件的所有数据。将varchar参数传递给过程
从各个表中收集数据的脚本非常易于编写。其中之一是以下:
select * From Table_name a
Where a.FILE_ID in pi_fileid
and a.FILE_TYPE = 'L';
我也尝试过测试,如下图所示:
EXECUTE IMMEDIATE 'select count(*) from Table_name
where file_id in (' || pi_fileid || ')
AND file_type = ''L''
AND status = ''FP''' INTO cnt;
pi_fileid是在它的文件名的VARCHAR。他们将以逗号分隔。
我的程序执行调用如下:
DECLARE
PI_FILETYPE VARCHAR2(200);
PI_DATE DATE;
PI_FILEID VARCHAR2(200);
BEGIN
PI_FILETYPE := 'BLA_BLA';
PI_DATE := '30-dec-2009';
PI_FILEID := (''Z1100E71g'' ,''Y1100E71g'');
GATHER_PKG.GATHEREFILE (PI_FILETYPE, PI_DATE, PI_FILEID);
COMMIT;
END;
但是,从外观上来看,这不会从数据库服务器似乎想我在寻找'Z1100E71g收集什么.def'',而不是'Z1100E71g.def'。所以,这没有任何回报。
有没有办法做到这一点?
答
最好是使用SQL数组:
SQL> create type file_id_tab as table of varchar2(20);
2/
Type created.
SQL> create or replace package GATHER_PKG
2 as
3 procedure gatherfile(pi_filetype table_name.file_type%type,
4 pi_date date,
5 pi_fileid file_id_tab);
6 end;
7/
Package created.
SQL> create or replace package body GATHER_PKG
2 as
3 procedure gatherfile(pi_filetype table_name.file_type%type,
4 pi_date date,
5 pi_fileid file_id_tab)
6 is
7 v_cnt number;
8 begin
9
10 select count(*)
11 into v_cnt
12 from table_name
13 where file_id in (select /*+ cardinality(p, 10) */ p.column_value
14 from table(pi_fileid) p)
15 and file_type = pi_filetype
16 and status = 'FP';
17
18 dbms_output.put_line(v_cnt);
19 end;
20 end;
21/
Package body created.
SQL> DECLARE
2 PI_FILETYPE VARCHAR2(200);
3 PI_DATE DATE;
4 PI_FILEID file_id_tab;
5
6 BEGIN
7 PI_FILETYPE := 'BLA_BLA';
8 PI_DATE := '30-dec-2009';
9 PI_FILEID := file_id_tab('Z1100E71g' ,'Y1100E71g');
10
11 GATHER_PKG.GATHERFILE (PI_FILETYPE, PI_DATE, PI_FILEID);
12 COMMIT;
13 END;
14/
2
PL/SQL procedure successfully completed.
基数暗示/*+ cardinality(p, 10) */
用来告诉行预言TEH约数。最好将其设置为您希望阵列保持的大约数量(因为oracle会默认以8k的卡片猜测)否
您是否允许更改过程的定义?你能传入一个合适的集合,而不是传递逗号分隔的'VARCHAR2'吗? –
这是一个全新的软件包,我正在写...所以我可以做出任何类型的改变,我可以......你能详细说说你在想什么吗? – Dash