ORACLE：对象类型集合的插入表值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36112518/
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
ORACLE: INSERT INTO Table VALUES of object type collection
提问by RafalK
Having created the below types;
创建了以下类型；
CREATE OR REPLACE TYPE OBJ_TYPE AS OBJECT
( FLAG DATE
, NUMB NUMBER(2,0)
, VARC VARCHAR2(40 BYTE));
/
CREATE OR REPLACE TYPE TBL_OBJ_TYPE AS TABLE OF OBJ_TYPE;
/
I want to simply insert the data set into a table;
我想简单地将数据集插入表中；
DECLARE
DATA_SET TBL_OBJ_TYPE := TBL_OBJ_TYPE();
BEGIN
FOR REC IN (SELECT * FROM TBL_01)
LOOP
DATA_SET.EXTEND;
DATA_SET(DATA_SET.COUNT) :=
OBJ_TYPE( 1
, REC.TBL_01_COL1
, REC.TBL_01_COL2);
END LOOP;
FORALL REC IN DATA_SET.FIRST..DATA_SET.LAST
INSERT INTO TBL_02
VALUES ( DATA_SET(REC).FLAG --listed column
, DATA_SET(REC).NUMB --listed column
, DATA_SET(REC).VARC); --listed column
END;
This one works fine, but is it somehow possible to change the "VALUES" clause to avoid naming each attribute in the source object? I would like something like this:
这个工作正常，但是是否可以以某种方式更改“VALUES”子句以避免命名源对象中的每个属性？我想要这样的东西：
VALUES DATA_SET(REC)
Any help will be much appreciated.
任何帮助都感激不尽。
回答by krokodilko
Assumming that columns in the table TBL_01have the same names as names in the object declaration:
假设表中的列TBL_01与对象声明中的名称具有相同的名称：
DECLARE
DATA_SET TBL_OBJ_TYPE := TBL_OBJ_TYPE();
BEGIN
SELECT OBJ_TYPE( sysdate, numb, varc ) BULK COLLECT INTO DATA_SET
FROM TBL_01;
INSERT INTO TBL_02( flag, numb, varc )
SELECT * FROM Table( DATA_SET );
END;
/
If TBL_01columns have different names, say X,Y,Z, then change respectively this line: SELECT OBJ_TYPE( X, Y, Z ) BULK COLLECT ...
如果TBL_01列具有不同的名称，例如 X、Y、Z，则分别更改此行：SELECT OBJ_TYPE( X, Y, Z ) BULK COLLECT ...
Note: you cannot assign 1into the first field here:
注意：您不能1在此处分配到第一个字段：
OBJ_TYPE( 1
, REC.TBL_01_COL1
, REC.TBL_01_COL2);
because the first field FLAGis declared as of type datein the object declaration.
I've replaced 1with sysdatein my example.
因为第一个字段在对象声明中FLAG被声明为类型date。
我已经在我的例子中替换1了sysdate。
回答by APC
We can create tables using object type definitions:
我们可以使用对象类型定义创建表：
SQL> create table TBL_02 of OBJ_TYPE
2 /
Table created.
SQL>
There aren't many good reasons to do this, but we can work with the types in our programs.
这样做的理由并不多，但我们可以在我们的程序中使用这些类型。
Here is a little example, starting with the test data
这是一个小例子，从测试数据开始
SQL> select * from tbl_01
2 /
COL_1 COL_2
---------- ----------------------------------------
23 ABC
42 XYZ
SQL> DECLARE
2 DATA_SET TBL_OBJ_TYPE := TBL_OBJ_TYPE();
3 BEGIN
4 FOR REC IN (SELECT * FROM TBL_01)
5 LOOP
6 DATA_SET.EXTEND;
7 DATA_SET(DATA_SET.COUNT) :=
8 OBJ_TYPE( sysdate
9 , REC.COL_1
10 , REC.COL_2);
11 END LOOP;
12
13 FORALL REC IN DATA_SET.FIRST..DATA_SET.LAST
14 INSERT INTO TBL_02
15 VALUES DATA_SET(REC)
16 ;
17 END;
18 /
PL/SQL procedure successfully completed.
SQL> select * from tbl_02;
FLAG NUMB VARC
--------- ---------- ----------------------------------------
20-MAR-16 23 ABC
20-MAR-16 42 XYZ
SQL>
Alternatively we can use a PL/SQL object defined against the target table. This uses a regular heap table for TBL_02:
或者，我们可以使用针对目标表定义的 PL/SQL 对象。这对 TBL_02 使用常规堆表：
DECLARE
type tgt_nt is table of TBL_02%rowtype;
data_set tgt_nt;
BEGIN
SELECT sysdate, COL_1, COL_2
bulk collect into data_set
FROM TBL_01;
FORALL REC IN DATA_SET.FIRST..DATA_SET.LAST
INSERT INTO TBL_02
VALUES DATA_SET(REC)
;
END;
/
回答by rohit purwar
--Rollbaclk--
DROP TYPE TBL_OBJ_TYPE;
DROP TYPE OBJ_TYPE;
DROP TABLE TBL_02;
DROP TABLE TBL_01;
--execute--
CREATE OR REPLACE TYPE OBJ_TYPE AS OBJECT
(
FLAG DATE,
NUMB NUMBER (2, 0),
VARC VARCHAR2 (40 BYTE)
);
/
CREATE OR REPLACE TYPE TBL_OBJ_TYPE AS TABLE OF OBJ_TYPE;
/
CREATE TABLE TBL_02
(
FLAG DATE,
NUMB NUMBER (2, 0),
VARC VARCHAR2 (40 BYTE)
);
CREATE TABLE TBL_01
(
TBL_01_COL0 DATE,
TBL_01_COL1 NUMBER (2, 0),
TBL_01_COL2 VARCHAR2 (40 BYTE)
);
INSERT INTO TBL_01
VALUES (SYSDATE, 1, 'mohit');
INSERT INTO TBL_01
VALUES (SYSDATE, 2, 'vijay');
INSERT INTO TBL_01
VALUES (SYSDATE, 3, 'sohan');
COMMIT;
SELECT * FROM TBL_01;
DECLARE
DATA_SET TBL_OBJ_TYPE := TBL_OBJ_TYPE ();
BEGIN
FOR REC IN (SELECT * FROM TBL_01)
LOOP
DATA_SET.EXTEND;
DATA_SET (DATA_SET.COUNT) :=
OBJ_TYPE (REC.TBL_01_COL0, REC.TBL_01_COL1, REC.TBL_01_COL2);
END LOOP;
FORALL REC IN DATA_SET.FIRST .. DATA_SET.LAST
INSERT INTO TBL_02
VALUES (
DATA_SET (REC).FLAG,
DATA_SET (REC).NUMB,
DATA_SET (REC).VARC);
END;
SELECT * FROM TBL_02;
