oracle 多语句返回,oracle 多条语句同时执行（比如返回两个dataset）

public DataSet GetQualityStatistics(DateTime start_date,DateTime end_date,string modality,stringhospital_id)

{

StringBuilder sql= newStringBuilder();

List paras = new List();

IDBHelper dbhelper=DBFactory.createDBHleper();

DataSet ds= newDataSet();switch(sqlType)

{case "MSSQL2008":break;case "Oracle":

sql.Append("begin");//科室内图像质量评价

sql.Append(@"open :var_1 for ‘SELECT COUNT(*)NUM, NVL(B.Dept_image_quality,2)QUALITY,to_char(A.REGISTRATION_DATE,‘‘mm‘‘) MONTH FROM PATIENT_REGISTRATION A

LEFT JOIN QUALITY_EVALUATION B ON A.REGISTRATION_ID=B.REG_ID WHERE to_char(A.REGISTRATION_DATE,‘‘yyyy-MM-dd hh:mi:ss‘‘) >=‘‘"+start_date+"‘‘")

.Append(@"AND to_char(A.REGISTRATION_DATE,‘‘yyyy-MM-dd hh:mi:ss‘‘) <=‘‘"+end_date+"‘‘ AND MODALITY=‘‘"+modality+"‘‘ AND HOSPITAL_ID=‘‘"+hospital_id+"‘‘")

.Append(@"GROUP BY B.Dept_image_quality,to_char(A.REGISTRATION_DATE,‘‘mm‘‘) ORDER BY B.Dept_image_quality,to_char(A.REGISTRATION_DATE,‘‘mm‘‘)‘ ;");//sql.Append(" ");

科室内报告质量评价

//sql.Append(" begin ");

sql.Append(@"open :var_2 for ‘ SELECT COUNT(*)NUM, NVL(B.Dept_report_quality,2)QUALITY,to_char(A.REGISTRATION_DATE,‘‘mm‘‘) MONTH FROM PATIENT_REGISTRATION A

LEFT JOIN QUALITY_EVALUATION B ON A.REGISTRATION_ID=B.REG_ID WHERE to_char(A.REGISTRATION_DATE,‘‘yyyy-MM-dd hh:mi:ss‘‘) >=‘‘" +start_date+"‘‘")

.Append("AND to_char(A.REGISTRATION_DATE,‘‘yyyy-MM-dd hh:mi:ss‘‘) <=‘‘"+end_date+"‘‘ AND MODALITY=‘‘"+modality+"‘‘ AND HOSPITAL_ID=‘‘"+hospital_id+"‘‘")

.Append(@"GROUP BY B.Dept_report_quality,to_char(A.REGISTRATION_DATE,‘‘mm‘‘) ORDER BY B.Dept_report_quality,to_char(A.REGISTRATION_DATE,‘‘mm‘‘)‘;");//sql.Append(" end; ");

临床对图像质量评价

//sql.Append(" begin ");

sql.Append(@"open :var_3 for ‘SELECT COUNT(*)NUM, NVL(B.Clinic_image_quality,2)QUALITY,to_char(A.REGISTRATION_DATE,‘‘mm‘‘) MONTH FROM PATIENT_REGISTRATION A

LEFT JOIN QUALITY_EVALUATION B ON A.REGISTRATION_ID=B.REG_ID

WHERE to_char(A.REGISTRATION_DATE,‘‘yyyy-MM-dd hh:mi:ss‘‘) >=‘‘" +start_date+"‘‘ AND to_char(A.REGISTRATION_DATE,‘‘yyyy-MM-dd hh:mi:ss‘‘) <=‘‘"+end_date+"‘‘ AND MODALITY=‘‘"+modality+"‘‘ AND HOSPITAL_ID=‘‘"+hospital_id+"‘‘")

.Append(@"GROUP BY B.Clinic_image_quality,to_char(A.REGISTRATION_DATE,‘‘mm‘‘) ORDER BY B.Clinic_image_quality,to_char(A.REGISTRATION_DATE,‘‘mm‘‘)‘;");//sql.Append(" end; ");

临床对报告质量评价

//sql.Append(" begin ");

sql.Append(@"open :var_4 for ‘SELECT COUNT(*)NUM, NVL(B.Clinic_report_quality,2)QUALITY,to_char(A.REGISTRATION_DATE,‘‘mm‘‘) MONTH FROM PATIENT_REGISTRATION A

LEFT JOIN QUALITY_EVALUATION B ON A.REGISTRATION_ID=B.REG_ID

WHERE to_char(A.REGISTRATION_DATE,‘‘yyyy-MM-dd hh:mi:ss‘‘) >=‘‘" +start_date+"‘‘ AND to_char(A.REGISTRATION_DATE,‘‘yyyy-MM-dd hh:mi:ss‘‘) <=‘‘"+end_date+"‘‘ AND MODALITY=‘‘"+modality+"‘‘ AND HOSPITAL_ID=‘‘"+hospital_id+"‘‘")

.Append("GROUP BY B.Clinic_report_quality,to_char(A.REGISTRATION_DATE,‘‘mm‘‘) ORDER BY B.Clinic_report_quality,to_char(A.REGISTRATION_DATE,‘‘mm‘‘)‘;");

sql.Append("end;");var p = new OracleParameter(":var_1", OracleDbType.RefCursor);

p.Direction=ParameterDirection.Output;

paras.Add(p);var p2=new OracleParameter(":var_2", OracleDbType.RefCursor);

p2.Direction=ParameterDirection.Output;

paras.Add(p2);var p3=new OracleParameter(":var_3", OracleDbType.RefCursor);

p3.Direction=ParameterDirection.Output;

paras.Add(p3);var p4=new OracleParameter(":var_4", OracleDbType.RefCursor);

p4.Direction=ParameterDirection.Output;

paras.Add(p4);

ds=dbhelper.Query(sql.ToString(),paras);break;default: break;

}returnds;

}

以上代码返回的是4个dataset。

贴别注意，open :var_1for ‘ ’这里面的语句，所有的单引号都要变成两个单引号。