站点图标 IDC铺

关于Oracle动态SQL语句的简单执行

在使用ODP.NET进行Oracle编程时,有时候SQL语句非常复杂,需要采用动态构造查询语句的情况,有两种方法可以构造动态的SQL语句,并执行返回结果集。

1、在数据访问层构造SQL语句

例如下面的语句,将构造完整的SQL语句赋值给CommandText,再传递到数据库进行执行,返回结果集。

loadCommand.CommandType = CommandType.Text
loadCommand.CommandText = “Select * From Users”
dataAdapter .SelectCommand = loadCommand
dataAdapter . Fill(data)

dataAdapter .SelectCommand = loadCommand
dataAdapter . Fill(data)

该方法需要将整个SQL的构造过程放在DataAccess层,业务逻辑发生变化,修改不方便,而且每次查询需要传递给数据库很长的查询字符串,传递参数的效率也不高。

 

2、在存储过程中构造动态SQL语句并执行

以下为一个完整的事例(经过删减),其中RefCursor 为自定义游标类型

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

退出移动版