I have a procedure that handles generic searches submitted by user.
User can enter name, hiredate or salary or stock number.
All the data can be fetched from EMP table except when user enters a "Stock Number". If he does, then query needs to lookup in EMP_ORDERS_DETAILS to search for records that match the stock number. For example, if user entered "DVD100", I would need to find all customers who placed orders with that stock number "DVD100".
Would it best to create another query string for this condition (when user enters this field) that JOINS both table EMP and EMP_ORDER_DETAILS like this
--Add this to declaration
l_query_detail varchar2(512) default 'select * from emp a, emp_order_details b where ...'
--Addd this after begin
if (p_stock_no is NOT NULL) then
l_query := l_query_detail;
if ( p_stock_no is NOT NULL )
l_query := l_query ||‘ and stock_no =:p_stock_no'‘;
l_query := l_query || ‘ and (1=1 or :p_stock_no is null) ‘;
....continue code below as normal
create or replace procedure my_new_procedure
( p_ename in varchar2 default NULL,
p_hiredate in date default NULL,
p_sal in number default NULL)
l_query varchar2(512) default ‘select * from emp’;
cursor l_template is select * from emp;
if ( p_ename is NOT NULL )
l_query := l_query ||‘ where ename like ‘’%’’||:p_ename||’’%’’ ‘;
l_query := l_query ||‘ where (1=1 or :p_ename is null) ‘;
if ( p_hiredate is NOT NULL )
l_query := l_query ||‘ and hiredate > :p_hiredate ‘;
l_query := l_query || ‘ and (1=1 or :p_hiredate is null) ‘;
if ( p_sal is NOT NULL )
l_query := l_query || ‘ and sal > :p_sal ‘;
l_query := l_query || ‘ and (1=1 or :p_sal is null) ‘;
dbms_output.put_line ( l_query );
open l_cursor for l_query using p_ename, p_hiredate, p_sal;
fetch l_cursor into l_rec;
exit when l_cursor%notfound;
dbms_output.put_line ( l_rec.ename || ‘,’ || l_rec.hiredate || ‘,’ || l_rec.sal );