使用 cfprocparam 将数组或列表放入 Oracle
我有一个要通过存储过程插入到表中的值列表.我想我会将一个数组传递给 oracle 并循环遍历该数组，但我不知道如何将一个数组传递给 Oracle.我会传递一个列表，但我看不到如何使用 PL/SQL 将其转换为数组(我对 PL/SQL 还很陌生).我是不是走错了路?
I have a list of values I want to insert into a table via a stored procedure. I figured I would pass an array to oracle and loop through the array but I don't see how to pass an array into Oracle. I'd pass a list but I don't see how to work with the list to turn it into an array using PL/SQL (I'm fairly new to PL/SQL). Am I approaching this the wrong way?
使用 Oracle 9i 和 CF8.
Using Oracle 9i and CF8.
编辑
也许我想错了?我确定我在这里没有做任何新的事情......我想我会将列表转换为关联数组，然后循环该数组，因为 Oracle 似乎不适用于列表(在我有限的观察中).
Perhaps I'm thinking about this the wrong way? I'm sure I'm not doing anything new here... I figured I'd convert the list to an associative array then loop the array because Oracle doesn't seem to work well with lists (in my limited observation).
我正在尝试添加产品，然后为管理团队添加记录.
-- 产品表
I'm trying to add a product, then add records for the management team.
-- product table
产品名称 = 'foo'产品描述 = '酒吧'......等等
productName = 'foo' productDescription = 'bar' ... ... etc
-- managementteam 表只有产品的 id 和从下拉列表中选择的用户的 id.
-- The managementteam table just has the id of the product and id of the users selected from a drop down.
用户 ID 通过类似1,3,6,20"的列表传入
The user IDs are passed in via a list like "1,3,6,20"
我应该如何将记录添加到管理团队表中?
How should I go about adding the records to the management team table?
理论上，我将列表1,2,3,4"传递给 inserts.addProduct.
inserts.addProduct 应该调用 tools.listToArray 并返回一个数组.
inserts.addProduct 重新创建一个带有 * delim 的列表作为测试.
创建或替换包工具为
In theory I pass a list "1,2,3,4" to inserts.addProduct.
inserts.addProduct should call tools.listToArray and return an array.
inserts.addProduct recreates a list with a * delim as a test.
CREATE OR REPLACE PACKAGE tools AS
TYPE array_type is TABLE OF VARCHAR2(225) INDEX BY BINARY_INTEGER;
FUNCTION listToArray(in_list IN VARCHAR,
in_delim IN VARCHAR2 DEFAULT ',')
RETURN array_type;
END tools;
CREATE OR REPLACE PACKAGE BODY tools
AS
FUNCTION listToArray(in_list IN VARCHAR,
in_delim IN VARCHAR2 DEFAULT ',')
RETURN array_type
IS
l_token_count BINARY_INTEGER := 0;
-- l_token_tbl type_array;
i pls_integer;
l_start_pos INTEGER := 1;
l_end_pos INTEGER :=1;
p_parsed_table array_type;
BEGIN -- original work by John Spencer
WHILE l_end_pos <> 0 LOOP
l_end_pos := instr(in_list,in_delim,l_start_pos);
IF l_end_pos <> 0 THEN
l_token_count := l_token_count + 1;
p_parsed_table(l_token_count ) :=
substr(in_list,l_start_pos,l_end_pos - l_start_pos);
l_start_pos := l_end_pos + 1;
END IF;
END LOOP;
IF l_token_count = 0 THEN /* We haven't parsed anything so */
l_token_count := 1;
p_parsed_table(l_token_count) := in_list;
ELSE /* We need to get the last token */
l_token_count := l_token_count + 1;
p_parsed_table(l_token_count) := substr(in_list,l_start_pos);
END If;
RETURN p_parsed_table;
END listToArray; -- Procedure
END tools;
CREATE OR REPLACE PACKAGE inserts AS
TYPE array_type is TABLE OF VARCHAR2(225) INDEX BY BINARY_INTEGER;
PROCEDURE addProduct (inList IN VARCHAR2,
outList OUT VARCHAR2
);
END inserts;
CREATE OR REPLACE PACKAGE BODY inserts
AS
PROCEDURE addProduct (inList IN VARCHAR2,
outList OUT VARCHAR2
)
IS
i NUMBER;
localArray array_type := tools.listToArray(inList);
BEGIN
outList := '';
FOR i IN localArray.first .. localArray.last LOOP
outList := outList || '*' ||localArray(i); -- return a string just to test this mess
END LOOP;
END addProduct;
END inserts;
我目前在 localArray array_type := tools.listToArray(inList); 上收到错误PLS-00382:表达式类型错误"；
I'm currently getting an error "PLS-00382: expression is of wrong type" on localArray array_type := tools.listToArray(inList);
--创建sql类型集合
-- create sql type collection
CREATE OR REPLACE TYPE array_type is TABLE OF VARCHAR2(225);
/
CREATE OR REPLACE PACKAGE tools AS
FUNCTION listToArray(in_list IN VARCHAR,
in_delim IN VARCHAR2 DEFAULT ',')
RETURN array_type;
END tools;
/
CREATE OR REPLACE PACKAGE BODY tools
AS
FUNCTION listToArray(in_list IN VARCHAR,
in_delim IN VARCHAR2 DEFAULT ',')
RETURN array_type
IS
l_token_count BINARY_INTEGER := 0;
i pls_integer;
l_start_pos INTEGER := 1;
l_end_pos INTEGER :=1;
p_parsed_table array_type := array_type();
BEGIN
WHILE l_end_pos <> 0 LOOP
l_end_pos := instr(in_list,in_delim,l_start_pos);
IF l_end_pos <> 0 THEN
p_parsed_table.extend(1);
l_token_count := l_token_count + 1;
p_parsed_table(l_token_count ) :=
substr(in_list,l_start_pos,l_end_pos - l_start_pos);
l_start_pos := l_end_pos + 1;
END IF;
END LOOP;
p_parsed_table.extend(1);
IF l_token_count = 0 THEN /* We haven't parsed anything so */
l_token_count := 1;
p_parsed_table(l_token_count) := in_list;
ELSE /* We need to get the last token */
l_token_count := l_token_count + 1;
p_parsed_table(l_token_count) := substr(in_list,l_start_pos);
END If;
RETURN p_parsed_table;
END listToArray; -- Procedure
END tools;
/
CREATE OR REPLACE PACKAGE inserts AS
PROCEDURE addProduct (inList IN VARCHAR2,
outList OUT VARCHAR2
);
END inserts;
/
CREATE OR REPLACE PACKAGE BODY inserts
AS
PROCEDURE addProduct (inList IN VARCHAR2,
outList OUT VARCHAR2
)
IS
i NUMBER;
mylist VARCHAR(100);
localArray array_type := array_type();
BEGIN
localArray := tools.listToArray(inList);
mylist := '';
FOR i IN localArray.first .. localArray.last LOOP
mylist := mylist || localArray(i) || '*';
END LOOP;
aList := mylist;
END addProduct;
END inserts;
/
推荐答案
PL/SQL 从 Oracle 8.0 开始支持数组.它们曾经被称为 PL/SQL 表，这让每个人都感到困惑，所以现在它们被称为集合.了解更多.
PL/SQL has supported arrays since Oracle 8.0. They used to be called PL/SQL tables which confused the heck out of everybody, so now they are called collections. Find out more.
问题是，它们被实现为用户定义的类型(即对象).我对 ColdFusion 文档 的阅读表明 cfprocparam 仅支持原始"数据类型(数字、varchar2 等).所以不支持UDT.
The problem is, that they are implemented as User-Defined Types (i.e. objects). My reading of the ColdFusion documents suggests that cfprocparam only supports the "primitive" datatypes (number, varchar2, etc). So UDTs are not supported.
我不确定你的意思是什么:
I'm not sure what you mean by this:
我会传递一个列表，但我不知道如何传递使用列表将其变成使用 PL/SQL 的数组
I'd pass a list but I don't see how to work with the list to turn it into an array using PL/SQL
如果您的意思是要传递逗号分隔值的字符串 ....
If you mean you want to pass a string of comma separated values ....
"Fox in socks, Mr Knox, Sam-I-Am, The Lorax"
那么我有一个解决方法给你.Oracle 不提供内置的 Tokenizer.但很久以前，John Spencer 在 OTN 论坛上发布了一个适用于 Oracle 9i 的手动解决方案.在这里找到它.
then I have a workaround for you. Oracle doesn't provide a built-in Tokenizer. But a long time ago John Spencer published a hand-rolled solution which works in Oracle 9i on the OTN forums. Find it here.
编辑
但是...甲骨文讨厌我
but... Oracle hates me
不要绝望.自从 John 发布该消息以来，OTN 论坛已经升级了几次，而且格式似乎已经在某处损坏了代码.有几个以前没有的编译错误.
Do not despair. The OTN forums have been upgraded a few times since John posted that , and the formatting seems to have corrupted the code somewhere along the way. There were a couple of compilation errors which it didn't use to have.
我重写了 John 的代码，包括一个新函数.主要区别在于嵌套表被声明为 SQL 类型而不是 PL/SQL 类型.
I have rewritten John's code, including a new function. THe main difference is that the nested table is declared as a SQL type rather than a PL/SQL type.
create or replace type tok_tbl as table of varchar2(225)
/
create or replace package parser is
function my_parse(
p_str_to_search in varchar2
, p_delimiter in varchar2 default ',')
return tok_tbl;
procedure my_parse(
p_str_to_search in varchar2
, p_delimiter in varchar2 default ','
, p_parsed_table out tok_tbl);
end parser;
/
如您所见，函数只是过程的包装器.
As you can see, the function is just a wrapper to the procedure.
create or replace package body parser is
procedure my_parse ( p_str_to_search in varchar2
, p_delimiter in varchar2 default ','
, p_parsed_table out tok_tbl)
is
l_token_count binary_integer := 0;
l_token_tbl tok_tbl := tok_tbl();
i pls_integer;
l_start_pos integer := 1;
l_end_pos integer :=1;
begin
while l_end_pos != 0
loop
l_end_pos := instr(p_str_to_search,p_delimiter,l_start_pos);
if l_end_pos != 0 then
l_token_count := l_token_count + 1;
l_token_tbl.extend();
l_token_tbl(l_token_count ) :=
substr(p_str_to_search,l_start_pos,l_end_pos - l_start_pos);
l_start_pos := l_end_pos + 1;
end if;
end loop;
l_token_tbl.extend();
if l_token_count = 0 then /* we haven't parsed anything so */
l_token_count := 1;
l_token_tbl(l_token_count) := p_str_to_search;
else /* we need to get the last token */
l_token_count := l_token_count + 1;
l_token_tbl(l_token_count) := substr(p_str_to_search,l_start_pos);
end if;
p_parsed_table := l_token_tbl;
end my_parse;
function my_parse ( p_str_to_search in varchar2
, p_delimiter in varchar2 default ',')
return tok_tbl
is
rv tok_tbl;
begin
my_parse(p_str_to_search, p_delimiter, rv);
return rv;
end my_parse;
end parser;
/
在 SQL 中声明类型的好处是我们可以像这样在 FROM 子句中使用它:
The virtue of declaring the type in SQL is that we can use it in a FROM clause like this:
SQL> insert into t23
2 select trim(column_value)
3 from table(parser.my_parse('Fox in socks, Mr Knox, Sam-I-Am, The Lorax'))
4 /
4 rows created.
SQL> select * from t23
2 /
TXT
------------------------------------------------------------------------------
Fox in socks
Mr Knox
Sam-I-Am
The Lorax
SQL>
相关文章