将PLSQL上的10个选项添加到列中

Question

我想添加列，但它应该是我的数据库(PL/SQL(的10个选项。

我的sql查询如下：

ALTER TABLE mytable
ADD NEWCOL

你认为这是有效的吗？

Answer 1

我不必思考，我知道这行不通。

SQL> INSERT_INTO MYTABLE
2  (MYNEW_COL)
3  VALUES
4  (1,2,3,4,5,6,7,8,9,10);
INSERT_INTO MYTABLE
*
ERROR at line 1:
ORA-00900: invalid SQL statement

如果您想插入这些值，请使用行生成器：

SQL> insert into mytable (mynew_col)
2  select level from dual
3  connect by level <= 10;
10 rows created.
SQL> select * from mytable;
MYNEW_COL
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL>

否则，请找到其他有效的方法，例如

SQL> insert into mytable (mynew_col)
2  select 1 from dual union all
3  select 2 from dual union all
4  select 3 from dual;
3 rows created.

或

SQL> insert all
2    into mytable (mynew_col) values (1)
3    into mytable (mynew_col) values (2)
4    into mytable (mynew_col) values (3)
5  select * from dual;
3 rows created.
SQL>

---

[EDIT]啊，你把问题搞颠倒了。如果您想添加一个新列并限制有效值的数量，那么：

SQL> alter table mytable add newcol number;
Table altered.
SQL> alter table mytable add constraint
2    ch_col check (newcol between 1 and 10);
Table altered.

测试：

SQL> update mytable set newcol = 0;
update mytable set newcol = 0
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH_COL) violated

SQL> update mytable set newcol = 11;
update mytable set newcol = 11
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH_COL) violated

SQL> update mytable set newcol = 2;
16 rows updated.
SQL>