oracle 在 PL SQL 循环中更改表添加列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32279470/
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
Alter Table Add Column in PL SQL loop
提问by user2907249
I am trying to insert columns in a table using a for loop that iterates over a cursor. The code is:
我正在尝试使用遍历游标的 for 循环在表中插入列。代码是：
declare
cursor Months_ET is
SELECT distinct to_char(FEE_CD_ACT_SUM_ACCTG_DA, 'MON-YY') as "Month_U"
FROM EXPORT_TABLE
WHERE EXPORT_TABLE.FEE_CD_ACT_SUM_ACCTG_DA >= to_date('10/01/2013','mm/dd/yyyy')
AND EXPORT_TABLE.FEE_CD_ACT_SUM_ACCTG_DA < to_date('10/01/2014', 'mm/dd/yyyy');
n integer := 1;
begin
for mon in Months_ET loop
dbms_output.put_line(mon."Month_U");
execute immediate 'Alter table "Fee_CT" add('|| mon."Month_U" ||' varchar(20))';
n := n +1;
end loop;
end;
The cursor in the beginning jsut gets a list of unique month names which the dbms_output.put_line prints out as:
开头 jsut 中的光标获取一个唯一的月份名称列表，dbms_output.put_line 将其打印为：
SEP-14
AUG-14
JUL-14
So I know the variable is not empty.
所以我知道变量不是空的。
So using those results I want to add three columns for each month- yr. However I get an invalid datatype error. I have also tried altering to the for loop to concatenate the table name outside of the quotes like this:
因此，使用这些结果，我想为每个月-年添加三列。但是我得到一个无效的数据类型错误。我还尝试更改 for 循环以连接引号之外的表名，如下所示：
for mon in Months_ET loop
--Month_List(n) := mon."Month_U";
dbms_output.put_line(mon."Month_U");
execute immediate 'Alter table' ||"Fee_CT" || 'add('|| mon."Month_U" ||' varchar(20))';
n := n +1;
But I get a message that "Table,View Or Sequence reference 'Fee_CT' not allowed in this context." Not sure what I am doing wrong. The actual data is much larger and covers a wider time frame so using multiple alter table statements isn't realistic. plus the underlying data will be changing, so I need to be able to change the column names with the underlying data.
但是我收到一条消息“在这种情况下不允许表、视图或序列引用‘Fee_CT’。” 不知道我做错了什么。实际数据要大得多，涵盖的时间范围更广，因此使用多个 alter table 语句是不现实的。加上基础数据将发生变化，所以我需要能够使用基础数据更改列名。
采纳答案by mahi_0707
Not sure why you want to create columns dynamically.But it is possible though:
不确定为什么要动态创建列。但这是可能的：
Errors :
1.Column names can only have '_'(underscore),no other special character. ie.,
AUG-15 --> AUG_15
When using Alias names for further processing use
SUBQUERY(Month_U )Quotes should be properly used.
- space between keywords/variable in execute statement.
错误：
1.列名只能有'_'（下划线），不能有其他特殊字符。IE。，
AUG-15 --> AUG_15
使用别名进行进一步处理时使用
SUBQUERY(Month_U )应正确使用引号。
- 执行语句中关键字/变量之间的空格。
create table Table_A
(id integer,
date1 date
);
-- Table created.
begin
insert into table_A values (1,trunc(sysdate) );
insert into table_A values (2,trunc(sysdate+100) );
end;
select to_char(date1, 'MON-YY') as "Month_U" from table_A;
--AUG-15
--DEC-15
Declare
cursor months_ET is select month_u from
( select to_char(date1, 'MON_YY') AS Month_U from table_A) DUAL;
sql_stmnt varchar2(400) ;
table_name varchar2(20) := 'Table_A';
column_name varchar2(20) := 'New_col1';
data_type varchar2(20) := 'date' ; -- you can change to varchar2
Begin
FOR MON in months_ET
LOOP
sql_stmnt := ' alter table ' || table_name || ' add( ' || MON.MONTH_U
|| ' ' || data_type || ' ) ' ;
dbms_output.put_line(sql_stmnt );
Execute immediate sql_stmnt ;
END LOOP;
End;
OUTPUT:
输出：
alter table Table_A add( AUG_15 date )
alter table Table_A add( DEC_15 date )
Table altered.
回答by APC
Your table name and column names use non-standard characters - lower case letters, dashes. This is a really bad idea, because it means having to wrap every reference in double-quotes. Every person who has to use your schema will curse you whenever they have to fix a PLS-00357, ORA-00903or ORA-00904exception because they forgot to double-quote an identifier. Look, it's even caught you out :)
您的表名和列名使用非标准字符 - 小写字母、破折号。这是一个非常糟糕的主意，因为这意味着必须将每个引用都用双引号括起来。每个必须使用您的模式的人都会在他们必须修复PLS-00357,ORA-00903或ORA-00904异常时诅咒您，因为他们忘记了双引号标识符。看，它甚至把你抓到了:)
But if you really want to persist, the statement you need is:
但如果你真的想坚持，你需要的语句是：
execute immediate 'Alter table "Fee_CT" add("'|| mon."Month_U" ||"' varchar(20))';
The table name is part of the boilerplate text not a variable. You need to wrap the non-standard column name in double-quotes. Make sure the boilerplate has spaces around the key-words.
表名是样板文本的一部分，而不是变量。您需要用双引号将非标准列名括起来。确保样板文件的关键字周围有空格。
Above all, remember that a syntax error in dynamic SQL throws a runtime error, not a compilation error. Use logging or DBMS_OUTPUT to review the assembled statements.
最重要的是，请记住，动态 SQL 中的语法错误会引发运行时错误，而不是编译错误。使用日志记录或 DBMS_OUTPUT 查看组合语句。
回答by mahi_0707
Always use a DBMS_OUTPUT.PUT_LINEto test your execute immediatestatement.
始终使用 aDBMS_OUTPUT.PUT_LINE来测试您的execute immediate语句。
- Give space between keywords/variables.
- Use single quotes
- 在关键字/变量之间留出空间。
- 使用单引号
Now Check this Example:
现在检查这个例子：
create table Table_A(id integer);
-- Table created.
Declare
sql_stmnt varchar2(400) ;
table_name varchar2(20) := 'Table_A';
column_name varchar2(20) := 'New_col1';
data_type varchar2(20) := 'varchar2(20)' ;
Begin
sql_stmnt := ' alter table ' || table_name || ' add( ' || Column_name || ' ' || data_type || ' ) ' ;
execute immediate sql_stmnt ;
dbms_output.put_line(sql_stmnt );
End;
-- alter table Table_A add( New_col1 varchar2(20) )
-- Table altered.
Desc Table_A;
Column Data Type Length Precision Scale
ID NUMBER 22 - 0 - - -
NEW_COL1 VARCHAR2 20 - - - - -
