pengdali(大力 V3.0) :我测试了,出了错误:存储过程如下:
procedure pro_cx_test(my_cur in out my_cursor) is
-- v_dw char(2) default '03';
sqlstr varchar2(4000); cursor c_bm is select distinct bm_bm from gdzckp where dw_bm=v_dw;
-- TYPE my_cursor is REF CURSOR;
-- my_cur my_cursor;begin for v_bm in c_bm loop
sqlstr:=sqlstr||',sum(decode(bm_bm,'''||v_bm.bm_bm||''',sl,0)) '|| v_bm.bm_bm;
end loop;
--这句做调试用
dbms_output.put_line('select flmc,count(*) 合计'||sqlstr||' from gdzckp where dw_bm='''||v_dw||''' group by flmc');
open my_cur FOR 'select flmc,count(*) 合计'||sqlstr||' from gdzckp where dw_bm='''||v_dw||''' group by flmc';end pro_cx_test;
错误出现在:
open my_cur FOR 'select flmc,count(*) 合计'||sqlstr||' from gdzckp where dw_bm='''||v_dw||''' group by flmc';错误信息:
异常详细信息: Oracle.DataAccess.Client.OracleException: ORA-00923: 未找到预期 FROM 关键字 ORA-06512: 在"GDZC.PKG_GDZC_CX", line 238 ORA-06512: 在line 1dbms_output的结果为:
select flmc,count(*) 合计,sum(decode(bm_bm,'03',sl,0)) 03 from gdzckp where dw_bm='03' group by flmc
我该怎么办?
procedure pro_cx_test(my_cur in out my_cursor) is
-- v_dw char(2) default '03';
sqlstr varchar2(4000); cursor c_bm is select distinct bm_bm from gdzckp where dw_bm=v_dw;
-- TYPE my_cursor is REF CURSOR;
-- my_cur my_cursor;begin for v_bm in c_bm loop
sqlstr:=sqlstr||',sum(decode(bm_bm,'''||v_bm.bm_bm||''',sl,0)) '|| v_bm.bm_bm;
end loop;
--这句做调试用
dbms_output.put_line('select flmc,count(*) 合计'||sqlstr||' from gdzckp where dw_bm='''||v_dw||''' group by flmc');
open my_cur FOR 'select flmc,count(*) 合计'||sqlstr||' from gdzckp where dw_bm='''||v_dw||''' group by flmc';end pro_cx_test;
错误出现在:
open my_cur FOR 'select flmc,count(*) 合计'||sqlstr||' from gdzckp where dw_bm='''||v_dw||''' group by flmc';错误信息:
异常详细信息: Oracle.DataAccess.Client.OracleException: ORA-00923: 未找到预期 FROM 关键字 ORA-06512: 在"GDZC.PKG_GDZC_CX", line 238 ORA-06512: 在line 1dbms_output的结果为:
select flmc,count(*) 合计,sum(decode(bm_bm,'03',sl,0)) 03 from gdzckp where dw_bm='03' group by flmc
我该怎么办?
这是什么样的错误?怎么修改呀?
非常感谢你!
函数:CREATE OR REPLACE PACKAGE dali.packtest as
TYPE my_cursor is REF CURSOR;
function f_get_result (indw in varchar2) return my_cursor;
end;create or replace package body dali.packtest as
function f_get_result(indw in varchar2) return my_cursor as
cursor c_bm is select distinct bm from dali.table1 where dw=indw;
my_cur my_cursor;
sqlstr varchar2(4000);
begin
for v_bm in c_bm loop
sqlstr:=sqlstr||',sum(decode(bm,'''||v_bm.bm||''',sl,0)) '|| v_bm.bm;
end loop;
open my_cur FOR 'select mc,count(*) 合计'||sqlstr||' from dali.table1 where dw='''||indw||''' group by mc';
return my_cur;
end;
end;
function f_get_result(indw in varchar2) return my_cursor as
cursor c_bm is select distinct bm from dali.table1 where dw=indw;
my_cur my_cursor;
sqlstr varchar2(4000);
begin
for v_bm in c_bm loop
sqlstr:=sqlstr||',sum(decode(bm,'''||v_bm.bm||''',sl,0)) '|| v_bm.bm||'sl,sum(decode(bm,'''||v_bm.bm||''',je,0)) '|| v_bm.bm||'je';
end loop;
open my_cur FOR 'select mc,count(*) 合计'||sqlstr||' from dali.table1 where dw='''||indw||''' group by mc';
return my_cur;
end;
end;
表1
bm mc
bm1 部门1
bm2 部门2
bm3 部门3
bm4 部门4
bm5 部门5表2
dw bm mc sl je
01 bm1 计算机 1 10000
01 bm1 空调 2 20000
01 bm1 计算机 2 20000
01 bm2 打印机 1 10000
01 bm2 计算机 1 10000
01 bm4 电视机 1 4000
02 bm2 电视机 1 4000
03 bm1 计算机 1 10000
04 bm1 计算机 1 10000
要得到的查询结果是:输入dw 后(例如01)出现下面的结果
合计 部门1 部门2 部门3 部门4 部门5
mc sl je sl je sl je sl je sl je sl je
计算机 4 40000 3 30000 1 10000 0 0 0 0 0 0
空调 2 20000 2 20000 0 0 0 0 0 0 0 0
打印机 1 10000 0 0 1 10000 0 0 0 0 0 0
电视机 1 4000 0 0 0 0 0 0 1 4000 0 0
... .. ..... .. .. .. ...... .. ....
根据输入的dw 不同,查出不同的结果这个查询的该怎么写呢?也返回的是ref cursor 类型