PL/SQL question

[maswien]

Any thing wrong with following PL/SQL? I kept get compliling error.

create or replace procedure sp_adduser
(pid in contact.person_id%type,
email in contact.e_mail%type,
pass in contact.password%type,
ret out number )
as
begin
declare
cnt number;
begin
select count(*) into cnt from contact;
if cnt > 0 then
ret:=1;
else
insert into contact (person_id, e_mail, password) values(pid, email,pass, sysdate);
ret:=0;
end if

EXCEPTION
ret:=1;
end;
end sp_adduser;

 

[Custom24]

I don't know if there's anything else wrong with it, but I think the part

as
begin
  declare 
    cnt number;
  begin
    select count(*) into cnt from contact;

should just be

as
    cnt number;
begin
    select count(*) into cnt from contact;

Mark

 

[maswien]

Thanks mark,

That makes the code looks much better.

There is another error in the SP, it is:

EXCEPTION
ret:=1;
end;

I changed it to:

EXCEPTION
when PROGRAM_ERROR then
ret:=1;
when others then
ret:=1;


Then the errors are gone, is there any way that I can
handle any exceptions and assign 1 to ret? I mean no need
to specify a error name.

Thanks

 

[carp]

You've already done it (almost)!

EXCEPTION
   WHEN OTHERS THEN
      ret := 1;
END;

 

[fmsousa]

your code shoud stay like this:

CREATE OR REPLACE PROCEDURE sp_adduser
(pid IN  contact.person_id%TYPE,
 email IN contact.e_mail%TYPE,
 pass  IN contact.PASSWORD%TYPE,
 ret OUT NUMBER )
AS

cnt NUMBER;
procedure_error EXCEPTION;

BEGIN
    SELECT COUNT(*) INTO cnt FROM contact;
    IF cnt > 0 THEN 
	   RAISE procedure_error;
    ELSE
       INSERT INTO contact (person_id, e_mail, PASSWORD) VALUES(pid, email,pass, SYSDATE);
       ret:=0;
    END IF;

   EXCEPTION
   WHEN procedure_error THEN
       ret := 1;
   WHEN OTHERS THEN
      ret := 1;

END sp_adduser;

 

[maswien]

Thanks a lot for all these advice, they are really helpful!

Mark