在 Oracle 存储过程中创建和使用序列 - 序列不存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40016510/
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
Creating and using Sequence in Oracle stored procedure - Sequence doesn't exist
提问by Doubtful
DECLARE
v_emp_id NUMBER;
empid NUMBER;
stmt VARCHAR2(1000);
BEGIN
SELECT MAX(emp_id) + 1 INTO v_emp_id FROM employees;
BEGIN
dbms_output.put_line(v_emp_id );
stmt := 'CREATE SEQUENCE emp_seq start with ' ||v_emp_id|| ' increment by 1 NOCYCLE';
EXECUTE IMMEDIATE stmt;
COMMIT;
END;
insert into emp_new select emp_seq.nextval,empname from (select * from employee where active = 0);
dbms_output.put_line(empid);
END;
/
When executing above procedure, I get the following errors ORA-06550: line 13, column 10: PL/SQL: ORA-02289: sequence does not exist ORA-06550: line 13, column 3: PL/SQL: SQL Statement ignored
执行上述过程时，出现以下错误 ORA-06550：第 13 行，第 10 列：PL/SQL：ORA-02289：序列不存在 ORA-06550：第 13 行，第 3 列：PL/SQL：忽略 SQL 语句
But when executing the below procedure, it is successful and sequence is created.
但是当执行下面的过程时，它成功并创建了序列。
DECLARE
v_emp_id NUMBER;
empid NUMBER;
stmt VARCHAR2(1000);
BEGIN
SELECT MAX(emp_id) + 1 INTO v_emp_id FROM employees;
BEGIN
dbms_output.put_line(v_emp_id );
stmt := 'CREATE SEQUENCE emp_seq start with ' ||v_emp_id|| ' increment by 1 NOCYCLE';
EXECUTE IMMEDIATE stmt;
COMMIT;
END;
dbms_output.put_line(empid);
END;
/
回答by Kacper
During compile time sequence not exists so compiler returns error. Execute immediate will be executed on runtime but compiler don't know that it will create sequence you called later in code.
在编译时间序列不存在所以编译器返回错误。立即执行将在运行时执行，但编译器不知道它会创建您稍后在代码中调用的序列。
create or replace procedure createtest as
begin
execute immediate 'create table t1 (c1 number)';
insert into t1 values (1);
end;
/
Gives the same error as yours as table t1 not exists. So insert statement is invalid. This is error during compilation of PL/SQL.
给出与您相同的错误，因为表 t1 不存在。所以插入语句无效。这是 PL/SQL 编译期间的错误。
create table t1 (c1 number);
After that I can create procedure but when I do:
之后我可以创建程序，但是当我这样做时：
exec createtest;
I got error that table already exists. But compiler didn't knew that I'm trying create again same table so it will be returned on run not during compilation.
我收到错误表已经存在。但是编译器不知道我正在尝试再次创建同一个表，因此它将在运行时而不是在编译期间返回。
If you really need to do it such way please do:
如果您真的需要这样做，请执行以下操作：
create or replace procedure createtest as
begin
execute immediate 'create table t1 (c1 number)';
execute immediate 'insert into t1 values (1)';
end;
/
In you case:
在你的情况下：
execute immediate 'SELECT emp_seq.nextval FROM dual' INTO empid;
[EDIT] What I understand is you want to be sure that sequence is set to max(empid) so please do not try to create it inside procedure. Create sequence once then in procedure body execute:
[编辑] 我的理解是你想确保序列设置为 max(empid) 所以请不要尝试在过程中创建它。创建序列一次然后在过程体中执行：
select max(empid) into maxempid from employee;
select emp_seq.currval into maxseq from dual;
if(empid-maxseq>0) then
execute immediate 'alter sequence emp_seq increment by ' || empid-maxseq;
end if;
回答by Rusty
You should create sequence statical before you create procedure.
您应该在创建过程之前创建序列静态。
DROP SEQUENCE emp_seq
/
BEGIN
SELECT MAX(emp_id) + 1 INTO v_emp_id FROM employees;
dbms_output.put_line(v_emp_id );
execute immediate 'CREATE SEQUENCE emp_seq start with ' ||v_emp_id|| ' increment by 1 NOCYCLE';
END;
/
and then CREATE OR REPLACE FUNCTION/PROCEDURE/PACKAGE .... which refers to your sequence
然后 CREATE OR REPLACE FUNCTION/PROCEDURE/PACKAGE .... 指的是你的序列
