Hand-Aurora-数据库常用设计

程序包

带有表级锁的包创建

首先在包开头处声明要抛出的类型.
随后调用pragma exception_init()方法,为异常为分配错误代码
在执行select语句中使用for update nowait,进行锁表
当有自定义锁表异常出现时,进行异常处理

PRAGMA EXCEPTION_INIT(e_lock_table, -54);                                     -- 初始化锁表的异常                                                                    

  FUNCTION get_contract_rec(p_contract_id NUMBER,
                            p_user_id     NUMBER) RETURN con_contract%ROWTYPE IS
    v_con_contract_rec con_contract%ROWTYPE;
  BEGIN
    SELECT *
      INTO v_con_contract_rec
      FROM con_contract t
     WHERE t.contract_id = p_contract_id
       FOR UPDATE NOWAIT;                                                      -- 进行操作的锁表                                                                      
    RETURN v_con_contract_rec;
  EXCEPTION
    WHEN no_data_found THEN                                                   -- 当into语句没有实现,则不做处理                                                                    
      RETURN NULL;
    WHEN e_lock_table THEN                                                     -- 发生锁表时                                                                                       
      sys_raise_app_error_pkg.raise_user_define_error(p_message_code            => 'CON_CONTRACT_PKG.CONTRACT_LOCK_ERROR',
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => 'con_contract_pkg',
                                                      p_procedure_function_name => 'get_contract_rec');
      raise_application_error(sys_raise_app_error_pkg.c_error_number,
                              sys_raise_app_error_pkg.g_err_line_id);
  END;

异常处理

no_data_found异常处理

这里使用begin…end进行包围,然后在异常处理中做no_data_found捕获异常,不做处理
注意,在异常抛出前的操作依然会写入数据库中,因此需要将所有的select…into…写在操作前面.

  begin
      select * into XXXX from emp;
      update emp set XXXX;      
  exception
      when no_data_found then
        null;  
  end ;

兜底异常处理

  EXCEPTION
    WHEN OTHERS THEN
       sys_raise_app_error_pkg.raise_sys_others_error(p_message                  => dbms_utility.format_error_backtrace || '' ||
                                                                                   SQLERRM,
                                                      p_created_by              => p_user_id,
                                                      p_package_name            => g_package_name,
                                                      p_procedure_function_name => 'submit_allocation_record');
       raise_application_error(sys_raise_app_error_pkg.c_error_number,
                               sys_raise_app_error_pkg.g_err_line_id);

JSON处理

Oralce11g 扩展对JSON字符串的支持…

-- 测试JSON数据结构
declare
  /**
       JSON-  单个JSON对象
          |-  json_value_array 值列表
                |-json_value   值
       JSON_LIST 多个JSON对象列表
       JSON_EXT 工具包   
  **/
  obj      json; -- json对象
  obj_list json_list; -- json对象列表  
begin
  obj := json('{
         "name":"Jion",
         "age" : 23
      }');

  --===========
  --  JSON对象
  --===========
  -- 添加对象属性
  obj.put('id', '41060319940412XXXX');
  -- 存放新的
  obj.put('Address',  json_list('["ShangHai","HeNan",true]'));
  -- json对象相互嵌套  
  obj.put('others',
          json('
               {"phone":15516559772,
               "eamil":"1434501783@qq.com"}
               '));
  -- 日期支持,存入的为数据库日期对象
  obj.put('birthday',
          json_ext.to_json_value(to_date('1994-04-12',  'yyyy-MM-dd')));
  -- 获得的是数据库日期对象
   dbms_output.put_line(to_char(json_ext.to_date2(obj.get('birthday')),
                               'yyyy-MM-dd'));
  -- 修改对象属性
  obj.put('name', 'JionJion');
  -- 删除对象属性
  obj.remove('age');
  -- 统计当前对象属性数量
  dbms_output.put_line(obj.count);
  -- 属性值是否存在
  if obj.exist('name') then
    dbms_output.put_line('name 属性存在');
  end if;
  if not obj.exist('age') then
    dbms_output.put_line('age 属性不存在');
  end if;
  -- 原格式输出
  obj.print;
  -- 打印  false:压缩格式输出   true:保留换行缩进格式
  dbms_output.put_line(obj.to_char(false));

  --==============
  -- JSON对象列表
  --==============
  -- 首先通过构造函数,构造一个空的json列表
  obj_list := json_list();
  -- 使用to_json_value方法,将json对象转为json_value对象,随后添加
  obj_list.append(json('{"name":"Arise"}').to_json_value);
  -- 使用json_list构造函数,直接构造一个数组.支持空数组
  obj_list.append(json_list('[1,2,3,[]]'));
  -- 原格式输出
  obj_list.print;
  -- 统计列表长度
  dbms_output.put_line(obj_list.count);

  --================
  -- JSON 路径表达式
  --================
  obj := json('{
        "name" : "JionJion",
        "id" : "41060319940412XXXX",
        "Address" : ["ShangHai", "HeNan", true],
        "birthday" : "1994-04-12 00:00:00",
        "others" : {
            "phone" : 15516559,
            "eamil" : "123456@qq.com"
         }
      }');
  -- 支持 .属性 和 [索引] 两种方式 . 注意,数字格式不能直接用get_string获取
  dbms_output.put_line(json_ext.get_number(obj,  'others.phone'));
  dbms_output.put_line(json_ext.get_string(obj,  'Address[2]'));
  -- 添加/修改属性
  json_ext.put(obj, 'others.qq', '1434501783');
  -- 删除属性
  json_ext.remove(obj, 'others.eamil');
  -- 输出
  obj.print;

  --==============
  -- 重复性检查
  --==============
  -- 构造一个空JSON
  obj := json();
  -- 重复性检查
  obj.check_duplicate(false);
  -- 加入数据
  obj.put('a', 1);
  obj.put('b', 2);
  obj.put('c', 3);
  -- 重复加入,后一个值不会覆盖前一个值
  obj.put('a', 11);
  obj.print;
  -- 开启重复性检查
  obj.check_duplicate(true);
  -- 去除重复的,最后添加的元素会替换之前的元素
  obj.remove_duplicates();
  obj.print;

  --=============
  -- 表查询构建JSON
  --=============
  -- 通过表查询,查询JSON列表
  obj_list := json_dyn.executeList('select * from sys_user  where rownum <= 3');
  obj_list.print;
  -- 单条查询,创建JSON对象
  obj := json_dyn.executeObject('select * from sys_user  where rownum <= 1');
  obj.print;
end;

处理逗号分隔的字符串

拼接

-- 主键逗号拼接,完成分隔
declare
  v_empnos      varchar2(300);  -- 逗号拼接
begin
  -- 拼接方式一,默认拼接时采用中文逗号拼接,根据安装语境决定
  select replace(wm_concat(e.empno),'，',',') into v_empnos from emp e ;
  dbms_output.put_line(v_empnos);
  -- 拼接方式二
  v_empnos := null;
  for r_emp in (select * from emp)loop
    v_empnos := v_empnos || ',' || r_emp.empno;
  end loop;
  v_empnos := trim( ',' from v_empnos);
  dbms_output.put_line(v_empnos);  
end;

拆分

使用临时表方式拆分

-- 逗号拆分
declare
  v_empnos      varchar2(300):=  '7369,7499,7521,7566,7654,7698,7782,7788,7839,7844,7876,7900,7902,7934';
  r_emp         emp%rowtype;
begin
  -- 使用split分隔逗号,创建一张虚表
  for r_empno in ( select column_value from  table(split(v_empnos,','))) loop
    select * into r_emp from emp e where e.empno =  r_empno.column_value;
  end loop;
end ;

使用正则方式拆分

select *
  from (
        -- 使用,正则截取
        select nvl(regexp_substr('1,2,3,5', '[^,]+', 1,  level, 'i'), 'NULL') as str
          from dual
        -- 限制行数,必输
        connect by level <= 1000
        ) t

查询

自定义连接符号,进行拼接显示

select listagg(u.description, ';') within group(order by  u.description) seal_name
  from sys_user u
where rownum <= 10

将字符进行URL编码

begin
  -- 目标字符串, 是否转码符号 , 字符集
  dbms_output.put_line(  utl_url.escape('辣鸡框架',true,'UTF8') );
  dbms_output.put_line(   utl_url.unescape('%E8%BE%A3%E9%B8%A1%E6%A1%86%E6%9E%B6','UTF8') );
end;