oracle动态查询 [英] oracle dynamic queries
问题描述
这些oracle动态查询是我的新手,我只是想构建一个动态查询,所以我可以根据它获取记录.
I am new with these oracle dynamic queries, I am just trying to build a dynamic query, so I could get records according to it.
这是我的PL/SQL语句:
Here is my PL/ SQL statement:
declare
sql_query varchar2(5000) := 'select FKOM_OFFICE_ID,FKBAM_BUDGET_ID ';
begin
for x in (select distinct PFS_SOURCE_ID,PFS_SOURCE_ENG from PBS_FC_SOURCE WHERE PFS_UPPER_SOURCE_ID!=0 ORDER BY PFS_SOURCE_ID ASC )
loop
sql_query := sql_query ||
' , nvl(sum(case when FKBAB_SOURCE_ID = '||x.PFS_SOURCE_ID||' then FKOD_AMOUNT ELSE 0 end),0) as source_'||x.PFS_SOURCE_ID;
dbms_output.put_line(sql_query);
end loop;
sql_query := sql_query || ' FROM FMS_K_OFFICEWISE_DTL
JOIN FMS_K_OFFICEWISE_MST ON FMS_K_OFFICEWISE_MST.FKOM_OFFICE_MST_ID=FMS_K_OFFICEWISE_DTL.FKOD_OFFICE_MST_ID
JOIN FMS_K_BUDGET_ALLOCATION_DTL ON FMS_K_BUDGET_ALLOCATION_DTL.FKBAD_BUDGET_ALLOC_DTL_ID=FMS_K_OFFICEWISE_DTL.FKOD_BUDGET_ALLOC_AD_ID
JOIN FMS_K_BUDGET_ALLOCATION_MST ON FMS_K_BUDGET_ALLOCATION_MST.FKBAM_BUDGET_ALLOC_ID=FMS_K_BUDGET_ALLOCATION_DTL.FKBAB_BUDGET_ALLOC_ID
JOIN PBS_FC_BUDGET ON PBS_FC_BUDGET.PFB_BUDGET_ID=FMS_K_BUDGET_ALLOCATION_MST.FKBAM_BUDGET_ID
WHERE PFB_LEVEL_ID=2 GROUP BY FKOM_OFFICE_ID,FKBAM_BUDGET_ID ';
dbms_output.put_line(sql_query);
end;
由于希望查询看起来不错,如何从上述语句中获取记录?
How to get records from this above statement since I hope the query seems fine ?
推荐答案
一种可能是使用引用游标-构建SQL语句,然后打开该语句的引用游标.
One possibility is using a ref cursor - you build your SQL statement and open the ref cursor for this statement afterwards.
简体:
declare
l_cursor sys_refcursor;
sql_query varchar2(4000);
begin
sql_query := 'select 1 as value from dual';
open l_cursor for sql_query;
end;
然后,根据您的IDE/宿主语言,您可以遍历光标等.
Depending on your IDE / host language, you can then iterate over the cursor etc.
例如,在SQL Plus中打印引用光标:
For example, to print a ref cursor in SQL Plus:
variable x refcursor
set autoprint on
declare
sql_query varchar2(4000);
begin
sql_query := 'select 1 as value from dual';
open :x for sql_query;
end;
这个概念在11g中甚至更强大,因为您可以
This concept is even more powerful in 11g, because you can
- 对SQL语句使用CLOB(从而使您可以动态生成任意长度的查询)
- 将引用游标转换为DBMS_SQL游标(反之亦然),如果您需要在运行时获取列数和列类型,这将为您提供更大的灵活性
这篇关于oracle动态查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!