oralce 变量绑定问题请教

Question

本帖最后由 hhwytfliq 于 2010-09-07 13:29:17 编辑

Answer 1

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

Answer 2

应该不会啊,你的语句不就类似下面这样么
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
        20Execution 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

Answer 3

发个排版好看点的.
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> 

Answer 4

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

Answer 5

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

Answer 6

是我没有把问题说清楚,如果单单说的解决你们说的那种情况完全可以这样
empname = decode(v_empname,'',empname ,v_empname)我要实现的之所以要那样绑定,是因为会有动态增加的SQL,我们权限控制是在最后面增加一段SQL语句来实现的

Answer 7

对于输入参数不确定的动态语句，可以使用 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; /

Answer 8

同意。and (V_empbrithday　IS NULL or V_empbrithday = empbrithday);