plsql块获取动态sql查询结果 [英] plsql block to get the dynamic sql query result

问题描述

我们已经创建了以下匿名块........

we have created following anonymous block...........

DECLARE
   sql_str long(32000);
   where_str long(32000);
   counter NUMBER(3):=0;
   BEGIN
   sql_str:='SELECT '||' A.bio_id ,';
   where_str:=' where '||'A.bio_id=B.bio_id AND'||' A.bio_id<>0 and rownum<25 AND (' ;
   LOOP
   counter:=counter+1;
  sql_str:=sql_str||'decode(A.wk_units'||(counter+1)||' - B.wk_units'|| (counter)||',0,NULL,A.wk_units'||(counter+1)||')';
 sql_str:=sql_str||', decode(A.wk_units'||(counter+1)||' - B.wk_units'|| (counter)||',0,NULL,B.wk_units'||(counter)||')' ;
  where_str:=where_str||' A.wk_units'||(counter+1)||'<> B.wk_units'||(counter) ;
  EXIT WHEN counter=5;
  sql_str:=sql_str||', ';
  where_str:=where_str||' or ';
  END LOOP;
  sql_str:=sql_str||' from cnt_sls_dm.fct_sales_summary A, cnt_sls_dm.fct_sales_summary B';
  sql_str:=sql_str||where_str||')';
  dbms_output.put_line(sql_str);
  EXECUTE IMMEDIATE(sql_str);
  END;

所需结果: 我们编写了一个动态sql查询. 它应该获得选择查询的结果集. 但是我们在运行此块时得到查询本身. 而不是获得此查询的任何结果.

Result needed: We have written a dynamic sql query. It should get the result set of select query. But we are getting the query itself on running this block. rather than getting any result for this query.

让我们知道我们是否在礼仪轨道上. 或者我们需要做其他事情才能得出结果.

Let us know are we on the rite track. Or we need to do something else to get the result out.

结果是

SQL> /
SELECT  A.bio_id ,decode(A.wk_units2 - B.wk_units1,0,NULL,A.wk_units2),
decode(A.wk_units2 - B.wk_units1,0,NULL,B.wk_units1), decode(A.wk_units3 -
B.wk_units2,0,NULL,A.wk_units3), decode(A.wk_units3 -
B.wk_units2,0,NULL,B.wk_units2), decode(A.wk_units4 -
B.wk_units3,0,NULL,A.wk_units4), decode(A.wk_units4 -
B.wk_units3,0,NULL,B.wk_units3), decode(A.wk_units5 -
B.wk_units4,0,NULL,A.wk_units5), decode(A.wk_units5 -
B.wk_units4,0,NULL,B.wk_units4), decode(A.wk_units6 -
B.wk_units5,0,NULL,A.wk_units6), decode(A.wk_units6 -
B.wk_units5,0,NULL,B.wk_units5) from cnt_sls_dm.fct_sales_summary A,
cnt_sls_dm.fct_sales_summary B where A.bio_id=B.bio_id AND A.bio_id<>0 and
rownum<25 AND ( A.wk_units2<> B.wk_units1 or  A.wk_units3<> B.wk_units2 or
A.wk_units4<> B.wk_units3 or  A.wk_units5<> B.wk_units4 or  A.wk_units6<>
B.wk_units5)

PL/SQL procedure successfully completed.

推荐答案

对我来说，欧文想要实现的目标尚不清楚:

For me it is unclear what Irveen wants to achieve:

@Irveen，为什么不包含105条记录的表?每周您都会删除一条记录，并添加另一条记录.您将永远不会更改您的查询.

@Irveen, Why not a table with 105 records? Every week you drop one record and you add enother one. You will have never to change your query.

另一种解决方案是转移数据，因此您将第2列的数据放入1、3放入2、2、4放入3，...，103放入102.您将不必添加列或删除列.

Another solution is to shift your data, so you put the data of column 2 into 1, 3 into 2, 4 into 3, ..., 103 into 102. You will never have to add a column and drop a column.

这篇关于plsql块获取动态sql查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！