关于pl/sql返回数据集问题

Question

有个sql语句在pl/sql里面可以执行，有数据集返回
可是放到存储过程里在pl/sql里面调试就返回不出数据集
简单来说就是一个
CREATE OR REPLACE PROCEDURE system."UP_INFACC" (workid
varchar2,result out test_infacc.testresultset)
ASbegin
open result for
SELECT v_infacc1. 任务号,v_infacc1.工装号,件号,max(v_infacc1.制件数量) as 数量,v_infacc1.工装名称,v_infacc1.零件名称,ljfini,       max (CASE  WHEN 工序号=1  THEN 工序内容   END  ) AS 工序1  ,max (CASE  WHEN 工序号=1   THEN 单件定额  END  ) AS 定额1  ,MAX(CASE  WHEN 工序号=1  THEN gxfini END) AS gxfini1 , MAX(CASE  WHEN 工序号=1  THEN sdt END) AS sdt1 , MAX(CASE  WHEN 工序号=1  THEN edt END) AS edt1 ,MAX(CASE  WHEN 工序号=1  THEN 任务分配表.finishnum END) AS finishnum1 ,MAX(CASE  WHEN 工序号=1  THEN ww END) AS ww1 ,MAX(CASE  WHEN 工序号=1  THEN wwgd END) AS wwgd1 ,
       max (CASE  WHEN 工序号=2  THEN 工序内容   END  ) AS 工序2  ,max (CASE  WHEN 工序号=2   THEN 单件定额  END  ) AS 定额2  ,MAX(CASE  WHEN 工序号=2  THEN gxfini END) AS gxfini2 , MAX(CASE  WHEN 工序号=2  THEN sdt END) AS sdt2 , MAX(CASE  WHEN 工序号=2  THEN edt END) AS edt2 ,MAX(CASE  WHEN 工序号=2  THEN 任务分配表.finishnum END) AS finishnum2 ,MAX(CASE  WHEN 工序号=2  THEN ww END) AS ww2 ,MAX(CASE  WHEN 工序号=2  THEN wwgd END) AS wwgd2 ,
。。
。。
        xldjd.outtime ,           任务分配表.pzrw ,           任务分配表.pzdt
FROM v_infacc1,任务分配表,xldjd
WHERE  (v_infacc1.工装号 = workid)  and
(  rtrim(upper(TO_SINGLE_BYTE(v_infacc1.任务号)))=  rtrim(upper(TO_SINGLE_BYTE(任务分配表.任务号)))) 
and (  rtrim(upper(TO_SINGLE_BYTE(v_infacc1.工装号)))=  rtrim(upper(TO_SINGLE_BYTE(任务分配表.工装号)))) 
and (  rtrim(upper(TO_SINGLE_BYTE(任务分配表.任务号)))=  rtrim(upper(TO_SINGLE_BYTE(xldjd.taskid)))) 
and (  rtrim(upper(TO_SINGLE_BYTE(任务分配表.工装号)))=  rtrim(upper(TO_SINGLE_BYTE(xldjd.workid))))
GROUP BY v_infacc1.任务号,v_infacc1.工装号,v_infacc1.工装名称,件号,零件名称,ljfini,jhpx,xldjd.outtime ,           任务分配表.pzrw ,           任务分配表.pzdt
ORDER BY v_infacc1.任务号,v_infacc1.工装号,件号,jhpx,xldjd.outtime ,           任务分配表.pzrw ,           任务分配表.pzdt;commit;
exception
 when others then
 rollback ;
 return ;
end;
为什么，这个查询返回可能超过了200列，是因为列太多了吗 还是其他原因 跟踪变量workid是存在值得

Answer 1

--用动态拼接形式
CREATE OR REPLACE PROCEDURE system."UP_INFACC" (workid
varchar2,result out test_infacc.testresultset)
ASbegin
    open result for  --这里将查询语句用动态sql拼接
'SELECT v_infacc1. 任务号,v_infacc1.工装号,件号,max(v_infacc1.制件数量) as 数量,v_infacc1.工装名称,v_infacc1.零件名称,ljfini,  max (CASE WHEN 工序号=1 THEN 工序内容 END ) AS 工序1 ,max (CASE WHEN 工序号=1 THEN 单件定额 END ) AS 定额1 ,MAX(CASE WHEN 工序号=1 THEN gxfini END) AS gxfini1 , MAX(CASE WHEN 工序号=1 THEN sdt END) AS sdt1 , MAX(CASE WHEN 工序号=1 THEN edt END) AS edt1 ,MAX(CASE WHEN 工序号=1 THEN 任务分配表.finishnum END) AS finishnum1 ,MAX(CASE WHEN 工序号=1 THEN ww END) AS ww1 ,MAX(CASE WHEN 工序号=1 THEN wwgd END) AS wwgd1 ,
  max (CASE WHEN 工序号=2 THEN 工序内容 END ) AS 工序2 ,max (CASE WHEN 工序号=2 THEN 单件定额 END ) AS 定额2 ,MAX(CASE WHEN 工序号=2 THEN gxfini END) AS gxfini2 , MAX(CASE WHEN 工序号=2 THEN sdt END) AS sdt2 , MAX(CASE WHEN 工序号=2 THEN edt END) AS edt2 ,MAX(CASE WHEN 工序号=2 THEN 任务分配表.finishnum END) AS finishnum2 ,MAX(CASE WHEN 工序号=2 THEN ww END) AS ww2 ,MAX(CASE WHEN 工序号=2 THEN wwgd END) AS wwgd2 ,
。。
。。
  xldjd.outtime , 任务分配表.pzrw , 任务分配表.pzdt
FROM v_infacc1,任务分配表,xldjd
WHERE (v_infacc1.工装号 = '||workid||') and  --参数workid
( rtrim(upper(TO_SINGLE_BYTE(v_infacc1.任务号)))= rtrim(upper(TO_SINGLE_BYTE(任务分配表.任务号))))  
and ( rtrim(upper(TO_SINGLE_BYTE(v_infacc1.工装号)))= rtrim(upper(TO_SINGLE_BYTE(任务分配表.工装号))))  
and ( rtrim(upper(TO_SINGLE_BYTE(任务分配表.任务号)))= rtrim(upper(TO_SINGLE_BYTE(xldjd.taskid))))  
and ( rtrim(upper(TO_SINGLE_BYTE(任务分配表.工装号)))= rtrim(upper(TO_SINGLE_BYTE(xldjd.workid))))
GROUP BY v_infacc1.任务号,v_infacc1.工装号,v_infacc1.工装名称,件号,零件名称,ljfini,jhpx,xldjd.outtime , 任务分配表.pzrw , 任务分配表.pzdt
ORDER BY v_infacc1.任务号,v_infacc1.工装号,件号,jhpx,xldjd.outtime , 任务分配表.pzrw , 任务分配表.pzdt';commit;
exception
    when others then
         rollback ;
    return ;
end;

Answer 2

你如此动态拼接 难道不用execute immediate 这个吗

Answer 3

看错了 以为你的result out是一个动态游标
不过我看你result是一个 test_infacc.testresultset类型的  是什么啊？游标？

Answer 4

create or replace package system.test_infacc is
type testresultset is ref cursor;
end test_infacc;
就是一个程序包啊

Answer 5

既然是动态游标，就用我说的那样做！动态拼接你的SQL
open result for 'sql'; 

Answer 6

WHERE (v_infacc1.工装号 = '||workid||') and  --参数workid
这里改成
WHERE (v_infacc1.工装号 = '''||workid||''') and  --参数workid 就可以编译过去了  虚心求教2者的区别  还有
CREATE OR REPLACE PROCEDURE system."TEST" (workid
varchar2,result out test_infacc.testresultset)
ASbegin
open result for
'SELECT * from 任务分配表 where 任务号= '''||workid||''' ';
commit;
exception
 when others then
 rollback ;
 return ;
end;
与
CREATE OR REPLACE PROCEDURE system."TEST" (workid
varchar2,result out test_infacc.testresultset)
ASbegin
open result for
SELECT * from 任务分配表 where 任务号= workid ';
commit;
exception
 when others then
 rollback ;
 return ;
end;
的区别

Answer 7

直接
WHERE (v_infacc1.工装号 = workid) and --参数workid

Answer 8

CREATE OR REPLACE PROCEDURE system."TEST" (workid
varchar2,result out test_infacc.testresultset)
ASbegin
open result for
'SELECT * from 任务分配表 where 任务号= '''||workid||''' ';
commit;
exception
 when others then
 rollback ;
 return ;
end;
与
CREATE OR REPLACE PROCEDURE system."TEST" (workid
varchar2,result out test_infacc.testresultset)
ASbegin
open result for
SELECT * from 任务分配表 where 任务号= workid ;
commit;
exception
 when others then
 rollback ;
 return ;
end;
的区别

Answer 9

WHERE (v_infacc1.工装号 = '||workid||') and --参数workid
与
WHERE (v_infacc1.工装号 = '''||workid||''') 的区别 我刚测试了 比方变量是wow
前者WHERE (v_infacc1.工装号 = wow)
后者WHERE (v_infacc1.工装号 = 'wow')这种区别 
下面的
CREATE OR REPLACE PROCEDURE system."TEST" (workid
varchar2,result out test_infacc.testresultset)
ASbegin
open result for
'SELECT * from 任务分配表 where 任务号= '''||workid||''' ';
commit;
exception
 when others then
 rollback ;
 return ;
end;
与
CREATE OR REPLACE PROCEDURE system."TEST" (workid
varchar2,result out test_infacc.testresultset)
ASbegin
open result for
SELECT * from 任务分配表 where 任务号= workid ;
commit;
exception
 when others then
 rollback ;
 return ;
end;
的区别有什么区别 为什么我换成动态的 数据集15秒就出来了 不动态的等几十分钟也不出来呢

Answer 10

一个绑定变量的普通sql,一个是动态sql串.
普通sql oracle会自动把变量替换成相应值,比如你的变量值是wow,oracle在执行sql的时候就是 字段='wow'.动态sql是拼串,需要自己把单引号拼上去.
速度会有那么大差异?

Answer 11

恩 因为我执行的是一个非常多列的sql语句 大约200多个列 而且其中全是
max (CASE WHEN 工序号=1 THEN 工序内容 END ) AS 工序1 ,max (CASE WHEN 工序号=1 THEN 单件定额 END ) AS 定额1 ,MAX(CASE WHEN 工序号=1 THEN gxfini END) AS gxfini1 , MAX(CASE WHEN 工序号=1 THEN sdt END) AS sdt1 , MAX(CASE WHEN 工序号=1 THEN edt END) AS edt1 ,MAX(CASE WHEN 工序号=1 THEN 任务分配表.finishnum END) AS finishnum1 ,MAX(CASE WHEN 工序号=1 THEN ww END) AS ww1 ,MAX(CASE WHEN 工序号=1 THEN wwgd END) AS wwgd1 ,
  max (CASE WHEN 工序号=2 THEN 工序内容 END ) AS 工序2 ,max (CASE WHEN 工序号=2 THEN 单件定额 END ) AS 定额2 ,MAX(CASE WHEN 工序号=2 THEN gxfini END) AS gxfini2 , MAX(CASE WHEN 工序号=2 THEN sdt END) AS sdt2 , MAX(CASE WHEN 工序号=2 THEN edt END) AS edt2 ,MAX(CASE WHEN 工序号=2 THEN 任务分配表.finishnum END) AS finishnum2 ,MAX(CASE WHEN 工序号=2 THEN ww END) AS ww2 ,MAX(CASE WHEN 工序号=2 THEN wwgd END) AS wwgd2 ,
这东西 一个执行完15秒就出来 一个执行完 我点那个返回数据集的...几十分钟都不出来 

Answer 12

在这里问你一个其他问题啊 一会我给你加分 嘿嘿 
CREATE OR REPLACE FUNCTION system."IS_NUMERIC" (STR IN VARCHAR2
   ) RETURN NUMBER
AS
V_STR FLOAT;
begin
 if str is null then
 return 0;
 else
 begin
 select to_number(str) into V_STR from dual;
 exception
 when others then
 return 0 ;
 end;
 return V_STR;
end if ;
end IS_NUMERIC;
这是一个函数 SELECT  a.no,
  a.refno ,
  a.gzh ,
  a.type ,
  a.refseq ,
  a.jh ,
  a.jhname ,
  a.gxh ,
  (select cast(max(system.is_numeric(c.gxh)) as varchar2(10)) from system.mom30100 c
where  rtrim(upper(TO_SINGLE_BYTE(c.rwh)))=  rtrim(upper(TO_SINGLE_BYTE(a.rwh)))and 
 rtrim(upper(TO_SINGLE_BYTE(a.gzh)))= rtrim(upper(TO_SINGLE_BYTE(c.gzh))) and 
 rtrim(upper(TO_SINGLE_BYTE(a.jh)))=  rtrim(upper(TO_SINGLE_BYTE(c.jh)))
 and system.is_numeric(c.gxh)<system.is_numeric(a.gxh) and type<>'2' ) as prior1,
  (select cast(max(system.is_numeric(c.gxh)) as varchar2(10)) from system.mom30100 c
where  rtrim(upper(TO_SINGLE_BYTE(c.rwh)))=  rtrim(upper(TO_SINGLE_BYTE(a.rwh)))and 
 rtrim(upper(TO_SINGLE_BYTE(a.gzh)))= rtrim(upper(TO_SINGLE_BYTE(c.gzh))) and 
 rtrim(upper(TO_SINGLE_BYTE(a.jh)))=  rtrim(upper(TO_SINGLE_BYTE(c.jh)))
 and system.is_numeric(c.gxh)>system.is_numeric(a.gxh)  and type<>'2'  ) as next1,
  a.gxdh ,
  a.num ,
  b.operator ,
  b.operatorname ,
  a.sj ,
  a.rwh ,
  a.usefix ,
  a.usefixname ,degs,delx,planfinish
FROM system.mom30100 a,system.operatorlist b
WHERE a.no = b.no  and
   a. = '0' and a.operator like 'yg_075%' and
  a.no not in (select no from system.mom30101); 执行这个要10分钟左右 system.mom30100这个表数据量在30万左右 ，而且system.mom30100这个表中gxh存在不能to_number()转化的值，这个语句时间太长了 有没有办法优化下 我把这个语句放到存储过程里面跟其他语句在一起 存储过程50多分钟都执行不完 这个也太慢了点

Answer 13

上面的太乱了点 简化点就这样也行
SELECT a.no,
  a.gzh ,
    a.gxh ,
  (select cast(max(system.is_numeric(c.gxh)) as varchar2(10)) from system.mom30100 c
where rtrim(upper(TO_SINGLE_BYTE(c.rwh)))= rtrim(upper(TO_SINGLE_BYTE(a.rwh)))and  
 rtrim(upper(TO_SINGLE_BYTE(a.gzh)))= rtrim(upper(TO_SINGLE_BYTE(c.gzh))) and  
 rtrim(upper(TO_SINGLE_BYTE(a.jh)))= rtrim(upper(TO_SINGLE_BYTE(c.jh)))
 and system.is_numeric(c.gxh)<system.is_numeric(a.gxh) and type<>'2' ) as prior1,
  (select cast(max(system.is_numeric(c.gxh)) as varchar2(10)) from system.mom30100 c
where rtrim(upper(TO_SINGLE_BYTE(c.rwh)))= rtrim(upper(TO_SINGLE_BYTE(a.rwh)))and  
 rtrim(upper(TO_SINGLE_BYTE(a.gzh)))= rtrim(upper(TO_SINGLE_BYTE(c.gzh))) and  
 rtrim(upper(TO_SINGLE_BYTE(a.jh)))= rtrim(upper(TO_SINGLE_BYTE(c.jh)))
 and system.is_numeric(c.gxh)>system.is_numeric(a.gxh) and type<>'2' ) as next1,
FROM system.mom30100 a,system.operatorlist b
WHERE a.no = b.no and
  a. = '0' and a.operator like 'yg_075%' and
  a.no not in (select no from system.mom30101); 把这个简化下就可以

Answer 14

用了太多函数在where条件里,哪个字段是有索引的?不能换个逻辑把函数放到没索引的一边么

Answer 15

以前的数据库是sql server 2000 前台是pb 老程序写的时候没加约束
我那些条件里存在大小写不同，存在右空格 存在半角全角的问题 
sql server 2000这些问题全给忽略了 转到oracle里问题全体现出来了 不那么写两边数据就不一致，我现在正测试2边的存储过程数据是否一致呢

Answer 16

返回游标为什么不使用函数
CREATE OR REPLACE PROCEDURE system."UP_INFACC" (workid varchar2) 
 return sys_refcursor AS()
这个在调试时也比较容易select up_infacc('11') from dual;返回游标为什么还要使用commit;你又没有做I/D/U操作即便有I/D/U操作exception
 when others then
 rollback ;
 return ;
end;我觉得也不应该写，oracle会自动回滚的，这样写的话如果出现异常你在之前做的DML操作也会回滚

Answer 17

exception
 when others then
 rollback ;
 return ;
end;
写这个得目的就是为了 存储过程出问题 整个存储过程操作全取消 还有你说的
返回游标为什么不使用函数
CREATE OR REPLACE PROCEDURE system."UP_INFACC" (workid varchar2)  
 return sys_refcursor AS()
这个在调试时也比较容易
这个我不太懂 我接触oracle时间不长 返回数据集只会那么用

Answer 18

这个想法也考虑过 可是企业的数据库我们不想动，出什么问题责任谁担啊，只能从程序方面考虑，以前的pb与sql server2000的老程序都是历史问题了，我接手这套erp系统时间也不长，数据库的数据逻辑还没怎么搞明白，先别说数据库200多张表，就是改起来工作量也不小啊，我现在的工作就是把2边数据弄一致了，存储过程执行结果一致就ok了 别的也不多考虑

Answer 19

1.楼主的代码没有任何insert/update/delete操作，故可以使用函数
2.使用存储过程不能使用
  exception
 when others then
 rollback;--替换为 null;
end;
因为oracle会自动回滚,照楼主的写法如下情况会出现问题1.insert into table;
2.exec system.UP_INFACC();
在步骤1之后如果没有commit而在执行步骤2时出现异常oracle会回滚到当前联接最后一次提交之后的savepoint

Answer 20

sorry
这个函数应该这样写CREATE OR REPLACE function system."UP_INFACC" (workid varchar2) return sys_refcursor AS(
..............
)
我写错了

Answer 21

呵呵 其实我的存储过程是存在insert/update/delete操作的 不然为什么写存储过程啊
其实上面的select 语句是在insert 语句后面接的

Answer 22

CREATE OR REPLACE function system."UP_INFACC" (workid varchar2) return sys_refcursor AS(
..............
)
这个函数里面该怎么写