存储过程动态Sql效率问题？

游北亮
博客专家认证
2008-11-28 09:40:52
目前我的程序是使用拼接Sql的方式，例如：
string sql = "select * from tb1 where 1=1 ";

if(id != null){

  sql += "and id = :vId ";

}

if(name != null){

  sql += "and name = :vName ";

}


因为这样执行的速度比较慢，就想改用存储过程
不过如果在存储过程里，岂不是也要使用动态Sql，那效率是不是也是一样的低呢？
比如：
CREATE OR REPLACE PROCEDURE P_GetData(vId in varchar2,vName in varchar2) IS

    vSql Varchar2(1000);

BEGIN

    vSql := 'select * from tb1 where 1=1 ';

    if vId is not null then 

       vSql := vSql || 'and id = ' || vId || '';

    end if;

    if vName is not null then 

       vSql := vSql || 'and name = ' || vName || '';

    end if;

     execute immediate vSql;

END P_GetData;


那这样效率应该也是一样低吧
请各位老大给予指导，应该如何提高效率？
总不能这样吧：
CREATE OR REPLACE PROCEDURE P_GetData(vId in varchar2,vName in varchar2) IS

       tmp renyb%ROWTYPE;

BEGIN

    if vId is not null and vName is not null then 

       select * into tmp from renyb where id = vId and name = vName;

    else 

       if vId is not null then

          select * into tmp from renyb where id = vId;

       end if;

       if vName is not null then

          select * into tmp from renyb where name = vName;

       end if;

    end if;

END P_GetData;
...全文
885 26 打赏 收藏 转发到动态 举报
写回复
用AI写文章
26 条回复
切换为时间正序
请发表友善的回复…
发表回复
fenix_111 2012-07-16
  • 打赏
  • 举报
 回复
oracle真是深啊!学习学习
lijiajing 2009-08-06
  • 打赏
  • 举报
 回复
看来要学的东西还真不少嘿嘿!
sleepzzzzz 2008-11-28
  • 打赏
  • 举报
 回复
又写了两次，破网！
sleepzzzzz 2008-11-28
  • 打赏
  • 举报
 回复
恩，注入攻击确实是个存在的问题．
只要有sql的地方都要面对这个问题，但不见得所有的查询都分别做成一个procedure吧？

防注入攻击是要从很多层面综合去解决的，比如编程人员代码是否足够严谨，你的web架构设计等等，太深了呵呵我也不太懂．


[Quote=引用 21 楼 lpc19598188 的回复:]
引用 20 楼 sleepzzzzz 的回复:

在前台(C#)去判断是否为空的问题，是最佳的解决办法，怎么没有和我有相同的观点呢？尽量不要集中在db server端去判断！这是一般设计的原则．

打错成Ｏ了．


前台拼容易被注入攻击, 后台拼或者传参数不会被攻击, 呵呵
否则, 在前台需要N多的判断过滤

好多不严谨的网站, 都可以插一个flash进飘着 (:
[/Quote]
sleepzzzzz 2008-11-28
  • 打赏
  • 举报
 回复
恩，你说注入攻击确实是个存在的问题，
只要有sql的地方都要面对这个问题，但不见得所有的查询都分别做成一个procedure吧？

防注入攻击，还得看前台编程人员的代码严谨性和你的架构设计问题，这个可以通过多方面去防备的．



[Quote=引用 21 楼 lpc19598188 的回复:]
引用 20 楼 sleepzzzzz 的回复:

在前台(C#)去判断是否为空的问题，是最佳的解决办法，怎么没有和我有相同的观点呢？尽量不要集中在db server端去判断！这是一般设计的原则．

打错成Ｏ了．


前台拼容易被注入攻击, 后台拼或者传参数不会被攻击, 呵呵
否则, 在前台需要N多的判断过滤

好多不严谨的网站, 都可以插一个flash进飘着 (:
[/Quote]
又是违规昵称 2008-11-28
  • 打赏
  • 举报
 回复
[Quote=引用 20 楼 sleepzzzzz 的回复:]

在前台(C#)去判断是否为空的问题，是最佳的解决办法，怎么没有和我有相同的观点呢？尽量不要集中在db server端去判断！这是一般设计的原则．

打错成Ｏ了．
[/Quote]

前台拼容易被注入攻击, 后台拼或者传参数不会被攻击, 呵呵
否则, 在前台需要N多的判断过滤

好多不严谨的网站, 都可以插一个flash进飘着 (:
sleepzzzzz 2008-11-28
  • 打赏
  • 举报
 回复 


string sql = "select * from tb1 where 1=1 ";

if(id != null){

  sql += "and id = :vId ";

}

if(name != null){

  sql += "and name = :vName ";

}



在前台(C#)去判断是否为空的问题，是最佳的解决办法，怎么没有和我有相同的观点呢？尽量不要集中在db server端去判断！这是一般设计的原则．

打错成Ｏ了．
sleepzzzzz 2008-11-28
  • 打赏
  • 举报
 回复 

string sql = "select * from tb1 where 1=1 ";

if(id != null){

  sql += "and id = :vId ";

}

if(name != null){

  sql += "and name = :vName ";

}




在前台(0#)去判断是否为空的问题，是最佳的解决办法，怎么没有和我有相同的观点呢？尽量不要集中在db server端去判断！这是一般设计的原则．

范佩西_11 2008-11-28
  • 打赏
  • 举报
 回复
建议你直接来一个参数
CREATE OR REPLACE PROCEDURE P_GetData(v_instr varchar2) IS

  vSql Varchar2(1000);

  emptype emp%rowtype;

BEGIN

  vSql := 'select * from emp where  '||v_instr;



  execute immediate vSql into emptype;

  dbms_output.put_line(emptype.empno||emptype.ename);

END P_GetData;

SQL> exec P_GetData('empno=1 and deptno=20 ');

 

1SMITH

 

PL/SQL procedure successfully completed
又是违规昵称 2008-11-28
  • 打赏
  • 举报
 回复 





SQL> create index i1 on emp(ename);



Index created.



SQL> select /*+ index(emp i1)*/ empno,ename from emp where (ename=:ename or :ename is null);



     EMPNO ENAME

---------- ----------

      7902 FORD





Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932



--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     2 |    20 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    20 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   1 - filter(:ENAME IS NULL OR "ENAME"=:ENAME)



SQL> select /*+rule*/ empno,ename from emp where (ename=:ename or :ename is null);



     EMPNO ENAME

---------- ----------

      7902 FORD





Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932



----------------------------------

| Id  | Operation         | Name |

----------------------------------

|   0 | SELECT STATEMENT  |      |

|*  1 |  TABLE ACCESS FULL| EMP  |

----------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   1 - filter("ENAME"=:ENAME OR :ENAME IS NULL)



Note

-----

   - rule based optimizer used (consider using cbo)



SQL> select empno,ename from emp where ename=:ename;



     EMPNO ENAME

---------- ----------

      7902 FORD





Execution Plan

----------------------------------------------------------

Plan hash value: 727703203



------------------------------------------------------------------------------------

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |      |     1 |    10 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP  |     1 |    10 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | I1   |     1 |       |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   2 - access("ENAME"=:ENAME)


强制索引还是用不上

实际上10g下全表扫描性能也不坏, 并行读, 分区表, 解决的办法仍然很多

又是违规昵称 2008-11-28
  • 打赏
  • 举报
 回复
我上面说错了, ename没有空值的
又是违规昵称 2008-11-28
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 codearts 的回复:]
这种写法在sql server下面不错的，但是在oracle里似乎效率有点问题，oracle不进行断路径求值，有点郁闷.

上面的测试可以看出，本来可以用索引的，但是加了一个or :x is null之后，cbo就选择了全表扫描

[/Quote]

空值不入b-tree索引, oracle准则

楼主可以在数据库中设置非空约束和默认值, 如果为空, 则填'NONE'
游北亮 2008-11-28
  • 打赏
  • 举报
 回复
汗，那有没有更好的解决方案呢？

现在这个项目中，检索的字段很多
也就是where的条件很多


[Quote=引用 13 楼 codearts 的回复:]
这种写法在sql server下面不错的，但是在oracle里似乎效率有点问题，oracle不进行断路径求值，有点郁闷.

上面的测试可以看出，本来可以用索引的，但是加了一个or :x is null之后，cbo就选择了全表扫描
[/Quote]
codearts 2008-11-28
  • 打赏
  • 举报
 回复
这种写法在sql server下面不错的，但是在oracle里似乎效率有点问题，oracle不进行断路径求值，有点郁闷.

上面的测试可以看出，本来可以用索引的，但是加了一个or :x is null之后，cbo就选择了全表扫描
codearts 2008-11-28
  • 打赏
  • 举报
 回复
2楼的写法：

select empno,ename from emp where (ename=:ename or :ename is null);

这种写法在sql下面不错的，但是在oracle里似乎效率有点问题，oracle不进行断路径求值，有点郁闷.

scott@ORA1> select * from emp where ename=:x OR :x is null;



     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20





执行计划

----------------------------------------------------------

Plan hash value: 3956160932



--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     2 |    74 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    74 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   1 - filter(:X IS NULL OR "ENAME"=:X)





统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          8  consistent gets

          0  physical reads

          0  redo size

        718  bytes sent via SQL*Net to client

        377  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed



scott@ORA1> select * from emp where ename=:x;



     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20





执行计划

----------------------------------------------------------

Plan hash value: 79656022



--------------------------------------------------------------------------------------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |     1 |    37 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |     1 |    37 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_EMP_NAME |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   2 - access("ENAME"=:X)





统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        722  bytes sent via SQL*Net to client

        377  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed
又是违规昵称 2008-11-28
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 youbl 的回复:]
至于我觉得2楼 where (ename=:ename or :ename is null) 不错的原因，请参考我的一篇转载博客：
http://blog.csdn.net/youbl/archive/2008/11/12/3279608.aspx
尽量使用相同的Sql

不知道这个理论有没有问题呢？不过我测试确实是如此，同一个Sql，执行第一遍很慢，第二遍就很快了。
[/Quote]

使用相同的sql要快, 同一个sql只需硬解析一次, 以后就直接调用了
如果少了一个条件, 下次要重新解析的
游北亮 2008-11-28
  • 打赏
  • 举报
 回复
至于我觉得2楼 where (ename=:ename or :ename is null) 不错的原因，请参考我的一篇转载博客：
http://blog.csdn.net/youbl/archive/2008/11/12/3279608.aspx
尽量使用相同的Sql

不知道这个理论有没有问题呢？不过我测试确实是如此，同一个Sql，执行第一遍很慢，第二遍就很快了。
游北亮 2008-11-28
  • 打赏
  • 举报
 回复
当然可能为空了，为什么不能为空？
[Quote=引用 7 楼 linzhangs 的回复:]
语法不对阿。你调用存储过程时候肯定两个参数都不为空的阿
[/Quote]
junying2yu 2008-11-28
  • 打赏
  • 举报
 回复
嗯,关注,学习了
范佩西_11 2008-11-28
  • 打赏
  • 举报
 回复
语法不对阿。你调用存储过程时候肯定两个参数都不为空的阿
加载更多回复（6）
3天掌握SQL Server 2012 数据库
SQL Server是由Microsoft开发和推广...2012版本演示数据库的安装使用，SQL(Structured Query Language)语言、T-SQL语言，数据库的增删改查、函数、存储过程、事务等重要知识点，让学员轻松入门并快速掌握关系型数据库！
为什么存储过程比sql语句效率高？
存储过程经过预编译处理 而SQL查询没有 ...如果是只执行一次的话,存储过程不一定比SQL语句效率高.因为存储过程有个预先编译的过程.就是.启动服务器后或者第一次执行后(可以设置).就可以把存储过程加载到高速缓存中,这样
SQL系列——存储过程
注意事项可能遇到的问题：使用语法（Mysql）基础用法创建删除调用参数游标的使用基本用法3中循环方式 while, repeat, loop,handler使用示例：常用系统存储过程 存储过程基础 ？什么是存储过程 就是写在SQL中的方法，...
存储过程中执行动态Sql语句
存储过程中执行动态Sql语句 　MSSQL为我们提供了两种动态执行SQL语句的命令，分别是EXEC和sp_executesql;通常,sp_executesql则更具有优势，它提供了输入输出接口，而EXEC没有。还有一个最大的好处就是利用sp_...
SQL Server 2012 存储过程
文章目录1.存储过程2.无参存储过程3.带入参存储过程4.带出参存储过程5....使用存储过程可以实现代码的复用、提高运行效率、降低通信流量以及方便实施业务规则。 1.系统存储过程： SQL Server内置存储过程，...
Oracle

17,086

社区成员

55,238

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章