一個利用遊標返回資料集的例子

昨日給人做儲存過程的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;