create or replace procedure pro_dml(p_type in varchar2, p_tabname in varchar2, p_colname in varchar2, p_value in varchar2, p_condition in varchar2 default null, p_errcode out number, p_errmsg out varchar2) authid current_user as begin if p_type = '1' then --insert execute immediate 'insert into ' || p_tabname || '(' || p_colname || ') values(' || p_value || ')'; elsif p_type = '2' then --update execute immediate 'update ' || p_tabname || ' set (' || p_colname || ')=(select ' || p_value || ' from dual)' || case when p_condition is not null then ' where ' || p_condition end; elsif p_type = '3' then --delete execute immediate 'delete from ' || p_tabname || case when p_condition is not null then ' where ' || p_condition end; end if; p_errcode:=0; p_errmsg:=''; exception when others then p_errcode := sqlcode; p_errmsg := sqlerrm; end;select * from c;C1 C2 A3 C4 0 3 8 2declare v1 number; v2 varchar2(30); begin pro_dml('1','c','a3,c4','5,5','', v1,v2); dbms_output.put_line(v1||' '||v2); end;select * from c;C1 C2 A3 C4 5 5 0 3 8 2这里举个例子 能实现简单的增删改操作。实际上大部分的操作会复杂得多 如果没有特殊需求,不要采用这种方式
但是insert,update这些也都是一个sql语句,既然都传一个sql语句,何不就直接执行了。
买一本书或者下载一个关于ORACLE SQL/PLSQL的资料学习一下吧。
还是那句话,磨刀不误砍柴工!!
p_tabname in varchar2,
p_colname in varchar2,
p_value in varchar2,
p_condition in varchar2 default null,
p_errcode out number,
p_errmsg out varchar2) authid current_user as
begin
if p_type = '1' then
--insert
execute immediate 'insert into ' || p_tabname || '(' || p_colname ||
') values(' || p_value || ')';
elsif p_type = '2' then
--update
execute immediate 'update ' || p_tabname || ' set (' || p_colname ||
')=(select ' || p_value || ' from dual)' || case when p_condition is not null then ' where ' || p_condition end;
elsif p_type = '3' then
--delete
execute immediate 'delete from ' || p_tabname || case when p_condition is not null then ' where ' || p_condition end;
end if;
p_errcode:=0;
p_errmsg:='';
exception
when others then
p_errcode := sqlcode;
p_errmsg := sqlerrm;
end;select * from c;C1 C2 A3 C4
0 3 8 2declare
v1 number;
v2 varchar2(30);
begin
pro_dml('1','c','a3,c4','5,5','', v1,v2);
dbms_output.put_line(v1||' '||v2);
end;select * from c;C1 C2 A3 C4
5 5
0 3 8 2这里举个例子
能实现简单的增删改操作。实际上大部分的操作会复杂得多
如果没有特殊需求,不要采用这种方式