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