oracle pl/sql中如何选择成嵌套类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15577364/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you select into a nested type in oracle pl/sql?
提问by ScrappyDev
I want to be able to delete by rowid then immediately insert the data being deleted in an audit table.
我希望能够通过 rowid 删除，然后立即将要删除的数据插入审计表中。
There are far too many records to
INSERT INTO ... SELECT CRITERIAthen DELETE ... CRITERIA.
有太多的记录到
INSERT INTO ... SELECT CRITERIA那时DELETE ... CRITERIA。
I already know how to do everything just using rowid and INSERT INTO ... SELECT.
我已经知道如何仅使用 rowid 和INSERT INTO ... SELECT.
Inside package body:
内部包装体：
TYPE some_type IS RECORD (
row_id ROWID,
full_row table_name%ROWTYPE
);
TYPE some_type_list IS TABLE OF some_type
INDEX BY BINARY_INTEGER;
PROCEDURE do_stuff
IS
lc_data SYS_REFCURSOR;
lt_recs some_type_list;
BEGIN
OPEN lc_date FOR
SELECT rowid, a.*
FROM table_name;
LOOP
FETCH lc_data
BULK COLLECT INTO lt_recs
LIMIT 50000;
EXIT WHEN lt_recs.COUNT = 0;
--
FORALL i IN lt_recs.FIRST..lt_recs.LAST
DELETE table_name
WHERE ROWID = lt_recs(i).row_id;
--
FORALL i IN lt_recs.FIRST..lt_recs.LAST
INSERT INTO table_name_audit VALUES lt_recs(i).full_row;
END LOOP;
END;
If I try that i get the following error:
如果我尝试，我会收到以下错误：
Line: 117 Column: 25 Type: error Text: PLS-00597: expression 'LT_RECS' in the INTO list is of wrong type
Line: 117 Column: 25 Type: error Text: PLS-00597: expression 'LT_RECS' in the INTO list is of wrong type
采纳答案by Rachcha
Oracle versions prior to 11gR2 restrict us to use BULK COLLECT into a collection (nested table or varray) of records. Read more hereon Oracle Docs.
11gR2 之前的 Oracle 版本限制我们将 BULK COLLECT 用于记录的集合（嵌套表或变量数组）。在此处阅读有关 Oracle 文档的更多信息。
If you want to see how it is done in 11gR2, scroll down to EDIT 2section of this answer.
如果您想了解它是如何在 11gR2 中完成的，请向下滚动到此答案的EDIT 2部分。
An alternative tho this can be the use of separate collections for every column- an approach that is most widely used. In this you can have:
另一种方法是为每一列使用单独的集合——这是一种最广泛使用的方法。在这个你可以有：
/*
TYPE some_type IS RECORD (
row_id ROWID,
full_row table_name%ROWTYPE
);
TYPE some_type_list IS TABLE OF some_type
INDEX BY BINARY_INTEGER;
-- */
CREATE TYPE t_row_id IS TABLE OF ROWID;
CREATE TYPE t_col1 IS TABLE OF table_name.col1%TYPE;
CREATE TYPE t_col2 IS TABLE OF table_name.col2%TYPE;
CREATE TYPE t_col3 IS TABLE OF table_name.col3%TYPE;
...
...
CREATE TYPE t_colN IS TABLE OF table_name.colN%TYPE;
PROCEDURE do_stuff
IS
lc_data SYS_REFCURSOR;
-- lt_recs some_type_list;
row_id t_row_id;
col1 t_col1;
col2 t_col2;
col3 t_col3;
...
...
colN t_colN;
BEGIN
OPEN lc_date FOR
SELECT rowid, a.*
FROM table_name;
LOOP
FETCH lc_data
BULK COLLECT INTO row_id, col1, col2, col3, ..., colN
LIMIT 50000;
EXIT WHEN lt_recs.COUNT = 0;
--
FORALL i IN row_id.FIRST..row_id.LAST
DELETE table_name
WHERE ROWID = row_id(i);
--
FORALL i IN col1.FIRST..col1.LAST
INSERT INTO table_name_audit VALUES (col1(i), col2(i), col3(i), ..., colN(i));
END LOOP;
END;
I have not removed many of the rows in your program in order to let you understand the changes.
为了让您了解更改，我没有删除您程序中的许多行。
EDIT :Refer to the "Restrictions on BULK COLLECT" section of the Oracle Docs link I have given above and also here.
编辑：请参阅我在上面和此处给出的 Oracle Docs 链接的“BULK COLLECT 限制”部分。
EDIT #2 :
编辑#2：
You have to use CREATE TYPE ... IS OBJECTinstead of RECORD. Also, You need to modify the SELECTstatement the way I have done when I tried it. Please see the Oracle Docs hereand a StackOverflow question herefor further reference.
你必须使用CREATE TYPE ... IS OBJECT而不是RECORD. 另外，您需要按照SELECT我尝试时所做的方式修改语句。请参阅Oracle文档这里和StackOverflow的问题，在这里进行进一步的参考。
The code I tried on my machine (runs Oracle 11g R2) is as follows:
我在我的机器上试过的代码（运行 Oracle 11g R2）如下：
-- SELECT * FROM user_objects WHERE object_type = 'TYPE'; CLEAR SCREEN; SET SERVEROUTPUT ON;
-- SELECT * FROM user_objects WHERE object_type = 'TYPE'; 清除屏幕；设置服务器输出；
CREATE OR REPLACE TYPE temp_t_test AS OBJECT ( -- << OBJECT, not RECORD.
test_id INTEGER
, test_val VARCHAR2(50)
);
/
CREATE OR REPLACE TYPE temp_tbl_test AS TABLE OF TEMP_T_TEST;
/
DECLARE
v_test TEMP_TBL_TEST;
BEGIN
SELECT temp_t_test(t_id, t_val) -- << Notice the syntax
-- I'm selecting the columns as the defined OBJECT type.
BULK COLLECT INTO v_test
FROM (SELECT 1 AS t_id, 'ABCD' AS t_val FROM dual
UNION ALL
SELECT 2, 'WXYZ' FROM dual
UNION ALL
SELECT 3, 'PQRS' FROM dual);
dbms_output.put_line('Bulk Collect Successful!');
END;
/
** OUTPUT **:
** 输出 **：
TYPE temp_t_test compiled
TYPE temp_tbl_test compiled
anonymous block completed
Bulk Collect Successful!
回答by APC
What you're trying to works in 11gR2 - what version are you on?.
您尝试在 11gR2 中使用什么 - 您使用的是哪个版本？
The only wrong-looking thing in your post is this:
您的帖子中唯一看起来不对劲的是：
OPEN lc_date FOR
SELECT rowid, a.*
FROM table_name;
It ought to be this ...
应该是这个...
OPEN lc_data FOR
SELECT a.rowid, a.*
FROM table_name a;
... but these may simply be typos you introduced when sanitizing your code to post here.
...但这些可能只是您在清理代码以在此处发布时引入的拼写错误。
回答by David Aldridge
I don't think that I'd take this approach at all, to be honest.
老实说，我认为我根本不会采用这种方法。
A faster method would be along the lines of performing a multitable insert:
一种更快的方法是执行多表插入：
- insert the table columns into the audit table, possibly using direct path (APPEND hint) for efficiency
- insert the rowid's into an on-commit-delete-rows global temporary table.
- 将表列插入审计表，可能使用直接路径（APPEND 提示）以提高效率
- 将rowid 插入到on-commit-delete-rows 全局临时表中。
Then perform a delete against the original table using DELETE .. WHERE ROWID IN (SELECT ORIGINAL_ROWID FROM MY_GLOBAL_TEMP_TAB)
然后使用 DELETE .. WHERE ROWID IN (SELECT ORIGINAL_ROWID FROM MY_GLOBAL_TEMP_TAB) 对原始表执行删除
... and then commit.
...然后提交。
Faster, and less code I think.
我认为更快，更少的代码。
