oracle查询给出错误 [英] oracle query giving error

问题描述

我声明了一个可变的即srID

然后从select查询传递给它然后我试图传递这个变量值插入

in insert statment但它给出了我跟随错误：



PL / SQL：ORA-00984：此处不允许列





以下是我的查询---

I have declare one varaible i.e. srID
then passed value to it from select query then i am trying to pass this variable value insert
in insert statment but it giving me following error:

PL/SQL: ORA-00984: column not allowed here


below is my query---

declare srID Number;
        begin
        select max(ID)+1 as ID  into srID from table1;
        end;

         INSERT INTO table2 (ID)
         VALUES(srID)

推荐答案

看看这里：http://www.dba-oracle.com/t_ora_00984_column_not_allowed.htm [ ^ ]并尝试不带 AS ID的代码部分：

Have a look here: http://www.dba-oracle.com/t_ora_00984_column_not_allowed.htm[^] and try your code without AS ID part:

declare srID Number;
BEGIN
    SELECT ID INTO srID
    FROM (
        SELECT MAX(ID)+1 AS ID
        FROM table1
        ) AS T;
END;

 INSERT INTO table2 (ID)
 VALUES(srID)

秒方式：

Second way:

INSERT INTO table2 (ID)
SELECT MAX(ID)+1 AS ID
FROM table1

http://docs.oracle.com/cd/E17952_01/refman-5.1-en/insert-select.html [ ^ ]

您应该使用序列为表生成键，可能是这样的：



创建序列：

You should use a sequence to generate keys for your table, perhaps something like this:

Create the sequence:

CREATE SEQUENCE mySequence
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

使用序列生成表的唯一主键：

Use the sequence to generate unique primary keys for the table:

INSERT INTO myTable (id, name)
VALUES (mySequence.nextval, 'Oh my ;-)');

< br $> b $ b

最好的问候

Espen Harlinn

Best regards
Espen Harlinn

这篇关于oracle查询给出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！