ORACLE游标使用

DROP TABLE GW_PROC_IF_GETALARMPARAMS;
CREATE OR REPLACE PROCEDURE "GW_PROC_IF_GETALARMPARAMS"
   (
       AlarmTypeCode varchar2,  --报警类型编码
       my_cur out sys_refcursor --输出结果集合

    )
    is
   TYPE ref_cursor_type IS REF CURSOR;  --定义一个动态游标
   v_sql VARCHAR2(4000);
   sqlCount integer;
   is_exists integer;
   v_AlarmPolicyTemplateId integer;
   v_AlarmTypeCode varchar2(50);
   v_ParamFieldName varchar(50);
   v_POLLTTANTNAME varchar2(50);
   v_Val varchar2(50);
   usrs ref_cursor_type;
   err_desc varchar2(2048);
  begin

  --创建指标临时表，存储传入的指标
   is_exists := 0;
  select count(1) into is_exists from ALL_OBJECTS where OBJECT_NAME=upper('TMP_IF_GetAlarmParams');
  if is_exists = 0 then
    Execute  immediate 'Create global temporary table TMP_IF_GetAlarmParams(
      ID integer ,
      AlarmPolicyTemplateId integer,
      ALARMTYPECODE varchar2(50),
      ALARMTYPENAME varchar2(50),
      POLLTTANTNAME varchar2(50),
      PARAMNAME varchar2(50),
      PARAMFIELDNAME varchar2(50),
      VAL varchar(50)
    ) on commit  preserve rows';
  else
    Execute  immediate 'truncate table TMP_IF_GetAlarmParams';
  end if;

  v_sql:= ' insert into TMP_IF_GetAlarmParams(ID,AlarmPolicyTemplateId,ALARMTYPECODE,ALARMTYPENAME,POLLTTANTNAME,PARAMNAME,PARAMFIELDNAME)
                      select p.ID,p.AlarmPolicyTemplateId,p.ALARMTYPECODE,t.ALARMTYPENAME,p.POLLTTANTNAME,p.PARAMNAME,p.PARAMFIELDNAME from gw_conf_alarmparams p, gwm_dim_alarmtype t
                      where p.AlarmTypeCode = t.AlarmTypeCode
                      and t.active = 1';

  if AlarmTypeCode is not null then
    v_sql:= v_sql || ' and p.AlarmTypeCode = '''||AlarmTypeCode||'''';
    end if;

  execute immediate v_sql;
    --
  v_sql := 'select AlarmPolicyTemplateId,ALARMTYPECODE ,PARAMFIELDNAME,POLLTTANTNAME from TMP_IF_GetAlarmParams';
  --打开游标
  open usrs for v_sql ;
  loop
      fetch usrs into v_AlarmPolicyTemplateId, v_AlarmTypeCode,v_ParamFieldName,v_POLLTTANTNAME;
      exit when usrs%notfound;

       execute immediate 'select '||v_ParamFieldName||' from gwm_dim_alarmpoltplt where POLLTTANTNAME = '''||v_POLLTTANTNAME||'''
        and AlarmTypeCode = '''||v_AlarmTypeCode||''' and id = '||v_AlarmPolicyTemplateId||''  into v_Val;

      execute immediate ' update TMP_IF_GetAlarmParams set VAL = '''||v_Val||'''
                          where TMP_IF_GetAlarmParams.POLLTTANTNAME = '''||v_POLLTTANTNAME||'''
                          and TMP_IF_GetAlarmParams.AlarmTypeCode = '''||v_AlarmTypeCode||'''
                          and TMP_IF_GetAlarmParams.PARAMFIELDNAME = '''||v_ParamFieldName||'''
                          and TMP_IF_GetAlarmParams.AlarmPolicyTemplateId = '||v_AlarmPolicyTemplateId||'';

  end loop;
  close usrs;

  v_sql:='select  ID ,AlarmPolicyTemplateId,ALARMTYPECODE, ALARMTYPENAME ,replace(POLLTTANTNAME,''PM25'',''PM2.5'')POLLTTANTNAME,PARAMNAME , PARAMFIELDNAME,VAL from TMP_IF_GetAlarmParams
  order by alarmtypecode,pollttantname,alarmpolicytemplateid,id asc';
 open my_cur for v_sql;

 EXCEPTION

 WHEN OTHERS THEN
    err_desc := SUBSTR(SQLERRM,1,1000);
   insert into gwm_fact_log(AddTime,proname,message) values(sysdate,'GW_PROC_IF_GetAlarmType',to_char(err_desc));
   DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' ||SQLERRM);
 end;