关于ORACLE存储过程的问题。

Question

select * from table 
where 1=1 and ...

Answer 1

参考下面的列：declare 
   type c is ref cursor;
   c1   c;
   r1   test%rowtype;
   vsql  varchar2(1000);
begin
  vsql :='select * from test' || ' where rem =''111'' ';
  dbms_output.put_line(vsql);
  open c1 for vsql;
  loop
     fetch c1 into r1;
     exit when  c1%notfound;
     dbms_output.put_line(r1.rem);
  end loop;
end;

Answer 2

I'll demonstrate below.  I'll write a write that will print out some EMP data.  
This routine will take upto 3 inputs to constrain the result set.  I want to 
have upto 8 different cursors possible hereo 1 with NO where clause (all inputs null)
o 3 with a single predicate
o 3 with "pairs" of predicate conditions
o 1 with all three predicate conditionsAdditionally, since the use of BIND VARIABLES is one of the MOST important 
things in programming Oracle -- I'll want to make sure I use them as well.  This 
will be trickly since I don't know if I'll have 0, 1, 2, or 3 of them until 
runtime.  I'll use an application context to solve that problem.Here is a sample implementation:
[email protected]> create or replace context MY_CTX using MY_PROCEDURE
  2  /Context created.That created our application context and bound it to our yet to be created 
procedure "MY_PROCEDURE".  Note that only MY_PROCEDURE will be able to set 
values in this context.  See http://osi.oracle.com/~tkyte/article2/index.html
for more info on application contexts and their [email protected]> create or replace 
     procedure p ( p_str in varchar2 )
  2  is
  3     l_str   long := p_str || chr(10);
  4     l_piece long;
  5     n       number;
  6  begin
  7      loop
  8          exit when l_str is null;
  9          n := instr( l_str, chr(10) );
 10          l_piece := substr( l_str, 1, n-1 );
 11          l_str   := substr( l_str, n+1 );
 12             loop
 13                exit when l_piece is null;
 14                dbms_output.put_line( substr( l_piece, 1, 
                                                        250 ) );
 15                l_piece := substr( l_piece, 251 );
 16          end loop;
 17     end loop;
 18  end;
 19  /Procedure created.
P is just a little procedure I use to print things out nicer then dbms_output 
would.  I use it below to dump the dynamically generated query so we can see 
what was built for each execution.  It is not really relevant to the example, 
just part of the [email protected]> create or replace
  2  procedure my_procedure( p_ename   in varchar2 default NULL,
  3                          p_hiredate  in date default NULL,
  4                          p_sal       in number default NULL)
  5  as
  6      type rc is REF CURSOR;
  7  
  8      l_cursor rc;
  9      l_query  varchar2(512)
 10               default 'select * from emp where 1 = 1 ';
 11  
 12      cursor l_template is select * from emp;
 13      l_rec  l_template%rowtype;
 14  Here I use what I call a "TEMPLATE" cursor.  I like to use these with my ref 
cursors.  I use them to define a record to fetch into.  Here, in this simple 
example, I could have skipped it and just defined l_rec as EMP%rowtype -- but I 
wanted to show how this would work if you didn't select * from a single table 
but had many columns from many tables.  This just helps me create a nice record. 
 The template query ONLY has a SELECT and a FROM.  I never put a WHERE clause on 
it (even when joining) since I never use it any where.  I just use it to get the 
default datatypes, names and so on for a record definition right below it. 15  begin
 16  
 17      if ( p_ename is NOT NULL ) then
 18          dbms_session.set_context( 'MY_CTX', 'ENAME',
 19                                    '%'||upper(p_ename)||'%');
 20          l_query := l_query ||
 21               ' and ename like
 22                 sys_context( ''MY_CTX'', ''ENAME'' ) ';
 23      end if;
 24  for each input -- i'm inspecting it to see if it is non-null.  If it is, I add 
to the where clause and set the value in the context.  Notice how in the where 
clause -- I always use the SYS_CONTEXT function.  I NEVER put the literal value 
in to the query (that would be very bad and would trash the shared pool -- very 
extremely important to use bind variables).  Note also the use of '' to get a 
single ' into the where clause! 25      if ( p_hiredate is NOT NULL ) then
 26          dbms_session.set_context( 'MY_CTX', 'HIREDATE',
 27                    to_char(p_hiredate,'yyyymmddhh24miss'));
 28          l_query := l_query ||
 29                ' and hiredate >
 30                    to_date(
 31                       sys_context( ''MY_CTX'',
 32                                    ''HIREDATE'' ),
 33                       ''yyyymmddhh24miss'') ';
 34      end if;Note here how I am careful to preserve the date and time component -- if 
necessary!  Always wrap the sys_context in a TO_DATE call if you are comparing 
to a DATE to avoid implicit conversions in the query at runtime!
 35  
 36      if ( p_sal is NOT NULL ) then
 37          dbms_session.set_context( 'MY_CTX', 'SAL', p_sal);
 38          l_query := l_query ||
 39                ' and sal >
 40                   to_number(
 41                       sys_context( ''MY_CTX'',
 42                                    ''SAL'' )
 43                             ) ';
 44      end if;
 45  Same caveat for the NUMBER here.  Use TO_NUMBER to avoid IMPLICIT conversions 46      p( l_query );
 47  
 48      open l_cursor for l_query;
 49  
 50      loop
 51          fetch l_cursor into l_rec;
 52          exit when l_cursor%notfound;
 53  
 54          dbms_output.put_line( l_rec.ename || ',' ||
 55                                l_rec.hiredate || ',' ||
 56                                l_rec.sal );
 57      end loop;
 58  
 59      close l_cursor;
 60  end;
 61  /Procedure created.
 and that is it.  I now have a routine that will open 1 of 8 possible 
different cursors.  Here is a small test run just to see how it works

Answer 3

declare 
   type c is ref cursor;
   c1   c;
   r1   test%rowtype;
   vsql  varchar2(1000);
begin
  vsql :='select * from test' || ' where rem =''111'' ';
  dbms_output.put_line(vsql);
  open c1 for vsql;
  loop
     fetch c1 into r1;
     exit when  c1%notfound;
     dbms_output.put_line(r1.rem);
  end loop;
end;
---------------------------------"fetch顺序出错"！！不解

Answer 4

create table test (rem  vachar2(5));

Answer 5

参考这个吧:
http://expert.csdn.net/Expert/topic/2492/2492452.xml?temp=6.695193E-02

Answer 6

大家搞这么复杂!
如果要求不高的话下面这样算了：
 procedure procname(p1 in varchar2,p2 in number,p3 in date) is
 begin
   select * from ....
    where (p1 is null or (p1 is not null and col1=p1)) --参数为空,全查
         and (p2 is null or (p2 is not null and col2=p2))
         and (p3 is null or (p3 is not null and col2=p3))
........

Answer 7

谢谢大家的帮忙。我用了zuhuisu(我是谁) 的方法实现了我的想法。谢谢！