oracle 多条语句同时执行（比如返回两个dataset）
时间：2021-07-01 10:21:17
帮助过：1人阅读
DataSet GetQualityStatistics(DateTime start_date,DateTime end_date,
string modality,
string hospital_id)
{
StringBuilder sql =
new StringBuilder();
List<DbParameter> paras =
new List<DbParameter>
();
IDBHelper dbhelper =
DBFactory.createDBHleper();
DataSet ds =
new DataSet();
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;
}
return ds;
}
以上代码返回的是4个dataset。
贴别注意，open :var_1 for ‘ ’这里面的语句，所有的单引号都要变成两个单引号。
oracle 多条语句同时执行（比如返回两个dataset）
标签：
