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