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