oracle 从 SQL 存储过程创建/更改
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1999460/
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
Create/alter from SQL stored procedure
提问by M.J.
I want to call create table/ alter table command from a procedure. Is it possible?
我想从过程中调用 create table/alter table 命令。是否可以？
My requirement is to change the datatype of a column in all tables. So, I am just getting the column name from user_tab_cols. Now I want to create a temp table which requires create statement .. but i am unable to use that within a proc.
我的要求是更改所有表中列的数据类型。所以，我只是从user_tab_cols. 现在我想创建一个需要 create 语句的临时表 .. 但我无法在 proc 中使用它。
Can anyone please help me out?
任何人都可以帮我吗？
回答by APC
I presume from the reference to USER_TAB_COLUMNSthat this is Oracle. ALTERand CREATEstatements are DDL, which we cannot execute directly in PL/SQL. However, there are a couple of ways around this restriction: EXECUTE IMMEDIATEand DBMS_UTILITY.EXEC_DDL(). I will use EXECUTE IMMEDIATEin the following example.
我从参考中USER_TAB_COLUMNS推测这是 Oracle。 ALTER和CREATE语句是 DDL，我们不能直接在 PL/SQL 中执行。但是，有几种方法可以解决此限制：EXECUTE IMMEDIATE和DBMS_UTILITY.EXEC_DDL(). 我将EXECUTE IMMEDIATE在下面的例子中使用。
begin
for lrec in ( select table_name from user_tab_columns
where column_name = 'UNIVERSAL_COLUMN_NAME')
loop
execute immediate 'alter table '||lrec.table_name||
' modify UNIVERSAL_COLUMN_NAME varchar2(255)';
end loop;
end;
Note that the usual restrictions apply: the new datatype has to be compatible with the existing datatype (unless the column is empty), and things are trickier with some specilaized datatypes like CLOBs.
请注意，通常的限制适用：新数据类型必须与现有数据类型兼容（除非该列为空），并且对于某些特定数据类型（如 CLOB），事情会更加棘手。
edit
编辑
I haven't addressed the CREATE TABLE statement. The principle is the same, it is just longer to type out. Besides, I am not entirely clear how it applies to your prior requirement to change the datatype of those columns.
我还没有提到 CREATE TABLE 语句。原理是一样的，就是打出来的时间长了点。此外，我并不完全清楚它如何适用于您之前更改这些列的数据类型的要求。
回答by Fedor Hajdu
you can generate the query as string and execute it with 'exec' keyword.
您可以将查询生成为字符串并使用“exec”关键字执行它。
