Here’s a SELECT INTO 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, uses SELECT INTO mechanism to assign the matched values to OUT parameters.
CREATE OR REPLACE PROCEDURE getDBUSERByUserId(
p_userid IN DBUSER.USER_ID%TYPE,
o_username OUT DBUSER.USERNAME%TYPE,
o_createdby OUT DBUSER.CREATED_BY%TYPE,
o_date OUT DBUSER.CREATED_DATE%TYPE)
IS
BEGIN
SELECT USERNAME , CREATED_BY, CREATED_DATE
INTO o_username, o_createdby, o_date
from DBUSER WHERE USER_ID = p_userid;
END;
/
3. Calls from PL/SQL
Call from PL/SQL like this :
DECLARE
o_username DBUSER.USERNAME%TYPE;
o_createdby DBUSER.CREATED_BY%TYPE;
o_date DBUSER.CREATED_DATE%TYPE;
BEGIN
getDBUSERByUserId(1001,o_username,o_createdby,o_date);
DBMS_OUTPUT.PUT_LINE('username : ' || o_username);
DBMS_OUTPUT.PUT_LINE('createdby : ' || o_createdby);
DBMS_OUTPUT.PUT_LINE('createddate : ' || o_date);
END;
/
Result
Record with userId=1001 is displayed via getDBUSERByUserId store procedure.
uikk
Hi,
While compiling the code i am getting below error.
Error(2,17): PLS-00201: identifier ‘DBUSER.USER_ID’ must be declared.
how to reslove this?
You should have a DBUSER table with field name user_id
after pl/sql successfully completed, how can i view the result for specific empid?
Add “set serveroutput on”
set serveroutput on
DECLARE
o_username DBUSER.USERNAME%TYPE;
o_createdby DBUSER.CREATED_BY%TYPE;
o_date DBUSER.CREATED_DATE%TYPE;
BEGIN
getDBUSERByUserId(1001,o_username,o_createdby,o_date);
DBMS_OUTPUT.PUT_LINE(‘username : ‘ || o_username);
DBMS_OUTPUT.PUT_LINE(‘createdby : ‘ || o_createdby);
DBMS_OUTPUT.PUT_LINE(‘createddate : ‘ || o_date);
END;
Thanks !!!
This helps me too much
Do you have any example returning a ref_cursor and executing it with Codefirst EF6 in C#?
what if we retrieve data from more than one tables by using joins?
here, because user_id is primary key, return 1 row. if return 2 rows, can you help me?
as simple as just split the leg then insert !
Very useful. Thanks!