调用及一个普通的查询过程
调用代码(一个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;