数组或列表导入Oracle中使用cfprocparam [英] array or list into Oracle using cfprocparam
问题描述
我有一个值列表，我想通过存储过程插入到表中。
我想我会传递一个数组到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.
EDIT
也许我在想这个错误的方式？我确定我不在这里做任何新的...
我想我会将列表转换为关联数组，然后循环数组，因为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
productName ='foo'
productDescription ='bar'
...
...
etc
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的列表作为测试。
CREATE OR REPLACE PACKAGE工具AS
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上得到一个错误PLS-00382：expression is type type ：= tools.listToArray（inList）;
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;
/
推荐答案
自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 只支持原始数据类型（number，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。但很久以前，约翰·斯潘塞发表了一个手动的解决方案，在Oracle 9i在OTN论坛上工作。 在此处查找。
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.
编辑
但是... Oracle讨厌我
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;
/
正如你所看到的，函数只是一个包装器。 / p>
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>
这篇关于数组或列表导入Oracle中使用cfprocparam的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
