小小编
在使用ODP.NET进行Oracle编程时，有时候SQL语句非常复杂，需要采用动态构造查询语句的情况，有两种方法可以构造动态的SQL语句，并执行返回结果集。
1、在数据访问层构造SQL语句
例如下面的语句，将构造完整的SQL语句赋值给CommandText，再传递到数据库进行执行，返回结果集。
loadCommand.CommandText = “Select * From Users”
dataAdapter .SelectCommand = loadCommand
dataAdapter . Fill(data)
dataAdapter .SelectCommand = loadCommand
dataAdapter . Fill(data)
该方法需要将整个SQL的构造过程放在DataAccess层，业务逻辑发生变化，修改不方便，而且每次查询需要传递给数据库很长的查询字符串，传递参数的效率也不高。
2、在存储过程中构造动态SQL语句并执行
以下为一个完整的事例（经过删减），其中RefCursor 为自定义游标类型
P_ControlType IN NUMBER,
P_Progress IN CHAR,
P_DepartID IN VARCHAR2,
P_ProjectName IN NVARCHAR2,
C_Projects OUT RefCursor) IS
e_ErrInterruption EXCEPTION;
v_ErrID NUMBER; –Variable to hold the errorlog id
v_ErrCode NUMBER; –Variable to hold the error message code
v_ErrText VARCHAR2(512); –Variable to hold the error message text
v_ErrProc VARCHAR2(50) := \’G_Search\’;
v_DepartID VARCHAR2(16);
v_ProjectName NVARCHAR2(128);
v_SQL VARCHAR2(512);
v_Where VARCHAR2(256);
BEGIN
v_SQL := \’SELECT PROJECTID, PARENTID, PROJECTNAME \’;
v_SQL := v_SQL || \’ FROM PROJECTS A\’;
v_Where := \’ Where\’;
— 年度
IF P_YearNO < 9999 THEN
v_Where := v_Where || \’ A.YearNO = \’ || P_YearNO || \’ And\’;
ELSE
v_Where := v_Where || \’ A.YearNO < \’ || P_YearNO || \’ And\’;
END IF;
— 控制类别
IF P_ControlType = 9 THEN
v_Where := v_Where || \’ A.ControlType < 9 And\’;
ELSE
v_Where := v_Where || \’ A.ControlType = \’ || P_ControlType ||
\’ And\’;
END IF;
— 进度
IF P_Progress < \’Z\’ THEN
v_Where := v_Where || \’ A.Progress = \’\’\’ || P_Progress || \’\’\’ And\’;
ELSE
v_Where := v_Where || \’ A.Progress < \’\’\’ || P_Progress || \’\’\’ And\’;
END IF;
IF TRIM(P_DepartID) <> \’%\’ THEN
v_Where := v_Where || \’ A.DepartID = \’\’\’ || P_DepartID || \’\’\’ And\’;
ELSE
v_Where := v_Where || \’ A.DepartID Like \’\’\’ || P_DepartID ||
\’\’\’ And\’;
END IF;
–项目名称
v_ProjectName := NVL(P_ProjectName,
\’%\’);
IF v_ProjectName <> \’%\’ THEN
v_ProjectName := \’%\’ || P_ProjectName || \’%\’;
END IF;
v_Where := v_Where || \’ A.ProjectName Like \’ || \’\’\’\’ || v_ProjectName ||
\’\’\’ And\’;
v_SQL := v_SQL || v_Where;
OPEN C_PROJECTS FOR v_SQL;
–COMMIT;
EXCEPTION
–根据需要定义错误异常
WHEN OTHERS THEN
–ROLLBACK;
v_ErrID := SQLCODE;
v_ErrText := SQLERRM;
raise_application_error(v_ErrID,
v_ErrText);
END G_Search;
该方法只需要传递给存储过程一些参数，使用游标返回数据。参数传递效率较高，而且业务逻辑在存储过程中，调整比较方便。该方法关键的在下面的语句：
Open C_Projects For v_SQL;
它直接使用游标打开构造的查询字符串即可。
注意事项：
A）、构造的SQL语句最后不能带有分号；
B）、SQL语句中对于字符和字符串的条件需要用单引号包括起来
C）、最重要：动态SQL语句需要防止SQL注入攻击。我们采用最简单的办法，只允许一个关键词查询，将关键词中的所有空格去掉。对于多关键词，需要将他们用空格拆开，再构造。
