oracle 立即执行 'alter table' 错误 pl/sql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29672541/
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
Execute immediate 'alter table' error pl/sql
提问by XhensB
I have a table and I want to create a copy of it. But I want this copy to be dynamic. First table may change. In a FOR LOOP, for each i (except 1), I want to alter the second table and add a column with the name "Col"+"i". Example: Col1, Col2, Col3.
While debugging, everything is ok except this line: EXECUTE IMMEDIATE 'ALTER TABLE '||tab_name||' ADD Col'||i||' NUMBER(2)';which throws 2 errors: "ORA-01403: no data found" and "ORA-06512"
What am I doing wrong? Thank you!
我有一张桌子，我想创建它的副本。但我希望这个副本是动态的。第一张桌子可能会改变。在 FOR 循环中，对于每个 i（1 除外），我想更改第二个表并添加一个名为“Col”+“i”的列。示例：Col1、Col2、Col3。调试时，除了这一行，一切正常：EXECUTE IMMEDIATE 'ALTER TABLE '||tab_name||' ADD Col'||i||' NUMBER(2)';这会引发 2 个错误：“ORA-01403：未找到数据”和“ORA-06512”我做错了什么？谢谢！
CREATE TABLE first_table (
id NUMBER(2,0) NOT NULL PRIMARY KEY,
col1 NUMBER(2,0) NOT NULL,
col2 NUMBER(2,0) NOT NULL,
col3 NUMBER(2,0) NOT NULL,
col4 NUMBER(2,0) NOT NULL,
col5 NUMBER(2,0) NOT NULL,
col6 NUMBER(2,0) NOT NULL,
col7 NUMBER(2,0) NOT NULL
);
CREATE TABLE second_table (
first_col NUMBER PRIMARY KEY
);
CREATE OR REPLACE PROCEDURE second_table
AS
nr_columns NUMBER(2);
tab_name VARCHAR2(20);
col_nam VARCHAR2(20) := 'COL';
BEGIN
SELECT COUNT (*)
INTO nr_columns
FROM user_tab_columns
WHERE table_name='FIRST_TABLE';
SELECT table_name
INTO tab_name
FROM user_tab_columns
WHERE column_name ='FIRST_COL';
FOR i IN 1..NR_COLUMNS
LOOP
IF (i=1) THEN
EXECUTE IMMEDIATE 'ALTER TABLE '||tab_name||' RENAME COLUMN FIRST_COL TO id';
ELSE
EXECUTE IMMEDIATE 'ALTER TABLE '||tab_name||' ADD Col'||i||' NUMBER(2)';
END IF;
END LOOP;
END;
/
回答by Lalit Kumar B
It works perfectly for me.
它非常适合我。
Tables
表
SQL> CREATE TABLE first_table (
2 id NUMBER(2,0) NOT NULL PRIMARY KEY,
3 col1 NUMBER(2,0) NOT NULL,
4 col2 NUMBER(2,0) NOT NULL,
5 col3 NUMBER(2,0) NOT NULL,
6 col4 NUMBER(2,0) NOT NULL,
7 col5 NUMBER(2,0) NOT NULL,
8 col6 NUMBER(2,0) NOT NULL,
9 col7 NUMBER(2,0) NOT NULL
10 );
Table created.
SQL>
SQL> CREATE TABLE second_table (
2 first_col NUMBER PRIMARY KEY
3 );
Table created.
SQL>
Procedure
程序
SQL> CREATE OR REPLACE PROCEDURE p_second_table
2 AS
3 nr_columns NUMBER(2);
4 tab_name VARCHAR2(20);
5 col_nam VARCHAR2(20) := 'COL';
6 BEGIN
7 SELECT COUNT (*)
8 INTO nr_columns
9 FROM user_tab_columns
10 WHERE table_name='FIRST_TABLE';
11
12 SELECT table_name
13 INTO tab_name
14 FROM user_tab_columns
15 WHERE column_name ='FIRST_COL';
16
17 FOR i IN 1..NR_COLUMNS
18 LOOP
19 IF (i=1) THEN
20 EXECUTE IMMEDIATE 'ALTER TABLE '||tab_name||' RENAME COLUMN FIRST_COL TO id';
21 dbms_output.put_line('if');
22 ELSE
23 EXECUTE IMMEDIATE 'ALTER TABLE '||tab_name||' ADD Col'||i||' NUMBER(2)';
24 dbms_output.put_line('else');
25 END IF;
26 END LOOP;
27 END;
28 /
Procedure created.
SQL>
SQL> sho err
No errors.
SQL>
I have added DBMS_OUTPUTto show you when it goes into IF-ELSEblock.
我添加了DBMS_OUTPUT来显示它何时进入IF-ELSE块。
Let's testit and see:
让我们测试一下，看看：
SQL> set serveroutput on
SQL> EXEC p_second_table;
if
else
else
else
else
else
else
else
PL/SQL procedure successfully completed.
Let's check table definition:
让我们检查表定义：
SQL> desc second_table;
Name Null? Type
----------------------------------------- -------- ---------
ID NOT NULL NUMBER
COL2 NUMBER(2)
COL3 NUMBER(2)
COL4 NUMBER(2)
COL5 NUMBER(2)
COL6 NUMBER(2)
COL7 NUMBER(2)
COL8 NUMBER(2)
SQL>
回答by Ilia Maskov
You should check this query
你应该检查这个查询
SELECT table_name
INTO tab_name
FROM user_tab_columns
WHERE column_name ='FIRST_COL';
Exception ORA-01403: no data foundmeans that select did't return a row. Check if table second_tableexists, or may be you have created it in another schema and now works in another. Also check name of coolumn SECOND_TABLE.FIRST_COLin user_tab_columns
异常ORA-01403: no data found意味着 select 没有返回一行。检查表是否second_table存在，或者您是否在另一个模式中创建了它并且现在在另一个模式中工作。同时检查coolumn的名称SECOND_TABLE.FIRST_COL中user_tab_columns
