序列程序如下:
create sequence SEQ_MID
start with 1
increment by 1
minvalue 1
nomaxvalue
nocache
插入并获取刚插入的序列函数如下:
create or replace function message_fun
(m in message_package.message_record
)
return number
as
num varchar2(10);
begin
num := 'MSG'||SEQ_MID.NEXTVAL;
insert into message
values
(num ,m.userid,m.ustateid,m.mtype,m.stime,m.etime,m.specification,
m.linkman,m.phone,m.photo,m.video,m.music,m.qq,m.msn);
return num;
end;
函数报错:在此上下文种不允许表,视图或序列引用“SEQ_MID.NEXTVAL”;
个人分析了一下,不知为什么这个SEQ_MID.NEXTVAL只能出现在insert语句中,求解!
create sequence SEQ_MID
start with 1
increment by 1
minvalue 1
nomaxvalue
nocache
插入并获取刚插入的序列函数如下:
create or replace function message_fun
(m in message_package.message_record
)
return number
as
num varchar2(10);
begin
num := 'MSG'||SEQ_MID.NEXTVAL;
insert into message
values
(num ,m.userid,m.ustateid,m.mtype,m.stime,m.etime,m.specification,
m.linkman,m.phone,m.photo,m.video,m.music,m.qq,m.msn);
return num;
end;
函数报错:在此上下文种不允许表,视图或序列引用“SEQ_MID.NEXTVAL”;
个人分析了一下,不知为什么这个SEQ_MID.NEXTVAL只能出现在insert语句中,求解!
CREATE OR REPLACE FUNCTION MESSAGE_FUN(M IN MESSAGE_PACKAGE.MESSAGE_RECORD)
RETURN NUMBER AS
NUM VARCHAR2(10);
BEGIN
--NUM := 'MSG' || SEQ_MID.NEXTVAL; 向下面这样写,或者直接写在insert 中
SELECT 'MSG' || SEQ_MID.NEXTVAL
INTO NUM
FROM DUAL; INSERT INTO MESSAGE
VALUES
(NUM, --'MSG' || SEQ_MID.NEXTVAL, 这么写也行
M.USERID,
M.USTATEID,
M.MTYPE,
M.STIME,
M.ETIME,
M.SPECIFICATION,
M.LINKMAN,
M.PHONE,
M.PHOTO,
M.VIDEO,
M.MUSIC,
M.QQ,
M.MSN);
RETURN NUM;
END;
INTO NUM
FROM DUAL; 这样写。
11g 之前使用 select 'MSG'||SEQ_MID.NEXTVAL into v_seq from dual;
我一般是用触发器,在before insert中处理。
select SEQ_MID.nextval into :new.NUM from dual;
11g 之前使用 select 'MSG'||SEQ_MID.NEXTVAL into v_seq from dual;