oracle 如何使用 PL/SQL 过程中的动态数据类型动态创建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2516640/
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 I create a table dynamically with dynamic datatype from a PL/SQL procedure
提问by Swapna
CREATE OR REPLACE PROCEDURE p_create_dynamic_table IS
v_qry_str VARCHAR2 (100);
v_data_type VARCHAR2 (30);
BEGIN
SELECT data_type || '(' || data_length || ')'
INTO v_data_type
FROM all_tab_columns
WHERE table_name = 'TEST1' AND column_name = 'ZIP';
FOR sql_stmt IN (SELECT * FROM test1 WHERE zip IS NOT NULL)
LOOP
IF v_qry_str IS NOT NULL THEN
v_qry_str := v_qry_str || ',' || 'zip_' || sql_stmt.zip || ' ' ||v_data_type;
ELSE
v_qry_str := 'zip_' || sql_stmt.zip || ' ' || v_data_type;
END IF;
END LOOP;
IF v_qry_str IS NOT NULL THEN
v_qry_str := 'create table test2 ( ' || v_qry_str || ' )';
END IF;
EXECUTE IMMEDIATE v_qry_str;
COMMIT;
END p_create_dynamic_table;
Is there any better way of doing this ?
有没有更好的方法来做到这一点？
回答by Allan
If I'm reading this correctly, it appears that you want to create a new table containing one column for each zip code.
如果我没看错，您似乎想创建一个新表，其中每个邮政编码都包含一列。
I think the answer you came up with is the best possible way to accomplish your stated goals. I would add that you probably want to sort the cursor used for the loop, which will ensure that the columns are always in the same order.
我认为您提出的答案是实现既定目标的最佳方式。我想补充一点，您可能希望对用于循环的游标进行排序，这将确保列始终处于相同的顺序。
However, your goal is highly suspect. It might be better to take a step back and consider whether creating this table is really the right way to solve your problem. This appears to be a massive de-normalization and will be a nightmare to maintain. Without knowing why you're taking this approach I can't offer a better solution, but, nonetheless, I think there probably is one.
但是，您的目标非常可疑。最好退后一步，考虑创建此表是否真的是解决问题的正确方法。这似乎是一个大规模的反规范化，将是一场噩梦。在不知道您为什么采用这种方法的情况下，我无法提供更好的解决方案，但是，尽管如此，我认为可能有一个。
回答by Oliver Michels
Why don't you create a view on the table, which contains only those columns with a zip?
为什么不在表上创建一个视图，它只包含那些带有 zip 的列？
create or replace view Zip_View as
select * from test1
where test1.zip is not null;
That way you don't need to copy the data. Or what are your exact requirements?
这样你就不需要复制数据。或者你的具体要求是什么？
