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
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'))
);
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;
/
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 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;
/
begin
if findname('smith') is null then
dbms_output.put_line('null
end if;
end;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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.cus tomer_name );
end if;
end loop;
end;
/
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.cus
end if;
end loop;
end;
/
mrjoltcola,
Thanks for pointing out that I had left off the parameter description.
Thanks for pointing out that I had left off the parameter description.
as
findname varchar2(20);
begin
select customer_name into findname from customer;
return findname;
end;
/