Re: Dynamic SQL-Command in PL/SQL

From: MBPP <mpacheco_at_directnet.com.br>
Date: 25 Feb 2005 06:56:49 -0800
Message-ID: <1109343409.287545.176200@g14g2000cwa.googlegroups.com>

SQL> create table t1 (c1 number);

Table created.

SQL>
SQL> insert into t1 values (1);

1 row created.

SQL> insert into t1 values (2);

1 row created.

SQL> insert into t1 values (3);

1 row created.

SQL>
SQL> set serveroutput on
SQL>
SQL> declare
  2
  3     p1 number := null;

  4
  5 begin
  6
  7 for r1 in (select c1 from t1 where p1 is null or (p1 is not null and c1 = p1)) loop

  8        dbms_output.put_line(r1.c1);
  9     end loop;

 10
 11 end;
 12 /
1
2
3

PL/SQL procedure successfully completed.

SQL>
SQL> declare
  2
  3 p1 number := 2;
  4
  5 begin
  6
  7 for r1 in (select c1 from t1 where p1 is null or (p1 is not null and c1 = p1)) loop

  8        dbms_output.put_line(r1.c1);
  9     end loop;

 10
 11 end;
 12 /
2

PL/SQL procedure successfully completed.

SQL>
SQL> drop table t1;

Table dropped. Received on Fri Feb 25 2005 - 08:56:49 CST