Check for customer in table

ba_trainer
ba_trainer used Ask the Experts™
on
I need to create a pl/sql procudure that will check to see if the customer name is already there, and if it exists return the customer name.

I need to look for possible matches, not an just an exact match.
I included the customer table details below.
Can anyone help? Thanks! Beth
CREATE TABLE CUSTOMER                           
  ( CUSTOMER_NO NUMBER(8,0) NOT NULL    
  , CUSTOMER_NAME   VARCHAR2(20) NOT NULL
  , CUSTOMER_TYPE CHAR(1)
  , ATTENTION_NAME VARCHAR2(20)
  , ADDRESS_1_BILL VARCHAR2(20)
  , ADDRESS_2_BILL VARCHAR2(20)
  , CITY_BILL VARCHAR2(20)
  , STATE_BILL VARCHAR2(2)
  , ZIPCODE_BILL NUMBER(5,0)
  , COUNTRY_BILL VARCHAR2(10)
  , CONSTRAINT CUSTOMER_PK PRIMARY KEY(CUSTOMER_NO)           
  , CONSTRAINT CUST_TYPE CHECK (CUSTOMER_TYPE IN ('B', 'C', 'S'))
);

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
create or replace function findname(name in varchar2) return varchar2
as
  findname varchar2(20);
begin
  select customer_name into findname from customer;
  return findname;
end;
/

Top Expert 2009

Commented:
oops skip that. Actually that is not what you want, it could return multiple rows, i had customer_id in my head when I wrote that (too quickly).


Top Expert 2009

Commented:

create or replace function findname(name in varchar2) return varchar2
as
 customer_no integer;
begin
  select max(customer_no) into customer_no from customer where customer_name = name;
  if customer_no is not null then
     return name;
  else
     return null;
  end if;
end;
/
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Can you tell me how do I run from command line? Thanks!
Top Expert 2009

Commented:
select findname('smith') from dual;


Or in a PL/SQL block:

declare
  name varchar2(20);
begin
  name := findname('smith');
  if name is null then
      ...
  end if;
end;
/
Top Expert 2009

Commented:
OR:

begin
 if findname('smith') is null then
   dbms_output.put_line('null');
 end if;
end;
/
Information Technology Specialist
Commented:
This should work if the names sound the same but are spelled differently.
create or replace procedure find_name_match(p_name) is
v_name  varchar2(50);
begin
v_name := p_name;
for n in
(select customer_name from customer)
loop
if soundex(v_name) = soundex(n.customer_name) then
 dbms_output.put_line(n.customer_name);
end if;
end loop;
end;
/
Top Expert 2009

Commented:
@awking00: Nice idea!
Top Expert 2009

Commented:
FYI awking00's good solution had a compile error. Here is the corrected.

create or replace procedure find_name_match(p_name in varchar2) is
v_name  varchar2(50);
begin
v_name := p_name;
for n in
(select customer_name from customer)
loop
if soundex(v_name) = soundex(n.customer_name) then
 dbms_output.put_line(n.customer_name);
end if;
end loop;
end;
/
awking00Information Technology Specialist

Commented:
mrjoltcola,
Thanks for pointing out that I had left off the parameter description.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial