Oracle- 存储过程和异常捕捉,oracle存储过程抛出异常并继续执行

摘要：

我学习了ORACLE存储过程的基础知识，并做了一些备注以供将来参考。创建一个无参数存储过程createprocedure_myPro1isbegininsertintdepthvalues；终止修改无参数存储过程createorreplaceprocedure_myPro1开始插入深度值；终止创建和修改参数存储过程createorreplaceprocedure_myPro2isbeginupdatepsetsal=newSalwherename=spName；终止callp_myPro2；使用反向值createorreplacefunctionf_myPro3returnnumberisyearSalnumber（7,2）创建和修改参数化存储过程；开始选择*12年从emp开始，其中ename=spName；回归年Sal；终止调用方法：select_mypro3fromdual；查看错误消息showerror；调用存储过程有两种方法。Call和execexec是sqlplus的命令，只能在sqlplus中使用。调用是一个可以被任何工具使用的sql命令。例如：callp_MyPro1（）Oracle输出语句过程dbms_output是Oracle提供的一个包，其中包含一些进程_行是dbms_输出包的进程。

这段时间晚上有时候去打打球，回家看看电视剧，日子一天天过……。学了点ORACLE存储过程基础，作一下备注，以便日后需查阅。

创建无参存储过程

create procedure p_myPro1 is
begin
  insert into dept(deptno,dname,loc) values(60,'ccx','321321');
end;

修改无参存储过程

create or replace procedure p_myPro1 is
begin
  insert into dept(deptno,dname,loc) values(60,'ccx','321321'); 
end;

创建和修改有参存储过程

create or replace procedure p_myPro2(spName varchar2,newSal number) is
begin
    update emp set sal=newSal where ename=spName;
end;


call p_myPro2('SCOTT','1111');

创建和修改有参存储过程带反回值

create or replace function f_myPro3(spName varchar2)
return number isyearSal number(7,2);
begin
  select sal*12 into yearSal from emp where ename=spName;
  returnyearSal;
end;

调用方法：select f_mypro3('SCOTT') from dual;

查看错误信息
show error;

调用存储过程
调用存储过程的方式有两种，call,exec

exec是sqlplus的命令，只能在sqlplus中使用。和 set serveroutput on 一起用。call是sql命令，任何工具都可以使用。

例如：call p_myPro1()

Oracle输出语句过程

dbms_output是oracle所提供的包(类似java的开发包)，该包包含一些过程，put_line就是dbms_output包的一个过程。

Oracle变量的使用例子

declarev_ename varchar2(5);
v_sal number(7,2);
begin
  select ename,sal into v_ename,v_sal from emp where empno=7369;
  dbms_output.put_line('姓名'||v_ename||'工资'||v_sal);
end;

Oracle异常捕捉

declarev_ename varchar2(5);
v_sal number(7,2);
begin
  select ename,sal into v_ename,v_sal from emp where empno=1111;
  dbms_output.put_line('姓名'||v_ename||'工资'||v_sal);
  
  exception
  when no_data_found thendbms_output.put_line('SELECT INTO没有找到数据');
  when not_logged_on thendbms_output.put_line('没有连接到ORACLE');
  
end;

错误号	异常错误信息名称	说明
ORA-0001	Dup_val_on_index	违反了唯一性限制
ORA-0051	Timeout-on-resource	在等待资源时发生超时
ORA-0061	Transaction-backed-out	由于发生死锁事务被撤消
ORA-1001	Invalid-CURSOR	试图使用一个无效的游标
ORA-1012	Not-logged-on	没有连接到ORACLE
ORA-1017	Login-denied	无效的用户名/口令
ORA-1403	No_data_found	SELECTINTO没有找到数据
ORA-1422	Too_many_rows	SELECTINTO返回多行
ORA-1476	Zero-divide	试图被零除
ORA-1722	Invalid-NUMBER	转换一个数字失败
ORA-6500	Storage-error	内存不够引发的内部错误
ORA-6501	Program-error	内部错误
ORA-6502	Value-error	转换或截断错误
ORA-6504	Rowtype-mismatch	宿主游标变量与PL/SQL变量有不兼容行类型
ORA-6511	CURSOR-already-OPEN	试图打开一个已处于打开状态的游标
ORA-6530	Access-INTO-null	试图为null对象的属性赋值
ORA-6531	Collection-is-null	试图将Exists以外的集合(collection)方法应用于一个nullpl/sql表上或varray上
ORA-6532	Subscript-outside-limit	对嵌套或varray索引得引用超出声明范围以外
ORA-6533	Subscript-beyond-count	对嵌套或varray索引得引用大于集合中元素的个数.

对这种异常情况的处理，只需在PL/SQL块的异常处理部分，直接引用相应的异常情况名，并对其完成相应的异常错误处理即可。

看存储过程示例：

这里通过IM_INSTOCK_D0.LOCNO%TYPE 取得表的类型

is下面的存储过程的变量，()里面是存储过程的参数

create or replace procedureP_IM_INSTOCK_M_AUDIT_LINE
(
       as_LOCNO             IM_INSTOCK_D0.LOCNO%TYPE,                   --仓别
       as_OWNER_NO          IM_INSTOCK_D0.OWNER_NO%TYPE,                --委托业主
       as_SHEET_ID          IM_INSTOCK_D0.SHEET_ID%TYPE,                --上架单号
       as_SEQ_ID            IM_INSTOCK_D0.SEQ_ID%TYPE,                  --上架单内序号
       as_INSTOCK_WORKER    IM_INSTOCK_D0.UPDT_NAME%TYPE,               --上架人员
       as_TerminalFlag      container_content_move.terminal_flag%type,  --作业设备
       as_strMsg            out varchar2                                --错误信息返回
)
is

  ls_IMPORT_NO              IM_INSTOCK_D0.IMPORT_NO%TYPE;
  ls_CONTAINER_NO           IM_INSTOCK_D0.CONTAINER_NO%TYPE;
  ls_CELL_NO                IM_INSTOCK_D0.DEST_CELL_NO%type;   --源架储位
  ls_CELL_ID                IM_INSTOCK_D0.DEST_CELL_ID%type;   --源架储位ID
  ls_DEST_CELL_NO           IM_INSTOCK_D0.DEST_CELL_NO%type;   --预上架储位
  ls_DEST_CELL_ID           IM_INSTOCK_D0.DEST_CELL_ID%type;   --预上架储位ID
  ls_DEST_QTY               IM_INSTOCK_D0.DEST_QTY%type;       --预上架数量
  ls_REAL_CELL_NO           IM_INSTOCK_D0.REAL_CELL_NO%type;   --实际上架储位
  ls_REAL_CELL_ID           IM_INSTOCK_D0.REAL_CELL_ID%type;   --实际上架储位ID
  ls_REAL_QTY               IM_INSTOCK_D0.REAL_QTY%type;       --实际上架数量
  ls_Article_NO             IM_INSTOCK_D0.ARTICLE_NO%TYPE;
  li_Article_ID             IM_INSTOCK_D0.ARTICLE_ID%TYPE;
  ls_DepID                  IM_INSTOCK_M0.dept_id%type;
  ls_Lot_No                 IM_INSTOCK_D0.Lot_No%type;
  ls_errMsg                 varchar2(200);                     --错误信息
  li_RowCount               int;
  li_MixFlag                cm_defcell.mix_flag%TYPE;
  li_MixBatch               cm_defcell.mix_batch%TYPE;
  ls_CellStatus             cm_defcell.cell_status%TYPE;
  ls_CellCheckStatus        cm_defcell.check_status%TYPE;
  ld_ProduceDate            CONTAINER_ARTICLE_INFO.PRODUCE_DATE%TYPE;
  ls_o_type                 cm_defarea.o_type%type;

  ls_AREA_HABITUDE          CM_DEFAREA.AREA_HABITUDE%type;
  ls_BUFFER_FLAG            CM_DEFAREA.BUFFER_FLAG%type;

  ls_ERROR          exception;                         --异常

begin


   --锁定当前表的行
   update im_instock_d0  set status=status
   where LOCNO=as_LOCNO and OWNER_NO=as_OWNER_NO and SHEET_ID=as_SHEET_ID and SEQ_ID=as_SEQ_ID and status in ('10','11');

   IF SQL%ROWCOUNT=0 THENls_errMsg:='更新此单失败';
      raise ls_ERROR;
   END IF;



  selectIMD.CELL_NO,IMD.CELL_ID,DEST_CELL_NO,DEST_CELL_ID,DEST_QTY,REAL_CELL_NO,REAL_CELL_ID,REAL_QTY,IMD.CONTAINER_NO,
    IMD.ARTICLE_NO,IMD.ARTICLE_ID,IMD.DEPT_ID,IMD.LOT_NO,IMD.IMPORT_NO
    intols_CELL_NO,ls_CELL_ID, ls_DEST_CELL_NO,ls_DEST_CELL_ID,ls_DEST_QTY,ls_REAL_CELL_NO,ls_REAL_CELL_ID,ls_REAL_QTY,
    ls_CONTAINER_NO,ls_Article_NO,li_Article_ID,ls_DepID,ls_Lot_No,ls_IMPORT_NO
    fromIM_INSTOCK_D0 IMD,IM_INSTOCK_M0 IMM
   where IMD.LOCNO=IMM.LOCNO AND IMD.OWNER_NO=IMM.OWNER_NO ANDIMD.SHEET_ID=IMM.SHEET_ID AND IMD.LOCNO=as_LOCNO and IMD.OWNER_NO=as_OWNER_NO
     and IMD.SHEET_ID=as_SHEET_ID and IMD.SEQ_ID=as_SEQ_ID;

   IF ls_DEST_CELL_NO <> ls_REAL_CELL_NO THEN
      BEGIN
          selectMIX_FLAG,MIX_BATCH,CELL_STATUS,CHECK_STATUS
          intoli_MixFlag,li_MixBatch,ls_CellStatus,ls_CellCheckStatus
          fromcm_defcell
          where cell_no=ls_REAL_CELL_NO;
          EXCEPTION WHEN NO_DATA_FOUND THENls_errMsg   :=ls_REAL_CELL_NO||'储位不存在';
             raise ls_ERROR;
      END;
      if ls_CellStatus <> '0' thenls_errMsg   :=ls_REAL_CELL_NO||'储位不可用吗，无法上架';
          raise ls_ERROR;
      end if;
      if ls_CellCheckStatus <> '0' thenls_errMsg   :=ls_REAL_CELL_NO||'在盘点中，无法上架';
          raise ls_ERROR;
      end if;

      IF li_MixFlag = '0' THEN
         select count(1) intoli_RowCount
         fromcontainer_content t1
         where t1.locno=as_LOCNO and t1.cell_no =ls_REAL_CELL_NO
           and ARTICLE_NO <>ls_Article_NO;
         IF li_RowCount > 0 THENls_errMsg   :=ls_REAL_CELL_NO||'储位为不可混载储位，请重新指定储位上架';
             raise ls_ERROR;
         END IF;
      END IF;

      IF li_MixBatch = '0' THEN
         select produce_date intold_ProduceDate
         fromcontainer_article_info
         where article_id =li_Article_ID;

         select count(1) intoli_RowCount
         fromcontainer_content t1
         where t1.locno=as_LOCNO and t1.cell_no =ls_REAL_CELL_NO
           and ARTICLE_NO =ls_Article_NO
           and not exists(select 1 fromcontainer_article_info t2
                          where t1.article_id=t2.article_id and t2.produce_date =ld_ProduceDate);
         IF li_RowCount > 0 THENls_errMsg   :=ls_REAL_CELL_NO||'储位为不可混批储位，请重新指定储位上架';
             raise ls_ERROR;
         END IF;
      END IF;

   END IF;


  update cm_defcell set cell_status=cell_status where cell_no=ls_REAL_CELL_NO and cell_status=0
         and check_status=0;

  if SQL%ROWCOUNT=0 thenls_errMsg:=ls_REAL_CELL_NO||'储位不存在或不可用';
     raise ls_ERROR;
  end if;

  begin
      select t1.AREA_HABITUDE,t1.BUFFER_FLAG,t1.o_type intols_AREA_HABITUDE,ls_BUFFER_FLAG,ls_o_type
      fromCM_DEFAREA t1,CM_DEFCELL t2
       where t1.locno=t2.locno and t1.ware_no=t2.ware_no and t1.area_no=t2.area_no
         and t2.locno=as_locno and t2.cell_no=ls_REAL_CELL_NO;

      IF  ls_AREA_HABITUDE ='7' THENls_errMsg:=ls_REAL_CELL_NO||'所在储区为异常区，请核实';
         raise ls_ERROR;
      END IF;

      if  ls_BUFFER_FLAG<>'0' thenls_errMsg:=ls_REAL_CELL_NO||'不是作业区';
         raise ls_ERROR;
      end if;

      if (ls_AREA_HABITUDE<>'1' and ls_AREA_HABITUDE<>'20' and  ls_AREA_HABITUDE<>'6')  thenls_errMsg:=ls_REAL_CELL_NO||'不是良品区或限制出货区';
         raise ls_ERROR;
      end if;

  end;


   P_BS_StockChangeLog(as_LOCNO,as_OWNER_NO,as_SHEET_ID,'100',ls_CELL_NO,ls_CELL_ID,ls_REAL_CELL_NO,ls_REAL_CELL_ID,
        ls_DepID,ls_Lot_No,ls_Article_NO,li_Article_ID,ls_REAL_QTY,as_INSTOCK_WORKER,as_TerminalFlag);


    if ls_DEST_CELL_NO<>ls_REAL_CELL_NO then
        P_BS_StockBackRevServ(as_LOCNO,as_OWNER_NO,ls_CELL_NO,ls_CELL_ID,ls_DEST_CELL_NO,ls_DEST_CELL_ID,ls_DEST_QTY,0,ls_DepID,as_INSTOCK_WORKER,as_strMsg);
        if substr(as_strMsg,1,1)='N' thenls_errMsg:='修改库存失败'|| SUBSTR(as_strMsg,3);
           raise ls_ERROR;
        end if;

        P_BS_StockChangePal(as_LOCNO,as_OWNER_NO,ls_CELL_NO,ls_CELL_ID, ls_REAL_CELL_NO,-1,ls_CONTAINER_NO,ls_DepID, ls_REAL_QTY,as_INSTOCK_WORKER,0,ls_REAL_CELL_ID,as_strMsg );

        if substr(as_strMsg,1,1)='N' thenls_errMsg:='转移库存失败'|| SUBSTR(as_strMsg,3);
           raise ls_ERROR;
        end if;

        update im_instock_d0 d set d.status='13',d.real_cell_id=ls_REAL_CELL_ID,d.Updt_Name=as_INSTOCK_WORKER,
           d.Updt_Date=sysdate where LOCNO=as_LOCNO and OWNER_NO=as_OWNER_NO
           and SHEET_ID=as_SHEET_ID and SEQ_ID=as_SEQ_ID;


    else
        P_BS_StockBackRevServ(as_LOCNO,as_OWNER_NO,ls_CELL_NO,ls_CELL_ID,ls_DEST_CELL_NO,ls_DEST_CELL_ID,ls_DEST_QTY,ls_REAL_QTY,ls_DepID,as_INSTOCK_WORKER,as_strMsg);

        if substr(as_strMsg,1,1)='N' thenls_errMsg:='修改库存失败' || SUBSTR(as_strMsg,3);
           raise ls_ERROR;
        end if;

        IF as_TerminalFlag=1 THEN   ---表单
            update im_instock_d0 d set d.status='13',d.real_cell_id=d.dest_cell_id
             where LOCNO=as_LOCNO and OWNER_NO=as_OWNER_NO
               and SHEET_ID=as_SHEET_ID and SEQ_ID=as_SEQ_ID;
        ELSE
            update im_instock_d0 d set d.status='13',d.real_cell_id=d.dest_cell_id,
               d.Updt_Name=as_INSTOCK_WORKER,  d.Updt_Date=sysdate
                where LOCNO=as_LOCNO and OWNER_NO=as_OWNER_NO
                 and SHEET_ID=as_SHEET_ID and SEQ_ID=as_SEQ_ID;
        END IF;
    end if;

    P_BS_StockChangeListLog(as_LocNo,as_OWNER_NO,ls_DepID,ls_Article_NO,li_Article_ID,ls_Lot_No,ls_REAL_QTY,'100','N');
/*P_CONTENTER_LOTNO(as_LOCNO,as_OWNER_NO,'100',ls_IMPORT_NO,ls_Article_NO,li_Article_ID,'N',0,ls_Lot_No,ls_REAL_QTY,0,as_INSTOCK_WORKER,as_strMsg);
    if substr(as_strMsg,1,1)='N' then
       raise ls_ERROR;
    end if;
*/
    update bm_article_pcs bm set bm.recent_cell=ls_REAL_CELL_NO,bm.recent_pickcell=
    case when ls_o_type='P' then bm.recent_pickcell else ls_REAL_CELL_NO end where  bm.OWNER_NO=as_OWNER_NO and bm.article_no=ls_Article_NO;

   ----更新标签表的状态
     UPDATE CONTAINER_LABEL_M0 SET STATUS='13' WHERE CONTAINER_NO=ls_CONTAINER_NO  AND NOT EXISTS(SELECT 1 FROM im_instock_d0 WHERE CONTAINER_NO=ls_CONTAINER_NO AND STATUS<'13');

     IF SQL%ROWCOUNT<>0 THEN
        P_TRANS_CONTAINER_LABEL(as_locno,as_owner_no,ls_CONTAINER_NO,ls_errMsg);

         if substr(ls_errMsg,1,1)<>'Y' thenls_errMsg:=SUBSTR(ls_errMsg,3);
               raise ls_ERROR;
         end if;

     END IF;


      --导入头档
     insert intoIM_INSTOCK_M(LOCNO, OWNER_NO, SHEET_ID, SHEET_TYPE,SHEET_DATE,DEPT_ID, AUTO_LOCATE_FLAG, ASSIGN_WORKER, ASSIGN_DATE,
                              REAL_WORKER, OPERATE_TYPE, STATUS, PRINT_TIMES, PRT_MEMO, BILL_MEMO,
                              AC_NO, RGST_NAME, RGST_DATE, UPDT_NAME, UPDT_DATE)
          selectLOCNO, OWNER_NO, SHEET_ID, SHEET_TYPE, SHEET_DATE, DEPT_ID, AUTO_LOCATE_FLAG, ASSIGN_WORKER, ASSIGN_DATE,
                 REAL_WORKER, OPERATE_TYPE,'13' asSTATUS, PRINT_TIMES, PRT_MEMO, BILL_MEMO,
                 SEQ_EOSBILL.NEXTVAL,RGST_NAME, RGST_DATE, UPDT_NAME, UPDT_DATE
            fromIM_INSTOCK_M0
           where LOCNO=as_LOCNO and OWNER_NO=as_OWNER_NO and SHEET_ID=as_SHEET_ID
             and not exists(select 'X' fromIM_INSTOCK_d0
                            where LOCNO=as_LOCNO and OWNER_NO=as_OWNER_NO and SHEET_ID=as_SHEET_ID
                              and status<>'13');
     if SQL%ROWCOUNT<>0 then

        delete from IM_INSTOCK_M0 where LOCNO=as_LOCNO and OWNER_NO=as_OWNER_NO and SHEET_ID=as_SHEET_ID;

        --导入明细表
        insert intoIM_INSTOCK_D(LOCNO, OWNER_NO, SHEET_ID, SEQ_ID, CELL_NO, CELL_ID, CONTAINER_NO,
                              ARTICLE_NO, ARTICLE_ID, LOT_NO, SUPPLIER_NO,DEST_CELL_NO,DEST_CELL_ID, DEST_CONTAINER_NO,DEST_QTY,
                              REAL_CELL_NO, REAL_CELL_ID, REAL_CONTAINER_NO, REAL_QTY, IMPORT_NO, IMPORTSUM_NO, CHECK_NO,
                              WORKLOAD_P, WORKLOAD_C, WORKLOAD_B, STATUS,ROW_FLAG,LABEL_NO,DEPT_ID,UPDT_NAME,UPDT_DATE)
          selectLOCNO, OWNER_NO, SHEET_ID, SEQ_ID, CELL_NO, CELL_ID, CONTAINER_NO, ARTICLE_NO,
                 ARTICLE_ID, LOT_NO,SUPPLIER_NO, DEST_CELL_NO,DEST_CELL_ID, DEST_CONTAINER_NO,DEST_QTY, REAL_CELL_NO, REAL_CELL_ID,
                 REAL_CONTAINER_NO, REAL_QTY, IMPORT_NO, IMPORTSUM_NO, CHECK_NO, WORKLOAD_P, WORKLOAD_C, WORKLOAD_B,
                 '13' asSTATUS,ROW_FLAG,LABEL_NO,DEPT_ID,UPDT_NAME,UPDT_DATE
            fromIM_INSTOCK_D0
           where LOCNO=as_LOCNO and OWNER_NO=as_OWNER_NO and   SHEET_ID=as_SHEET_ID ;
     if SQL%ROWCOUNT<>0 then
        delete from IM_INSTOCK_D0 where LOCNO=as_LOCNO and OWNER_NO=as_OWNER_NO and SHEET_ID=as_SHEET_ID;
     end if;
   end if;

   as_strMsg:='Y|成功';
--异常处理
exception
   WHEN ls_ERROR THENas_strMsg:='N|'||ls_errMsg;
   when others thenas_strMsg:='N|' || to_char(SQLCODE) || '      ' || SUBSTR(SQLERRM, 1, 100);
end P_IM_INSTOCK_M_AUDIT_LINE;

Oracle- 存储过程和异常捕捉

相关文章

一次library cache pin故障的解决过程

Oracle数据库的函数，存储过程，程序包，游标，触发器

MySQL 日期与时间的处理

设计模式之外观模式（门面模式）以及如何利用到接口设计中

.net 中dapper实现事务的三种方式总结

DB2时间函数大全（转）

最新文章

随机推荐

思享工具箱导航

JSON工具

格式化转换

加解密编码

文本数字

网络

站长

计算

其他

对照列表