create or replace procedure p_createseq(tablename in varchar2,key in varchar2)

Authid Current_User --使用角色权限，否则无法创建序列或表

is

strsql varchar2(500);

t_key  varchar2(500);

begin

--创建序列

strsql:='create sequence seq_'||tablename||

' minvalue 1 maxvalue 99999999 start with 1 increment by 1 nocache';

execute immediate strsql;

--设置触发器默认主键名

if(key is NULL) then t_key := 'ID' ;

else t_key := key;

end if;

--创建触发器

strsql := 'CREATE OR REPLACE TRIGGER TRG_'||tablename||'

BEFORE INSERT ON '||tablename ||'

FOR EACH ROW

BEGIN

SELECT SEQ_'||tablename||'.NEXTVAL INTO :NEW.'||t_key||' FROM DUAL;

END; ';

execute immediate strsql;

end p_createseq;

conn / as sysdba;

create tablespace jinwin;

create user jwst identified by jwst default tablespace jinwin temporary tablespace temp;

grant dba to jwst;

conn jwst/jwst;

select * from ( select row_number() over (partition by sqbm  order by  SERIALNO_  ) RN from table) t where rn <