Link to home
Start Free TrialLog in
Avatar of ba_trainer
ba_trainer

asked on

Check for customer in table

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

Avatar of mrjoltcola
mrjoltcola
Flag of United States of America image

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;
/

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).



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;
/
Avatar of ba_trainer
ba_trainer

ASKER

Can you tell me how do I run from command line? Thanks!
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;
/
OR:

begin
 if findname('smith') is null then
   dbms_output.put_line('null');
 end if;
end;
/
ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@awking00: Nice idea!
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;
/
mrjoltcola,
Thanks for pointing out that I had left off the parameter description.