Oracle Stored Procedure - SELECT - Example

Source code to create and add sql select stored procedure to catalog

The following example is for creating a simple select stored procedure. You can run it through an explicit call from a host language program or directly from a DBMS query execution shell like dbOrchestra. 

CREATE OR REPLACE PROCEDURE SP_STUDENTS_SELECT_BYPK
     ( 
        p_STUDENT_ID   IN   STUDENTS.STUDENT_ID%TYPE    ,                  
        p_ACTIVE_FLG           OUT  STUDENTS.ACTIVE_FLG%TYPE    , 
        p_LASTNAME             OUT  STUDENTS.LASTNAME%TYPE      , 
        p_FIRSTNAME            OUT  STUDENTS.FIRSTNAME%TYPE     , 
        p_MARITAL_STATUS_CODE  OUT  STUDENTS.MARITAL_STATUS_CODE%TYPE                
     ) 
AS 
BEGIN  
    SELECT  
           ACTIVE_FLG                    , 
           LASTNAME                      , 
           FIRSTNAME                     , 
           MARITAL_STATUS_CODE           
    INTO   
           p_ACTIVE_FLG                    , 
           p_LASTNAME                      , 
           p_FIRSTNAME                     , 
           p_MARITAL_STATUS_CODE           
    FROM   STUDENTS
    WHERE  STUDENT_ID  = p_STUDENT_ID  ; 

EXCEPTION 
        WHEN OTHERS THEN 
             RAISE_APPLICATION_ERROR (-20001, 
                                      p_STUDENT_ID || ':$:' || SQLERRM, TRUE) ; 

END SP_STUDENTS_SELECT_BYPK ; 
/ 

You will note that just like in a SQL SELECT or a SQL VIEW you do not have to use all of the columns available when creating a stored procedure. Additionally, for this stored procedure I used the primary key of the table as the means of access (the WHERE clause). You can write a stored procedure that uses an alternate key as a means of access. 

Executing the sql select stored procedure

Execute sql insert stored procedure

To run the stored procedure you need to supply a value to the student_id variable. The "WHERE" clause in the code above maps this variable to the student_id column in the table.

P_STUDENT_ID          = 23
-------------------------------
P_MARITAL_STATUS_CODE OUT VARCHAR2 
P_FIRSTNAME           OUT VARCHAR2 
P_LASTNAME            OUT VARCHAR2 
P_ACTIVE_FLG          OUT NUMBER 
P_STUDENT_ID          IN  NUMBER 

#OBJECTTYPE=PROCEDURE#
#OWNER=COURSEREGISTRATION#
#OBJECT=SP_STUDENTS_SELECT_BYPK#
#H2DB-END-OF-PROC#

Stored procedure results

===============================
P_MARITAL_STATUS_CODE = null

===============================
P_FIRSTNAME           = Harley

===============================
P_LASTNAME            = Sazuki

===============================
P_ACTIVE_FLG          = 1

Link to schema for Students table