Oracle Stored Procedure SELECT INTO example

By mkyong | Updated: August 29, 2012

Viewed: 8,792 (+41 pv/w)

Tags: oracle select stored procedure

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.