存储过程：从中间用户COPY数据给正式用户

为保证数据安全，不对接口用户（中间表的用户，其实和正式表的用户是同一个数据库）开放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;