在使用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注入攻击。我们采用最简单的办法,只允许一个关键词查询,将关键词中的所有空格去掉。对于多关键词,需要将他们用空格拆开,再构造。