oracle 数组的使用与调试(二)

create or replace package body PKG_PT_ADS is
　　 --获取过滤条件
　　 FUNCTION GET_ACT_CONDITION(p_columns IN VARCHAR2,p_val IN array1) RETURN VARCHAR2
　　 IS
　　 v_act_array VARCHAR2(32767) DEFAULT '';
　　 BEGIN
　　 IF p_val.COUNT <= 0 THEN--判断数组是否为空
　　 RETURN '';
　　 ELSE
　　 FOR i IN 1..p_val.COUNT LOOP--循环取出数组中的数据
　　 IF lower(p_val(i)) = 'all' THEN RETURN '';
　　 ELSE
　　 v_act_array := v_act_array||''''|| lower(p_val(i)) || ''',';
　　 END IF;
　 　 END LOOP;
　　 v_act_array := RTRIM(trim(v_act_array), ',') ;--去掉多余,号
　　 RETURN ' and '|| p_columns || ' in ( '|| v_act_array || ')';
　　 END IF;
　　 END GET_ACT_CONDITION;
　　
　　 --impression 选择
　　 FUNCTION GET_IMP_CONDITION(p_columns IN VARCHAR2,p_val IN VARCHAR2) RETURN VARCHAR2
　　 IS
　　 v_act_array VARCHAR2(32767) DEFAULT '';
　　 BEGIN
　　 IF p_val IS NULL OR TRIM(p_val) IS NULL OR p_val = '0' THEN
　　 RETURN '';
　　 ELSE
　　 RETURN ' and '||p_columns|| ' > 0 ';
　 　 END IF;
　　 END GET_IMP_CONDITION;
　　
　　
　　 --p_network 选择
　　 FUNCTION GET_NET_CONDITION(p_columns IN VARCHAR2,p_val IN VARCHAR2 ) RETURN VARCHAR2
　　 IS
　　 v_act_array VARCHAR2(32767) DEFAULT '';
　　 BEGIN
　　 IF p_val IS NULL OR TRIM(p_val) IS NULL THEN
　　 RETURN '';
　　 ELSIF p_val = '0' THEN
　　 RETURN ' and '|| p_columns || ' in (''search only'',''content only'') ';
　　 ELSIF p_val = '1' THEN
　 　 RETURN ' and '|| p_columns || ' in (''all'') ';
　　 END IF;
　　 END GET_NET_CONDITION;
　　
　　 procedure proc_report_act (
　　 /*********************************************************************
　 　 *功能：reporting报表
　　 *parameter:
　　 *作者：XIANGMIN.MENGXM
　　 *时间：2010-03-20
　　 *备注：
　　 *********************************************************************/
　 　 p_network varchar2 default '0', --network 0:分表 1：总表
　　 p_zero_imp varchar2 default '0', --zero impression 0:显示zero数据 1：不显示zero数据
　　 p_act_summary varchar2 default '0', --account summary 0:有account列 1：没有account列
　　 p_Date_range varchar2 default 'DAILY', --Date range
　　 p_Date_start varchar2 default to_char(sysdate,'yyyy-mm-dd'), --Date start yyyy-mm-dd
　　 p_Date_end varchar2 default to_char(sysdate,'yyyy-mm-dd'), --Date end yyyy-mm-dd
　　 p_user varchar2 default 'ptautotest',
　　 p_act_array array1 --存储多个account name
　　 ) is
　　 /***********************************************************************************************
　 　 pt_test.pt_search_test_tmp
　　 ************************************************************************************************/
　 　 v_sql_sentence varchar2(32767) default '';
　　 v_user_id number;
　 　
　　 BEGIN
　　 --获得用户ID
　　 select user_id into v_user_id from pt_auth_user where user_name = p_user;
　　
　　 --每次调用过程删除临时表中原有数据
　　 EXECUTE IMMEDIATE ('TRUNCATE TABLE pt_test.PT_ADS_REPORT_TMP');
　　 EXECUTE IMMEDIATE ('TRUNCATE TABLE pt_test.pt_ads_report_perf');
　　
　　
　　 --拼接插入数据SQL
　　 v_sql_sentence := 'insert/*+APPEND */ into pt_test.PT_ADS_REPORT_TMP' ||
　 　 ' (account_id,account_name,AD_DISTRIBUTION,' ||
　 　 ' clicks,impressions,cost,average_position,cpc,ctr,active_buyer,cpab,cr,SESSION_CNT,validSession) ' ||
　　 ' select t6.ACCOUNT_ID,t6.ACCOUNT,t6.AD_DISTRIBUTION,' ||
　　 ' t6.g_clicks,t6.g_imp,t6.g_cost,t6.g_average_position,t6.g_cpc,t6.g_ctr,t6.a_actbuyer_cnt,' ||
　　 ' t6.cpab,t6.cr,t6.SESSION_CNT,t6.validSession' ||
　　 ' from pt_test.pt_auth_user_account t5,' ||
　　 ' ( ' ||
　　 ' SELECT /*+ordered use_hash(A2,A1) parallel(A1,4)*/' ||
　　 ' A1.ACCOUNT_ID,' ||
　 　 ' A1.ACCOUNT,' ||
　　 ' A1.status,' ||
　　 ' A1. AD_DISTRIBUTION,' ||
　　 ' SUM(A1.G_CLICKS) AS G_CLICKS,' ||
　　 ' SUM(A1.A_SESSION_CNT) AS SESSION_CNT,' ||
　　 ' (SUM(A1.A_SESSION_CNT) - sum(a1.A_SAP_CNT)) AS validSession,' ||
　 　 ' SUM(A1.G_IMP) AS G_IMP,' ||
　　 ' SUM(A1.G_COST) AS G_COST,' ||
　　 ' substr(max(to_char(a1.yyyymmdd,''yyyymmdd'') || a1.g_average_position),9) as g_average_position, ' ||
　　 ' DECODE(SUM(A1.G_CLICKS),0,0,SUM(A1.G_COST) / SUM(A1.G_CLICKS)) AS G_CPC,' ||
　　 ' DECODE(SUM(A1.G_IMP),0,0,SUM(A1.G_CLICKS) / SUM(A1.G_IMP)) AS g_ctr,' ||
　 　 ' DECODE(SUM(A1.G_IMP), 0, 0, SUM(A1.G_COST) / SUM(A1.G_IMP)) AS G_CPM,' ||
　　 ' COUNT(DISTINCT(A2.INNUM_VALUE)) AS A_ACTBUYER_CNT,' ||
　　 ' decode(COUNT(DISTINCT(A2.INNUM_VALUE)),0,0,sum(A1.G_COST)/COUNT(DISTINCT(A2.INNUM_VALUE))) as cpab,' ||
　　 ' decode(sum(A1.G_CLICKS),0,0,COUNT(DISTINCT(A2.INNUM_VALUE))/sum(A1.G_CLICKS)) as cr' ||
　　 ' FROM PT_TEST.pt_ads_innum_effect A2,' ||
　　 ' PT_TEST.Pt_Ads_Account_Effect A1' ||
　　 ' WHERE A1.ACCOUNT_ID = A2.ACCOUNT_ID(+)' ||
　　 ' AND A1.YYYYMMDD = A2.YYYYMMDD(+)' ||
　　 ' AND A1.AD_DISTRIBUTION = A2.AD_DISTRIBUTION(+)' ||
　　 ' AND A2.type(+) = ''M''' ||
　　 ' AND A2.site(+) = ''I''' ||
　　 ' AND A1.yyyymmdd between to_date(' || '''' || nvl(p_date_start,to_char(SYSDATE,'yyyy-mm-dd')) || '''' || ',' || '''yyyy-mm-dd''' || ')' ||
　　 ' and to_date(' || '''' || nvl(p_date_start,to_char(SYSDATE,'yyyy-mm-dd')) || '''' || ',' || '''yyyy-mm-dd''' || ')' ||
　　 GET_NET_CONDITION('lower(a1.AD_DISTRIBUTION)',p_network) || --判断选择的类型：总表 or 分表
　　 ' GROUP BY A1.ACCOUNT_ID,a1.account,A1.status,A1.AD_DISTRIBUTION' ||
　　 ' ) t6' ||
　　 ' where t5.user_id =' || v_user_id ||
　　 ' and t5.account_id = t6.ACCOUNT_ID' ||
　　 ' and t6.status = ' || '''' || nvl(p_Date_range,'DAILY') || '''' ||
　　 GET_IMP_CONDITION('t6.g_imp',p_zero_imp) ||
　　 GET_ACT_CONDITION('lower(T6.ACCOUNT)',p_act_array);--此处涉及到数组的使用
　　
　 　
　　 dbms_output.put_line(substr(v_sql_sentence,1,1800));
　　 dbms_output.put_line(substr(v_sql_sentence,1801));
　　 --执行插入数据语句
　 　 EXECUTE IMMEDIATE v_sql_sentence;
　　 COMMIT;
　　
　　 --插入到performance表*/
　　 IF p_act_summary = '0' THEN
　　 insert into pt_test.pt_ads_report_perf
　　 (account_id,account_name,AD_DISTRIBUTION,clicks,impressions,cost,cpc,ctr,active_buyer,cpab,cr,SESSION_CNT,validSession)
　 　 select account_id,account_name,AD_DISTRIBUTION,clicks,impressions,cost,cpc,ctr,active_buyer,cpab,cr,SESSION_CNT,validSession
　 　 from pt_test.PT_ADS_REPORT_TMP;
　　 commit;
　　 ELSE
　 　 insert into pt_test.pt_ads_report_perf
　　 (clicks,impressions,cost,cpc,ctr,active_buyer,cpab,cr,SESSION_CNT,validSession)
　 　 SELECT sum(clicks),sum(impressions),sum(cost),
　　 DECODE(SUM(clicks),0,0,SUM(cost) / SUM(clicks)) AS cpc,
　　 DECODE(SUM(impressions),0,0,SUM(clicks) / SUM(impressions)) AS ctr,
　 　 sum(active_buyer) AS active_buyer,
　　 decode(sum(active_buyer),0,0,sum(cost)/sum(active_buyer)) AS cpab,
　　 decode(sum(clicks),0,0,sum(active_buyer)/sum(clicks)) AS cr,
　 　 sum(SESSION_CNT) AS SESSION_CNT,
　　 sum(validSession) AS validSession
　　 from pt_test.PT_ADS_REPORT_TMP;
　　 commit;
　　 END IF;
　　
　　
　　end proc_report_act;
　　
　 　end PKG_PT_ADS;