create or replace procedure WRITELOG(s_bmc in varchar2,s_jsbh in varchar2,s_bzbh in varchar2,s_czr varchar2,
s_ipdz in varchar2,s_jqm in varchar2,s_czms in varchar2,i_czlx in number)
is
--s_jlbh number(21);
s_sql varchar2(500);
i_jlbh number(21);
type rc is ref cursor;
v_rc rc;
REC RZJLPZB%ROWTYPE;
zdnr varchar(100);
zdgbqnr varchar(100);
begin
execute immediate 'select JLBH_AUTOID.NEXTVAL from dual' into i_jlbh;
--写操作日志
insert into sjczrzb (JLBH,BMC,JSBH,BZBH,CZLX,CZR,CZSJ,IPDZ,JQM,CZMS) values(i_jlbh,upper(s_bmc),s_jsbh,
s_bzbh,i_czlx,s_czr,sysdate,s_ipdz,s_jqm,s_czms);
if i_czlx=2 then
open v_rc for
select * from RZJLPZB where bmc=upper(s_bmc);
loop
FETCH v_rc into REC;
EXIT WHEN v_rc%NOTFOUND;
--rec.zdmc,rec.zdlx,rec.zjmc,rec.bmc;
s_sql:='select '||rec.zdmc||' as zd from '||rec.bmc||' where '||rec.zjmc||'='''||s_bzbh||'''';
--dbms_output.put_line(s_sql);
--字段修改后的内容
execute immediate s_sql into zdnr;
--字段修改后的内容
zdgbqnr:=getlogcontent(rec.bmc,rec.zdmc,s_bzbh);
if (zdnr is not null and zdgbqnr is not null and zdnr<>zdgbqnr) or (zdnr is null and zdgbqnr is not null) or (zdnr is not null and zdgbqnr is null) then
insert into ZDXXBGB (JLBH,BMC,ZDMC,GBQDNR,GBHDNR,ZJBH,CZR,CZSJC) values (i_jlbh,upper(s_bmc),rec.zdmc,substr(zdgbqnr,0,100),substr(zdnr,0,100),s_bzbh,s_czr,sysdate);
end if;
end loop;
close v_rc;
end if;
/*
select * from RZJLPZB where bmc=upper('ryb')
select xm from sjs_ryjbxxb where rybh='110000141200510010001'
*/
end WRITELOG;上面是我的源码,请各位大大帮忙解释一下,越详细越好。满意给分,谢谢!!!
s_ipdz in varchar2,s_jqm in varchar2,s_czms in varchar2,i_czlx in number)
is
--s_jlbh number(21);
s_sql varchar2(500);
i_jlbh number(21);
type rc is ref cursor;
v_rc rc;
REC RZJLPZB%ROWTYPE;
zdnr varchar(100);
zdgbqnr varchar(100);
begin
execute immediate 'select JLBH_AUTOID.NEXTVAL from dual' into i_jlbh;
--写操作日志
insert into sjczrzb (JLBH,BMC,JSBH,BZBH,CZLX,CZR,CZSJ,IPDZ,JQM,CZMS) values(i_jlbh,upper(s_bmc),s_jsbh,
s_bzbh,i_czlx,s_czr,sysdate,s_ipdz,s_jqm,s_czms);
if i_czlx=2 then
open v_rc for
select * from RZJLPZB where bmc=upper(s_bmc);
loop
FETCH v_rc into REC;
EXIT WHEN v_rc%NOTFOUND;
--rec.zdmc,rec.zdlx,rec.zjmc,rec.bmc;
s_sql:='select '||rec.zdmc||' as zd from '||rec.bmc||' where '||rec.zjmc||'='''||s_bzbh||'''';
--dbms_output.put_line(s_sql);
--字段修改后的内容
execute immediate s_sql into zdnr;
--字段修改后的内容
zdgbqnr:=getlogcontent(rec.bmc,rec.zdmc,s_bzbh);
if (zdnr is not null and zdgbqnr is not null and zdnr<>zdgbqnr) or (zdnr is null and zdgbqnr is not null) or (zdnr is not null and zdgbqnr is null) then
insert into ZDXXBGB (JLBH,BMC,ZDMC,GBQDNR,GBHDNR,ZJBH,CZR,CZSJC) values (i_jlbh,upper(s_bmc),rec.zdmc,substr(zdgbqnr,0,100),substr(zdnr,0,100),s_bzbh,s_czr,sysdate);
end if;
end loop;
close v_rc;
end if;
/*
select * from RZJLPZB where bmc=upper('ryb')
select xm from sjs_ryjbxxb where rybh='110000141200510010001'
*/
end WRITELOG;上面是我的源码,请各位大大帮忙解释一下,越详细越好。满意给分,谢谢!!!
主要使用了如下语法功能点:
a、动态SQL:execute immediate sql语句;
b、动态游标:open for sql语句;2、关于效率和习惯方面的
a、在不需要使用动态SQL,请尽量使用静态SQL。
b、能使用for游标循环的尽量使用。
c、字段不要使用拼单缩写。