Oracle 存储过程中查询序列值并用变量接收

1、创建一个序列

-- Create sequence

createsequence SEQ_TEST_JOB

minvalue 1

maxvalue 9999999999999999

start with 2

increment by 1

cache 20;

2、创建包头

CREATEORREPLACE PACKAGE PKG_TEST_JOB IS

-- Author : ADMINISTRATOR

-- Created : 2012-11-2 PM 11:35:40

-- Purpose : 测试JOB定时运行

FUNCTION F_TEST_JOB_SEQ RETURN NUMBER;

END PKG_TEST_JOB;

3、创建包体

CREATEORREPLACE PACKAGE BODY PKG_TEST_JOB IS

-- Author : ADMINISTRATOR

-- Created : 2012-11-2 PM 11:35:40

-- Purpose : 测试JOB定时运行

FUNCTION F_TEST_JOB_SEQ RETURN NUMBER IS

V_JOB_SEQ NUMBER(10);

BEGIN

SELECT HUIW.SEQ_TEST_JOB.NEXTVAL INTO V_JOB_SEQ FROM DUAL;

RETURN V_JOB_SEQ;

END F_TEST_JOB_SEQ;

END PKG_TEST_JOB;

注意红色字体：

SELECT HUIW.SEQ_TEST_JOB.NEXTVAL INTO V_JOB_SEQ FROM DUAL;

不能直接将序列值赋给变量，如：

V_JOB_SEQ :=HUIW.SEQ_TEST_JOB.NEXTVAL;

会产生错误：PLS-00357: 在此上下文中不允许表，视图或序列引用 'SEQ_TEST_JOB.NEXTVAL'

转载于:https://blog.51cto.com/cuug520/1430655