Please help with PL/SQL code

[AndreaL]

Can anyone help me create a procedure or
anonymous block named New_Member that will
prompt the user for First_Name, Last_Name, Street,
City, Phone, and Valid_Date.
It will insert this data into a table
called S_Member. If this data
is distinct, if it is not already in the
database it will give a message that
gives the new users member id. I have a
sequence generator named s_member_id
that will give the id. If the person is
already in the database it will give a message saying
that this user is already in the database.
Any help will be appreciated. Thanks.
Andrea

 

[scotty7998]

Here is the url for the oracle documentation. If you need further help let me know and I will respond this evening.

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a82950/ch3.htm#1002144

Kevin M. Watford
kwatford@ddyne.com

 

[mkey]

try this code. I have not encluded all the columns you need in your table. I only added the first and the last name. But you could just add the other information as you require;

create or replace procedure new_member
is
name_one varchar2(10);
name_two varchar2(10);
ctr number;
id_number number;

CURSOR C_ONE IS SELECT first_name,last_name FROM table_a;
begin
select '&name1' into name_one from dual;
select '&name2' into name_two from dual;
FOR C_TWO IN C_ONE LOOP
IF C_TWO.FIRST_NAME <> name_one or c_two.last_name <> name_two then
insert into table_a(first_name,last_name) values(name_one,name_two);
id_number := ctr;
dbms_output.put_line(id_number);

else
dbms_output.put_line ('name already exist');
end if;


end loop;
end;
/

hope this helps you out!