Oracle 存储过程中查询序列值并用变量接收
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'
评论暂时关闭