How to create a procedure in oracle using SQL developer

Need to create a procedure that will perform an update. I attached an example screenshot of what I have attempted with no success. Any help will greatly appreciated.

UPDATE WAREHOUSE_LEVEL SET QTY_MIN = '0', QTY_MAX = '0' WHERE WHS_AUTO_KEY = '31'

Open in new window

Capture.PNG
maximus1974Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Loop is not required in there. Please try like this -

CREATE OR REPLACE Procedure BALOKAD_PROC_WHRSE_MIN_MAX_31
IS
BEGIN
  
   UPDATE WAREHOUSE_LEVEL SET QTY_MIN = '0', QTY_MAX = '0' WHERE WHS_AUTO_KEY = '31';
   COMMIT;
 
END;

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Sample trial for you

--

CREATE TABLE WAREHOUSE_LEVEL 
(
    ID INT  
  , QTY_MIN VARCHAR2(5)
  , QTY_MAX VARCHAR2(5)
  , WHS_AUTO_KEY VARCHAR2(5)
);

INSERT INTO WAREHOUSE_LEVEL VALUES ( 1,'2','1','31' );

CREATE OR REPLACE Procedure BALOKAD_PROC_WHRSE_MIN_MAX_31
IS
BEGIN
  
   UPDATE WAREHOUSE_LEVEL SET QTY_MIN = '0', QTY_MAX = '0' WHERE WHS_AUTO_KEY = '31';
   COMMIT;
 
END;\\

begin
BALOKAD_PROC_WHRSE_MIN_MAX_31;
end;\\

--

Open in new window


Check the updated Data

--

select * from WAREHOUSE_LEVEL\\


--

Open in new window


OUTPUT

--

/*------------------------
OUTPUT
------------------------*/
ID          QTY_MIN     QTY_MAX     WHS_AUTO_KEY
----------- ----------- ----------- ------------
1           0           0           31

--

Open in new window



Read for more details - https://www.mkyong.com/oracle/oracle-stored-procedure-update-example/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.