Oracle--存储过程创建、执行、排错

1、创建存储过程

create or replace procedure p_desk_info(v_desk_name IN varchar2,v_desk_type IN varchar2,v_desk_no IN varchar2,v_org_name IN varchar2) is 
BEGIN
       declare   
          org_id number;
          desk_type_id  number;
          desk_num number;
          global_ex exception;
       BEGIN
         BEGIN
          select id into desk_type_id from t_base_desk_type where deleted='N' and desk_type=v_desk_type;
          EXCEPTION
            WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.put_line('错误:请检查台席类型【'||v_desk_type||'】是否存在,并重新导入该台席信息');
         END;
         BEGIN
             select id into org_id from t_sys_organization where deleted='N' and TYPE=8 and ORG_NAME=v_org_name;
             EXCEPTION
             WHEN NO_DATA_FOUND THEN
             DBMS_OUTPUT.put_line('错误:请检查使用单位【'||v_org_name||'】是否存在,并重新导入该台席信息');
         END;
         --台席不存在或使用单位不存在抛出异常
         if desk_type_id is null or org_id is null then
          raise global_ex;
         end if;
         select count(*) into desk_num from t_base_desk_info t where t.deleted='N' and t.name=v_desk_name and t.type_id=desk_type_id and t.serial_number=v_desk_no and t.org_id=org_id;
         --已存在台席信息抛出异常
         if desk_num is not null  then
          DBMS_OUTPUT.put_line('错误:请台席【'||v_desk_name||'】信息已存在');
          raise global_ex;
         end if;
         insert into t_base_desk_info (id,NAME,type_id,serial_number,org_id,deleted,create_user_name,create_user_id,create_date) 
                       VALUES (seq_t_base_desk_info.nextval, v_desk_name,desk_type_id,v_desk_no,org_id,'N','小明',1,sysdate);  
         
         commit;
       EXCEPTION
         WHEN OTHERS THEN
             DBMS_OUTPUT.put_line('错误：新增台席【'||v_desk_name||'】信息失败');
       END;
END p_desk_info;
/

2、排错命令

show errors

3、查看执行结果

set serveroutput on;
exec p_desk_info('台席','高柜','09','支局');