动态游标里不能使用变量吗？

Question

我建了个存储过程，因为所取列是变化的，所以应用了动态游标，但现在出现一个问题，在运行动态游标时报错，错误提示“ora-00904 "end_date" invalid identifier",我已经试过了，如果采用普通游标end_date 变量是没问题的，感觉是执行动态游标时数据库没把end_date作为一个变量来识别，不知是不是动态游标里不能再使用变量，请各位大虾解答。create or replace procedure p_search_client_month_sale
 (st_date in date,
  end_date in date) is
  new_st_date date;
  new_sql varchar2(4000);
 -- new_sql varchar2(4000); --用于保存最终执行的sql语句
 
 type cur is ref cursor; --定义动态游标变量
 col cur;/*以下开始procedure执行*/     --获得转换交叉表sql       
     declare one_col varchar2(200);  --存储每1列的sql语句
             col_sql varchar2(4000); --存储所有列sql语句
             cur_sql varchar2(1000); --动态游标构造sql
     begin
          cur_sql :='select '||''',nvl((select sum( usercount) from cq_tmp_sale_data where clientcls=a.clientcls and orddate =''||'|| 'orddate' ||'||''),0) as'||' "''||'|| 'orddate' || '||''"'''  
                    ||' from cq_tmp_sale_data'
                    ||' where orddate between to_char(st_date,'||'''yyyymm'''||') and to_char(end_date,'||'''yyyymm'''||')' 
                    ||' group by orddate';
          open col for cur_sql;
          loop
          fetch col into one_col;
          exit when col%notfound;
          col_sql := col_sql || one_col ;
          end loop;
          close col;
                --dbms_output.put_line(col_sql);
          new_sql:='select clientcls,1 as usercount ' || col_sql || 
                   ' from cq_tmp_sale_data a where a.orddate between  '
                   || to_char(st_date,'yyyymm') || ' and '|| to_char(end_date,'yyyymm') || ' group by clientcls';         
    --execute immediate new_sql;
    end ;
end p_search_client_month_sale;

Answer 1

使用动态游标时请注意自己的动态sql,你的cur_sql内容为:
select ',nvl((select sum( usercount) from cq_tmp_sale_data where clientcls=a.clientcls and orddate ='||orddate||'),0) as "'||orddate||'"' from cq_tmp_sale_data where orddate between to_char(st_date,'yyyymm') and to_char(end_date,'yyyymm') group by orddate
这样的sql是不可能执行的
关于动态sql的帮定变量应该这样:
declare
type myc is type of ref coursor;
var_sql varchar2(8000);
var_1   varchar2(20);
var_2   varchar2(20);
myc1 myc;
begin
...
var_sql := 'select col1 from  table where table.col2 = :var1 and table.col3 = :var2';
open myc1 for var_sql using var_1,var_2;
...
end;

Answer 2

刚才敲错个地方，应该这样
declare
type myc is  ref coursor;
var_sql varchar2(8000);
var_1   varchar2(20);
var_2   varchar2(20);
myc1 myc;
begin
...
var_sql := 'select col1 from  table where table.col2 = :var1 and table.col3 = :var2';
open myc1 for var_sql using var_1,var_2;
...
end;

Answer 3

sql 书写上的问题,在open前你把cur_sql打印出来执行一下 看看是否可以执行,然后再继续...

Answer 4

谢谢adaizi1980(阿代)，已搞定。
我刚学oracle，很多都不懂。

Answer 5

现在又有个问题：我在pl/sql developer里执行execute immediate 之后提示执行成功，为什么看不到返回的查询结果？

Answer 6

execute immediate
结果在服务端,dev里是客户端.

Answer 7

存储过程一般只是实现功能性过程，除非用户定义输出，否则不会产生输出；
要验证过程是否成功执行了，一般有两种途径：
一是在过程中定义输出，可以把执行结果和执行时间存储到指定的表，以备以后查询执行情况，如果只是为了调试用，可以直接用dbms_output.putline(变量)函数将你希望执行后得到的变量结果分别打印到缓冲区(在pl/sql的testwindow里调用过程时，会在output窗口显示结果);
二是给过程定义out类型的参数，可以是你需要的任何类型，通过其他存储过程或者是客户程序（如java、pb、vb等语言开发的两层或3层客户程序）里调用该过程，out类型的参数会将执行结果返回到客户端.