Oracle Stored Procedure - UPDATE - Example

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

The following example is for creating a simple update 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_UPDATE_BYPK
       (
          p_STUDENT_ID  IN STUDENTS.STUDENT_ID%TYPE   ,  
          p_ACTIVE_FLG  IN STUDENTS.ACTIVE_FLG%TYPE                   
       )
AS 
BEGIN 

      UPDATE STUDENTS
      SET 
           ACTIVE_FLG     = p_ACTIVE_FLG                         
      WHERE  STUDENT_ID  = p_STUDENT_ID  ;

      COMMIT ; 

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

END SP_STUDENTS_UPDATE_BYPK ; 
/

It is important to note that for every column you include in the update code you will need to enter data for. Typically, an sql update stored procedure would be created for all the columns. There would be a mapping layer that moves existing data to the exposed variables. In the example provided I have created an update stored procedure that could be tied directly to a function that would activate or inactivate a student. Having an sql update stored procedure for this type of activity is wholly rational and appropriate.

Executing the sql update stored procedure

Execute sql update stored procedure

To run the stored procedure you need to supply a values to the applicable variables.

P_ACTIVE_FLG = 0
P_STUDENT_ID = 22
-------------------------------
P_ACTIVE_FLG IN  NUMBER 
P_STUDENT_ID IN  NUMBER 

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

Link to schema for Students table

If you find this site useful and are a book buyer/reader... You can support my site by clicking on one of the Links below to Abe books By doing this, I will receive a small commision on your purchase and making your purchase will not cost you any more money than if you went directly the site yourself! Abe Books sells books at a fraction of the original cost. I have purchased many books from them originally cost $40.00 for as little as $5 to $10 dollars including shipping costs Click on applicable icon to find books at Abe Books on ...
Oracle 8i and 9i	Oracle 10g	PL/SQL

Abe Books is one of my affiliates. I only use affiliates that I also purchase products from. I love Abe books. If it is printed they likely have a copy. I have also included a generic link to Abe Books below. Once on their site, you will find that they have a nice, easy to use search engine so to find what you are looking for.