日期:2014-05-17  浏览次数:20993 次

orcale 用游标输出多行数据集(动态select语句)?
下面的存储过程执行到OPEN cv_1 FOR v_sql;时提示,无效字符。请各位高手看看咋回事啊?小弟是新人请多多担待啊!!

CREATE OR REPLACE PROCEDURE SearchOrdersByField/****** 对象: StoredProcedure [dbo].[SearchOrdersByField] 脚本日期: 03/13/2012 16:19:43 ******/
/*注:修改存储过程SearchOrdersByField时SearchOrdersByFieldCount存储过程要同时更新*/
(
  v_accNo IN NVARCHAR2 DEFAULT NULL ,
  v_Status IN NVARCHAR2 DEFAULT NULL ,
  v_startDate IN DATE DEFAULT NULL ,
  v_endDate IN DATE DEFAULT NULL ,
  v_patientName IN NVARCHAR2 DEFAULT NULL ,
  v_groupId IN NVARCHAR2 DEFAULT NULL ,
  v_externalPatientClass IN NUMBER DEFAULT NULL ,
  v_externalPatientId IN VARCHAR2 DEFAULT NULL, 
  cv_1 OUT SYS_REFCURSOR
)
AS
  v_sql VARCHAR2(5000);
BEGIN

  v_sql := ' select o.*
  from order_ o 
  inner join RequestedProcedure rp 
  on o.OrderUid = rp.OrderUid and o.AcessFee<>1' ;
  IF v_accNo IS NOT NULL THEN
   
  BEGIN
  v_sql := v_sql || ' and o.AccessionNumber =''' || v_accNo || '''' ;
  /*按照申请号查询*/
  END;
  END IF;
  IF v_Status IS NOT NULL THEN
   
  BEGIN
  v_sql := v_sql || ' and o.OrderStatusUid =' || v_Status ;
  /*按照登记状态查询*/
  END;
  END IF;
  IF v_groupId IS NOT NULL
  AND v_groupId <> '0' THEN
   
  BEGIN
  v_sql := v_sql || ' and rp.InsurancePreauthComments=''' || v_groupId || '''' ;
  /*按照科室查询*/
  END;
  END IF;
  v_sql := v_sql || ' inner join ScheduledProcedureStep step
  on step.RequestedProcedureUid = rp.RequestedProcedureUid
  inner join ReservationMap rvMap 
  on rvMap.ScheduledProcedureStepUid = step.ScheduledProcedureStepUid
  inner join Reservation rv
  on rvMap.ReservationUid = rv.ReservationUid ' ;
  IF v_startDate IS NOT NULL THEN
   
  BEGIN
  v_sql := v_sql || ' and rv.StartDateTime >=to_date(''';
  v_sql:= v_sql || to_char(v_startDate,'yyyy-mm-dd hh24:mi:ss')||''','''||'yyyy-mm-dd hh24:mi:ss'||''')' ;
  /*按照开始时间查询*/
  END;
  END IF;
  IF v_endDate IS NOT NULL THEN
   
  BEGIN
  v_sql := v_sql || ' and rv.StartDateTime <=to_date(''';
  v_sql:= v_sql ||to_char(v_endDate,'yyyy-mm-dd hh24:mi:ss')||''','''||'yyyy-mm-dd hh24:mi:ss'||''')' ;
  /*按照结束日期查询*/
  END;
  END IF;
  v_sql := v_sql || ' inner join Patient p 
  on o.PatientUid = p.PatientUid' ;
  IF v_patientName IS NOT NULL THEN
   
  BEGIN
  v_sql := v_sql || ' and p.LastName like ''' || v_patientName || '%''' ;
  /*按病人姓名查询*/
  END;
  END IF;
  IF v_externalPatientClass IS NOT NULL THEN
   
  BEGIN
  v_sql := v_sql || ' and p.ExternalPatientClass=' || CAST(v_externalPatientClass AS VARCHAR2) ;
  END;
  END IF;
  IF v_externalPatientId IS NOT NULL THEN 
   
  BEGIN
  v_sql := v_sql || ' and p.ExternalPatientId=''' || v_externalPatientId || '''' ;
  END;
  END IF;
  v_sql := v_sql || ' order by o.orderUid desc;' ;
 
  OPEN cv_1 FOR v_sql;
END;




------解决方案--------------------
你在open cv_1 f