Oracle数据库使用小技巧

oracle查询某个字段无查询结果时返回0

场景:
根据khbh查询数据库中的记录，如果无查询结果集，需要返回0给前端。如果使用mybatis查询无结果集会默认返回null给前端，这里可以使用业务代码返回，也可以使用sql操作

查询数据库中不存在的数据返回的结果集为：

解决

select nvl(sum(t.ea_message_remind),0) ea_message_remind,nvl(sum(t.eo_message_remind),0) eo_message_remind from t_kh_jlcsz t where khbh = '17';

判null处理

-- varchar2 类型
if v_fwpj is not null and v_fwpj != '满意' then
    
end If;

-- int 类型
if v_zxthsc > 0 then
  nb_sql := nb_sql || ' and  h.thsc >=  '||v_zxthsc;
end if;

游标

游标是指向此上下文区域的指针。PL/SQL通过游标控制上下文区域，游标保存SQL语句返回的行(一个或多个)。 游标所在的行集称为活动集。
可以命名一个游标，以便在程序中引用它来获取和处理SQL语句返回的行，一次处理一个(行)。PL/SQL中有两种类型的游标

隐式游标

当执行SQL语句时，如果语句没有显式游标，则Oracle会自动创建隐式游标。程序员无法控制隐式游标及其信息。

每当发出DML语句(INSERT，UPDATE和DELETE)时，隐式游标与此语句相关联。 对于INSERT操作，游标保存需要插入的数据。对于UPDATE和DELETE操作，游标标识将受到影响的行。

在PL/SQL中，可以将最近的隐式游标引用为SQL游标，它始终具有%FOUND，%ISOPEN，%NOTFOUND和%ROWCOUNT等属性。 SQL游标具有额外的属性%BULK_ROWCOUNT和%BULK_EXCEPTIONS，旨在与FORALL语句一起使用。下表提供了游标中最常用属性的描述 -

属性	描述
%FOUND	如果INSERT，UPDATE或DELETE语句影响一行或多行，或老兄SELECT INTO语句返回一行或多行，则返回TRUE，否则返回FALSE。
%NOTFOUND	与%FOUND的逻辑相反。 如果INSERT，UPDATE或DELETE语句没有影响任何行，或SELECT INTO语句未返回任何行，则返回TRUE。 否则返回FALSE。
%ISOPEN	由于Oracle在执行关联的SQL语句后会自动关闭SQL游标，因此总是为隐式游标返回FALSE。
%ROWCOUNT	返回受INSERT，UPDATE或DELETE语句，或者受SELECT INTO语句影响的行数
任何SQL游标属性将被访问为sql％属性

显式游标

显式游标是用于获得对上下文区域的更多控制的程序员定义的游标。应在PL/SQL块的声明部分中定义一个显式游标。它是在一个返回多行的SELECT语句中创建的。

使用显式游标包括以下步骤
1、声明游标初始化内存 (cursor v_cursor is select * from aaa;)
2、打开游标分配内存 (open v_cursor; )
3、从游标获取数据 ( fetch v_cursor into v_row;)
4、关闭游标以释放分配的内存 (close v_cursor;)

declare 
  cursor v_cursor is select * from aaa; --1.声明游标
  v_row aaa%rowtype; --2. 声明变量v_row,变量类型为aaa表的一行
begin
  open v_cursor;  --3.打开游标
  loop --4、使用 loop 循环遍历游标
    fetch v_cursor into v_row;   --5、遍历游标 v_cursor 的每一行结果给(into)变量v_row
    exit when v_cursor%notfound;  --6、当游标 v_cursor 没有再读取到值时，则退出循环 
    --dbms_output.put_line()：plsql 的输出语句
    dbms_output.put_line('ywlxmc'||v_row.ywlxmc ||' thcs: ' ||v_row.thcs||' ywlxcj: ' ||v_row.ywlxcj ||' thszb: ' ||v_row.thszb );
  end loop;
  close v_cursor;
end;

declare
   cursor v_cursor(v_thcs number) is select * from aaa where thcs = v_thcs;
   v_row aaa%rowtype; 
begin
   open v_cursor(55);
   loop
     fetch v_cursor into v_row;
     if v_cursor%notfound then
       exit;
     end if;
     dbms_output.put_line('证券：'|| v_row.ywlxmc  || ' thcs：'|| v_row.thcs);
   end loop;
   close v_cursor;
end;

使用系统游标显示定义

declare 
   v_sys_cursor sys_refcursor;
   v_row aaa%rowtype;
begin
   open v_sys_cursor for select * from aaa;
   loop 
     fetch v_sys_cursor into v_row;
     exit when v_sys_cursor%notfound;
     dbms_output.put_line('证券：'|| v_row.ywlxmc  || ' thcs：'|| v_row.thcs);
   end loop;
   close v_sys_cursor;
end;

使用for遍历游标，可以自动关闭游标

declare
  cursor v_cursor is select * from aaa;
begin
  for v_row in v_cursor loop
    dbms_output.put_line('证券：'|| v_row.ywlxmc  || ' thcs：'|| v_row.thcs);
  end loop;
end;

异常

异常处理函数用于取得Oracle错误号和错误信息，其中：

• 函数SQLCODE用于取得错误号
• SQLERRM用于取得错误信息。

RAISE_APPLICATION_ERROR该函数用于在PL/SQL子程序中自定义错误信息。
语法格式：raise_application_error(error_number,message)
1、error_number：用于定义错误号(-20000-20999)
2、message：用于指定错误信息，长度不能超过2048个字节

优雅抛出异常消息

-- 根据客户编号查询记录，如果存在及修改，如果不存在及新增
create or replace procedure up_mid_99400001(
  v_khbh           varchar2,       --  客户编号
  v_ea_message_remind        varchar2,       -- 是否开启消息提醒(默认不提醒0/1提醒)
  v_eo_message_remind         varchar2,       -- 消息提醒是否自动关闭(默认自动关闭0/1开启)
  rc1             out           jz_oraoledb.m_refcur,
  rc2             out           jz_oraoledb.m_refcur
)
as
  -- v_result sys_refcursor;
  nb_errormsg varchar2(200);
  v_result varchar2(40);
begin
    select count(*) into v_result from t_kh_jlcsz  where khbh = v_khbh;
    if v_result=0 then
       insert into t_kh_jlcsz(khbh,ea_message_remind,eo_message_remind)
       values(v_khbh,v_ea_message_remind,v_eo_message_remind);
       commit;
       open rc1 for select  0 as errorcode, '新增成功！' as errormsg from dual;
       open rc2 for select  0 as errorcode, '新增成功！' as errormsg from dual;
    else
      update t_kh_jlcsz set
            ea_message_remind =  v_ea_message_remind,
            eo_message_remind = v_eo_message_remind where khbh = v_khbh;
            commit;
      open rc1 for select  0 as errorcode, '修改成功！' as errormsg from dual;
      open rc2 for select  0 as errorcode, '修改成功！' as errormsg from dual;
    end if;

exception when others then
  -- SQLERRM用于取得错误信息。
  nb_errormsg := substr(sqlerrm,1,200);
  -- 函数SQLCODE用于取得错误号
  open rc1 for select  -123456 as errorcode, '业务操作异常！'||nb_errormsg as errormsg from dual;
  open rc2 for select  -123456 as errorcode, '业务操作异常！'||nb_errormsg as errormsg from dual;
end up_mid_99400001;

自定义异常

1、第一方式自定义异常

declare 
   cursor v_cursor is select * from aaa ;
   number_exception exception;
begin
   for v_row in v_cursor loop
       exit when v_cursor%notfound;
       case v_row.thcs
         when 203 then dbms_output.put_line('203');    
         when 563 then dbms_output.put_line('563');  
         when 632 then dbms_output.put_line('632');
         when 55 then raise number_exception;
       end case;
   end loop;    
exception
  when number_exception then dbms_output.put_line('数据出错了');
end;

2、第二种方式创建自定义异常

-- 创建存储过程
create or replace procedure change_sal(eno number,salary number)
is 
begin
  update emp set sal = salary where empno=eno;
  if sql%notfound then
    raise_application_error(-20002,'该员工不存在！');
  else
    dbms_output.put_line('更新成功!');
    commit;
  end if;
end;
 
-- 执行存储过程
begin
  change_sal(8888,800);
end;