Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Can insert but not update in stored procedure ?

Re: Can insert but not update in stored procedure ?

From: Miroslav Narodetskiy <miroslav_at_earthnet.com>
Date: 1998/02/05
Message-ID: <6bbnh2$k3o@news.earthcom.net>#1/1

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
   BEGIN
      SELECT user_name INTO old_usr_name FROM usrs_users WHERE user_id = userid;
      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
         );

   END;
END sp_insert_user;

>
>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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US