Home » SQL & PL/SQL » SQL & PL/SQL » Need Opinion on the Better Approach (10g)
Need Opinion on the Better Approach [message #412203] Wed, 08 July 2009 03:58 Go to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
Hi All,

I am new to the Oracle..I have been assigned a task where in the front end application they use DOT NET and Back end they were using both SQL Server and oracle..

I was given a task to write a stored procedure which should display the records from the table based on the input parameters and if the i/p params are null then all the records from the table should be displayed and will be called from DOT NET script and they need the o/p as a result/record set...

I have written the stored procedure in the below way...please advise me whether I am following in the right way as there is no one guide me here...


CREATE OR REPLACE PROCEDURE Emp_Sp (		
		empid			Emp.eno%TYPE,
		ename			Emp.name%TYPE,
		ret_cursor		OUT SYS_REFCURSOR) 
IS  
	l_empid		Emp.eno%TYPE;
	l_ename		Emp.name%TYPE;
BEGIN
      
      l_empid	:=	empid;
	l_ename	:=	ename;      
     
      OPEN ret_cursor FOR 
       	SELECT eno,
			 name,
			 CreatedBy,
			 CreatedDate,
			 UpdatedBy,
			 UpdatedDate
		 FROM	Emp		
		WHERE eno 	LIKE 	NVL(l_empid,'%') 
		AND	name 	LIKE	NVL(l_ename,'%');
		
		COMMIT;

END Emp_Sp ;
/


Any suggestions will be helpful.

Thanks,
Hemanth

[Updated on: Wed, 08 July 2009 03:59]

Report message to a moderator

Re: Need Opinion on the Better Approach [message #412211 is a reply to message #412203] Wed, 08 July 2009 04:14 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well the commit is pointless.
The local variables aren't necessary - just use the parameters directly.

I'd consider multiple selects, so if the parameters are null open the ref_cursor for a select with no where clause, otherwise open the cursor for a select with the where clause matching the parameters. That way you don't need the like or nvl.
Re: Need Opinion on the Better Approach [message #412213 is a reply to message #412203] Wed, 08 July 2009 04:21 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
if the i/p params are null then all the records from the table should be displayed


where are you satisfying this in your code
Use the where clause something like this
		
WHERE (eno 	LIKE 	NVL(l_empid,'%') OR l_empid IS NULL)
AND   (name 	LIKE	NVL(l_ename,'%') OR l_ename IS NULL);
Re: Need Opinion on the Better Approach [message #412221 is a reply to message #412213] Wed, 08 July 2009 04:34 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
ayush_anand wrote on Wed, 08 July 2009 10:21
Quote:
if the i/p params are null then all the records from the table should be displayed


where are you satisfying this in your code
Use the where clause something like this
		
WHERE (eno 	LIKE 	NVL(l_empid,'%') OR l_empid IS NULL)
AND   (name 	LIKE	NVL(l_ename,'%') OR l_ename IS NULL);



He has satisfied it.
What do you think
name 	LIKE	NVL(l_ename,'%');

does?
Re: Need Opinion on the Better Approach [message #412233 is a reply to message #412221] Wed, 08 July 2009 05:47 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
apologies
Re: Need Opinion on the Better Approach [message #412378 is a reply to message #412233] Thu, 09 July 2009 00:57 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
@cookiemonster

Well OP has not mentioned if Eno and name field allows null or not because if they allow null then name like nvl(l_ename,'%') will not give all the rows


SQL>describe employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

SQL>variable phone_no varchar2(20)
SQL>exec :phone_no := null;

PL/SQL procedure successfully completed.

SQL>select count(*) from employees where phone_number like nvl(:phone_no,'%');

  COUNT(*)
----------
       106

1 row selected.

SQL>select count(*) from employees;

  COUNT(*)
----------
       107

1 row selected.

SQL>select count(*) from employees where phone_number is null;

  COUNT(*)
----------
         1

1 row selected.

SQL>

SQL>select count(*) from employees where phone_number like nvl(:phone_no,'%') or
 :phone_no is null;

  COUNT(*)
----------
       107

1 row selected.





@hemanth123 You can read this link for an example on this

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1669972300346534908

[Update] Added Tom's link

[Updated on: Thu, 09 July 2009 01:35]

Report message to a moderator

Re: Need Opinion on the Better Approach [message #412415 is a reply to message #412203] Thu, 09 July 2009 04:05 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Good point.
Obviously didn't think that one through properly. Though I suspect I just assumed they'd be not null.
Re: Need Opinion on the Better Approach [message #412627 is a reply to message #412203] Fri, 10 July 2009 03:28 Go to previous message
Hemanth123
Messages: 56
Registered: April 2009
Member
Hi,

Thank you very much for all your opinions...
@bonker

Eno and Name fields allow nulls..sorry for missing that..

Thanks,
Hemanth
Previous Topic: Superscript data
Next Topic: merging 2 databases with all constraints
Goto Forum:
  


Current Time: Mon Dec 05 12:49:01 CST 2016

Total time taken to generate the page: 0.10172 seconds