为保证数据安全,不对接口用户(中间表的用户,其实和正式表的用户是同一个数据库)开放select和update权限,而是创建一个copy数据的存储过程(在正式用户)并且赋予execute权限给中间用户,然后在中间用户创建同义词以提供调用。
1、创建存储过程
--p_cflsh0入参:处方流水号,(errMsg返回:错误信息)
create or replace procedure copyMzcfAndmzcfmx(p_cflsh0 in varchar2,errMsg out varchar2) is
begin
--同时copy门诊处方和处方明细
declare
cursor cur_mzcf is select * from v_mzcf00 a where a.cflsh0=p_cflsh0;
cursor cur_mzcfmx is select * from v_mzcfmx b where b.cflsh0=p_cflsh0;
row v_mzcf00%rowtype;--记录型变量
zyts number;--中药贴数
ypdlbh char;--药品大类编号
pszt char;--皮试状态
v_cflsh00 varchar2(10);--保存处方流水号(cf表的id,并且关联mx表)的临时变量
mx_row v_mzcfmx%rowtype;
cxbz char;--冲销标志
ypzsl number;--药品总数量(处方单位)(乘以贴数后)
begin
open cur_mzcf;
loop
fetch cur_mzcf into row;
exit when cur_mzcf%notfound;
---中间表没有这些约束:ZYTS00 >= 1 AND YPDLBH IN ('0','1','2','4','9') AND PSZT00 IN ('0','1','2','3'),数据不同步,所以要处理下
begin
if row.ZYTS00<1 then
zyts:=1;
else
zyts:=row.ZYTS00;
end if;
if row.YPDLBH IN('0','1','2','4','9') then
ypdlbh:=row.YPDLBH;
else
ypdlbh:='0';
end if;
if row.PSZT00 IN('0','1','2','3') then
pszt:=row.PSZT00;
else
pszt:='0';
end if;
end;
begin
select sq_yf_mzcf00_cflsh0.nextval into v_cflsh00 from dual;--生成新id,用以cf表和mx表
dbms_output.put_line(v_cflsh00);
insert into SD_YL.yf_mzcf00(
YYID00,CFLSH0,
YFBMBH,GHH000,
BRID00,BRXM00,
XB0000,CSRQ00,
DJH000,YSGZH0,
YSXM00,SSKS00,
GHKS00,ZYTS00,
YPDLBH,CFXZ00,
BZID00,BZMC00,
CFZT00,FJFDJH,
CZY000,CZYXM0,
FHR000,FYR000,
SRRQ00,SRSJ00,
JZRQ00,JZSJ00,
FYRQ00,FYSJ00,
ZFRQ00,ZFSJ00,
CFZJE0,BZ0000,
BCCFH0,PSZT00,
CFSRBZ,TSBZBH,
CFDGS0,SFZYDJ,
DJFDJH,PSFDJH )
values(
row.yyid00,v_cflsh00,
row.YFBMBH,row.GHH000,
row.BRID00,row.BRXM00,
row.XB0000,row.CSRQ00,
row.DJH000,row.YSGZH0,
row.YSXM00,row.SSKS00,
row.GHKS00,
zyts,
ypdlbh,
row.CFXZ00,
row.BZID00,row.BZMC00,
row.CFZT00,row.FJFDJH,
row.CZY000,row.CZYXM0,
row.FHR000,row.FYR000,
row.SRRQ00,row.SRSJ00,
row.JZRQ00,row.JZSJ00,
row.FYRQ00,row.FYSJ00,
row.ZFRQ00,row.ZFSJ00,
row.CFZJE0,row.BZ0000,
row.BCCFH0,
pszt,
row.CFSRBZ,row.TSBZBH,
row.CFDGS0,row.SFZYDJ,
row.DJFDJH,row.PSFDJH
);
EXCEPTION--异常处理,注意的是它必须和语句在一个begin-end中
when others then
errMsg:=substr(sqlerrm,1,100);
ROLLBACK;
end;
end loop;
close cur_mzcf;
begin
open cur_mzcfmx;
loop
fetch cur_mzcfmx into mx_row;
exit when cur_mzcfmx%notfound;
--约束:CXBZ00 IN ('Z','-','+','*') AND YPZSL0 <> 0
begin
if mx_row.CXBZ00 IN ('Z','-','+','*') then
cxbz:=mx_row.CXBZ00;
else
cxbz:='Z';
end if;
if mx_row.YPZSL0<>0 then
ypzsl:=mx_row.YPZSL0;
else
ypzsl:=1;
end if;
end;
begin
insert into SD_YL.yf_MZCFMX(
CFID00,CFLSH0,
YPNM00,YPMC00,
YPGG00,GJDJ00,
LSDJ00,YFID00,
YPYFMC,PCID00,
PCMC00,CSL000,
MZKFDW,F2J000,
YYSJ00,YYSJDW,
ZB0000,XMYFID,
XMYFMC,YYZF00,
YYZFID,YPZSL0,
CFDW00,ZHL000,
CXBZ00,CXCFID,
YPSCCJ,YPSCPH,
BZ0000,ZQCS00,
ZQCD00,ZFBL00,
YBXMLB,PSLBID,
YPDLBH,ZXCS00,
ZFJE00,ZCXSL0,
BCCXSL,HJJE00,
HSZQCD,YPPC00,
SCPH00
)
values(
sq_yf_mzcfmx_cfid00.nextval,v_cflsh00,
mx_row.YPNM00,mx_row.YPMC00,
mx_row.YPGG00,mx_row.GJDJ00,
mx_row.LSDJ00,mx_row.YFID00,
mx_row.YPYFMC,mx_row.PCID00,
mx_row.PCMC00,mx_row.CSL000,
mx_row.MZKFDW,mx_row.F2J000,
mx_row.YYSJ00,mx_row.YYSJDW,
mx_row.ZB0000,mx_row.XMYFID,
mx_row.XMYFMC,mx_row.YYZF00,
mx_row.YYZFID,
ypzsl,
mx_row.CFDW00,mx_row.ZHL000,
cxbz,
mx_row.CXCFID,
mx_row.YPSCCJ,mx_row.YPSCPH,
mx_row.BZ0000,mx_row.ZQCS00,
mx_row.ZQCD00,mx_row.ZFBL00,
mx_row.YBXMLB,mx_row.PSLBID,
mx_row.YPDLBH,mx_row.ZXCS00,
mx_row.ZFJE00,mx_row.ZCXSL0,
mx_row.BCCXSL,mx_row.HJJE00,
mx_row.HSZQCD,mx_row.YPPC00,
mx_row.SCPH00
);
EXCEPTION
when others then
errMsg:=substr(sqlerrm,1,100);
ROLLBACK;
end;
end loop;
begin--判断若没有异常发生过,就提交并返回succeed
if errMsg is null or errMsg='' then
errMsg:='succeed';
commit;
end if;
end;
close cur_mzcfmx;
end;
end;
end copyMzcfAndmzcfmx;
2、给中间用户赋予可以执行存储过程的execute权限
grant execute on 表名 to 用户名;
《如果不行,可以使用plsql在user列表中赋予该用户权限》
3、中间表创建同义词
CREATE OR REPLACE [PUBLIC] SYSNONYM [当前用户.]synonym_name FOR [其他用户.]object_name;