PROCEDURE P_AND_CPT_RATIOOTH_APP_BAK2_N(
V_APPIDS IN VARCHAR2,
V_TYPE IN VARCHAR2,
V_CHANNEL IN VARCHAR2,
V_TABLE IN VARCHAR2,
V_START IN VARCHAR2,
V_END IN VARCHAR2,
RESULT OUT mycursor
) IS
V_SQL CLOB;
V_SQLWHERE VARCHAR2(32767) default '';
V_SQLWHERE_CHANNEL VARCHAR2(32767) default '';
V_SQL_DATES CLOB;
V_Sdate DATE;
V_Edate DATE;
V_TABLE_DATE VARCHAR2(50);
V_TABLE_TYPE VARCHAR2(50);
V_START_DATE VARCHAR2(50);
V_END_DATE VARCHAR2(50);
V_DAY VARCHAR2(50);
BEGIN
select column_name into V_TABLE_DATE from user_tab_columns where table_name=''||V_TABLE||'' and column_id=1;
select column_name into V_TABLE_TYPE from user_tab_columns where table_name=''||V_TABLE||'' and column_id=5;
dbms_lob.createtemporary(V_SQL,true);--创建一个临时lob
dbms_lob.createtemporary(V_SQL_DATES,true);--创建一个临时lob
IF V_APPIDS is NOT NULL THEN
V_SQLWHERE := 'AND t.appid in ('||V_APPIDS||')';
END IF;
IF V_CHANNEL IS NOT NULL THEN
V_SQLWHERE_CHANNEL := 'AND t.channel = '''||V_CHANNEL||'''';
END IF;
IF V_TABLE_DATE = 'MON' THEN
V_START_DATE := SUBSTR(V_START,0,6);
V_END_DATE := SUBSTR(V_END,0,6);
v_sdate := to_date(V_START_DATE, 'yyyymm');
v_edate := to_date(V_END_DATE, 'yyyymm');
WHILE (v_sdate <= v_edate) LOOP
dbms_lob.append(v_SQL_DATES,to_char(v_sdate, 'yyyymm'));--把临时字符串付给v_str
IF v_sdate != v_edate THEN
dbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str
END IF;
v_sdate := add_months(v_sdate,1);
END LOOP;
ELSE --周和日 类型 都是 DAY
v_sdate := to_date(V_START, 'yyyymmdd');
v_edate := to_date(V_END, 'yyyymmdd');
V_END_DATE := V_END;
IF SUBSTR(V_TYPE,0,1)='d' THEN
V_START_DATE := to_char(v_sdate, 'yyyymmdd');
WHILE (v_sdate <= v_edate) LOOP
dbms_lob.append(v_SQL_DATES,to_char(v_sdate, 'yyyymmdd'));--把临时字符串付给v_str
IF v_sdate != v_edate THEN
dbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str
END IF;
v_sdate := v_sdate+1;
END LOOP;
ELSIF SUBSTR(V_TYPE,0,1)='w' THEN
select to_char(V_Sdate,'d') INTO V_DAY from dual;
IF V_DAY!=2 THEN
V_Sdate:=V_Sdate-7;
END IF;
V_START_DATE := to_char(v_sdate, 'yyyymmdd');
WHILE (v_sdate <= v_edate) LOOP
select to_char(V_Sdate,'d') INTO V_DAY from dual;
IF V_DAY=2 THEN
dbms_lob.append(v_SQL_DATES,to_char(v_sdate, 'yyyymmdd'));--把临时字符串付给v_str
IF V_Edate-v_sdate >7 THEN
dbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str
END IF;
END IF;
v_sdate := v_sdate+1;
END LOOP;
END IF;
END IF;
dbms_lob.append(v_sql,'SELECT * FROM( SELECT *
FROM '||V_TABLE||' t
WHERE
t.'||V_TABLE_TYPE||' = '''||V_TYPE||'''
AND t.'||V_TABLE_DATE||' >= '''||V_START_DATE||'''
AND t.'||V_TABLE_DATE||' <= '''||V_END_DATE||'''
'||V_SQLWHERE||'
'||V_SQLWHERE_CHANNEL||' ) t1
pivot(sum(MARKETSHARE)
for '||V_TABLE_DATE||' in(');
dbms_lob.append(v_sql,v_SQL_DATES);
dbms_lob.append(v_sql,'))');
dbms_output.put_line(v_sql);
OPEN result FOR v_sql;
dbms_lob.freetemporary(v_sql);--释放lob
dbms_lob.freetemporary(v_SQL_DATES);--释放lob
--dbms_output.put_line(V_SQLDATE);
-- dbms_output.put_line(v_SQL_DATES);
--记录操作日志及错误日志
END;