Re: PL/SQL question

Hi Dana,

try

CURSOR cr_result IS
SELECT id, name
FROM employee
WHERE name IN in_names
and in_names is not null

--
union all
--
SELECT id, name
FROM employee
WHERE 1 = 1
and in_names is null
;

Martin



Dana Jian wrote:


> 

> Hi,

> 

> I need implement a procedure which takes input parameters for limiting

> records in the cursor defined in the proc.

> 1) the input parameter is a string contains the name list of the employee

> which need to be retrieved, like '(Mulan, Tom, Laysee...)"

> 2) If the input parameter is empty, return all the employees in the cursor

> 

> Let's see the simple code,

> 

> PROCEDURE SP_TEST

> (

> in_names      IN   VARCHAR2,

> .....

> CURSOR cr_result IS

> SELECT id, name

> FROM employee

> WHERE name IN in_names;

> 

> The problem is this code doesn't suit my need of #2. A bad idea is just

> conditionally creating several cursor by checking the input parameter---that

> would not be a good one.

> 

> Any good idea?

> 

> Thanks a lot!

> 

> Dana

> djian_at_trasa.com