在 oracle 的存储过程中创建/声明序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12624261/
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
create/declare a sequence in a stored procedure in oracle
提问by user747291
I have a table non_employee with emp_no as the primary key and a package with a procedure to insert in to this table.
我有一个以 emp_no 为主键的表 non_employee 和一个包含插入该表的过程的包。
I need to be able to autoincrement the emp_no when the procedure is run. I tried creating a sequence within the procedure like this, but getting errors. please see below and advice.
我需要能够在程序运行时自动增加 emp_no。我尝试在这样的过程中创建一个序列，但出现错误。请参阅下文和建议。
CREATE OR REPLACE PACKAGE BODY WFDDEV."ERD" IS
create SEQUENCE @seq_emp_nmbr;
PROCEDURE INS_NON_EMPLOYEES
(
in_DATE_ADDED DATE,
in_EMPLOYEE_NAME VARCHAR2,
in_DEPT_ID VARCHAR2,
in_SUB_DEPARTMENT VARCHAR2,
in_LOCATION VARCHAR2,
in_WORK_TEL_NO VARCHAR2,
in_TOUR VARCHAR2,
in_REST_DAYS VARCHAR2,
in_HOME_ADDRESS VARCHAR2,
in_CITY VARCHAR2,
in_STATE VARCHAR2,
in_ZIP VARCHAR2,
in_HOME_TEL_NO VARCHAR2,
in_GENDER VARCHAR2,
in_RACE VARCHAR2,
in_DATE_OF_BIRTH DATE,
in_AGE VARCHAR2,
in_HIRE_DATE DATE,
in_UNION_AFFILIATION VARCHAR2,
in_TITLE VARCHAR2,
in_NON_EE_INDICATOR VARCHAR2
) IS
BEGIN
INSERT INTO WFDDEV.NON_EMPLOYEES
(
EMP_NO,
DATE_ADDED,
EMPLOYEE_NAME,
DEPT_ID,
SUB_DEPARTMENT,
LOCATION,
WORK_TEL_NO,
TOUR,
REST_DAYS,
HOME_ADDRESS,
CITY,
STATE,
ZIP,
HOME_TEL_NO,
GENDER,
RACE,
DATE_OF_BIRTH,
AGE,
HIRE_DATE,
UNION_AFFILIATION,
TITLE,
NON_EE_INDICATOR
)
VALUES
(
emp_no.NEXTVAL,
in_DATE_ADDED,
in_EMPLOYEE_NAME,
in_DEPT_ID,
in_SUB_DEPARTMENT,
in_LOCATION,
in_WORK_TEL_NO,
in_TOUR,
in_REST_DAYS,
in_HOME_ADDRESS,
in_CITY,
in_STATE,
in_ZIP,
in_HOME_TEL_NO,
in_GENDER,
in_RACE,
in_DATE_OF_BIRTH,
in_AGE,
in_HIRE_DATE,
in_UNION_AFFILIATION,
in_TITLE,
in_NON_EE_INDICATOR
);
END;
Im getting PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
我收到 PLS-00103：在期待以下其中一项时遇到符号“CREATE”：
begin end function pragma procedure subtype type
开始结束函数编译指示过程子类型类型
error with this...
这个错误...
回答by Wolf
You need to create the sequence just once outside of the package as a separate database object. Then, in the insert statement in your package body you can reference the sequence to get the next value.
您只需在包外创建一次序列作为单独的数据库对象。然后，在包主体的插入语句中，您可以引用序列以获取下一个值。
回答by AnBisw
Try-
尝试-
EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_NAME START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 1000000 NOCYCLE NOCACHE ORDER';
This should be inside a Procedure or Function body and not in the Declaration section i.e. this should be treated as a executable statement.
这应该在过程或函数体中，而不是在声明部分，即这应该被视为可执行语句。
Creating a sequence using Dynamic SQL is a bad idea and I am not sure why you would want to do that. However, if you are creating a sequence dynamically then remember to drop it as well after finishing up, using
使用动态 SQL 创建序列是一个坏主意，我不确定您为什么要这样做。但是，如果您正在动态创建一个序列，那么请记住在完成后也将其删除，使用
EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_NAME';
This way you would atleast not run into errors (like ORA-00955) while calling the package procedure.
这样你至少不会ORA-00955在调用包过程时遇到错误（比如）。
