ITPub博客

首页 > 数据库 > 数据库开发技术 > 一个利用游标返回数据集的例子

一个利用游标返回数据集的例子

原创 数据库开发技术 作者:zhyuh 时间:2005-03-18 11:14:42 0 删除 编辑
昨日给人做存储过程的tuning,加了个索引很简单地搞定了,返回结果从10秒钟降到2秒钟。但是那个存储过程的写法和用途却是第一次见到,贴过来,下次做参考,呵呵[@more@]

存储过程:

PROCEDURE RCA_GET_ENG_ALL_PARAM_PRC
(pi_model_id RDO.RDO_MODEL_MASTER.MODEL_ID%TYPE,
pi_engine_id RDO_ENGINE.ENGINE_ID%TYPE,
ResultSet OUT  RDO_APP_PKG.result_cursor) AS

PSQLSTMT  VARCHAR2(8000);
SQLSTR    VARCHAR2(2000);

BEGIN
  execute immediate 'alter session set sort_area_size=80000000';
  SQLSTR := '
select distinct a.parameter_seq_id,b.parameter_name,b.paramter_type,b.data_source,c.normalizer_value,
b.is_combined,b.paramter_status,b.flight_phase
from rdo_model_rule_parameter a,rdo_phase_param_mv b,(select distinct e.parameter_seq_id param_seq_id, e.flight_phase flight_phase1, f.*
 from rdo_model_rule_parameter e,
  (select * from rdo_parameter_flight where model_id = '''||pi_model_id||''') f
 where e.parameter_seq_id = f.parameter_seq_id(+) and e.model_id = '''||pi_model_id||''')  c
where a.parameter_seq_id = b.parameter_seq_id  and a.model_id = '''||pi_model_id||'''
and a.parameter_seq_id = c.param_seq_id  and a.flight_phase = c.flight_phase1
and a.flight_phase = b.flight_phase
union
select distinct a.param_seq_id,b.parameter_name,b.paramter_type,b.data_source,c.normalizer_value,
b.is_combined,b.paramter_status,d.flight_phase
from rdo_parameter_value a,rdo_phase_param_mv b, rdo_parameter_flight c, rdo_plot_fam d
where a.DIM_SEQ_ID = d.DIM_SEQ_ID
and a.PARAM_SEQ_ID = b.PARAMETER_SEQ_ID
and d.flight_phase = b.flight_phase
and a.PARAM_SEQ_ID = c.PARAMETER_SEQ_ID
and d.flight_phase = c.flight_phase
and d.engine_id = '''||pi_engine_id||'''
and c.model_id= '''||pi_model_id||'''';

 PSQLSTMT := SQLSTR;
 OPEN ResultSet FOR PSQLSTMT;

EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20101,'Error in RCA_GET_ENG_ALL_PARAM_PRC'||SQLCODE|| '-'|| SQLERRM);


END RCA_GET_ENG_ALL_PARAM_PRC;

测试

SQL>var a refcursor;

SQL> exec rdo_app_pkg. RCA_GET_ENG_ALL_PARAM_PRC('11','721969',:a);

SQL> print a;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-793216/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2008801