this code takes input for zipcode, city, and state and then inserts that into the table created Address. Prior to inserting data it will check if the zipcode is already in the table, if so calling procedure(error) to display an error code.
Im getting an error code pls-00103: encountered the symbol "CREATE" when trying to execute the code. Here is my code so far. Thanks for any help in advance.
drop table address;
create table address(zipcode NUMBER, city varchar2(30), state varchar2(20));
create or replace procedure error as
begin
dbms_output.put_line('Error Zip Code already found in table');
end error;
declare
zzip number;
ccity varchar2(30);
sstate varchar2(30);
create or replace procedure location(p_zipcode NUMBER,
p_city varchar2,
p_state varchar2) is
zip address.zipcode%type;
cit address.city%type;
st address.state%type;
begin
select count(*) from address into zip where zipcode = zip;
if any_rows_found then
error;
else
Insert into address values(zip, cit, st);
end if;
end location;
begin
select &zipcode into zzip from dual;
select &city into ccity from dual;
select &state into sstate from dual;
procedure location(zzip, ccity, sstate);
end;
/
I'm not sure what you're trying to do, but the following may be closer to what you had in mind:
drop table address;
create table address(zipcode NUMBER, city varchar2(30), state varchar2(20));
declare
zzip number;
ccity varchar2(30);
sstate varchar2(30);
procedure error is
begin
dbms_output.put_line('Error Zip Code already found in table');
end error;
procedure location(p_zipcode NUMBER, p_city varchar2, p_state varchar2) is
zip_count NUMBER;
begin
select count(*)
into zip_count
from address
where zipcode = p_zipcode;
if zip_count > 0 then
error;
else
Insert into address
(zipcode, city, state)
values
(p_zipcode, p_city, p_state);
end if;
end location;
begin
select &zipcode into zzip from dual;
select &city into ccity from dual;
select &state into sstate from dual;
location(zzip, ccity, sstate);
end;
/
Best of luck.