oracle删除嵌套类型,如何在oracle pl / sql中选择嵌套类型？

11gR2之前的Oracle版本限制我们将BULK COLLECT用于记录的集合(嵌套表或varray).在Oracle Docs上阅读更多

here.

如果你想看看它是如何在11gR2中完成的,请向下滚动到这个答案的EDIT 2部分.

另一种方法是,可以为每列使用单独的集合 – 这是一种应用最广泛的方法.在这里你可以有：

/*

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;

我没有删除程序中的许多行,以便让您了解更改.

编辑：请参阅我上面给出的Oracle Docs链接中的“BULK COLLECT限制”部分以及here.

编辑#2：

你必须使用CREATE TYPE … IS OBJECT而不是RECORD.此外,您需要按照我尝试时的方式修改SELECT语句.有关进一步的参考,请参阅Oracle Docs here和StackOverflow问题here.

我在我的机器上试过的代码(运行Oracle 11g R2)如下：

– SELECT * FROM user_objects WHERE object_type =’TYPE’;

清晰的屏幕;

SET SERVEROUTPUT ON;

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;

/

**输出**：

TYPE temp_t_test compiled

TYPE temp_tbl_test compiled

anonymous block completed

Bulk Collect Successful!