Create stored procedure that inserts  new customer into table

ba_trainer
ba_trainer used Ask the Experts™
on
I need to create a stored procudure that inserts a new customer into the customer table.
I am completely unfamiliar with pl/sql can anyone help?
I have included the exact table creation below.
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®

Author

Commented:
Also, if a duplicate or similar customer name exists, a DMBS output line should list the name. If the add is successful, I need it to say "Successful add" or something like that.
Thanks!!
Top Expert 2009
Commented:
Based off of awking00's solution in the related question: http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24580002.html?cid=1135#a24881986


create or replace procedure find_name_match(p_no in number, p_name in varchar2,
    p_attention_name in varchar2, p_type in char, p_address_1 in varchar2, p_address_2 in varchar2,
    p_city_bill in varchar2, p_state_bill in varchar2, p_zipcode_bill in number, p_country_bill 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;
  insert into customer(CUSTOMER_NO, CUSTOMER_NAME, CUSTOMER_TYPE, ATTENTION_NAME, ADDRESS_1_BILL,                                      ADDRESS_2_BILL, CITY_BILL, STATE_BILL, ZIPCODE_BILL, COUNTRY_BILL)
     values(p_no, p_name, p_attention_name, p_type, p_address_1, p_address_2,
            p_city_bill, p_state_bill, p_zipcode_bill, p_country_bill);
  dbms_output.put_line('Successful add');
end;
/

Open in new window

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