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