Oracle Stored Procedure UPDATE example
Here’s an UPDATE stored procedure example in Oracle database.
1. Table SQL Script
DBUSER table creation script.
CREATE TABLE DBUSER (
USER_ID NUMBER (5) NOT NULL,
USERNAME VARCHAR2 (20) NOT NULL,
CREATED_BY VARCHAR2 (20) NOT NULL,
CREATED_DATE DATE NOT NULL,
PRIMARY KEY ( USER_ID )
)
2. Stored Procedure
A stored procedure, accept 2 IN parameters and update the username field based on the provided userId.
CREATE OR REPLACE PROCEDURE updateDBUSER(
p_userid IN DBUSER.USER_ID%TYPE,
p_username IN DBUSER.USERNAME%TYPE)
IS
BEGIN
UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid;
COMMIT;
END;
/
3. Calls from PL/SQL
Call from PL/SQL like this :
BEGIN
updateDBUSER(1001,'new_mkyong');
END;
Result
The username, which has a userid of 1001 is updated via updateDBUSER store procedure.
About Author
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.
hi could you tell me why or when we use two time begin in plsql .
Can we get updated table name from sql query or through another procedure ?
I know this is very old – maybe someone will see it.
I am using ODA in VB.NET and trying
command = “updateDBUSER(:P1, :P2)”
cmd.Parameters.Add(“P1”, CInt(1))
cmd.Parameters.Add(“P2”, “12345”)
but every time it tells me NOT ALL VARIABLES BOUND. What am I missing?
Hi,
How i can put update query result (0 or 1) into OUT parameter of a procedure.?
Thanks,
Anikesh
Hello, Do you know how i can send a table´s name as parameter in a procedure? I did try so:
create or replace
PROCEDURE test1 (PARAM1 in varchar2) AS
BEGIN
SELECT *
FROM PARAM1;
END;
and it don´t work, may you tell me How I do that work? than you for you time!
Try this:
create or replace
PROCEDURE test1 (PARAM1 in varchar2) AS
BEGIN
EXECUTE IMMEDIATE ‘SELECT * FROM ‘ || PARAM1;
END;