--创建环境
Create Table sination
(
Fcode varchar(4) default '',
Fdesc varchar(20) default ''
);

--添加数据
Insert into sination(Fcode,Fdesc) Values('C001','汉族');
Insert into sination(Fcode,Fdesc) Values('C002','回族');
Insert into sination(Fcode,Fdesc) Values('C003','维吾尔族');
commit;

--得到最后编号
Create or replace Function GetNewcode
return varchar
as
Fcodeint number;
Fcode sination.fcode%Type;
begin
  Fcode:='';
  Select max(substr(Fcode,2,4))+1 into Fcodeint from sination;
  if (99-Fcodeint)>=90  then
    Fcode:='C00'+to_char(Fcodeint);
  elsif (99-Fcodeint)>=0 then
    Fcode:='C0'+to_char(Fcodeint);
  else 
    Fcode:='C'+to_char(Fcodeint);
  end if;
  return Fcode;
end GetNewcode;

--测试
select GetNewcode from dual

--插入
create or replace procedure Insert_nation(Fdesc varchar) is
Ssql varchar(250);
begin
  Ssql :='insert into sination(Fcode,Fdesc) values (:1,:2)';
  execute immediate Ssql using GetNewcode,Fdesc;
  commit;
end Insert_nation;

--更新
create or replace procedure Update_nation(Fcode varchar,Fdesc varchar) is
Ssql varchar(250);
begin
  Ssql := 'update sination set Fdesc=:1 where Fcode=:2';
  execute immediate Ssql using Fdesc,Fcode;
  Commit;
end Update_nation;

--删除
create or replace procedure delete_nation(Fcode varchar) is
Ssql varchar(250);
begin
  Ssql := 'Delete from Sination where Fcode=:1';
  execute immediate Ssql using Fcode;
  commit;
end delete_nation;

使用exec call执行过程
例如: call Insert_nation('朝鲜族');