oralce 变量绑定问题请教

[hhwytfliq]

现在一个项目中使用oracle数据库,为了提高性能要求常用的过程都使用变量绑定,
我们的业务处理都是通过存储过程完成. 如果使用常规的变量绑定书写起来蜚常麻烦

常规方法:
查询user.emp表的数据,可能使用的条件为id,姓名,地址,生日,当输入为0或空时表示存储过程传入的条件不生效

 select　＊ from user.emp 
where (V_empid=0 or V_empid = empid)
and (V_empname='' or V_empname = empname)
and (V_empaddr=0 or V_empaddr = empaddr)
and (V_empbrithday='' or V_empbrithday = empbrithday)
 ; 

这样有时会索引失效,为了使用索引,我们不得这样书

if V_empid0 then 
select　＊ from user.emp 
where (V_empid=0 )
and (V_empname='' or V_empname = empname)
and (V_empaddr=0 or V_empaddr = empaddr)
and (V_empbrithday='' or V_empbrithday = empbrithday) ; 
end if; 

如果emp是一个很大的表,上面有几个索引,就更麻烦了,得每个要使用索引的判断一下把其中的or去掉

现在想到一种办法是使用

--V_LIST是一个数组
if v_empid<>0 then
 V_LIST.EXTEND();
 V_LIST(V_LIST.COUNT) :=  v_empid ;
 v_sql = 'and empid=:1'
    end if;
 if v_empaddr<>'' then
 V_LIST.EXTEND();
 V_LIST(V_LIST.COUNT) := UPPER(v_empaddr);
 v_sql=v_sql||' and empaddr=:2' 
    end if;
  V_SQL:='select * from user.emp where 1=1 ' 
  CASE V_LIST.COUNT
    WHEN 0 THEN
      EXECUTE IMMEDIATE V_SQL;
    WHEN 1 THEN
      EXECUTE IMMEDIATE V_SQL
        USING V_STRLIST(1);
    WHEN 2 THEN
      EXECUTE IMMEDIATE V_SQL
        USING V_LIST(1), V_LIST(2);  
   end case;

[hhwytfliq]

不要沉撒

[minitoy]

用不到索引么?

[hhwytfliq]

补充一下,变量绑定能提高性能,不用多次解析

[hhwytfliq]

比如 empid上有索引,
这样的时候
V_empid=0 or V_empid = empid
索引就失效会走全表扫描

[Diza1986]

用HINT强制使用索引

[minitoy]

应该不会啊,你的语句不就类似下面这样么

SQL> set autotrace on
SQL> select * from emp where 0=7369 or empno=7369;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-80          800.1
        20



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=37)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car
          d=1 Bytes=37)

   2    1     INDEX (UNIQUE SCAN) OF 'PK_EMP' (INDEX (UNIQUE)) (Cost=0
           Card=1)





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        810  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

[minitoy]

发个排版好看点的.

SQL> explain plan for select * from emp where 0=7369 or empno=7369;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 212296133
--------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:0
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:0
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=7369)
 
14 rows selected
 
SQL> 

[hhwytfliq]

先谢谢,你这样不是使用变量绑定了,使用变量的话就会会走全表

[minitoy]

你刚才的语句真正执行的时候会把变量替换成相应的值,最后执行的语句跟上面的语句是类似的,应该不会造成索引失效.索引失效一般是字段 is null这样的条件造成的.
还有上面条件里and (V_empname='' or V_empname = empname)
=''这种不要用,虽然不报错,但是逻辑上是有问题的.''被oracle视为null,而null和null比较是没意义的

[minitoy]

应该改成and (V_empname is null or V_empname = empname)

[hhwytfliq]

是我没有把问题说清楚,如果单单说的解决你们说的那种情况完全可以这样
empname = decode(v_empname,'',empname ,v_empname)

我要实现的之所以要那样绑定,是因为会有动态增加的SQL,我们权限控制是在最后面增加一段SQL语句来实现的

[xman_78tom]

对于输入参数不确定的动态语句，可以使用 dbms_sql 包执行。lz 可以参考以下的例子，看看是否满足要求。

CREATE OR REPLACE PROCEDURE query_employees
(hiredate hr.employees.hire_date%TYPE := null, 
 jobid hr.employees.job_id%TYPE := null, 
 salary hr.employees.salary%TYPE := null,
 empcur OUT SYS_REFCURSOR)
IS
  stmt varchar2(4000);
  curid integer;
  ret integer;
BEGIN
  stmt := 'select * from hr.employees where 1=1 '||
    (case when hiredate is not null then 'and hire_date<:h ' end)||
    (case when jobid is not null then 'and job_id=:j' end)||
    (case when salary is not null then 'and salary>:s' end);
  
  curid := dbms_sql.open_cursor; 

  dbms_sql.parse(curid, stmt, dbms_sql.native); 

  IF hiredate is not null THEN 
    dbms_sql.bind_variable(curid,'h',hiredate); 
  END IF;
  IF jobid is not null THEN 
    dbms_sql.bind_variable(curid,'j',jobid); 
  END IF;
  IF salary is not null THEN 
    dbms_sql.bind_variable(curid,'s',salary); 
  END IF;

  ret := dbms_sql.execute(curid);

-- Convert the cursor number to the cursor variable (oracle 11g).
  empcur := dbms_sql.to_refcursor(curid); 
END; /

[xinxiangsui2008]

[Quote=引用 9 楼 minitoy 的回复:]
你刚才的语句真正执行的时候会把变量替换成相应的值,最后执行的语句跟上面的语句是类似的,应该不会造成索引失效.索引失效一般是字段 is null这样的条件造成的.
还有上面条件里and (V_empname='' or V_empname = empname)
=''这种不要用,虽然不报错,但是逻辑上是有问题的.''被oracle视为null,而null和null比较是没意义的
[/Quote]

同意。

and (V_empbrithday　IS NULL or V_empbrithday = empbrithday);