Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Can insert but not update in stored procedure ?
Gerry West wrote in message <01bd2db2$f558d470$847253ce_at_deepwater>...
>I executed it in SQL*PLUS using the following lines:
>
>One to insert:
>EXECUTE sp_insert_user (99, 'West', 'Gerry', 15, '303444-5555', '8888' )'
>Commit;
>Another to update the existing user:
>EXECUTE sp_insert_user (99, 'Gerry', 'West', 11, '1111111111', '1111' )'
>Commit
CREATE OR REPLACE PROCEDURE sp_insert_user(
userid IN NUMBER -- I like change next names. , m_user_name IN VARCHAR , m_password IN VARCHAR , m_security_level IN NUMBER , m_paging_phone_number IN VARCHAR , m_pin_number IN VARCHAR -- You can delete any line any time :-)) AS
UPDATE usrs_users SET user_name = m_user_name , password = m_password , security_level = m_security_level , paging_phone_number = m_paging_phone_number , pin_number = m_pin_number WHERE user_id = userid; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO usrs_users VALUES( userid , m_user_name , m_password , m_security_level , m_paging_phone_number , m_pin_number );
>
>Compiled Procedure:
>
>CREATE OR REPLACE PROCEDURE sp_insert_user( userid IN NUMBER, user_name IN
>VARCHAR, password IN VARCHAR,
> security_level IN NUMBER,
>paging_phone_number IN VARCHAR,
> pin_number IN VARCHAR
> )AS
>BEGIN
> -- Update an existing user
> UPDATE usrs_users
> SET user_name = user_name,
> password = password,
> security_level = security_level,
> paging_phone_number = paging_phone_number,
> pin_number = pin_number
> WHERE user_id = userid;
>
> -- Else insert a new user
> IF SQL%NOTFOUND THEN
> INSERT INTO usrs_users
> VALUES( userid,
> user_name,
> password,
> security_level,
> paging_phone_number,
> pin_number);
> END IF;
>END sp_insert_user;
>/
>
>Thanks in advance,
>Gerry
>
Received on Thu Feb 05 1998 - 00:00:00 CST