like keyword (full table scan)?

From: tone <dmclaude_at_yahoo.com>
Date: 13 Aug 2002 11:59:15 -0700
Message-ID: <5fff533a.0208131059.11c11754@posting.google.com>

hi everyone..

im an intern to a company where oracle is a new addition, so im wondering if they way they do sql is the best way. here is my question. i've included the sample code, but ill put it in english.

::question follows ::
basically, they currently use dynamic sql (concatenate an sql string and execute immediate).. from what i read, that is not so good, because each time the procedure is called, oracle has to reparse (?).. so i thought about using the like keyword. if the search field is specified, then replace the '%' (full table scan) with the provided keyword.

The big question is, is this a better way of doing things? or am i trying to be a smartass.... any opinions?

thanks
asok (dilbert is my buddy!)

--------sample code -------------------->

keep in mind this is sort of pseudo-code.. not exact.. just idea
=====old way============================>

procedure user_search (

ret_code	out		number,
cust_idin	in		user.cust_id%type,
user_idin	in		user.user_id%type,

) as

v_cust_id	user.cust_id%type;
v_user_id	user.user_id%type;

v_sql		varchar2(200);
v_sql1		varchar2(100) := 'SELECT * FROM schema.table ';

v_sql2		varchar2(50)  := ' ORDER BY user_id, cust_id';

v_sql_dyn	varchar2(50);

begin
	v_cust_id := cust_idin;
	v_user_id := user_idin;

	if (v_user_id is NULL) then
		v_sql_dyn := ' cust_id = :1';
	else if (v_cust_id is NULL) then
		v_sql_dyn := ' user_id = :2';
	else
		v_sql_dyn := ' cust_id = :1 AND user_id = :2';
	end if;

	v_sql := v_sql1 || v_sql_dyn || v_sql2;

	EXECUTE IMMEDIATE v_sql using v_cust_id, v_user_id;

	if SQL%ROWCOUNT = 1 then
		ret_code := 0;
	else
		ret_code := 8;
	end if;

return;

end user_search;

=====my way============================>

procedure Search (userin IN INTEGER, custin IN INTEGER, errorcode OUT INTEGER) as
v_user_id VARCHAR2(13);
v_cust_id VARCHAR2(13);
cursor CUR is

		SELECT *
		FROM ippprg.pni
		WHERE acno LIKE v_acno
			AND chkno LIKE v_chkno
			;

begin

IF userin is NULL THEN
v_user_id := '%';
ELSE
v_user_id := userin;
END IF; IF custin is NULL THEN
v_cust_id := '%';
ELSE
v_cust_id := custin;
END IF; errorcode := 0;

for each_row in CUR loop
dbms_output.put_line(each_row.user_id);

        end loop;

end Search;

---------------end sample code -------------> Received on Tue Aug 13 2002 - 13:59:15 CDT