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 for v_sql前面加上一句
dbms_output.put_line(v_sql);
把sql语句打印出来，这样看不清楚。
