​​PL/SQL中简单的增删改procedure​​


--创建环境

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('朝鲜族');