Class > Pr Pl Mail

create table tb_prmail (num_uid number(5), vch_uname varchar2(15), vch_passw varchar2(15), vch_cpassw varchar2(15), vch_name varchar2(15), dt_dob date, vch_addr varchar2(15), vch_state varchar2(15), vch_country varchar2(15), num_pinc number(7), num_phone number(10), vch_petname varchar2(15));

-------------->original create or replace procedure pro_prmail(uid in number,uname in varchar,pass in varchar,cpass in varchar,name in varchar, dob in date,addre in varchar,state in varchar, country in varchar,pinc in varchar,phone in varchar,petname in varchar)is cid number; cnt_na number; begin select count(*) into cid from tb_prmail where num_uid=uid; select count(*) into cnt_na from tb_prmail where vch_uname=uname; if(cid=0)and(cnt_na=0)and(pass=cpass)and(uname is not null)and(name is not null)and(dob is not null)and (addre is not null)and(state is not null)and(country is not null)and(pinc is not null)and(phone is not null)and (petname is not null)then insert into tb_prmail values(uid,uname,pass,cpass,name,dob,addre,state,country,pinc,phone,petname); commit; end if; if(uname is null)or(name is null)or(dob is null)or(addre is null)or(state is null)or(country is null)or(pinc is null) or(phone is null)or(petname is null)and(pass!=cpass)and(cnt_na>=1)and(cid>=1)then raise_application_error(-20003,'dont use null values password and confirm password must be same and user name are id aleady exists') end if; if(pass!=cpass)and(cnt_na>=1)and(cid>=1)then raise_application_error(-20003,'password and confirm password must be same and user name are id aleady exists'); end if; if(cnt_na>=1)and(cid>=1)then raise_application_error(-20003,'user name and user id already exists'); end if; if(cid>=1)then raise_application_error(-20003,'user id already exists'); end if; if(cnt_na>=1)then raise_application_error(-20003,'user name already exists'); end if; if(pass!=cpass)then raise_application_error(-20003,'password and confirm password must be same');

end if; if(uname is null)or(name is null)or(dob is null)or(addre is null)or(state is null)or(country is null) or(pinc is null)or(phone is null)or(petname is null)then raise_application_error(-20003,'all the fields are compulsory so please insert information'); end if; end; /

-------------->insert

create or replace procedure pro_mailins(uid in number,uname in varchar,pass in varchar,cpass in varchar,name in varchar, dob in date,addre in varchar,state in varchar, country in varchar,pinc in varchar,phone in varchar,petname in varchar)is cid number; begin select count(*) into cid from tb_prmail where num_uid=uid; if(cid=0)and(pass=cpass)and(uname is not null)and(name is not null)and(dob is not null)and (addre is not null)and(state is not null)and(country is not null)and(pinc is not null)and(phone is not null)and (petname is not null)then insert into tb_prmail values(uid,uname,pass,cpass,name,dob,addre,state,country,pinc,phone,petname); commit; else raise_application_error(-20003,'please insert correct values'); end if; end;

-------------->update

create or replace trigger tri_t_uni before insert on users1 for each row

declare cnt number; begin select count(*) into cnt from users1 where email=:new.email; if(cnt>=1)then raise_application_error(-20003,'unique constraint violated'); end if; end;

create or replace trigger tri_t_pk before insert on users1 for each row declare cnt number; begin select count(*) into cnt from users1 where user_id=:new.user_id; if(cnt>=1 or :new.user_id is null)then raise_application_error(-20003,'primary constraint violated'); end if; end;