oracle 执行带参数的sql语句_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 为自定义游标类型

PROCEDUREG_Search(P_YearNOINNUMBER,

                              P_ControlTypeINNUMBER,

                              P_ProgressINCHAR,

                              P_DepartIDINVARCHAR2,

                              P_ProjectNameINNVARCHAR2,

                              C_Projects    OUT RefCursor)IS        e_ErrInterruption EXCEPTION;

        v_ErrIDNUMBER;--Variable to hold the errorlog idv_ErrCodeNUMBER;--Variable to hold the error message codev_ErrTextVARCHAR2(512);--Variable to hold the error message textv_ErrProcVARCHAR2(50) :='G_Search';

        v_DepartIDVARCHAR2(16);

        v_ProjectName NVARCHAR2(128);

        v_SQLVARCHAR2(512);

        v_WhereVARCHAR2(256);

BEGIN    

        v_SQL   :='SELECT PROJECTID, PARENTID, PROJECTNAME';

        v_SQL   :=v_SQL||'FROM PROJECTS A';

        v_Where :='Where';

    

--年度IFP_YearNO<9999THEN            v_Where :=v_Where||'A.YearNO ='||P_YearNO||'And';

ELSE            v_Where :=v_Where||'A.YearNO 

ENDIF;

--控制类别IFP_ControlType=9THEN            v_Where :=v_Where||'A.ControlType 

ELSE            v_Where :=v_Where||'A.ControlType ='||P_ControlType||'And';

ENDIF;

--进度IFP_Progress            v_Where :=v_Where||'A.Progress ='''||P_Progress||'''And';

ELSE            v_Where :=v_Where||'A.Progress 

ENDIF;

    

IFTRIM(P_DepartID)<>'%'THEN            v_Where :=v_Where||'A.DepartID ='''||P_DepartID||'''And';

ELSE            v_Where :=v_Where||'A.DepartID Like'''||P_DepartID||'''And';

ENDIF;

--项目名称v_ProjectName :=NVL(P_ProjectName,

'%');

IFv_ProjectName<>'%'THEN            v_ProjectName :='%'||P_ProjectName||'%';

ENDIF;

        v_Where :=v_Where||'A.ProjectName Like'||''''||v_ProjectName||'''And';

    

    

        v_SQL :=v_SQL||v_Where;

    

OPENC_PROJECTSFORv_SQL;

    

--COMMIT;EXCEPTION

--根据需要定义错误异常WHENOTHERSTHEN--ROLLBACK;v_ErrID   :=SQLCODE;

            v_ErrText :=SQLERRM;

            raise_application_error(v_ErrID,

                                    v_ErrText);

ENDG_Search;

该方法只需要传递给存储过程一些参数，使用游标返回数据。参数传递效率较高，而且业务逻辑在存储过程中，调整比较方便。该方法关键的在下面的语句：

Open C_Projects For v_SQL;

它直接使用游标打开构造的查询字符串即可。

注意事项：

A)、构造的SQL语句最后不能带有分号；

B)、SQL语句中对于字符和字符串的条件需要用单引号包括起来

C)、最重要：动态SQL语句需要防止SQL注入攻击。我们采用最简单的办法，只允许一个关键词查询，将关键词中的所有空格去掉。对于多关键词，需要将他们用空格拆开，再构造。