Home » SQL & PL/SQL » SQL & PL/SQL » suggest a username
icon10.gif  suggest a username [message #213910] Fri, 12 January 2007 14:16 Go to next message
dgarcia59
Messages: 1
Registered: January 2007
Location: San Antonio, TX
Junior Member
I tried posting this on another forum, but didn't get any replies. Thought I'd try this community since it seems much bigger. I recently started writing PL/SQL and need to make a procedure that returns a username suggestion. It takes in a username that the person tried there is already a match in the database. Thus this procedure will truncate numerals off the end and then, starting at 1, increment the suffix until it finds a username that is not taken.

Here is the test code I wrote in PL/SQL Developer.

CREATE OR REPLACE PROCEDURE K_USER_SUGGEST
(
  IN_USERNAME  IN K_USER.KU_USERNAME%TYPE,
  OUT_USERNAME OUT K_USER.KU_USERNAME%TYPE
) IS
I NUMBER;
X NUMBER;
BEGIN
  -- Remove the numbers from the end of the string
  OUT_USERNAME := RTRIM(IN_USERNAME, '0123456789');
  -- Determine if the username passed in was only numerical
  OUT_USERNAME := NVL(OUT_USERNAME, 'Username is num');
  IF OUT_USERNAME != 'Username is num' THEN
    -- Look for the first instance of username### that is not taken
    I := 0;
    X := 1;
    WHILE X > 0 LOOP
    
    I := I + 1;
    SELECT COUNT(KU_USERNAME)
    INTO X
    FROM K_USER
    WHERE KU_USERNAME LIKE OUT_USERNAME || I;
   
    END LOOP;

    OUT_USERNAME := OUT_USERNAME || I;
  END IF;
END K_USER_SUGGEST;



So, is the above the proper way to go about doing this? What would an expert PL/SQL person have done differently?

For the line that says
WHERE KU_USERNAME LIKE OUT_USERNAME || I;
I was surprised that I didn't need to cast or convert I to a character before using it in a string concat. Is that acceptable?

Also, in that same command, using LIKE seemed to run slightly faster the using = in the profiler. I know you have to use LIKE if you have wildcards (such as % and _) in the clause, but is LIKE still preferable to use if you are just doing a straight string comparison?

Thanks for any feedback.

[Updated on: Fri, 12 January 2007 14:17]

Report message to a moderator

Re: suggest a username [message #214126 is a reply to message #213910] Mon, 15 January 2007 01:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If there are no wildcards present in the string, then you would be better off using a direct equality check rather than LIKE, as it will give the CBO a better idea of how many records that predicate is likely to include in the results.

Rather than checking
IF OUT_USERNAME != 'Username is num' THEN
, I would raise a user-defined exception in the case when the username was numeric, and handle that case in the exception block, leaving the rest of the code free to find a new username without being required to keep checking whether the username was invalid.
Re: suggest a username [message #214127 is a reply to message #214126] Mon, 15 January 2007 01:45 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The loop + select count... construction is horrible. Change it to a single select.

MHE
Re: suggest a username [message #214131 is a reply to message #214127] Mon, 15 January 2007 01:53 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Instead of the loop, do something along these lines:
select the max(username) where ku_username like out_username||'%' and the substr(ku_username, length(out_username), 1) between '0' and '9'
Then add 1 to the found postfix.

And change the in_username/out_username combination to an IN OUT parameter, or change it to a function that returns the available username.
Previous Topic: dependent column
Next Topic: How to print space before a number
Goto Forum:
  


Current Time: Sat Dec 10 16:56:45 CST 2016

Total time taken to generate the page: 0.09279 seconds