Oracle Stored Procedure - DELETE - Example

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

The following example is for creating a simple delete 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_DELETE_BYPK
       ( 
          p_STUDENT_ID  IN STUDENTS.STUDENT_ID%TYPE 
       )
AS 
BEGIN 

      DELETE 
      FROM   STUDENTS
      WHERE  STUDENT_ID  = p_STUDENT_ID  ; 

      COMMIT ; 

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

END SP_STUDENTS_DELETE_BYPK ; 
/ 

Executing the sql delete stored procedure

Execute sql delete stored procedure

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

P_STUDENT_ID = 25
-------------------------------
P_STUDENT_ID IN  NUMBER 

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

Link to schema for Students table