首先你需要建立一个序列:
CREATE SEQUENCE "CHY"."AUTOMARK" //chy为方案名,auto为序列名
INCREMENT BY 1
START WITH 1
MAXVALUE 100
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER
创建第二个序列
create sequence "chy"."atmk"
increment by 1
start with 201
maxvalue 300
minvalue 201
NOCYCLE
NOCACHE
NOORDER
然后创建存储过程:
CREATE OR REPLACE PROCEDURE "CHY"."CREATETABLE" (tbname in varchar2)
is
sqlstr varchar2(255);
sqlstr2 varchar2(255);
sqlstr3 varchar2(255);
temp number;
begin
sqlstr := 'create table '||tbname||' (TID number(5) primary key)';
execute immediate sqlstr;
sqlstr2 :='insert into '||tbname||' values(auto.nextval)';
execute immediate sqlstr2;
sqlstr3 :='select max(TID) from '||tbname;
execute immediate sqlstr3 into temp;
if temp=100 then
sqlstr2 :='insert into '||tbname||' values(atmk.nextval)';
execute immediate sqlstr2;
end if;
commit;
end;
搞定,接分。
CREATE SEQUENCE "CHY"."AUTOMARK" //chy为方案名,auto为序列名
INCREMENT BY 1
START WITH 1
MAXVALUE 100
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER
创建第二个序列
create sequence "chy"."atmk"
increment by 1
start with 201
maxvalue 300
minvalue 201
NOCYCLE
NOCACHE
NOORDER
然后创建存储过程:
CREATE OR REPLACE PROCEDURE "CHY"."CREATETABLE" (tbname in varchar2)
is
sqlstr varchar2(255);
sqlstr2 varchar2(255);
sqlstr3 varchar2(255);
temp number;
begin
sqlstr := 'create table '||tbname||' (TID number(5) primary key)';
execute immediate sqlstr;
sqlstr2 :='insert into '||tbname||' values(auto.nextval)';
execute immediate sqlstr2;
sqlstr3 :='select max(TID) from '||tbname;
execute immediate sqlstr3 into temp;
if temp=100 then
sqlstr2 :='insert into '||tbname||' values(atmk.nextval)';
execute immediate sqlstr2;
end if;
commit;
end;
搞定,接分。
我对存储过程不太了解.这语句是不是有问题啊?只能插一条记录吧