oracle存储过程之切割字符串(二)

调用及一个普通的查询过程

调用代码(一个function)：

 --通过分割，把一个字符串的值获取到最后一个
  function getLastValueBySeperator(p_str       IN VARCHAR2,
                                   p_delimiter IN VARCHAR2) return varchar2 as
    Cursor c is
      select t.* from table(common_tool.fn_split(p_str, p_delimiter)) t;
    r         c%rowtype;
    resultVal varchar(100);
  begin
    open c;
    loop
      fetch c
        into r;
      exit when c%notfound;
      -- dbms_output.put_line(r.column_value);
      resultVal := r.column_value;
    end loop;
  
    return resultVal;
  end;

参考代码：

包头：

create or replace package WCITY2_STATISTIC is

  -- Author  : ADMINISTRATOR
  -- Created : 2012/10/24 9:48:34
  -- Purpose : 
  --定义一个游标类型
  type Ref_Cursor is ref cursor;

  --通过分割，把一个字符串的值获取到最后一个
  function getLastValueBySeperator(p_str       IN VARCHAR2,
                                   p_delimiter IN VARCHAR2) return varchar2;

  --page统计分析子系统调用该接口查看页面信息，
  --页面信息包括页面编码、页面名称、页面归属地等，
  --其中页面编码字段要求唯一且不能为空。
  procedure sp_pager_stats;

  --访问信息
  procedure sp_uservisit_stat(c_uservisit out Ref_Cursor);

  --下载信息
  procedure sp_download_stat(c_download out Ref_Cursor);

  --资源信息
  procedure sp_resource_stat(c_resource out Ref_Cursor);

  --查询订购信息
  procedure sp_order_stat(c_order out Ref_Cursor);

  --查询应用信息
  procedure sp_application_stat(c_application out Ref_Cursor);
  
  --查询:服务订阅信息
  procedure sp_bind_stat(c_bind out Ref_Cursor);
  
  --查询产品信息
  procedure sp_product_stat(c_product out Ref_Cursor);

end WCITY2_STATISTIC;

包体：

create or replace package body WCITY2_STATISTIC is

  --通过分割，把一个字符串的值获取到最后一个
  function getLastValueBySeperator(p_str       IN VARCHAR2,
                                   p_delimiter IN VARCHAR2) return varchar2 as
    Cursor c is
      select t.* from table(common_tool.fn_split(p_str, p_delimiter)) t;
    r         c%rowtype;
    resultVal varchar(100);
  begin
    open c;
    loop
      fetch c
        into r;
      exit when c%notfound;
      -- dbms_output.put_line(r.column_value);
      resultVal := r.column_value;
    end loop;
  
    return resultVal;
  end;

  --页面信息
  procedure sp_pager_stats is
  begin
    -- cur_page as select * from OMS_WIRELESS. TEMPLATE_FILE_WORKING;
    null;
  end sp_pager_stats;

  --访问信息
  procedure sp_uservisit_stat(c_uservisit out Ref_Cursor) as
    --定义游标
    /*
    cursor c_uservisit is 
    select t.city,t.username,t.username as telphone,'' as ip 
    from INTERFACE_WIRELESS.USER_LOGIN_LOG t ;*/
  begin
    open c_uservisit For
      select t.city, t.username, t.username as telphone, '' as ip
        from INTERFACE_WIRELESS.USER_LOGIN_LOG t;
  end;

  --下载信息
  procedure sp_download_stat(c_download out Ref_Cursor) as
  begin
    open c_download for
      select *
        from interface_wireless.log_statistics t
       where t.uri like '%download%';
  end;

  --资源信息
  procedure sp_resource_stat(c_resource out Ref_Cursor) as
  
    --v_resource  oms_wireless.resource_info%rowtype;
    v_id   oms_wireless.resource_info.id%type;
    v_type oms_wireless.resource_info.name%type;
    v_name oms_wireless.resource_info.name%type;
  begin
  
    open c_resource for
      select t.id as id,
             getLastValueBySeperator(t.name, '.') as type,
             t.name
        from oms_wireless.resource_info t;
    /*
    while (c_resource%found) loop
      fetch c_resource
        into v_id, v_type, v_name;
      v_type := 'test';
      dbms_output.put_line('aa:' + v_type);
    end loop;*/
  end;

  --查询订购信息
  procedure sp_order_stat(c_order out Ref_Cursor) as
  begin
    open c_order for
      select pd.order_id, pd.pay_account, pd.pay_account
        from interface_wireless.pay_record pd;
  end;

  --查询应用信息
  procedure sp_application_stat(c_application out Ref_Cursor) as
  begin
    open c_application for
      select t.id, t.app_id as app_type, t.app_name
        from interface_wireless.app_action_relation t;
  end;

  --查询:服务订阅信息
  procedure sp_bind_stat(c_bind out Ref_Cursor) as
  begin
    open c_bind for
      select aar.app_name, aar.app_id, aar.app_name, aar.plat_form
        from interface_wireless.menu_info menu
        left join interface_wireless.app_action_relation aar
        on menu.app_id = aar.app_id;
  end;
  
  --产品信息
  procedure sp_product_stat(c_product out Ref_Cursor) as
    begin
      null;
      end;
end WCITY2_STATISTIC;