因为结果是要把行变成列,而行又不固定,所以是拼凑SQL语句,但是怎么执行并返回给游标呢?请各位帮忙,谢谢。CREATE TABLE rpt_ind_shi_var_sto_cur_off
(orgkey VARCHAR2(60) NOT NULL,
orglevel VARCHAR2(16),
orgname VARCHAR2(60),
yearmonth VARCHAR2(6) NOT NULL,
store_type_code VARCHAR2(10) NOT NULL,
category VARCHAR2(32) NOT NULL,
brand VARCHAR2(64) NOT NULL,
variants VARCHAR2(255) NOT NULL,
store_type_sequence NUMBER(4,0),
sign_type NUMBER(4,0),
offtake NUMBER(20,2),
idx_vs_last NUMBER(6,2),
percent_rate NUMBER(6,2))CREATE PROCEDURE SELECT_IND_SHI_VAR_STO_CUR_OFF
(
p_yearmonth IN VARCHAR2
,p_orglevel IN VARCHAR2
,p_orgkey IN VARCHAR2
,c_cursor OUT sys_refcursor
)
IS
/*****************************************************************************************************
MODULE : indirectship by category by storetype
Author : Paolo.Li
DATE : 2009-07-14
DESC : query IndirectShipment by Category by Storetype report data by yearmonth
data level: 1=channel, 2=division, 3=et, 4=rd, 5=hub
Output :
Modified BY :
Modified DATE :
*****************************************************************************************************/
v_sql VARCHAR2(4000);
v_sql_total VARCHAR2(4000);
v_store_type_code VARCHAR2(10);
CURSOR v_cursor IS
SELECT DISTINCT a.store_type_code
FROM rpt_ind_shi_var_sto_cur_off a
WHERE a.orgkey=p_orgkey
AND LOWER(a.orglevel)=LOWER(p_orglevel)
AND a.yearmonth=p_yearmonth;
BEGIN
v_sql := '';
v_sql_total := 'SELECT a.orgkey,a.orglevel,a.orgname,a.category,a.brand,a.variants';
OPEN v_cursor;
LOOP
FETCH v_cursor INTO v_store_type_code;
v_sql := v_sql||' ,MAX(DECODE(a.store_type_code,'''||v_store_type_code||''',a.offtake,0)) '||v_store_type_code||'_offtake';
v_sql := v_sql||' ,MAX(DECODE(a.store_type_code,'''||v_store_type_code||''',a.idx_vs_last,0)) '||v_store_type_code||'_index';
v_sql := v_sql||' ,MAX(DECODE(a.store_type_code,'''||v_store_type_code||''',a.percent_rate,0)) '||v_store_type_code||'_%';
EXIT WHEN v_cursor%NOTFOUND;
END LOOP;
CLOSE v_cursor;
v_sql_total := v_sql_total || v_sql;
v_sql_total := v_sql_total || ' FROM rpt_ind_shi_var_sto_cur_off a ';
v_sql_total := v_sql_total || ' WHERE a.orgkey='||p_orgkey||' AND a.yearmonth='||p_yearmonth||' AND LOWER(a.orglevel)='||LOWER(p_orglevel);
v_sql_total := v_sql_total || ' GROUP BY a.orgkey,a.orglevel,a.orgname,a.category,a.brand,a.variants ';
v_sql_total := v_sql_total || ' ORDER BY a.orgkey,a.orglevel,a.orgname,a.category,a.brand,a.variants;';
OPEN c_cursor FOR
v_sql_total;EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line(SQLERRM);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.put_line(SQLERRM);
WHEN OTHERS THEN
NULL;
END;
(orgkey VARCHAR2(60) NOT NULL,
orglevel VARCHAR2(16),
orgname VARCHAR2(60),
yearmonth VARCHAR2(6) NOT NULL,
store_type_code VARCHAR2(10) NOT NULL,
category VARCHAR2(32) NOT NULL,
brand VARCHAR2(64) NOT NULL,
variants VARCHAR2(255) NOT NULL,
store_type_sequence NUMBER(4,0),
sign_type NUMBER(4,0),
offtake NUMBER(20,2),
idx_vs_last NUMBER(6,2),
percent_rate NUMBER(6,2))CREATE PROCEDURE SELECT_IND_SHI_VAR_STO_CUR_OFF
(
p_yearmonth IN VARCHAR2
,p_orglevel IN VARCHAR2
,p_orgkey IN VARCHAR2
,c_cursor OUT sys_refcursor
)
IS
/*****************************************************************************************************
MODULE : indirectship by category by storetype
Author : Paolo.Li
DATE : 2009-07-14
DESC : query IndirectShipment by Category by Storetype report data by yearmonth
data level: 1=channel, 2=division, 3=et, 4=rd, 5=hub
Output :
Modified BY :
Modified DATE :
*****************************************************************************************************/
v_sql VARCHAR2(4000);
v_sql_total VARCHAR2(4000);
v_store_type_code VARCHAR2(10);
CURSOR v_cursor IS
SELECT DISTINCT a.store_type_code
FROM rpt_ind_shi_var_sto_cur_off a
WHERE a.orgkey=p_orgkey
AND LOWER(a.orglevel)=LOWER(p_orglevel)
AND a.yearmonth=p_yearmonth;
BEGIN
v_sql := '';
v_sql_total := 'SELECT a.orgkey,a.orglevel,a.orgname,a.category,a.brand,a.variants';
OPEN v_cursor;
LOOP
FETCH v_cursor INTO v_store_type_code;
v_sql := v_sql||' ,MAX(DECODE(a.store_type_code,'''||v_store_type_code||''',a.offtake,0)) '||v_store_type_code||'_offtake';
v_sql := v_sql||' ,MAX(DECODE(a.store_type_code,'''||v_store_type_code||''',a.idx_vs_last,0)) '||v_store_type_code||'_index';
v_sql := v_sql||' ,MAX(DECODE(a.store_type_code,'''||v_store_type_code||''',a.percent_rate,0)) '||v_store_type_code||'_%';
EXIT WHEN v_cursor%NOTFOUND;
END LOOP;
CLOSE v_cursor;
v_sql_total := v_sql_total || v_sql;
v_sql_total := v_sql_total || ' FROM rpt_ind_shi_var_sto_cur_off a ';
v_sql_total := v_sql_total || ' WHERE a.orgkey='||p_orgkey||' AND a.yearmonth='||p_yearmonth||' AND LOWER(a.orglevel)='||LOWER(p_orglevel);
v_sql_total := v_sql_total || ' GROUP BY a.orgkey,a.orglevel,a.orgname,a.category,a.brand,a.variants ';
v_sql_total := v_sql_total || ' ORDER BY a.orgkey,a.orglevel,a.orgname,a.category,a.brand,a.variants;';
OPEN c_cursor FOR
v_sql_total;EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line(SQLERRM);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.put_line(SQLERRM);
WHEN OTHERS THEN
NULL;
END;
procedure sp_Check_Turn_Condition(
p_run_id in number,
p_condition in varchar2,
p_return OUT Cur_retrieve
)
as
m_sql varchar2(4000) := 'SELECT';
cursor cur
is
select itemTitle,
itemValue
from s_t_flow_run_item_value
where runid = p_run_id;
curType cur%rowtype;
begin
open cur;
loop
fetch cur into curType;
exit when cur%notfound;
m_sql := m_sql || ' ''' || curType.Itemvalue || ''' as ' || curType.Itemtitle || ',';
end loop;
m_sql := substr(m_sql, 0, length(m_sql) - 1);
m_sql := m_sql || ' FROM DUAL';m_sql := 'SELECT * FROM (' || m_sql || ') WHERE ' || p_condition;open p_return for m_sql;
end sp_Check_Turn_Condition;
TYPE empcurtype IS REF CURSOR;
empcur empcurtype;
emprecord emp%ROWTYPE;
v_str VARCHAR2(100);
BEGIN
v_str := 'SELECT * FROM emp WHERE empno=:eno';
OPEN empcur FOR v_str USING &no;
LOOP
FETCH empcur INTO emprecord;
EXIT WHEN empcur%NOTFOUND;
dbms_output.put_line(emprecord.ename||' '||emprecord.sal);
END LOOP;
CLOSE empcur;
END;
c_cursor OUT sys_refcursor
OPEN c_cursor FOR v_sql_total;
这不已经实现了考虑到资源,open 前if (c_cursor%isopen) then
close c_cursor;
end if;